Let's imagine that I have the following data on a QuestDB table:
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
I can execute the following query to count distinct items sampled by day for an specific month:
SELECT DAY(ts) as day, COUNT_DISTINCT(item) AS count_items
FROM my_table
WHERE ts IN '2020-01'
SAMPLE BY d FILL(0);
I get the following data:
day | count_items
------------------
2 | 2
3 | 3
Instead of that result I would like to have a complete monthly report (even for days where the table doesn't have data and filling them with zero) like the following:
day | count_items
------------------
1 | 0
2 | 2
3 | 3
4 | 0
5 | 0
6 | 0
...
31 | 0
Whats the best way to generate such result? I have been thinking into using JOIN with a table that generates all the days for the specific month but QuestDB does not have LEFT JOIN implemented.
Do exists a simple way to generate these kind of reports with QuestDB?
I have modified Alex answer adding a CAST(DAY(ts) AS LONG) (without this a type error is raised in the left join condition) and using DAYS_IN_MONTH function:
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