row_number 函数的不稳定性

2023-09-05
广东
本文字数：2718 字
阅读完需：约 9 分钟

本文分享自华为云社区《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 SEQUENCEpostgres=# 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)