PostgreSQL query: obtain concise string of numbers

Question

Obtain a concise string of the numbers, for example, given 1,2,3,6,7,8,9,12,14, we expect 1-3,6-9,12,14.

Here is the Table:

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

Use Table tt8, Here is what the result should look like:

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

Here is what I have so far, but it's giving me type error. I don't think this is the correct way.

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;

Gordon Linoff · Accepted Answer · 2020-06-25 21:37:43Z

1

You can use a gaps-and-islands approach and then aggregation. The following gets the groups:

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);

You can then put them into a single string:

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 is a db<>fiddle.

edited Jun 25, 2020 at 21:37

answered Jun 25, 2020 at 19:14

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JackDaniels95 Over a year ago

This is not working for me, I get errors in postgreSQL

Gordon Linoff Over a year ago

@JackDaniels95 . . . I fixed the typos and added a fiddle.

Mike Organek · Accepted Answer · 2020-06-25 20:20:26Z

Please give this a try:

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)

You can change the last query to bring back the results of each CTE to see what is happening.

trans uses the lag() window function to mark rows that start groups:

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

groups uses the sum() window function with the implicit unbounded preceding to assign each row a grpnum:

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

ranges collapses each groupnum to its min() and max():

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

texts translates the low and high ranges into text representations:

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

The string_agg() turns the txt values into a comma-separated list.

Collectives™ on Stack Overflow

PostgreSQL query: obtain concise string of numbers

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related