Блог

Главная - Вопросы по программированию - Как создать ежемесячный отчет, заполняющий отсутствующие дни месяца нулем

Как создать ежемесячный отчет, заполняющий отсутствующие дни месяца нулем

#questdb

#questdb

Вопрос:

Давайте представим, что у меня есть следующие данные в таблице базы данных QuestDB:

 ts | item ------------------------ 2020-01-02T10:12 | a 2020-01-02T10:20 | b 2020-01-03T11:20 | a 2020-01-03T11:30 | b 2020-01-03T11:40 | c

Я могу выполнить следующий запрос, чтобы подсчитать отдельные элементы, отобранные по дням за определенный месяц:

 SELECT DAY(ts) as day, COUNT_DISTINCT(item) AS count_items FROM my_table WHERE ts IN '2020-01' SAMPLE BY d FILL(0);

Я получаю следующие данные:

 day | count_items ------------------ 2 | 2 3 | 3

Вместо этого результата я хотел бы иметь полный ежемесячный отчет (даже за те дни, когда в таблице нет данных и заполнение их нулем), как показано ниже:

 day | count_items ------------------ 1 | 0 2 | 2 3 | 3 4 | 0 5 | 0 6 | 0 ... 31 | 0

Каков наилучший способ получения такого результата? Я подумывал об использовании ОБЪЕДИНЕНИЯ с таблицей, которая генерирует все дни за определенный месяц, но в базе данных QuestDB не реализовано ОБЪЕДИНЕНИЕ.

Существует ли простой способ создания такого рода отчетов с помощью базы данных QuestDB?

Ответ №1:

Вы можете сделать это с помощью левого соединения

 SELECT x as day, COALESCE(sb.count_items, 0) AS count_items from long_sequence(31) ls LEFT JOIN (  SELECT DAY(ts) as day, COUNT_DISTINCT(item) AS count_items  FROM my_table  WHERE ts IN '2020-01'  SAMPLE BY d ) AS sb ON sb.day = ls.x

Комментарии:

1. Я внес некоторые изменения, включая приведение и замену приема вызова длинной последовательности DAYS_IN_MONTH вместо литерала. Пожалуйста, Алекс, можешь ли ты одобрить изменения, чтобы я мог установить этот ответ в качестве принятого?

2. @jfcalvo если этот ответ полезен для вас, то озвучьте его и подумайте о том, чтобы отметить его как решение. Если вы хотите показать свое улучшение, добавьте свой ответ. Это нормально-голосовать и принимать других и добавлять собственные ответы.

Ответ №2:

Я изменил ответ Алекса, добавив a CAST(DAY(ts) AS LONG) (без этого в условии соединения слева возникает ошибка типа) и используя DAYS_IN_MONTH функцию:

 SELECT x as day, COALESCE(sb.count_items, 0) AS count_items from long_sequence(DAYS_IN_MONTH('2020-01') ls LEFT JOIN (  SELECT CAST(DAY(ts) AS LONG) as day, COUNT_DISTINCT(item) AS count_items  FROM my_table  WHERE ts IN '2020-01'  SAMPLE BY d ) AS sb ON sb.day = ls.x
Метки: ,