Postgres window function using group and lag and other possible ways

Question

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.

There is a unique id for every row, basically 3 columns ID, Time and Price. I have edited the table structure above. — aajkaltak
– aajkaltak, Commented Nov 15, 2018 at 18:54

Laurenz Albe · Accepted Answer · 2018-11-15 19:00:33Z

2

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.

answered Nov 15, 2018 at 19:00

Laurenz Albe

257k22 gold badges311 silver badges387 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres window function using group and lag and other possible ways

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related