PostgreSQL Group By – Create New Group When Column Value Changes

group bypostgresql

I have a table for reservations as follows:

-------------------------
| dt         | reserved |
-------------------------
| 2017-01-01 | 10       |
-------------------------
| 2017-01-02 | 10       |
-------------------------
| 2017-01-03 |  5       |
-------------------------
| 2017-01-04 | 10       |
-------------------------
| 2017-01-05 | 10       |
-------------------------

If I do 

SELECT min(dt) AS from_date, max(dt) AS to_date, reserved
FROM reservations
GROUP BY reserved

It returns 2 groups:

--------------------------------------
| from_date  | to_date    | reserved |
--------------------------------------
| 2017-01-01 | 2017-01-05 | 10       |
--------------------------------------
| 2017-01-03 | 2017-01-03 |  5       |
--------------------------------------

While the output I am trying to achieve is:

--------------------------------------
| from_date  | to_date    | reserved |
--------------------------------------
| 2017-01-01 | 2017-01-02 | 10       |
--------------------------------------
| 2017-01-03 | 2017-01-03 |  5       |
--------------------------------------
| 2017-01-04 | 2017-01-05 | 10       |
--------------------------------------

Can this be done directly from SQL ? or do I have to do the partitioning on the application level ?

Best Answer

This is more complicated, you need to find consecutive rows with the same reserved value first and assign a common group number to it. There are several ways to achieve this, the following is the easest one to understand by submitting the Derived Tables individually:

SELECT min(dt) AS from_date, max(dt) AS to_date, reserved
FROM 
 (
   select dt, reserved, 
      -- assign a number to al rows with the same value
      sum(flag) over (order by dt rows unbounded preceding) as grp
   from
     ( SELECT dt, reserved,
          -- assigns 1 whenever reserved changes
          case when lag(reserved) over (order by dt) = reserved
               then 0 -- value of the previous same as current
               else 1 -- different value
          end as flag
       from reservations
     ) as dt
  ) as dt
GROUP BY reserved, grp
Related Question