SQL CUME_DIST

SQL Server CUME_DIST 是一种分析函数，用于计算分区或整个行集中的行累积分布。CUME_DIST 的基本语法如下所示。

SELECT CUME_DIST() 
           OVER ( PARTITION_BY_Clause 
                  ORDER_BY_Clause
                )
FROM [Source]
  • Partition_By_Clause：它将 SELECT 语句选择的记录划分为分区。
    • 如果指定了 Partition_By_Clause，则 CUME_DIST 函数将计算每个分区中行的累积分布。
    • 如果未指定 Partition_By_Clause，则所有行将被视为一个分区。
  • Order_By_Clause：此 Server 子句用于将分区数据按指定顺序排序。请参阅 Order By Clause。

我们将使用下面显示的数据来演示 CUME_DIST。

Source Table 0

不带 Partition By 子句的 SQL CUME_DIST

在此示例中，我们展示了如果在 CUME_DIST 函数中省略 Partition By 子句会发生什么。

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,CUME_DIST() OVER (ORDER BY [Sales] ASC) AS Cumulative 
  FROM [Employee]
CUME_DIST Example 2

让我将 SQL Server CUME_DIST 的 Order by 子句从升序更改为降序。

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,CUME_DIST() OVER (ORDER BY [Sales] ASC) AS Cumulative 
  FROM [Employee]
cumulative distribution 3

带 Partition By 的 CUME_DIST 示例

此示例说明了如何返回表中分区记录的 SQL Server 累积分布。以下查询将根据其销售额按职业对数据进行分类。然后，为每个类别单独计算累积分布。

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,CUME_DIST() OVER ( PARTITION BY [Occupation]
			  ORDER BY [Sales] ASC
			) AS Cumulative
  FROM [Employee]
CUME_DIST 4