將區間拆分為年份切片

作為新手 Postgres 使用者，我的adPostgreSQL 9.5 (x64) 數據庫中有一個包含 87 行的表。除了其他列之外，它還有兩列“開始”和“結束”，日期時間範圍如下：

ID      Start                   End
1       2003-06-07 00:00:00     2004-09-30 23:59:59

我需要將範圍拆分為儲存在數據庫中的單獨行（從開始年到間隔的最後一年）中的一年視窗，如下所示：

ID       Start
1_2003   2003-06-07 00:00:00     2003-12-31 23:59:59
1_2004   2004-01-01 00:00:00     2004-09-30 23:59:59

使用運算符||'_'||和Extract()函式，我可以將 ID 與年份連接起來。此外，這個問題解決瞭如何以周為單位劃分間隔，而這個問題展示瞭如何在幾天內做同樣的事情，但沒有一個問題具體解決瞭如何以年為單位劃分間隔。

我避免了這個問題，因為我不想採用基於儲存過程的方法。我知道generate_series()從開始和停止參數返回一系列，但實際上我正在努力打破一年中最後一天的間隔，然後從下一行的第一天重新開始。如果有人可以指導我這樣做，我將不勝感激？

幾個建議：

• 使用合法的、小寫的、不帶引號的標識符來避免很多混亂。end（並且，在較小程度start上）是保留字。
• 您的id列似乎是數字類型integer，而不是text。
• start_day由於and中不需要時間組件end_day，因此適當的數據類型是date, nottimestamp。
• 為什麼要連接id新的“ID”和年份？year如果您需要它進行 PK，請添加第二列。或者根本不添加額外的列。它可以廉價地從新的動態中提取出來start_day。一般來說，如果可以避免的話，不要冗餘儲存數據。
• 通常，timestamp範圍使用包含的下界和獨占的上界。由於時間戳可以有小數位（在 Postgres 中最多為 6 個），因此更清晰。您的輸入2003-12-31 23:59:59將失敗2003-12-31 23:59:59.123。

因此，您的表格可能如下所示：

CREATE TABLE ad (
 id        int PRIMARY KEY
, start_day date NOT NULL    -- *inclusive* lower bound
, end_day   date NOT NULL    -- *exclusive* upper bound
CHECK (end_day > start_day)  -- enforce legal input
);

正確的測試值：

INSERT INTO ad(id, start_day, end_day)
VALUES
 (1, '2003-06-07', '2004-10-01')   -- span 2 years (your example)
, (2, '2003-06-07', '2003-06-08')   -- 1 day in same year
, (3, '2003-06-07', '2003-10-01')   -- span 1 year
, (4, '2003-06-07', '2006-10-01');  -- span many years

解決方案

generate_series()在LATERALjoin中使用，基於開始和結束日期，截斷為年份date_trunc()。這將每年生成一行，並帶有新的開始日期。添加一年，您就有了新的結束日期。除了第一行和最後一行 per id，您可以分別用GREATEST和LEAST替換正確的開始/結束。瞧。

-- CREATE TABLE ad_year AS
SELECT ad.id
    , extract('year' FROM y)::int AS year
    , GREATEST(y                    , ad.start_day) AS start_day
    , LEAST   (y + interval '1 year', ad.end_day)   AS end_day
FROM   ad
    , generate_series(date_trunc('year', start_day::timestamp)  -- cast to ts here!
                    , date_trunc('year', end_day::timestamp)
                    , interval '1 year') y;

請注意，date_trunc()返回timestamptz輸入timestamptz和timestamp輸入timestamp。對於date輸入，它預設為timestamptz. 由於您似乎忽略了時區，因此date將timestamp顯式轉換為 ( start_day::timestamp)。

結果：

id | year |      start_day      |       end_day
----+------+---------------------+---------------------
 1 | 2003 | 2003-06-07 00:00:00 | 2004-01-01 00:00:00
 1 | 2004 | 2004-01-01 00:00:00 | 2004-10-01 00:00:00
 2 | 2003 | 2003-06-07 00:00:00 | 2003-06-08 00:00:00
 3 | 2003 | 2003-06-07 00:00:00 | 2003-10-01 00:00:00
 4 | 2003 | 2003-06-07 00:00:00 | 2004-01-01 00:00:00
 4 | 2004 | 2004-01-01 00:00:00 | 2005-01-01 00:00:00
 4 | 2005 | 2005-01-01 00:00:00 | 2006-01-01 00:00:00
 4 | 2006 | 2006-01-01 00:00:00 | 2006-10-01 00:00:00

如果您從結果創建一個新表，我建議(id, year)作為主鍵。

旁白：這不是接線員：（||'_'||也不是可愛的小臉）。它是 2 個連接運算符||和一個字元串文字'_'。

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