ClickHouse - ORDER BY WITH FILL - MONTH interval

Question

I've got a question regarding the FILL WITH function. I need a query grouped by month with empty rows to plot on a graph. I use the FILL WITH function.

I have a simple table:

CREATE TABLE IF NOT EXISTS fillwith
(
    `event_timestamp` Datetime64,
    `event_date` Date,
    `event_type` String
)
ENGINE = Memory

With some sample data

insert into fillwith (event_timestamp, event_date, event_type) values ('2021-01-07 19:14:33.000', '2021-01-07', 'PRODUCT_VIEW');
insert into fillwith (event_timestamp, event_date, event_type) values ('2021-02-07 19:14:33.000', '2021-02-07', 'PRODUCT_CLICK');
insert into fillwith (event_timestamp, event_date, event_type) values ('2020-11-07 19:14:33.000', '2020-11-07', 'PRODUCT_VIEW');
insert into fillwith (event_timestamp, event_date, event_type) values ('2020-12-07 19:14:33.000', '2020-12-07', 'PRODUCT_VIEW');
insert into fillwith (event_timestamp, event_date, event_type) values ('2020-09-07 19:14:33.000', '2020-09-07', 'PRODUCT_VIEW');

With a day interval, I get a full list of days but not sorted and feels likes they are random days

SELECT 
    toDate(toStartOfInterval(event_date, toIntervalDay(1))) AS date,
    countIf(event_type = 'PRODUCT_VIEW') AS views,
    countIf(event_type = 'PRODUCT_CLICK') AS clicks
FROM fillwith
GROUP BY toDate(toStartOfInterval(event_date, toIntervalDay(1)))
ORDER BY date ASC 
WITH FILL FROM toDate('2020-01-01') TO toDate('2021-12-01') STEP dateDiff('second', now(), now() + toIntervalDay(1))

Result:

┌───────date─┬─views─┬─clicks─┐
│ 2020-09-07 │     1 │      0 │
│ 2020-11-07 │     1 │      0 │
│ 2020-12-07 │     1 │      0 │
│ 2021-01-07 │     1 │      0 │
│ 2021-02-07 │     0 │      1 │
└────────────┴───────┴────────┘
┌───────date─┬─views─┬─clicks─┐
│ 2106-02-07 │     0 │      0 │
│ 2005-05-25 │     0 │      0 │
│ 2062-07-09 │     0 │      0 │
│ 2106-02-07 │     0 │      0 │
│ 1997-05-03 │     0 │      0 │
│ 2054-06-17 │     0 │      0 │
│ 2106-02-07 │     0 │      0 │
│ 1989-04-11 │     0 │      0 │
│ 2046-05-26 │     0 │      0 │
│ 2103-07-11 │     0 │      0 │

When I try the same for a Month interval:

select 
    toDate(toStartOfInterval(event_date, INTERVAL 1 month)) as date,  
    countIf(event_type = 'PRODUCT_VIEW') as views,
    countIf(event_type = 'PRODUCT_CLICK') as clicks 
from fillwith 
GROUP BY toDate(toStartOfInterval(event_date, INTERVAL 1 month)) 
ORDER BY date ASC WITH FILL
FROM toDate('2020-01-01') TO toDate('2021-04-01') STEP dateDiff('second',
    now(),
    now() + INTERVAL 1 month)

Result:

┌───────date─┬─views─┬─clicks─┐
│ 2020-01-01 │     0 │      0 │
│ 2020-09-01 │     1 │      0 │
│ 2020-11-01 │     1 │      0 │
│ 2020-12-01 │     1 │      0 │
│ 2021-01-01 │     1 │      0 │
│ 2021-02-01 │     0 │      1 │
└────────────┴───────┴────────┘

But I expect:

┌───────date─┬─views─┬─clicks─┐
│ 2020-01-01 │     0 │      0 │
│ 2020-02-01 │     0 │      0 │
│ 2020-03-01 │     0 │      0 │
│ 2020-04-01 │     0 │      0 │
│ 2020-05-01 │     0 │      0 │
│ 2020-06-01 │     0 │      0 │
│ 2020-07-01 │     0 │      0 │
│ 2020-08-01 │     0 │      0 │
│ 2020-09-01 │     1 │      0 │
│ 2020-10-01 │     0 │      0 │
│ 2020-11-01 │     1 │      0 │
│ 2020-12-01 │     1 │      0 │
│ 2021-01-01 │     1 │      0 │
│ 2021-02-01 │     0 │      1 │
│ 2021-03-01 │     0 │      0 │
│ 2021-04-01 │     0 │      0 │
└────────────┴───────┴────────┘

