2

I have a table with categorical values in a sequence, as such:

CREATE TABLE cat (name, v1, v2)
AS VALUES
    ('John', 1::int, 3::int),
    ('John', 3, 4),
    ('John', 4, 9),
    ('Mike', 9, 11),
    ('Mike', 11, 17),
    ('John', 17, 24),
    ('John', 24, 25),
    ('Dave', 25, 29);

And I want to group them by the name column, getting the minimum value_1 and maximum value_2, however group them as the names appear in unbroken sequence, as such:

+-----------+---------+---------+
| name      | value_1 | value_2 |
+-----------+---------+---------+
| John      | 1       | 9       |
| Mike      | 9       | 17      |
| John      | 17      | 25      |
| Dave      | 25      | 29      |
+-----------+---------+---------+

However, doing normal GROUP BY selection returns all the entries for a particular name together, regardless of whether they appear together or not:

SELECT name, MIN(value_1), MAX(value_2)
FROM table
GROUP BY name;

+-----------+---------+---------+
| name      | value_1 | value_2 |
+-----------+---------+---------+
| John      | 1       | 25      |
| Mike      | 9       | 17      |
| Dave      | 25      | 29      |
+-----------+---------+---------+

Now, I could probably do this with plpgsql, using traditional programming logic with variables, loops and such, but I was wondering if there is a more SQL-flavoured approach to this.

Software: PostgreSQL 9.6.3

  • Is there any other field to order this records? like serial ID? – McNets Feb 5 '18 at 19:45
  • @McNets not really, but an id column could be constructed, given that the data in value_1 and value_2 are sequential and non-repeating (one could order by value_1 and would get the correct sequence). – Roberto Ribeiro Feb 5 '18 at 19:53
  • OK, let me try by using v1 – McNets Feb 5 '18 at 19:58
  • What do you want to happen if there is a (dave, 31, 35) row – Evan Carroll Feb 6 '18 at 0:03
3

Using value_1 to order the row, you can make groups every time name changes.

Then use this groups to get max and min values.

select name, min(v1) v1, max(v2) v2
from (select name, v1, v2,
             sum(rst) over (order by v1) grp
      from (select name, v1, v2,
                   case when coalesce(lag(name) over (order by v1), '') <> name then 1 end rst
            from cat
            ) y
      ) z
group by name, grp
order by v1;
name | v1 | v2
:--- | -: | -:
John |  1 |  9
Mike |  9 | 17
John | 17 | 25
Dave | 25 | 29

dbfiddle here

  • i'm glad to help. – McNets Feb 6 '18 at 11:56
2

I read this different. I assume ranges are chained. For instance

bob, 2, 5
-- no bob 5,7
bob, 7,  10
bob, 11, 12

Should result in two distinct ranges. For that there is one question, does your schema permit overlapping ranges? If not, my assumption, this should work. Warning, this won't work if you can have a range like bob, 0, 10 in the above,

Here we calculate resets with a lag over the other column.

SELECT name, v1, v2, COALESCE(v1<>lag(v2) OVER (
  PARTITION BY name
  ORDER BY v1, v2
), true) AS rst
FROM cat;
 name | v1 | v2 | rst 
------+----+----+-----
 Dave | 25 | 29 | t
 John |  1 |  3 | t
 John |  3 |  4 | f
 John |  4 |  9 | f
 John | 17 | 24 | t
 John | 24 | 25 | f
 Mike |  9 | 11 | t
 Mike | 11 | 17 | f
(8 rows)

Then we count to get group (grp),

SELECT name, v1, v2, count(rst OR null) OVER (ORDER BY name, v1, v2) AS grp
FROM (
        SELECT name, v1, v2, COALESCE(v1<>lag(v2) OVER (
                PARTITION BY name
                ORDER BY v1, v2
        ), true) AS rst
        FROM cat
) AS t;
 name | v1 | v2 | grp 
------+----+----+-----
 Dave | 25 | 29 |   1
 John |  1 |  3 |   2
 John |  3 |  4 |   2
 John |  4 |  9 |   2
 John | 17 | 24 |   3
 John | 24 | 25 |   3
 Mike |  9 | 11 |   4
 Mike | 11 | 17 |   4
(8 rows)

Then we group by our grp column to finish it off.

SELECT name, min(v1), max(v2)
FROM (
        SELECT name, v1, v2, count(rst OR null) OVER (ORDER BY name, v1, v2) AS grp
        FROM (
                SELECT name, v1, v2, COALESCE(v1<>lag(v2) OVER (
                        PARTITION BY name
                        ORDER BY v1, v2
                ), true) AS rst
                FROM cat
        ) AS t1
) AS t2
GROUP BY grp, name;
 name | min | max 
------+-----+-----
 Dave |  25 |  29
 John |   1 |   9
 John |  17 |  25
 Mike |   9 |  17
(4 rows)

Behavior difference..

INSERT INTO cat (name, v1, v2)
VALUES ('Dave', 31, 35);

 name | min | max 
------+-----+-----
 Dave |  25 |  29
 Mike |   9 |  17
 Dave |  31 |  35
 John |   1 |   9
 John |  17 |  25
(5 rows)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.