CODEWITHSUNDEEP
postgresqlgroup-bylag
aajkaltak
aajkaltak

Reputation: 1537

Postgres window function using group and lag and other possible ways

Assume I have the table below and I would like to return only those rows where Price != price in the previous row but only within a group where the grouping is by Time

ID :           {    1   ,  2      ,      3   ,    4    ,    5    ,    6 }

Time        : { 09:20:00, 09:20:00,  09:20:00, 09:20:01, 09:20:02, 09:20:03 }

Price :       {   100   , 100     ,  101     , 102     ,   102   ,   103 }

Because of the grouping by time, the output returned should be:

ID                   : {   1     ,    3    ,    4    ,   5      ,  6 }
Time                 : { 09:20:00, 09:20:00, 09:20:01, 09:20:02 , 09:20:03 }
Price :                {   100   ,  101    ,  102     , 102     , 103 }

Do notice that the output discards one of the 100 price at 09:20 but does not discard 102 price at 09:20:02 even though it is same as price at 09:20:01 since the grouping has to be done by time. Can someone help me with a postgres query for something like this.

Upvotes: 1

Views: 329

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246043

SELECT time, price
FROM mytable
WHERE price <> coalesce(
                  lag(price) OVER (PARTITION BY time ORDER BY id),
                  price - 1
               )
ORDER BY time, id;

The coalesce takes care of the NULL value that appears for the first element in each partition.

Upvotes: 2

Related Questions