row_number函数的不稳定性

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的排序是不稳定的，所以不会按照初始顺序进行输出，需要人为给定顺序。