CODEWITHSUNDEEP
postgresqlwindow-functions
xibalba1
xibalba1

Reputation: 544

postgres LAG() using wrong previous value

Take the following data and queries:

create table if not exists my_example(a_group varchar(1)
                        ,the_date date
                        ,metric numeric(4,3)
                        );

INSERT INTO my_example
VALUES ('1','2018-12-14',0.514)
,('1','2018-12-15',0.532)
,('2','2018-12-15',0.252)
,('3','2018-12-14',0.562)
,('3','2018-12-15',0.361);

select
    t1.the_date
    ,t1.a_group
    ,t1.metric AS current_metric
    ,lag(t1.metric, 1) OVER (ORDER BY t1.a_group, t1.the_date) AS previous_metric
from
    my_example t1;

Which yields the following results:

+------------+---------+----------------+-----------------+
|  the_date  | a_group | current_metric | previous_metric |
+------------+---------+----------------+-----------------+
| 2018-12-14 |       1 | 0.514          | NULL            |
| 2018-12-15 |       1 | 0.532          | 0.514           |
| 2018-12-15 |       2 | 0.252          | 0.532           |
| 2018-12-14 |       3 | 0.562          | 0.252           |
| 2018-12-15 |       3 | 0.361          | 0.562           |
+------------+---------+----------------+-----------------+

I expected the value of previous_metric for the lone a_group==2 row to be NULL. However, as you can see, the value is showing as 0.532, which is being picked up from the previous row. How can I modify this query to yield a value of NULL as I expected?

Upvotes: 0

Views: 712

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522161

You need to use LAG with a partition on a_group, since you want the lag values from a specific frame:

SELECT
    t1.the_date,
    t1.a_group,
    t1.metric AS current_metric,
    LAG(t1.metric, 1) OVER (PARTITION BY t1.a_group ORDER BY t1.the_date)
        AS previous_metric
FROM my_example t1;

Upvotes: 1

Related Questions