分析函数-CUME_DIST()

编辑
  • 文档创建者:zsh331
  • 浏览次数:2545次
  • 编辑次数:8次
  • 最近更新:Kevin-s 于 2019-06-18
  • 注意事项:由于 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:

    SELECTpart, 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 BYpart, 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

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