分析函数-CUME_DIST()

编辑
文档创建者:zsh331 (73316 )     浏览次数:1603次     编辑次数:6次     最近更新:zsh331 于 2017-06-29     

       注意事项:由于8.0设计器内置的是sqlite数据库,在使用上很多语法都不支持,请将sqlite数据库文件迁移到主流数据库(MySql、MSSQL、Oracle…)后运行。

       迁移方法:使用第三方软件迁移内置FRDemo数据库到指定数据库

本文运行环境:Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 

                       Copyright (c) Microsoft Corporation

                       Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

目录:

1. 问题描述编辑

有时候,一些特殊的数据分析,我们很难通过报表单元格界面设计出来!比如我手里有一张企业的薪资表,现在想分析某个人薪资在本部门或全公司处于一个什么水平,就很难实现。在本部门,比他薪资高的人占多少?比他薪资低的又占多少?在本部门……等等。


2. 实现思路编辑

现在很多数据库都有自己的分析函数,通过指定的函数我们能很容易的得出想要的结果,比如CUME_DIST()函数!


3. 函数介绍编辑

分析函数CUME_DIST():–CUME_DIST 小于等于当前值的行数/分组内总行数  
语法:CUME_DIST( )   OVER ( [ partition_by_clause] order_by_clause )    
解释:通过 partition_by_clause 将划分为分区函数应用到的 FROM 子句生成的结果集。 如果未指定,则此函数将查询结果集的所有行视为单个组。 order_by_clause 确定在其中执行该操作的逻辑顺序。 order_by_clause 是必需的。  
返回类型:CUME_DIST 返回的值范围大于 0 并小于或等于 1的数值。 

4. 示例编辑

4.1 准备数据

使用以下SQL构建表:

-- ---------------------------- -- Table structure for ZSH_170222 -- ---------------------------- DROP TABLE [dbo].[ZSH_170222] GO CREATE TABLE [dbo].[ZSH_170222] ( [PART] varchar(255) NULL , [NAME_C] varchar(255) NULL , [PAY] int NULL ) GO -- ---------------------------- -- Records of ZSH_170222 -- ---------------------------- INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'技术部', N'小明', N'9741') GO GO INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'技术部', N'小兰', N'6908') GO GO INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'技术部', N'李东', N'6336') GO GO INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'技术部', N'杨澜', N'9089') GO GO INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'技术部', N'黄伟', N'1646') GO GO INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'技术部', N'赵丽', N'4486') GO GO INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'技术部', N'张军', N'3538') GO GO INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'刘伟', N'2143') GO GO INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'张强', N'6522') GO GO INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'黄渤', N'1247') GO GO INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'赵丽', N'7975') GO GO INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'刘东', N'2990') GO GO INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'张伟', N'4266') GO GO INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'黄俊', N'4815') GO GO INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'黄伟', N'7788') GO GO INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'刘康', N'4605') GO GO INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'刘冰', N'6184') GO GO

222

4.2 使用分析函数进行分析

执行sql:

SELECT part, name_c, pay, CUME_DIST () OVER (ORDER BY pay) AS cat_1, CUME_DIST () OVER (PARTITION BY part ORDER BY pay) AS cat_2 FROM ZSH_170222 ORDER BY part, pay  

结果预览与分析:

222

Cat_1: 没有PARTITION BY ,在整个公司里分析

所有数据均为1组,总行数为17  

第一行(黄伟):小于等于1646的行数为2,因此,2/17= 0.117647058823529  

第二行(张军):小于等于3538的行数为5,因此,5/17= 0.294117647058824  

第三行(赵丽):小于等于3538的行数为7,因此,7/17= 0.411764705882353  

…  

第十七行(赵丽君):小于等于7975的行数为15,因此,15/17= 0.882352941176471  

  

Cat_2: 按照部门(技术部/综合部)分析

技术组的行数为7,  

第一行(黄伟):小于等于1646的行数为1,因此,1/7= 0.142857142857143  

第二行(张军):小于等于3538的行数为2,因此,2/7= 0.285714285714286  

…  

第七行(小明):小于等于9741的行数为7,因此,7/7= 1  

综合部的行数为10,  

第一行(黄渤):小于等于1247的行数为1,因此,1/10= 0.1  

第二行(刘伟):小于等于2142的行数为2,因此,2/10= 0.2  

…  

第十行(赵丽君):小于等于7975的行数为10,因此,10/10= 1   

附件列表


主题: 专题总结
如果您认为本文档还有待完善,请编辑

文档内容仅供参考,如果你需要获取更多帮助,请咨询帆软技术支持
关于技术问题,您还可以前往帆软社区,点击顶部搜索框旁边的提问按钮
若您还有其他非技术类问题,可以联系帆软传说哥(qq:1745114201

此页面有帮助吗?