Запрос PostgreSQL для подсчета/группировки по дням и отображения дней без данных

Ответ 1

Вам просто нужно left outer join вместо внутреннего соединения:

SELECT d.date, count(se.id)
FROM (SELECT to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD') AS date 
      FROM generate_series(0, 365, 1) AS offs
     ) d LEFT OUTER JOIN
     sharer_emailshare se 
     ON d.date = to_char(date_trunc('day', se.created), 'YYYY-MM-DD'))  
GROUP BY d.date;

Ответ 2

Расширяя полезный ответ Гордона Линоффа, я бы предложил пару улучшений, таких как:

• Используйте ::date вместо date_trunc('day', ...)
• Присоединяется к типу даты, а не к типу символа (он чище).
• Используйте определенные диапазоны дат, чтобы их было легче изменить позже. В этом случае я выбираю за год до самой последней записи в таблице - то, что не могло быть легко выполнено с другим запросом.
• Вычислить итоговые значения для произвольного подзапроса (используя CTE). Вам просто нужно указать интересующий столбец типа даты и назвать его date_column.
• Включить столбец для общей суммы. (Почему бы и нет?)

Здесь мой запрос:

WITH dates_table AS (
    SELECT created::date AS date_column FROM sharer_emailshare WHERE showroom_id=5
)
SELECT series_table.date, COUNT(dates_table.date_column), SUM(COUNT(dates_table.date_column)) OVER (ORDER BY series_table.date) FROM (
    SELECT (last_date - b.offs) AS date
        FROM (
            SELECT GENERATE_SERIES(0, last_date - first_date, 1) AS offs, last_date from (
                 SELECT MAX(date_column) AS last_date, (MAX(date_column) - '1 year'::interval)::date AS first_date FROM dates_table
            ) AS a
        ) AS b
) AS series_table
LEFT OUTER JOIN dates_table
    ON (series_table.date = dates_table.date_column)
GROUP BY series_table.date
ORDER BY series_table.date

Я протестировал запрос, и он дает те же результаты, плюс столбец для совокупного итога.

Ответ 3

На основании ответа Гордона Линофф я понял, что другая проблема заключалась в том, что у меня было предложение WHERE, о котором я не упоминал в исходном вопросе.

Вместо голой WHERE я сделал подзапрос:

SELECT d.date, count(se.id) FROM (
    select to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD')
    AS date 
    FROM generate_series(0, 365, 1) 
    AS offs
    ) d 
LEFT OUTER JOIN (
    SELECT * FROM sharer_emailshare 
    WHERE showroom_id=5
) se
ON (d.date=to_char(date_trunc('day', se.created), 'YYYY-MM-DD')) 
GROUP BY d.date;

Ответ 4

Я постараюсь дать ответ, который включает некоторые объяснения. Я начну с самого маленького строительного блока и поработаю.

Если вы выполните запрос, подобный следующему:

SELECT series.number FROM generate_series(0, 9) AS series(number)

Вы получите такой вывод:

 number 
--------
      0
      1
      2
      3
      4
      5
      6
      7
      8
      9
(10 rows)

Это можно превратить в такие даты:

SELECT CURRENT_DATE + sequential_dates.date AS date
  FROM generate_series(0, 9) AS sequential_dates(date)

Который выдаст такой результат:

    date    
------------
 2019-09-29
 2019-09-30
 2019-10-01
 2019-10-02
 2019-10-03
 2019-10-04
 2019-10-05
 2019-10-06
 2019-10-07
 2019-10-08
(10 rows)

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

   SELECT sequential_dates.date,
          COUNT(calendar_items.*) AS calendar_item_count
     FROM (SELECT CURRENT_DATE + sequential_dates.date AS date
             FROM generate_series(0, 9) AS sequential_dates(date)) sequential_dates
LEFT JOIN calendar_items ON calendar_items.starts_at::date = sequential_dates.date
 GROUP BY sequential_dates.date

Который выдаст такой результат:

    date    | calendar_item_count 
------------+---------------------
 2019-09-29 |                   1
 2019-09-30 |                   8
 2019-10-01 |                  15
 2019-10-02 |                  11
 2019-10-03 |                   1
 2019-10-04 |                  12
 2019-10-05 |                   0
 2019-10-06 |                   0
 2019-10-07 |                  27
 2019-10-08 |                  24