Postgresql

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

  • January 13, 2021

考慮:

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;

關於生成一系列日期:

小分

  • 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