Does someone know why this happens and how I can improve this?

Thanks for your help!

vladimir · Accepted Answer · 2021-02-07T22:34:52.000

Try this query:

WITH toDate(0) AS start_date, toRelativeMonthNum(toDate(0)) AS relative_month_of_start_date
SELECT
    addMonths(start_date, relative_month - relative_month_of_start_date) AS month,
    views,
    clicks
FROM 
(
    SELECT
        toRelativeMonthNum(event_date) AS relative_month,
        countIf(event_type = 'PRODUCT_VIEW') AS views,
        countIf(event_type = 'PRODUCT_CLICK') AS clicks
    FROM fillwith
    GROUP BY relative_month
    ORDER BY relative_month ASC 
        WITH FILL 
            FROM toRelativeMonthNum(toDate('2020-01-01'))
            TO toRelativeMonthNum(toDate('2021-12-01')) STEP 1
)
ORDER BY month ASC

/*
┌──────month─┬─views─┬─clicks─┐
│ 2020-01-01 │     0 │      0 │
│ 2020-02-01 │     0 │      0 │
│ 2020-03-01 │     0 │      0 │
│ 2020-04-01 │     0 │      0 │
│ 2020-05-01 │     0 │      0 │
│ 2020-06-01 │     0 │      0 │
│ 2020-07-01 │     0 │      0 │
│ 2020-08-01 │     0 │      0 │
│ 2020-09-01 │     1 │      0 │
│ 2020-10-01 │     0 │      0 │
│ 2020-11-01 │     1 │      0 │
│ 2020-12-01 │     1 │      0 │
│ 2021-01-01 │     1 │      0 │
│ 2021-02-01 │     0 │      1 │
│ 2021-03-01 │     0 │      0 │
│ 2021-04-01 │     0 │      0 │
│ 2021-05-01 │     0 │      0 │
│ 2021-06-01 │     0 │      0 │
│ 2021-07-01 │     0 │      0 │
│ 2021-08-01 │     0 │      0 │
│ 2021-09-01 │     0 │      0 │
│ 2021-10-01 │     0 │      0 │
│ 2021-11-01 │     0 │      0 │
└────────────┴───────┴────────┘
*/

or alternate way:

SELECT
    toStartOfMonth(date) AS month,
    sum(views) AS views,
    sum(clicks) AS clicks
FROM 
(
    SELECT
        event_date AS date, /* or: toDate(toStartOfDay(event_timestamp)) AS date  */
        countIf(event_type = 'PRODUCT_VIEW') AS views,
        countIf(event_type = 'PRODUCT_CLICK') AS clicks
    FROM fillwith
    GROUP BY date
    ORDER BY date ASC 
        WITH FILL 
            FROM toDate('2020-01-01') 
            TO toDate('2021-12-01') 
            /* type of 'date' is Date => '1' means 1 day */
            STEP 1
)
GROUP BY month
ORDER BY month ASC

/*
┌──────month─┬─views─┬─clicks─┐
│ 2020-01-01 │     0 │      0 │
│ 2020-02-01 │     0 │      0 │
│ 2020-03-01 │     0 │      0 │
│ 2020-04-01 │     0 │      0 │
│ 2020-05-01 │     0 │      0 │
│ 2020-06-01 │     0 │      0 │
│ 2020-07-01 │     0 │      0 │
│ 2020-08-01 │     0 │      0 │
│ 2020-09-01 │     1 │      0 │
│ 2020-10-01 │     0 │      0 │
│ 2020-11-01 │     1 │      0 │
│ 2020-12-01 │     1 │      0 │
│ 2021-01-01 │     1 │      0 │
│ 2021-02-01 │     0 │      1 │
│ 2021-03-01 │     0 │      0 │
│ 2021-04-01 │     0 │      0 │
│ 2021-05-01 │     0 │      0 │
│ 2021-06-01 │     0 │      0 │
│ 2021-07-01 │     0 │      0 │
│ 2021-08-01 │     0 │      0 │
│ 2021-09-01 │     0 │      0 │
│ 2021-10-01 │     0 │      0 │
│ 2021-11-01 │     0 │      0 │
└────────────┴───────┴────────┘
*/

ClickHouse - ORDER BY WITH FILL - MONTH interval

1 Answers1