分析函数-CUME_DIST()

编辑
  • 文档创建者:zsh331
  • 浏览次数:2077次
  • 编辑次数:7次
  • 最近更新:jiangsr 于 2018-07-25
  •        注意事项:由于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


    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  

    结果预览与分析:


    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

    此页面有帮助吗?只是浏览 [ 去社区提问 ]