PostgreSQL unnest（）使用连续整数编号

问题描述：

给定一个独特的整数有序数组，我希望unnest数组并为每批连续整数指派一个组编号。PostgreSQL unnest（）使用连续整数编号

例如：{2,3,5,7,8,9,10,20,21,25}应该unnesting获得当前和以前行的值之差后返回

elem | group_nr 
-----+--------- 
    2 | 1 
    3 | 1 
    5 | 2 
    7 | 3 
    8 | 3 
    9 | 3 
    10 | 3 
    20 | 4 
    21 | 4 
    25 | 5

答

使用window functionslag()和sum()：

with the_data(arr) as (
values (array[2,3,5,7,8,9,10,20,21,25]) 
) 

select elem, sum(diff) over w as group_nr 
from (
    select elem, (elem- 1 is distinct from lag(elem) over w)::int as diff 
    from the_data, unnest(arr) as elem 
    window w as (order by elem) 
    ) s 
window w as (order by elem); 

elem | group_nr 
------+---------- 
    2 |  1 
    3 |  1 
    5 |  2 
    7 |  3 
    8 |  3 
    9 |  3 
    10 |  3 
    20 |  4 
    21 |  4 
    25 |  5 
(10 rows)

谢谢。我曾看过lag（）和sum（），但是不知道如何将它们放在一起。我需要更好地理解使用窗口函数。这将被用作与数组作为输入较大的功能的一部分，所以我已经简化一个位（在脑海中，反正）到： – Derek

WITH tmp_diff AS（ \t SELECT \t ELEM，（ELEM - 1 IS DISTINCT FROM lag（elem）OVER（ORDER BY elem））:: INTEGER AS diff FROM unnest（ARRAY [2,3,5,7,8,9,10,20,21,25]）AS elem ） SELECT \t elem， sum（差异）OVER（ORDER BY elem）AS group_nr FROM tmp_diff; – Derek

当然没关系，但是我更喜欢FROM语句中的派生表（FROM子句中的子查询）。在许多情况下，计划者为第一个变体生成更好的计划。请参阅[SqlFiddle。]（http://sqlfiddle.com/#!17/9eecb/4933） – klin

答

使用lag然后使用运行总和来分配组号码。

select id,num,sum(col) over(partition by id order by num) as grp 
from (select id,num,case when num-lag(num,1,0) over(partition by id order by num)=1 then 0 else 1 end as col 
     from (select id,unnest(array_column) as num from tbl) t 
    ) t

逻辑类似于我接受了上述的答案，但我不能看到其中 “TBL” coems。 – Derek

'tbl'是包含数据的表名。我认为你需要为表中的几行做这件事。因此一个'id'列。 –

PostgreSQL unnest（）使用连续整数编号

相关推荐