Dejan Sarka - Exploring Hidden Patterns and Rules in Your Data

This Apress imprint is published by the registered company APress Media, LLC part of Springer Nature.

The registered company address is: 1 New York Plaza, New York, NY 10004, U.S.A.

If you want to learn how to get information from your data with Transact-SQL, or the T-SQL language, this book is for you. It teaches you how to calculate statistical measures from descriptive statistics, including centers, spreads, skewness, and the kurtosis of a distribution, find the associations between pairs of variables, including calculating the linear regression formula, calculate the confidence level with definite integration, find the amount of information in your variables, and also do some machine learning or data science analysis, including predictive modeling and text mining.

The T-SQL language is in the latest editions of SQL Server, Azure SQL Database, and Azure Synapse Analytics. It has so many business intelligence (BI) improvements that it might become your primary analytic database system. Many database developers and administrators are already proficient with T-SQL. Occasionally they need to analyze the data with statistical or data science methods, but they do not want to or have time to learn a completely new language for these tasks. In addition, they need to analyze huge amounts of data, where specialized languages like R and Python might not be fast enough. SQL Server has been optimized for work with big datasets for decades.

To get the maximum out of these language constructs, you need to learn how to use them properly. This in-depth book shows extremely efficient statistical queries that use the window functions and are optimized through algorithms that use mathematical knowledge and creativity. The formulas and usage of those statistical procedures are explained as well.

Any serious analysis starts with data preparation. This book introduces some common data preparation tasks and shows how to implement them in T-SQL.

No analysis is good without good data quality. The book introduces data quality issues and shows how you can check for completeness and accuracy with T-SQL and measure improvements in data quality over time. It also shows how you can optimize queries with temporal data; for example, when you search for overlapping intervals. More advanced time-oriented information includes hazard and survival analysis.

Next, the book turns to data science. Some advanced algorithms can be implemented in T-SQL. You learn about the market basket analysis with association rules using different measures like support and confidence, and sequential market basket analysis when there is a sequence in the basket. Then the book shows how to develop predictive models with a mixture of k-nearest neighbor and decision tree algorithms and Bayesian inference analysis.

Analyzing text, or text mining, is a popular topic. You can do a lot of text mining in pure T-SQL, and SQL Server can become a text mining engine. The book explains how to analyze text in multiple natural languages with pure T-SQL and features from full-text search (FTS).

Advanced Analytics with Transact-SQL is for database developers and database administrators who want to take their T-SQL programming skills to the max. It is for those who want to efficiently analyze huge amounts of data by using their existing knowledge of the T-SQL language. It is also for those who want to improve querying by learning new and original optimization techniques.

This book assumes that the reader already has good knowledge of the Transact-SQL language. A few years of coding experience is very welcome. A basic grasp of performance tuning and query optimization can help you better understand how the code works.

There are eight chapters in this book, which are logically structured in four parts, each part with two chapters. The following is a brief description of the chapters.

Part I: Statistics Most advanced analytics starts with good old statistics. Sometimes statistical analysis might already provide the needed information, and sometimes statistics is only used in an overview of the data.

Chapter: Descriptive Statistics With descriptive statistics, the analyst gets an understanding of the distribution of a variable. One can analyze either continuous or discrete variables. Depending on the variable type, the analyst must choose the appropriate statistical measures.

Chapter: Associations Between Pairs of Variables When measuring associations between pairs of variables, there are three possibilities: both variables are continuous, both are discrete, or one is continuous and the other one is discrete. Based on the type of the variables, different measures of associations can be calculated. To calculate the statistical significance of associations, the calculation of the definite integrals is needed.

Part II: Data Quality and Preparation Before doing advanced analyses, it is crucial to understand the quality of the input data. A lot of additional work with appropriate data preparation is usually a big part of an analytics project in real life.