Postgres – как вернуть строки с 0 счетчиком для отсутствия данных?

У меня есть неравномерно распределенные данные (дата ответа) на несколько лет (2003-2008). Я хочу запросить данные для заданного набора даты начала и окончания, группируя данные с помощью любого из поддерживаемых интервалов (день, неделя, месяц, квартал, год) в PostgreSQL 8.3 ( http://www.postgresql.org/docs /8.3/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC ).

Проблема в том, что некоторые из запросов дают результаты, непрерывные в течение требуемого периода, так как это:

select to_char(date_trunc('month',date), 'YYYY-MM-DD'),count(distinct post_id) from some_table where category_id=1 and entity_id = 77 and entity2_id = 115 and date <= '2008-12-06' and date >= '2007-12-01' group by date_trunc('month',date) order by date_trunc('month',date); to_char | count ------------+------- 2007-12-01 | 64 2008-01-01 | 31 2008-02-01 | 14 2008-03-01 | 21 2008-04-01 | 28 2008-05-01 | 44 2008-06-01 | 100 2008-07-01 | 72 2008-08-01 | 91 2008-09-01 | 92 2008-10-01 | 79 2008-11-01 | 65 (12 rows) 

но некоторые из них пропускают несколько интервалов, потому что нет данных, поскольку этот:

 select to_char(date_trunc('month',date), 'YYYY-MM-DD'),count(distinct post_id) from some_table where category_id=1 and entity_id = 75 and entity2_id = 115 and date <= '2008-12-06' and date >= '2007-12-01' group by date_trunc('month',date) order by date_trunc('month',date); to_char | count ------------+------- 2007-12-01 | 2 2008-01-01 | 2 2008-03-01 | 1 2008-04-01 | 2 2008-06-01 | 1 2008-08-01 | 3 2008-10-01 | 2 (7 rows) 

где требуемый набор результатов:

  to_char | count ------------+------- 2007-12-01 | 2 2008-01-01 | 2 2008-02-01 | 0 2008-03-01 | 1 2008-04-01 | 2 2008-05-01 | 0 2008-06-01 | 1 2008-07-01 | 0 2008-08-01 | 3 2008-09-01 | 0 2008-10-01 | 2 2008-11-01 | 0 (12 rows) 

Счет 0 для отсутствующих записей.

Я видел более ранние дискуссии о переполнении стека, но они не решают мою проблему, потому что мой период группировки – один из (день, неделя, месяц, квартал, год) и определил время выполнения приложения. Поэтому подход, подобный левому соединению с таблицей календаря или таблицей последовательности, не поможет.

Мое текущее решение этого – заполнить эти пробелы в Python (в приложении Turbogears) с помощью модуля календаря.

Есть лучший способ сделать это.

3 Solutions collect form web for “Postgres – как вернуть строки с 0 счетчиком для отсутствия данных?”

Вы можете создать список всех первых дней прошлого года (скажем) с помощью

 select distinct date_trunc('month', (current_date - offs)) as date from generate_series(0,365,28) as offs; date ------------------------ 2007-12-01 00:00:00+01 2008-01-01 00:00:00+01 2008-02-01 00:00:00+01 2008-03-01 00:00:00+01 2008-04-01 00:00:00+02 2008-05-01 00:00:00+02 2008-06-01 00:00:00+02 2008-07-01 00:00:00+02 2008-08-01 00:00:00+02 2008-09-01 00:00:00+02 2008-10-01 00:00:00+02 2008-11-01 00:00:00+01 2008-12-01 00:00:00+01 

Затем вы можете присоединиться к этой серии.

Этот вопрос старый. Но поскольку другие пользователи выбрали это как мастер для нового дубликата, я добавляю правильный ответ.

Правильное решение

 SELECT * FROM ( SELECT day::date FROM generate_series(timestamp '2007-12-01' , timestamp '2008-12-01' , interval '1 month') day ) d LEFT JOIN ( SELECT date_trunc('month', date_col)::date AS day , count(*) AS some_count FROM tbl WHERE date_col >= date '2007-12-01' AND date_col <= date '2008-12-06' -- AND ... more conditions GROUP BY 1 ) t USING (day) ORDER BY day; 
  • Конечно, используйте LEFT JOIN .

  • generate_series() может создавать таблицу временных меток «на лету» и очень быстро.

  • Как правило, вы быстрее объединяетесь, прежде чем присоединяться. Недавно я представил тестовый пример на sqlfiddle.com в этом связанном ответе:

    • PostgreSQL – порядок по массиву
  • Отметьте timestamp date ( ::date ) для базового формата. Для большей пользы to_char() .

  • GROUP BY 1 – это синтаксическая стенограмма для ссылки на первый выходной столбец. Также может быть GROUP BY day , но это может противоречить существующему столбцу с тем же именем. Или GROUP BY date_trunc('month', date_col)::date но это слишком долго для моего вкуса.

  • Работает с доступными интервальными аргументами для date_trunc() .

  • count() никогда не производит NULL ( 0 без строк), но LEFT JOIN делает.
    Чтобы вернуть 0 вместо NULL во внешнем SELECT , используйте COALESCE(some_count, 0) AS some_count . Руководство.

  • Для более общего решения или произвольных временных интервалов рассмотрим этот тесно связанный ответ:

    • Лучший способ подсчета записей произвольными временными интервалами в Rails + Postgres

Вы можете создать временную таблицу во время выполнения и оставить ее. Это, кажется, имеет наибольший смысл.

  • Начало работы с Python на Heroku - исполняемый файл pg_config не найден
  • Как написать DataFrame в таблицу postgres?
  • Django задает параметры конфиденциальности для каждой модели
  • Быстрое преобразование из массивов Python в PostgreSQL?
  • psycopg2 не возвращает результаты
  • Django JSONField внутри ArrayField
  • С sqlalchemy, как динамически привязываться к движку базы данных по каждому запросу
  • Ошибка формы Django / базы данных: слишком длинное значение для символа типа (4)
  • Python - лучший язык программирования в мире.