本文分享自华为云社区《row_number函数的不稳定性》，作者： nullptr_ 。
row_number为窗口函数，用来为各组内数据生成连续排号
基础用法
postgres=# select id,name,age,row_number() over() from test; id | name | age | row_number ----+------+-----+------------ 2 | 张三 | 13 | 1 3 | 张四 | 16 | 2 3 | 张三 | 14 | 3 1 | 张三 | 12 | 4 3 | 张四 | 15 | 5 (5 rows)
postgres=# select id,name,age,row_number() over(order by age) from test; id | name | age | row_number ----+------+-----+------------ 3 | 张四 | 16 | 5 3 | 张三 | 14 | 3 3 | 张四 | 15 | 4 1 | 张三 | 12 | 1 2 | 张三 | 13 | 2 (5 rows)
postgres=# select id,name,age,row_number() over(partition by name order by age) from test; id | name | age | row_number ----+------+-----+------------ 1 | 张三 | 12 | 1 2 | 张三 | 13 | 2 3 | 张三 | 14 | 3 3 | 张四 | 15 | 1 3 | 张四 | 16 | 2 (5 rows)
特殊用法
postgres=# select * from test1 where id in (1,2,4); id | info ----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 2 | 张三,12;张三,13;张三,14 1 | 张四,13;张四,16;张四,17 4 | 张十一,14;张十一,13;张十一,16;张十一,146;张十一,136;张十一,166;张十一,46;张十一,36;张十一,66;张十一,246;张十一,236;张十一,26;张十一,36;张十一,76;张十一,17;张十一,13;张十一,15;张十一,19;张十一,10;张十一,6;张十一,966 (3 rows)
需求：将表中info字段转成行，并按照存储顺序生成组内序号。
将字段info 通过函数regexp_split_to_table用分号作为分隔符转成行，通过split_part将各行分段输出
postgres=# select split_part(c.a,',',1) as name, split_part(c.a,',',2) as age from (select regexp_split_to_table(info,';') as a from test1 where id in (1,2,4)) c; name | age --------+----- 张十一 | 14 张十一 | 13 张十一 | 16 张十一 | 146 张十一 | 136 张十一 | 166 张十一 | 46 张十一 | 36 张十一 | 66 张十一 | 246 张十一 | 236 张十一 | 26 张十一 | 36 张十一 | 76 张十一 | 17 张十一 | 13 张十一 | 15 张十一 | 19 张十一 | 10 张十一 | 6 张十一 | 966 张四 | 13 张四 | 16 张四 | 17 张三 | 12 张三 | 13 张三 | 14 (27 rows)
增加组内行号
postgres=# select split_part(c.a,',',1) as name, split_part(c.a,',',2) as age,row_number() over (partition by name) from (select regexp_split_to_table(info,';') as a from test1 where id in (1,2,4)) c; name | age | row_number --------+-----+------------ 张三 | 12 | 1 张三 | 13 | 2 张三 | 14 | 3 张十一 | 966 | 1 张十一 | 19 | 2 张十一 | 10 | 3 张十一 | 6 | 4 张十一 | 14 | 5 张十一 | 13 | 6 张十一 | 16 | 7 张十一 | 146 | 8 张十一 | 136 | 9 张十一 | 166 | 10 张十一 | 46 | 11 张十一 | 36 | 12 张十一 | 66 | 13 张十一 | 246 | 14 张十一 | 236 | 15 张十一 | 26 | 16 张十一 | 36 | 17 张十一 | 76 | 18 张十一 | 17 | 19 张十一 | 13 | 20 张十一 | 15 | 21 张四 | 16 | 1 张四 | 17 | 2 张四 | 13 | 3 (27 rows)
可见行号并非按照存储顺序进行赋值，使用sequence进行优化可实现相关需求
postgres=# create sequence test_seq;
CREATE SEQUENCE
postgres=# select split_part(c.a,',',1) as name, split_part(c.a,',',2) as age,row_number() over (partition by name order by c.line) from (select nextval('test_seq') as line, regexp_split_to_table(info,';') as a from test1 where id in (1,2,4)) c;
name | age | row_number
--------+-----+------------
张三 | 12 | 1
张三 | 13 | 2
张三 | 14 | 3
张十一 | 14 | 1
张十一 | 13 | 2
张十一 | 16 | 3
张十一 | 146 | 4
张十一 | 136 | 5
张十一 | 166 | 6
张十一 | 46 | 7
张十一 | 36 | 8
张十一 | 66 | 9
张十一 | 246 | 10
张十一 | 236 | 11
张十一 | 26 | 12
张十一 | 36 | 13
张十一 | 76 | 14
张十一 | 17 | 15
张十一 | 13 | 16
张十一 | 15 | 17
张十一 | 19 | 18
张十一 | 10 | 19
张十一 | 6 | 20
张十一 | 966 | 21
张四 | 13 | 1
张四 | 16 | 2
张四 | 17 | 3
(27 rows)
总结
row_number的排序是不稳定的，所以不会按照初始顺序进行输出，需要人为给定顺序。
号外！
华为将于2023年9月20-22日，在上海世博展览馆和上海世博中心举办第八届华为全联接大会（HUAWEICONNECT 2023）。本次大会以“加速行业智能化”为主题，邀请思想领袖、商业精英、技术专家、合作伙伴、开发者等业界同仁，从商业、产业、生态等方面探讨如何加速行业智能化。
我们诚邀您莅临现场，分享智能化的机遇和挑战，共商智能化的关键举措，体验智能化技术的创新和应用。您可以：
- 在100+场主题演讲、峰会、论坛中，碰撞加速行业智能化的观点
- 参观17000平米展区，近距离感受智能化技术在行业中的创新和应用
- 与技术专家面对面交流，了解最新的解决方案、开发工具并动手实践
- 与客户和伙伴共寻商机
感谢您一如既往的支持和信赖，我们热忱期待与您在上海见面。
大会官网：https://www.huawei.com/cn/events/huaweiconnect
欢迎关注“华为云开发者联盟”公众号，获取大会议程、精彩活动和前沿干货。
点击关注，第一时间了解华为云新鲜技术~
