开发者社区 问答 正文

最佳实践 -SQL Server -解析SQL Server 2012常用的分析函数



分析函数CUME_DIST


微软的定义:
计算某个值在SQL Server 2012中的一组值内的累积分布。CUME_DIST计算某指定值在一组值中的相对位置。对于行r,假定采用升序,r的CUME_DIST是值低于或等于r的值的行数除以在分区或查询结果集中求出的行数。
函数解析:
执行如下代码,构造一组数据。

  1. DECLARE
  2.     @analytic TABLE(
  3.         name varchar(35) ,
  4.         dept varchar(35),
  5.         salary money
  6.     )
  7. INSERT INTO @analytic
  8.         VALUES
  9. --bd
  10. ('andy01','bd',15000),
  11. ('andy02','bd',12000),
  12. ('andy03','bd',12000),
  13. ('andy04','bd',10000),
  14. ('andy05','bd',8000),
  15. --ca
  16. ('andy06','ca',20000),
  17. ('andy07','ca',18000),
  18. ('andy08','ca',18000),
  19. ('andy09','ca',15000),
  20. ('andy10','ca',12000),
  21. ('andy11','ca',12000),
  22. ('andy12','ca',10000),
  23. ('andy13','ca',8000),
  24. ('andy14','ca',8000),
  25. ('andy15','ca',8000)
  26. SELECT
  27.     dept,name ,salary,
  28.     CUME_DIST() OVER(PARTITION BY dept ORDER BY salary)  AS cume_dist_
  29. FROM @analytic
  30. ORDER BY dept,salary DESC

返回结果如下:
[url=http://docs-aliyun.cn-hangzhou.oss.aliyun-inc.com/assets/pic/67532/cn_zh/1519810205856/CUME_DIST%E8%BF%94%E5%9B%9E%E7%BB%93%E6%9E%9C.png][/url]
示例分析:
按照dept分区,根据salary排序,比较当前记录和后一条记录(偏移量为1)的salary值的差值,这个非常实用。

展开
收起
梨好橙 2018-09-09 23:12:27 1337 分享 版权
阿里云 AI 助理回答

分析函数CUME_DIST在数据库查询中扮演着重要的角色,它用于计算一组值内某个特定值的累积分布。具体来说,CUME_DIST函数能够帮助我们了解某个值在排序列表中的相对位置,通过计算小于等于(升序排列)或大于等于(降序排列)当前值的行数与总行数的比例来实现这一功能。

CUME_DIST函数解析

  • 定义:在SQL Server 2012及其它支持此函数的数据库系统中,CUME_DIST函数根据指定的排序标准(通常是ORDER BY子句),为每一行返回一个累积分布值。这个值表示的是,在当前行之前(包括当前行),具有相同或更低(升序)/更高(降序)排序值的行数占分区总行数的比例。

  • 使用场景:该函数常用于数据分析,比如评估员工薪资水平在部门内的分布情况、统计成绩排名的百分位等场景。

  • 示例代码

    DECLARE @analytic TABLE(
    name VARCHAR(35), 
    dept VARCHAR(35), 
    salary MONEY
    );
    
    INSERT INTO @analytic 
    VALUES
    ('andy01','bd',15000),
    ('andy02','bd',12000),
    -- 省略中间插入语句以保持简洁
    ('andy15','ca',8000);
    
    SELECT 
    dept, 
    name, 
    salary,
    CUME_DIST() OVER(PARTITION BY dept ORDER BY salary) AS cume_dist_
    FROM @analytic
    ORDER BY dept, salary DESC;
    
  • 结果解释:执行上述代码后,每一条记录将附加一列cume_dist_,显示该员工的薪水在其所在部门的累积分布比例。例如,如果某员工的累积分布值为66.67%,这意味着在该部门中,有66.67%的员工薪水低于或等于该员工。

注意事项

  • 分区与排序:正确使用PARTITION BYORDER BY子句是关键。PARTITION BY用来定义数据的分组依据,而ORDER BY则决定了累积分布的计算顺序。

  • 升降序影响:根据排序方式的不同,CUME_DIST计算逻辑会有所变化,确保按照实际需求选择正确的排序方向。

应用价值

CUME_DIST不仅能够提供数据集内部的相对位置信息,还能辅助进行更深层次的数据分析,如市场细分、绩效评估、风险分析等领域,是数据科学家和分析师不可或缺的工具之一。

通过上述说明和示例,您应该对CUME_DIST函数有了较为全面的理解,并能将其应用于具体的数据处理任务中。

有帮助
无帮助
AI 助理回答生成答案可能存在不准确,仅供参考
0 条回答
写回答
取消 提交回答