從 generate_series() 填寫記錄集中缺失的日期

考慮：

with days as (select day::date
from generate_series(date '2013-01-01', date '2013-01-01' + 365, interval '1 day' day) day
) 
select 'Inspections'::text as data_label,
count(i.reporting_id) as daily_count, d.day as date_column
from days d
left join inspection i on i.close_case_date = d.day
group by d.day
order by d.day

這將返回一個如下所示的集合：

data_label | daily_count | date_column
Inspections    1           01/01/13
Inspections    2           01/02/13
Inspections    4           01/04/13
Inspections    8           01/06/13

請注意記錄集中的 1 天和 2 天間隔。我需要生成一個用 0 填充的值的集合，如下所示：

data_label | daily_count | date_column
Inspections    1           01/01/13
Inspections    2           01/02/13
Inspections    0           01/03/13
Inspections    4           01/04/13
Inspections    0           01/05/13
Inspections    8           01/06/13

我該怎麼做？我不是一個數據庫開發人員，而且是 Postgres 的新手，所以我不確定從哪裡開始，也找不到任何關於如何實現這個目標的資訊。

這是某種誤解。您問題中的查詢已經返回您所要求的內容。我只更改了一些小細節：

SELECT text 'Inspections'       AS data_label
    , count(i.close_case_date) AS daily_count
    , d.day                    AS date_column
FROM  (
  SELECT generate_series(timestamp '2013-01-01'
                       , timestamp '2013-01-01' + interval '1 year - 1 day'
                       , interval  '1 day')::date
  ) d(day)
LEFT   JOIN inspection i ON i.close_case_date = d.day
GROUP  BY d.day
ORDER  BY d.day;

關於生成一系列日期：

• 在 PostgreSQL 中生成兩個日期之間的時間序列

小分

• date '2013-01-01' + interval '1 year - 1 day'總比date '2013-01-01' + 365涵蓋閏年要好。
• 使用更便宜的子查詢。不需要 CTE。
• 為什麼count(i.reporting_id)？要僅計算 中的行數i，請使用count(i.close_case_date)我們已經加入的 ，因此它不能為 NULL。count()只計算非空值。如果reporting_id可以為 NULL，則您冒著不計算這些行的風險。

或者，移動演員表，所以我們可以直接使用 set-returning 函式作為表表達式：

SELECT text 'Inspections'       AS data_label
    , count(i.close_case_date) AS daily_count
    , d.day::date              AS date_column
FROM   generate_series(timestamp '2013-01-01'
                    , timestamp '2013-01-01' + interval '1 year - 1 day'
                    , interval  '1 day') AS d(day)
LEFT   JOIN inspection i ON i.close_case_date = d.day::date
GROUP  BY d.day
ORDER  BY d.day;

引用自：https://dba.stackexchange.com/questions/72419