How can i fill empty values while summarizing over a frame?

Question

I have a query that calculates moving sum over a frame:

SELECT "Дата",
                              "Износ",
                              SUM("Сумма") OVER (partition by "Износ" order by "Дата"
                                  rows between unbounded preceding and current row) AS "Продажи"
                       FROM (
                                SELECT date_trunc('week', period) AS "Дата",
                                       multiIf(wear_and_tear BETWEEN 1 AND 3, '1-3',
                                               wear_and_tear BETWEEN 4 AND 10, '4-10',
                                               wear_and_tear BETWEEN 11 AND 20, '11-20',
                                               wear_and_tear BETWEEN 21 AND 30, '21-30',
                                               wear_and_tear BETWEEN 31 AND 45, '31-45',
                                               wear_and_tear BETWEEN 46 AND 100, '46-100',
                                               'Новые')           AS "Износ",
                                       SUM(quantity)              AS "Сумма"
                                FROM shinsale_prod.sale_1c sc
                                         LEFT JOIN product_1c pc ON sc.product_id = pc.id
                                         

                                WHERE 1=1
--                                    AND partner != 'Наше предприятие'
--                                    AND wear_and_tear = 0
--                                    AND stock IN ('ShinSale Щитниково', 'ShinSale Строгино', 'ShinSale Кунцево', 'ShinSale Санкт-Петербург', 'Шиномонтаж Подольск')
                                    AND seasonality = 'з'

--                             AND (quantity IN {{quant}} OR quantity  IN -{{quant}})
--                             AND stock in {{Склад}}
                                GROUP BY "Дата", "Износ"
                                HAVING "Дата" BETWEEN '2021-06-01' AND '2022-01-08'
                                ORDER BY 'Дата'
                                

The thing is that in some groups I have now rows dated between 2021-12-20 and 2022-01-03 Therefore the line that represent this group has a gap on my chart.

Is there a way I can fill this gap with average values or smth?

I tried to right join my subquery to empty range of dates, but then i get empty rows and my filters in WHERE section kill the query and then I get empty or nearly empty result

Answer 1

You can generate mockup dates and construct a proper outer join like this:

SELECT
    a.the_date,
    sum(your_query.value) OVER (PARTITION BY 1 ORDER BY a.the_date ASC)
FROM
(
    SELECT
        number AS value,
        toDate('2021-01-01') + value AS the_date
    FROM numbers(10)
) AS your_query
RIGHT JOIN
(
    WITH
        toStartOfDay(toDate('2021-01-01')) AS start,
        toStartOfDay(toDate('2021-01-14')) AS end
    SELECT arrayJoin(arrayMap(x -> toDate(x), range(toUInt32(start), toUInt32(end), 24 * 3600))) AS the_date
) AS a ON a.the_date = your_query.the_date

Then the results will have no gaps:

┌─a.the_date─┬─sum(value) OVER (PARTITION BY 1 ORDER BY a.the_date ASC)─┐
│ 2021-01-01 │                                                        0 │
│ 2021-01-02 │                                                        1 │
│ 2021-01-03 │                                                        3 │
│ 2021-01-04 │                                                        6 │
│ 2021-01-05 │                                                       10 │
│ 2021-01-06 │                                                       15 │
│ 2021-01-07 │                                                       21 │
│ 2021-01-08 │                                                       28 │
│ 2021-01-09 │                                                       36 │
│ 2021-01-10 │                                                       45 │
│ 2021-01-11 │                                                       45 │
│ 2021-01-12 │                                                       45 │
│ 2021-01-13 │                                                       45 │
└────────────┴──────────────────────────────────────────────────────────┘