I need to write a query so that there are 12 lines with the current exchange rate on the 1st day of each month 2024. I need to show all 12 months if some information is missing, I need to take previous lag currency value for a particular month.
SELECT '01.01.2024'::date AS data, 67 AS cur UNION ALL
SELECT '03.01.2024'::date AS data, 68 AS cur UNION ALL
SELECT '04.01.2024'::date AS data, 69 AS cur UNION ALL
SELECT '06.01.2024'::date AS data, 70 AS cur UNION ALL
SELECT '04.02.2024'::date AS data, 71 AS cur UNION ALL
SELECT '07.02.2024'::date AS data, 72 AS cur UNION ALL
SELECT '06.03.2024'::date AS data, 77 AS cur UNION ALL
SELECT '04.05.2024'::date AS data, 78 AS cur UNION ALL
SELECT '03.06.2024'::date AS data, 79 AS cur UNION ALL
SELECT '04.06.2024'::date AS data, 80 AS cur UNION ALL
SELECT '05.06.2024'::date AS data, 81 AS cur
SELECT MIN(data) AS first_date, cur AS currency
FROM (
SELECT data, cur,
ROW_NUMBER() OVER (PARTITION BY EXTRACT(YEAR FROM data)::int, ((EXTRACT(MONTH FROM data) - 1) / 12)::int ORDER BY data) AS rn
FROM your_table_name
) subquery
WHERE rn = 1
GROUP BY cur
ORDER BY first_date
The answer must be like this:
data | cur
------------+-----
2024-01-01 | 67
2024-02-01 | 70
2024-03-01 | 72
2024-04-01 | 77
2024-05-01 | 77
2024-06-01 | 78
2024-07-01 | 81
2024-08-01 | 81
2024-09-01 | 81
2024-10-01 | 81
2024-11-01 | 81
2024-12-01 | 81
First, generate the dates you want to see in your output. One method:
WITH dates (dt) AS
(
VALUES (DATE '2024-01-01'), (DATE '2024-02-01'), (DATE '2024-03-01'),
(DATE '2024-04-01'), (DATE '2024-05-01'), (DATE '2024-06-01'),
(DATE '2024-07-01'), (DATE '2024-08-01'), (DATE '2024-09-01'),
(DATE '2024-10-01'), (DATE '2024-11-01'), (DATE '2024-12-01')
)
another method:
WITH dates (dt) AS
(
SELECT generate_series::DATE AS dt FROM generate_series(DATE '2024-01-01',
DATE '2024-12-01',
'1 month')
)
Then get the latest currency entry for each date. This can easily be done with a subquery in the select clause or a lateral join.
WITH dates (dt) AS (...)
SELECT d.dt, t.cur
FROM dates d
LEFT JOIN LATERAL
(
SELECT *
FROM mytable mt
WHERE mt.data <= d.dt
ORDER BY mt.data DESC
FETCH FIRST ROW ONLY
) t ON TRUE
ORDER BY d.dt;