SQL Server 2012 introduced a new analytical function called CUME_DIST(). This function provides a cumulative distribution value, which can be very useful in various scenarios. In this blog post, we will explore the CUME_DIST() function and its applications.
Before we dive into the details, let’s start with a simple example using the AdventureWorks sample database. We will use the Sales.SalesOrderDetail table to demonstrate the CUME_DIST() function.
USE AdventureWorks;
SELECT SalesOrderID, OrderQty, CUME_DIST() OVER (ORDER BY SalesOrderID) AS CDist
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY CDist DESC;The above query will give us the following result:
| SalesOrderID | OrderQty | CDist |
|---|---|---|
| 43670 | 3 | 1 |
| 43669 | 2 | 0.75 |
| 43667 | 1 | 0.5 |
| 43663 | 4 | 0.25 |
Now, let’s understand the formula behind CUME_DIST() and why the values for SalesOrderID = 43670 are 1. The CUME_DIST() function calculates the cumulative distribution of a value within a group of values. In this case, the SalesOrderID values are ordered, and the CUME_DIST() function assigns a value between 0 and 1 based on the position of each SalesOrderID in the ordered sequence. The highest SalesOrderID (43670) receives a value of 1, while the lowest SalesOrderID (43663) receives a value of 0.25.
To further enhance our understanding, let’s use the PARTITION BY clause in the OVER clause and see the results:
USE AdventureWorks;
SELECT SalesOrderID, OrderQty, ProductID, CUME_DIST() OVER (PARTITION BY SalesOrderID ORDER BY ProductID) AS CDist
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY SalesOrderID DESC, CDist DESC;The result of this query will provide us with different results. By partitioning the data by SalesOrderID, we can see that the CUME_DIST() function now provides us with the cumulative distribution value for each group of SalesOrderID. This allows us to analyze the distribution of products within each sales order.
The CUME_DIST() function was a long-awaited addition to SQL Server’s analytical functions, and its introduction in SQL Server 2012 has been highly appreciated by developers and analysts alike.
Stay tuned for more articles on SQL Server 2012 analytic functions as we explore other powerful functions that can help you gain deeper insights into your data.