выбор записей без значения

У меня проблема, когда я пытаюсь достичь желаемого результата. Задача выглядит просто - ежедневно подсчитывает количество событий в ведущих странах.

Основная таблица выглядит так: 

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 года.

Есть идеи?

10000