group consecutive time intervals in sql

Question

Assuming a data structure of the type

stock_name, action, start_date, end_date
google, growing, 1, 2
google, growing, 2, 3
google, falling, 3, 4
google, growing, 4, 5
yahoo, growing, 1, 2

How can I aggregate it to merge consecutive time intervals?

The output would look like:

stock_name, action, start_date, end_date
google, growing, 1, 3
google, falling, 3, 4
google, growing, 4, 5
yahoo, growing, 1, 2

I thought of using rank window function to number the consecutive with a constant and then grouping by that and action/name, but I cannot quite get it to work, something as below:

stock_name, action, start_date, end_date, rank
google, growing, 1, 2, 1
google, growing, 2, 3, 1
google, falling, 3, 4, 1
google, growing, 4, 5, 2
yahoo, growing, 1, 2, 1

If this were Mysql, I would easily solve it with variables, but this is not possible in postgres.

There could be any number of consecutive intervals, so self joining a predetermined nr of times is not an option.

Elegance(performance, readability) of solution matters.

Can consecutive time intervals consist of more than 2 records (e.g. 3, 4, 5)? — Tim Biegeleisen, Sep 02 '16 at 07:29
@TimBiegeleisen yes, as stated, there can be any number of consecutive intervals. — AdrianBR, Sep 02 '16 at 07:45
@TimBiegeleisen i am not familiar with the problem name- thank you, will google around! — AdrianBR, Sep 02 '16 at 07:46
@AdrianBR `If this were Mysql, I would easily solve it with variables, but this is not possible in postgres` - What is not possible in postgres ? — Oto Shavadze, Sep 02 '16 at 07:58
@OTARIKI things like holding several values across rows in variables and only incrementing when condition is met thx to lazy evaluation of cases. http://dev.mysql.com/doc/refman/5.7/en/user-variables.html — AdrianBR, Sep 02 '16 at 08:42
@AdrianBR Use postgres procedural language, if you need user defined variables — Oto Shavadze, Sep 02 '16 at 09:02
This is typically solved using window functions. This seems to be close: http://stackoverflow.com/questions/38979745/complex-gaps-and-islands-issue or this: http://stackoverflow.com/questions/24474307/finding-the-largest-group-of-consecutive-numbers-within-a-partition or this: http://stackoverflow.com/questions/12467567/detect-sql-island-over-multiple-parameters-and-conditions or this: http://stackoverflow.com/questions/8246687/querying-for-a-run-of-consecutive-columns-in-postgres — , Sep 02 '16 at 09:30

score 1 · Accepted Answer · answered Sep 02 '16 at 09:28

You can use variables just fine in PL/pgSQL.

I would solve this with a table function.

Assuming the table is called stock, my code would look like this:

CREATE OR REPLACE FUNCTION combine_periods() RETURNS SETOF stock
   LANGUAGE plpgsql STABLE AS
$$DECLARE
   s stock;
   period stock;
BEGIN
   FOR s IN
      SELECT stock_name, action, start_date, end_date
      FROM stock
      ORDER BY stock_name, action, start_date
   LOOP
      /* is this a new period? */
      IF period IS NOT NULL AND
         (period.stock_name <> s.stock_name
            OR period.action <> s.action
            OR period.end_date <> s.start_date)
      THEN
         /* new period, output last period */
         RETURN NEXT period;
         period := NULL;
      ELSE
         IF period IS NOT NULL
         THEN
            /* period continues, update end_date */
            period.end_date := s.end_date;
         END IF;
      END IF;

      /* remember the beginning of a new period */
      IF period IS NULL
      THEN
         period := s;
      END IF;
   END LOOP;

   /* output the last period */
   IF period IS NOT NULL
   THEN
      RETURN NEXT period;
   END IF;

   RETURN;
END;$$;

And I would call it like this:

test=> SELECT * FROM combine_periods();
┌────────────┬─────────┬────────────┬──────────┐
│ stock_name │ action  │ start_date │ end_date │
├────────────┼─────────┼────────────┼──────────┤
│ google     │ falling │          3 │        4 │
│ google     │ growing │          1 │        3 │
│ google     │ growing │          4 │        5 │
│ yahoo      │ growing │          1 │        2 │
└────────────┴─────────┴────────────┴──────────┘
(4 rows)

group consecutive time intervals in sql

1 Answers1

Linked