|
注意事项：由于 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
|
