0

Minimal working example table:

    WITH _table AS (
    SELECT 'A' unit,1 id,'2022-02-27' date, '9' cost
    UNION ALL SELECT 'A', 1 , '2022-02-26', '15'
    UNION ALL SELECT 'A', 1, '2022-02-25', '18'
    UNION ALL SELECT 'A', 2, '2022-02-27', '12'
    UNION ALL SELECT 'A', 2, '2022-02-26', '16'
    UNION ALL SELECT 'A', 2, '2022-02-25', '8'
    UNION ALL SELECT 'B', 11, '2022-03-02', '2' 
    UNION ALL SELECT 'B', 11, '2022-03-01', '12'
    UNION ALL SELECT 'B', 11, '2022-02-28', '14'
    UNION ALL SELECT 'B', 11, '2022-02-27', '10'
    UNION ALL SELECT 'B', 11, '2022-02-26', '16'
    UNION ALL SELECT 'B', 11, '2022-02-25', '18'
    )
    SELECT * FROM _table

For unit 'A' I get data by mail on Monday, Wednesday and Friday and it's missing Data for last three days. So on Tuesday I will miss data for 4 days. I don't need data for today though.

For unit 'B' I'm getting data from other sources and it's up to date. Units 'A' and 'B' are unioned.

I need to fill the missing days in unit 'A' partitioned by id with the average cost of last let's say 3 days available. That is for 28/02 and 01/03 as of today 02/03. Tomorrow I'll need to fill also for 02/03, so for three days. In this example with 14 for id 1 and 12 for id 2.

I would be grateful for any example or how to approach it.

malganis
  • 781
  • 1
  • 5
  • 7
  • You'' have to generate a data set for all days within the desired date range, then join it in. Do you have such a table that lists all days or will we need to generate one dynamically? something like these: https://stackoverflow.com/questions/66214846/google-bigquery-sql-how-to-fill-in-gaps-in-a-table-with-dates . – xQbert Mar 02 '22 at 18:35

0 Answers0