У меня проблема, когда я пытаюсь достичь желаемого результата. Задача выглядит просто - ежедневно подсчитывает количество событий в ведущих странах.
Основная таблица выглядит так:
id | date | country | col1 | col2 | ...
1 | 2018-01-01 21:21:21 | US | value 1 | value 2 | ...
2 | 2018-01-01 22:32:54 | UK | value 1 | value 2 | ...
Из этой таблицы я хочу получать ежедневные подсчеты по странам, что достигается
SELECT date::DATE AT TIME ZONE 'UTC', country, COALESCE(count(id),0) FROM tab1
GROUP BY 1, 2
Проблема возникает, когда пользователь 2 октября 2018 года не совершил никакого события
country_events
date | country | count
2018-01-01 | US | 23
2018-01-01 | UK | 5
2018-01-02 | US | 30
2018-01-02 | UK | 0 -> is desired result, but row is missing
Я попытался создать серию дат и ряд стран, которые я ищу, а затем CROSS JOIN
этих двух таблицах. Этот helper
с date
столбцов и country
я остался, присоединился к моей таблице результатов, подобной
SELECT * FROM helper h
LEFT JOIN country_events c ON c.date::DATE = h.date::DATE AND c.country = h.country
Я использую PostgreSQL.
Всего 1 ответ
Вам нужно внешнее соединение, а не крест-соединение:
SELECT tab1.date::date, tab1.country, coalesce(count(*), 0)
FROM generate_series(TIMESTAMP -01-01 00:00:00',
TIMESTAMP -01-31 00:00:00',
INTERVAL Ƈ day') AS ts(d)
LEFT JOIN tab1 ON tab1.date >= ts.d AND tab1.date < ts.d + INTERVAL Ƈ day'
GROUP BY tab1.date::date, tab1.country
ORDER BY tab1.date::date, tab1.country;
Это даст желаемый список за январь 2018 года.