PostgreSQL对所有分组进行组排序并对每组组内数据进行排序
需求:1. 数据库表中有两个字段,分别是id_card(身份证号码)和create_date(创建时间)2. 表中相同的id_card数据有多条,其对应的create_date大小也不同3. 我希望id_card相同的数据相邻,并且通过create_date把id_card相同的数据按照时间升序排列4.再根据每个id_card相同的组中对应的最小create_date将id_card组进行升序排列
数据库环境:PostgreSQL 9.5.22
需求说明:
1. 数据库表中有两个字段,分别是id_card(身份证号码)和create_date(创建时间)
2. 表中相同的id_card数据有多条,其对应的create_date大小也不同
3. 要求id_card相同的数据相邻,并且通过create_date把id_card相同的数据按照时间升序排列
4.再根据每个id_card相同的组中对应的最小create_date将id_card组进行升序排列
5.再将每个id_card相同的组中,组内按照create_date升序排列
初始数据是这样的:
最终希望的结果如下:
分析:
实现步骤3,只需要通过多条件排序就可以得到,如下:
SELECT
receive_idcard,create_date
FROM
table_name
ORDER BY
receive_idcard ASC, create_date ASC
结果:
现在得到的结果就是相同id_card彼此相邻,并且相同id_card的数据按照create_date升序排列
但是这个结果并不满足需求,我希望在此结果集的基础上按照"相同id_card的数据所组成的组"中的最小create_date将不同的id_card组进行升序排列,每个组内再根据create_date进行升序排列。
这样就需要用到PgSQL的【窗口函数】
窗口函数的语法 :
窗口函数名([expr]) over(partition by <列名清单 > order by <排序列清单>)
1. over : 表示窗口函数关键字
2. partition by : 表示对查询返回的结果集进行分组
3. order by : 表示对查询返回的结果集分组后的数据进行组内排序
可以作为窗口函数的函数分类 :
1. 聚合函数 : SUM、MAX、MIN、AVG、COUNT
2.内置函数 : RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数
现在,使用窗口函数来完成第4、5步骤
1. 先通过窗口函数获取分组后每组id_card中最小create_date即min_date。
说明:【其中PARTITION BY receive_idcard根据id_card对结果集进行了分组;ORDER BY create_date ASC 对分组后的数据,组内按照create_date升序排列;MIN(create_date)获取 了分组数据内最小create_date值】
SELECT
receive_idcard,
create_date,
MIN(create_date) OVER(PARTITION BY receive_idcard ORDER BY create_date ASC) AS min_date
FROM
table_name
结果:
获取了id_card相同的数据组的最小create_date即min_date,组内create_date也是升序排列的
2. 在上面结果集的基础上通过min_date进行升序排列
说明:此时相同id_card数据的min_date相同,所以根据min_date排序后id_card相同的数据也还是相邻的
SELECT
receive_idcard,
create_date,
MIN(create_date) OVER(PARTITION BY receive_idcard ORDER BY create_date ASC) AS min_date
FROM
table_name
ORDER BY
min_date ASC
最终结果:
说明:现在获得的结果集按照min_date进行了升序排列,满足了全部需求
扩展:
窗口函数又叫分析函数。
MySQL 8.0提供了SQL窗口功能。与分组聚合函数类似,窗口函数对一组行进行一些计算,例如COUNT或SUM。但是,分组聚合将这组行聚合到一行中,而窗口函数将为结果集中的每一行执行聚合。
窗口函数有两种形式:用作窗口函数和专用窗口函数的SQL聚合函数。这是MySQL中支持窗口化的聚合函数集合:COUNT,SUM,AVG,MIN,MAX,BIT_OR,BIT_AND,BIT_XOR,STDDEV_POP(及其同义词STD,STDDEV),STDDEV_SAMP,VAR_POP(及其同义词VARIANCE)和VAR_SAMP。专门的窗口函数是:RANK,DENSE_RANK,PERCENT_RANK,CUME_DIST,NTILE,ROW_NUMBER,FIRST_VALUE,LAST_VALUE,NTH_VALUE,LEAD和LAG。
引用:
什么叫窗口?
窗口的概念非常重要,它可以理解为记录集合,窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数,有的函数随着记录不同,窗口大小都是固定的,这种属于静态窗口;有的函数则相反,不同的记录对应着不同的窗口,这种动态变化的窗口叫滑动窗口。
窗口函数的基本用法如下:
函数名([expr]) over子句
其中,over是关键字,用来指定函数执行的窗口范围,如果后面括号中什么都不写,则意味着窗口包含满足where条件的所有行,窗口函数基于所有行进行计算;如果不为空,则支持以下四种语法来设置窗口:
partition子句:窗口按照那些字段进行分组,窗口函数在不同的分组上分别执行。
order by子句:按照哪些字段进行排序,窗口函数将按照排序后的记录顺序进行编号。可以和partition子句配合使用,也可以单独使用。
frame子句:frame是当前分区的一个子集,子句用来定义子集的规则,通常用来作为滑动窗口使用。
按照功能划分,可以把MySQL支持的窗口函数分为如下几类:
1.序号函数:row_number() / rank() / dense_rank()
2.分布函数:percent_rank() / cume_dist()
3.前后函数:lag() / lead()
4.头尾函数:first_val() / last_val()
5.其他函数:nth_value() / nfile()
6.聚合函数作为窗口函数
————————————————
版权声明:本文为CSDN博主「Ch3n」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/weixin_43064185/article/details/108768609
个人理解:窗口类似之前【group by field】分的组,分的每个组都是一个窗口;窗口函数是由partition by 进行分组,over关键字前面的窗口函数(如:MIN(create_date))对分组组内的数据进行操作 。之前的分组聚合函数是将一组结果聚合为一条;而窗口函数是将结果聚合到了组中的每一条记录上,之前有几条记录,窗口函数执行完还是几条记录。
参考:
postgreSQL函数之《神奇的ROW_NUMBER() OVER(...) 》
mysql实现ROW_NUMBER() over (PARTITION BY xx ORDER BY ** DESC)-★★★★
PostgreSQL_row_number() over()
更多推荐
所有评论(0)