Postgresql
從 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;
關於生成一系列日期:
小分
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;