WITH FILL в ClickHouse

Question

Есть таблица с датами, айдишниками и с количеством выполненных задач конкретного id в конкретную дату. Вот сам запрос:

WITH
    toDateTime('2021-01-01 00:00:00') as startSelectedDate,
    toDateTime('2021-01-06 23:59:59') as endSelectedDate,
    toStartOfDay(start)               as date
SELECT
    date,
    id,
    count() as tasksCompleted,
FROM table
WHERE
    date BETWEEN startSelectedDate AND endSelectedDate
GROUP BY 
    id, 
    date
ORDER BY
    date WITH FILL FROM
        toDateTime('2021-01-01 00:00:00') TO
        toDateTime('2021-01-06 23:59:59') STEP 86400

Получается следующая таблица:

date	id	tasksCompleted
2021-01-01	123	4
2021-01-01	0	0
2021-01-02	0	0
2021-01-02	0	0
2021-01-03	0	0
2021-01-03	0	0
2021-01-04	123	1
2021-01-04	456	1
2021-01-05	0	0
2021-01-05	0	0
2021-01-06	123	6
2021-01-06	0	0

Все дни в выбранном диапазоне дат заполняются нулями, включая и сам id, но хотелось бы, чтобы id был именно тот, для которого было сделано заполнение. Т.е. я пытаюсь добиться такого результата:

date	id	tasksCompleted
2021-01-01	123	4
2021-01-01	456	0
2021-01-02	123	0
2021-01-02	456	0
2021-01-03	123	0
2021-01-03	456	0
2021-01-04	123	1
2021-01-04	456	1
2021-01-05	123	0
2021-01-05	456	0
2021-01-06	123	6
2021-01-06	456	0

Можете подсказать как добиться такого заполнения?

Answer 1

Если я правильно понял, в таблице table лежат записи о выполенных задачах, время и id пользователя:

CREATE TABLE table (id UInt32, date DateTime) ENGINE = Memory;

INSERT INTO TABLE table VALUES (123, '2021-01-03 10:20:00'), (123, '2021-01-03 10:10:00'), (456, '2021-01-04 10:10:00');

Можно воспользовать JOIN, сначала сгенерировать все возможные пары пользователь-день (q2 в примере) с помощью CROSS JOIN и WITH FILL. Потом соединить с таблицей, в которой есть число выполенных задач с пропусками (q1 в примере):

WITH
    toDateTime('2021-01-01 00:00:00') as startSelectedDate,
    toDateTime('2021-01-06 23:59:59') as endSelectedDate,
    q1 as (SELECT
            date,
            id,
            count() as tasksCompleted
        FROM table
        WHERE
            date BETWEEN startSelectedDate AND endSelectedDate
        GROUP BY 
            id, 
            date),
    q2 as (SELECT id, t2.date as date
            FROM (SELECT DISTINCT id from table) AS t1
            CROSS JOIN (
                SELECT toStartOfDay(startSelectedDate) as date ORDER BY date
                WITH FILL FROM
                    toDateTime('2021-01-01 00:00:00') TO
                    toDateTime('2021-01-06 23:59:59') STEP 86400
            ) as t2)
SELECT q2.date as date, q2.id as id, sum(q1.tasksCompleted) as tasksCompleted FROM q2
LEFT JOIN q1
ON q1.id = q2.id AND toDate(q2.date) == toDate(q1.date)
GROUP BY id, date
ORDER BY date

Недостаток такого подхода - повышенное потребление памяти и увеличенное время работы, необходимое для гернерации всмопогательной таблицы с парами. Впрочем, если число дней и пользователей не велико, то это будет работать.

БЛОГ НА HUSL

WITH FILL в ClickHouse

Ответы (1 шт):