Search code examples
sqlselectamazon-redshiftlag

Lag function - issue

I have data like that :

Year Value
2022 1000
2023 500

and I want to create column with previous values like that:

Year Value Value_PY
2022 1000 null
2023 500 1000
2024 null 500

The LAG() function isn't the best solution for this because I get the table without 2024 row. How can I solve this?

lag(value) OVER(order by Year)

How can I solve this? What should I add to this LAG() function?

Solution

  • Add a row for 2024. Union with a select '2024' as Year, NULL as value. Then apply your lag column.

    SELECT *, lag(value) OVER(order by Year) AS Value_PY FROM
(
SELECT * FROM example
UNION 
SELECT '2024' AS Year, NULL AS value
) t
    Year Value Value_PY
    2022 1000 null
    2023 500 1000
    2024 null 500