通过Sql实现的组内排序(排名)

编辑
  • 文档创建者:zsh331
  • 浏览次数:9536次
  • 编辑次数:8次
  • 最近更新:Kevin-s 于 2019-06-18
  • 1. 环境

    注意事项:由于 9.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)


    2. 描述

    对于一些特殊的数据表(年报,月报),可能只需要对它某一个时间点(某一年或某一个月)的数据进行分析,此时在你的脑海里肯定会闪现一个词--【组内排序】。设置单元格父格,进行分组扩展就能实现。但我们知道,这种方法是浏览器(客户端)获取到数据,通过决策报表处理再展示的,当我们面对的是大数据时,考虑到效率问题,上面的方法是否是最佳选择?有没有更好的选择方案呢?答案是肯定的!


    3. 思路

    我们可以直接在新建数据集时将其处理成带组内排序字段的结果集,然后将数据交付给浏览器直接展示。大家都知道,数据在服务器端处理肯定比在客户端处理快的多。


    4. 示例

    4.1 准备数据

    -- ---------------------------- -- Table structure for zsh_0220 -- ---------------------------- DROP TABLE [dbo].[zsh_0220] GO CREATE TABLE [dbo].[zsh_0220] ( [t_time] int NULL , [code] int NULL , [name] varchar(255) NULL , [cl] int NULL ) GO -- ---------------------------- -- Records of zsh_0220 -- ---------------------------- INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2015', N'110000', N'北京市', N'7631') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2015', N'120000', N'天津市', N'3861') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2015', N'130000', N'河北省', N'7631') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2015', N'140000', N'山西省', N'6651') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2015', N'150000', N'内蒙古自治区', N'4030') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2015', N'210000', N'辽宁省', N'4448') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2016', N'110000', N'北京市', N'8122') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2016', N'120000', N'天津市', N'3524') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2016', N'130000', N'河北省', N'9006') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2016', N'140000', N'山西省', N'6288') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2016', N'150000', N'内蒙古自治区', N'7787') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2016', N'210000', N'辽宁省', N'6288') GO GO



    4.2 执行(组内)排名 SQL

    SELECT t_time, code, name, CL, row_number () OVER (partition BY t_time ORDER BY cl) AS 组内排名1, --T_time组内,cl排名 row_number () OVER (ORDER BY cl) AS 排名1_1, --所有cl的排名 rank () OVER (partition BY t_time ORDER BY cl) AS 组内排名2, --T_time组内,cl排名 rank () OVER (ORDER BY cl) AS 排名2_1, --所有cl的排名 dense_rank () OVER (partition BY t_time ORDER BY cl) AS 组内排名3, --T_time组内,cl排名 dense_rank () OVER (ORDER BY cl) AS 排名3_1 --所有cl的排名 FROM zsh_0220 ORDER BY t_time,code;

    关键点分析:

    OVER (partition BY t_time ORDER BY cl)

    解释:按 t_time 分组(示例将 t_time 分为 2005,2006 二个组), cl 排序(默认:升序)。降序可设置 ORDER BY cl desc

    函数 ROW_NUMBER() OVER ()  ,RANK() OVER ()RANK(),DENSE_RANK() OVER() 的区别在这就不详细介绍了,可参考:开窗函数-排名


    4.3 预览结果:

    执行上述 SQL 语句,可得到如下效果:


    条件的分析函数有:

    row_number() over(partition by … order by …) 

    rank() over(partition by … order by …) 

    dense_rank() over(partition by … order by …) 

    count() over(partition by … order by …) 

    max() over(partition by … order by …) 

    min() over(partition by … order by …) 

    sum() over(partition by … order by …) 

    avg() over(partition by … order by …) 

    first_value() over(partition by … order by …) 

    last_value() over(partition by … order by …) 

    lag() over(partition by … order by …) 

    lead() over(partition by … order by …) 

    等等…有兴趣的朋友可以试试,这里就不详解了!


    附件列表


    主题: 报表应用
    标签: 暂无标签 编辑/添加标签
    如果您认为本文档还有待完善,请编辑

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

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