Group Consecutive Time Intervals In Sql

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, growin

Solution 1:

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:

CREATEOR REPLACE FUNCTION combine_periods() RETURNS SETOF stock
   LANGUAGE plpgsql STABLE AS
$$DECLARE
   s stock;
   period stock;
BEGINFOR s INSELECT stock_name, action, start_date, end_date
      FROM stock
      ORDERBY stock_name, action, start_date
   LOOP
      /* is this a new period? */
      IF periodISNOTNULLAND
         (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 periodISNOTNULLTHEN/* period continues, update end_date */
            period.end_date := s.end_date;
         END IF;
      END IF;

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

   /* output the last period */
   IF periodISNOTNULLTHENRETURN 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 │
└────────────┴─────────┴────────────┴──────────┘
(4rows)