PostgreSQL查询:获取简洁的数字字符串

Question

获取一个简洁的数字字符串，例如，给定1,2,3,6,7,8,9,12,14，我们期望1-3,6-9,12,14。

下面是表格：

create table tt8 (c1 numeric);
insert into tt8 values
(1),(2),(3),(6),(7),(8),(9),(12),(14);

使用表tt8，结果应该是这样的：

numbers
---------------
1-3,6-9,12,14

这是到目前为止我所知道的，但它给了我类型错误。我不认为这是正确的方式。

select c1,
case
when c1 = 1|2|3 then '1-3'
when c1 = 6|7|8|9 then '6-9'
else c1
end
from tt8;

Answer 1

您可以使用间隔和孤岛方法，然后进行聚合。下面是获取组的方法：

select min(c1) || (case when count(*) = 1 then '' else '-' || max(c1) end)
from (select tt8.*, row_number() over (order by c1) as seqnum
      from tt8
     ) t
group by (c1 - seqnum);

然后您可以将它们放入单个字符串中：

select string_agg(val, ',' order by min_c1)
from (select min(c1) || (case when count(*) = 1 then '' else '-' || max(c1) end) as val, min(c1) as min_c1
      from (select tt8.*, row_number() over (order by c1) as seqnum
            from tt8
           ) t
      group by (c1 - seqnum)
     ) t;

Here是一个db<>fiddle。

Answer 2

请尝试一下：

with trans as (
  select c1, 
         case when lag(c1) over (order by c1) = c1 - 1 then 0 else 1 end as new
    from tt8
), groups as (
  select c1, sum(new) over (order by c1) as grpnum
    from trans
), ranges as (
  select grpnum, min(c1) as low, max(c1) as high
    from groups
   group by grpnum
), texts as (
  select grpnum, 
         case 
           when low = high then low::text 
           else low::text||'-'||high::text
         end as txt
    from ranges
)
select string_agg(txt, ',' order by grpnum) as answer
  from texts;

    answer     
---------------
 1-3,6-9,12,14
(1 row)

您可以更改最后一个查询以返回每个CTE的结果，以查看发生了什么。

trans使用lag()窗口函数来标记开始分组的行：

 c1 | new 
----+-----
  1 |   1
  2 |   0
  3 |   0
  6 |   1
  7 |   0
  8 |   0
  9 |   0
 12 |   1
 14 |   1
(9 rows)

groups使用带有隐式unbounded preceding的sum()窗口函数为每行分配一个grpnum

 c1 | grpnum 
----+--------
  1 |      1
  2 |      1
  3 |      1
  6 |      2
  7 |      2
  8 |      2
  9 |      2
 12 |      3
 14 |      4
(9 rows)

ranges将每个groupnum折叠为其min()和max()

 grpnum | low | high 
--------+-----+------
      3 |  12 |   12
      4 |  14 |   14
      2 |   6 |    9
      1 |   1 |    3
(4 rows)

texts将low和high范围转换为文本表示形式：

 grpnum | txt 
--------+-----
      3 | 12
      4 | 14
      2 | 6-9
      1 | 1-3
(4 rows)

string_agg()将txt值转换为逗号分隔的列表。