How to generate rows from date to date with the same data?-CodePudding

In my database I have a row like this:

id     type     date      price
1       A    2022-11-09    100
2       A    2022-11-12    200

Provided I want to have a price for type A from day 9 to day 15 of November, my expectation result for this query is like this

 type     date      price
   A    2022-11-09    100
   A    2022-11-10    100
   A    2022-11-11    100
   A    2022-11-12    200
   A    2022-11-13    200
   A    2022-11-14    200
   A    2022-11-15    200

I wonder if is there anyway we can do it by query

CodePudding user response：

Since you want your output to have a row per date in that range, let's start from generate_series to put that range together:

SELECT series.date::date
FROM generate_series('2022-11-09', '2022-11-15', INTERVAL '1 day') series;

    date
------------
 2022-11-09
 2022-11-10
 2022-11-11
 2022-11-12
 2022-11-13
 2022-11-14
 2022-11-15
(7 rows)

Then we can fill in the price column using a subquery:

SELECT 
  'A' as type, 
  series.date::date, 
  (
    SELECT price 
    FROM mytable 
    WHERE type = 'A' 
    AND date <= series.date 
    ORDER BY date DESC 
    LIMIT 1
  ) as price 
FROM generate_series('2022-11-09', '2022-11-15', interval '1 day') series;

 type |    date    | price
------ ------------ -------
 A    | 2022-11-09 |   100
 A    | 2022-11-10 |   100
 A    | 2022-11-11 |   100
 A    | 2022-11-12 |   200
 A    | 2022-11-13 |   200
 A    | 2022-11-14 |   200
 A    | 2022-11-15 |   200
(7 rows)

Another approach might be to create ranges out of the data using a LEAD window function:

SELECT 
  type, 
  daterange(date, LEAD(date) OVER (PARTITION BY type ORDER BY date)) as range, 
  price 
FROM mytable;

 type |          range          | price
------ ------------------------- -------
 A    | [2022-11-09,2022-11-12) |   100
 A    | [2022-11-12,)           |   200
(2 rows)

And then join our generate_series with the ranges using the inclusion operator:

WITH ranges AS (
  SELECT 
    type, 
    daterange(date, LEAD(date) OVER (PARTITION BY type ORDER BY date)) as range, 
    price 
  FROM mytable
) 
SELECT type, series.date::date, price 
FROM generate_series('2022-11-09', '2022-11-15', interval '1 day') series 
JOIN ranges ON (ranges.range @> series.date) 
WHERE type = 'A';

 type |    date    | price
------ ------------ -------
 A    | 2022-11-09 |   100
 A    | 2022-11-10 |   100
 A    | 2022-11-11 |   100
 A    | 2022-11-12 |   200
 A    | 2022-11-13 |   200
 A    | 2022-11-14 |   200
 A    | 2022-11-15 |   200
(7 rows)