28th Mar 2017 10 minutes read Statistics in SQL: Calculating Frequencies & Histograms Dejan Sarka calculating statistical queries statistics data analysis data analyst Database and Business Intelligence (BI) developers create huge numbers of reports on a daily basis, and data analyses are an integral part of them. If you wonder whether you can perform statistical analysis in SQL, the answer is ‘yes’. Read my article to learn how to do this! Statistics are very useful as an initial stage of a more in-depth analysis, i.e. for data overview and data quality assessment. However, SQL statistical analysis possibilities are somewhat limited as there are not many statistical functions in SQL Server. . In addition, a good understanding of statistics is not very common among T-SQL practitioners. In SQL Server 2016, you can use R to calculate all kinds of statistical measures, but many SQL Server developers and database administrators do not program in R. And not every site has upgraded to SQL Server 2016. This series will explain the basics of SQL statistical analysis. The code used is based on my real-life experiences. I deal with BI projects, especially with data mining, and I often need to create a lot of statistical queries in the initial stages of a project. During these projects, many times the only software I can rely on is an RDBMS. Optimizing SQL Statistical Queries Optimizing statistical queries is different than optimizing transactional queries. In order to calculate the statistics, the query typically scans all the data. If the query is too slow, you can prepare a random sample of your data and scan that. However, if the queries blindly follow the formulas, they often perform multiple scans of the data. Optimizing such queries means minimizing the number of scans. To achieve this, you must develop an algorithm that uses additional mathematics to convert the formulas to equivalents that can be better optimized in SQL Server or any other RDBMS. You also need to understand SQL in depth. For example, you need a really good understanding of SQL’s window functions and calculations. Besides explaining statistics and statistical queries, this series will also give you some ideas for optimizing statistical and non-statistical queries. Preparing Your Data for SQL Statistical Analysis Before starting the analysis, you need to understand what you are analyzing. In statistics, you analyze cases using their variables. In RDBMS terminology, you can think of a case as a table row and a variable as a column in the same table. For most statistical analyses, you prepare a single table or view. Sometimes it is not so easy to exactly define your case. For example, if you’re performing a credit risk analysis, you might define a family as a case rather than a single customer. When you prepare data for SQL statistical analysis, you have to transform the source data accordingly. For each case, you need to encapsulate all available information in the columns of the table you are going to analyze. Continuous and Discrete Variables Before starting a serious data overview, you must understand how data values are measured in your data set. You might need to check this with a subject matter expert and analyze the business system that is the source for your data. There are several ways to measure data values and different types of columns: Discrete variables can take a value only from a limited domain of possible values. Discrete values include categorical or nominal variables that have no natural order. Examples include states, status codes, and colors. Ranks can also take a value only from a discrete set of values. They have an order but do not permit any arithmetic. Examples include opinion ranks and binned (grouped, discretized) true numeric values. There are also some specific types of categorical variables. Single-valued variables or constants are not very interesting for analysis because they do not contribute any information. Two-valued or dichotomous variables have two values that are minimally needed for any analysis. Binary variables are specific dichotomous variables that take on only the values 0 and 1. Continuous variables can take any of an unlimited number of possible values; however, the domain itself can have a lower and/or upper boundary. Intervals have one or two boundaries, have an order, and allow some arithmetic-like subtraction (but may not always allow summation). Examples include dates, times, and temperatures. True numeric variables support all arithmetic. Examples include amounts and values. Monotonic variables are a specific type of continuous variables which increase monotonously without bound. If they are simply IDs, they might not be interesting. Still, they can be transformed (binned into categories) if the ever-growing ID contains time order information (lower IDs are older than higher IDs). Data Used for SQL Statistical Analyses For this and all upcoming articles, I am using the AdventureWorksDW2014 demo database. You can download a full backup of this database from Microsoft’s SQL Server sample site. I am running all of the code on SQL Server 2016 Developer Edition. I prefer to use the AdventureWorks sample database for SQL Server 2014 instead of SQL Server 2016’s WideWorldImportersDW sample database. The WideWorldImporters database is very useful for demonstrating SQL Server 2016’s new features, but its data is lacking the correlations and associations needed for statistical analysis. Using Frequency Distribution in SQL to Understand Discrete Variables In SQL, frequency distribution (usually presented in the form of a table) is used to get a quick overview of discrete variables. It can show the actual values as well as their: Absolute frequency Absolute percentage Cumulative frequency Cumulative percent Plus, SQL frequency distribution displays a histogram of the values’ absolute percentage. Below, I’ll show you several ways to compute frequency distribution in SQL, starting with one that is fairly inefficient. Frequency Distribution in SQL without Using Window Functions Calculating values’ absolute frequency and absolute percentage is a straightforward aggregation. However, calculating the cumulative frequency and cumulative percentage means calculating running totals. Before SQL Server 2012 added support for window aggregate functions, you had to use either correlated subqueries or non-equi self joins for this task. Neither method is very efficient. Run the following code, which uses correlated subqueries, to analyze in SQL frequency distribution of the NumberCarsOwned variable from the dbo.vTargetMail view in the AdventureWorksDW2014 demo database. USE AdventureWorksDW2014; GO WITH freqCTE AS ( SELECT v.NumberCarsOwned, COUNT(v.NumberCarsOwned) AS AbsFreq, CAST(ROUND(100. * (COUNT(v.NumberCarsOwned)) / (SELECT COUNT(*) FROM vTargetMail), 0) AS INT) AS AbsPerc FROM dbo.vTargetMail AS v GROUP BY v.NumberCarsOwned ) SELECT c1.NumberCarsOwned AS NCars, c1.AbsFreq, (SELECT SUM(c2.AbsFreq) FROM freqCTE AS c2 WHERE c2.NumberCarsOwned <= c1.NumberCarsOwned) AS CumFreq, c1.AbsPerc, (SELECT SUM(c2.AbsPerc) FROM freqCTE AS c2 WHERE c2.NumberCarsOwned <= c1.NumberCarsOwned) AS CumPerc, CAST(REPLICATE('*',c1.AbsPerc) AS varchar(100)) AS Histogram FROM freqCTE AS c1 ORDER BY c1.NumberCarsOwned; This generates the following output: NCars AbsFreq CumFreq AbsPerc CumPerc Histogram ----- ------- ------- ------- ------- ----------------------------------- 0 4238 4238 23 23 *********************** 1 4883 9121 26 49 ************************** 2 6457 15578 35 84 *********************************** 3 1645 17223 9 93 ********* 4 1261 18484 7 100 ******* Frequency Distribution in SQL Using Window Functions – Solution 1 When you perform a SQL statistical analysis, window aggregate functions prove useful. They provide a much better solution. As already noted, these functions are available in SQL Server versions 2012 and later. If you look at the first part of the query, you’ll notice that the Common Table Expression query that calculates the absolute numbers is the same as in the previous query. However, the cumulative values – the running totals – are calculated with the help of window aggregate functions. WITH freqCTE AS ( SELECT v.NumberCarsOwned, COUNT(v.NumberCarsOwned) AS AbsFreq, CAST(ROUND(100. * (COUNT(v.NumberCarsOwned)) / (SELECT COUNT(*) FROM vTargetMail), 0) AS INT) AS AbsPerc FROM dbo.vTargetMail AS v GROUP BY v.NumberCarsOwned ) SELECT NumberCarsOwned, AbsFreq, SUM(AbsFreq) OVER(ORDER BY NumberCarsOwned ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumFreq, AbsPerc, SUM(AbsPerc) OVER(ORDER BY NumberCarsOwned ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumPerc, CAST(REPLICATE('*',AbsPerc) AS VARCHAR(50)) AS Histogram FROM freqCTE ORDER BY NumberCarsOwned; The result of this query is the same as the result of the previous query. Frequency Distribution in SQL Using Window Functions – Solution 2 I found another interesting solution using SQL’s window analytic functions. The CUME_DIST function calculates the cumulative distribution, or relative position, of a value in a group of values. For a row r, assuming ascending ordering, the CUME_DIST of r is the number of rows with values lower than or equal to the value of r, divided by the number of rows evaluated in the partition or query result set. The PERCENT_RANK function calculates the relative rank of a row within a group of rows. We can use PERCENT_RANK to evaluate the relative standing of a value within a query result set or partition. The following SQL statistical analysis query calculates the row number once partitioned over the NumberCarsOwned column and the row number once over all the input set. It also calculates the percent rank and the cumulative distribution over the complete input set. SELECT NumberCarsOwned AS NCars, ROW_NUMBER() OVER(PARTITION BY NumberCarsOwned ORDER BY NumberCarsOwned, CustomerKey) AS Rn_AbsFreq, ROW_NUMBER() OVER( ORDER BY NumberCarsOwned, CustomerKey) AS Rn_CumFreq, PERCENT_RANK() OVER(ORDER BY NumberCarsOwned) AS Pr_AbsPerc, CUME_DIST() OVER(ORDER BY NumberCarsOwned, CustomerKey) AS Cd_CumPerc FROM dbo.vTargetMail; The partial output, showing only those rows pertinent to the explanation of the calculating frequencies algorithm, is: NCars Rn_AbsFreq Rn_CumFre Pr_AbsPerc Cd_CumPerc ----- ---------- --------- ----------------- -------------------- 0 1 1 0 5.4100843973166E-05 0 2 2 0 0.000108201687946332 … … … … … 0 4238 4238 0 0.229279376758277 1 1 4239 0.22929178163718 0.229333477602251 … … … … … 1 4883 9121 0.22929178163718 0.493453797879247 2 1 9122 0.493480495590543 0.49350789872322 … … … … … As you can see, the last row number partitioned by NumberCarsOwned in a category actually represents the absolute frequency of the values in that category. The last un-partitioned row number in a category represents the cumulative frequency up to and including the current category. For example, the absolute frequency for NumberCarsOwned = “0” is 4,238 and the cumulative frequency is 4,238; for NumberCarsOwned = “1”, the absolute frequency is 4,883 and the cumulative frequency is 9,121. Next, consider the CUME_DIST function (the Cd_CumPerc column in the output). CUME_DIST in the last row in a category returns the cumulative percentage up to and including the category. If you subtract the PERCENT_RANK (the Pr_AbsPerc column in the output) for the last row in the category from the CUME_DIST of the last row in the same category, you get the absolute percentage for the category. For example, the absolute percentage for the category where NumberCarsOwned = “1” is more than 26 percent (0.493453797879247 – 0.22929178163718 = 0.264162016242067). The following query calculates the frequencies distribution using observations from the results of the previous query. WITH freqCTE AS ( SELECT NumberCarsOwned, ROW_NUMBER() OVER(PARTITION BY NumberCarsOwned ORDER BY NumberCarsOwned, CustomerKey) AS Rn_AbsFreq, ROW_NUMBER() OVER( ORDER BY NumberCarsOwned, CustomerKey) AS Rn_CumFreq, ROUND(100 * PERCENT_RANK() OVER(ORDER BY NumberCarsOwned), 0) AS Pr_AbsPerc, ROUND(100 * CUME_DIST() OVER(ORDER BY NumberCarsOwned, CustomerKey), 0) AS Cd_CumPerc FROM dbo.vTargetMail ) SELECT NumberCarsOwned AS NCars, MAX(Rn_AbsFreq) AS AbsFreq, MAX(Rn_CumFreq) AS CumFreq, MAX(Cd_CumPerc) - MAX(Pr_Absperc) AS AbsPerc, MAX(Cd_CumPerc) AS CumPerc, CAST(REPLICATE('*',MAX(Cd_CumPerc) - MAX(Pr_Absperc)) AS varchar(100)) AS Histogram FROM freqCTE GROUP BY NumberCarsOwned ORDER BY NumberCarsOwned; Although the idea of this last query is very interesting, this query is not as efficient as the second one (using the window aggregate function). Therefore, the second solution is the recommended one. Conclusion In this article, you learned how to compute SQL frequency distribution for discrete variables. You have also seen a solution that uses some creativity. In the following articles, you will get a grasp of other SQL statistical analysis methods. The next one will be dedicated to calculating basic statistical measures for continuous variables. You will also see how to write efficient queries that call for mathematical knowledge rather than creativity. Tags: calculating statistical queries statistics data analysis data analyst