SQL 日曆報表與季度日期計算

– 7 分鐘閱讀

在資料函式庫應用中，日期處理是不可或缺的一環。本文將探討兩種常見的日期處理場景：生成日曆報表以及計算季度起始和結束日期。針對不同資料函式庫系統，提供對應的 SQL 語法和實作細節，讓開發者能根據實際環境選擇最佳方案。首先，我們將介紹如何使用 SQL 生成包含星期和日期的日曆報表，並以 Oracle 語法為例，逐步講解如何透過 CTE 和日期函式構建報表。接著，我們將探討如何計算特定年份的季度起始和結束日期，並提供 DB2、PostgreSQL、MySQL 和 SQL Server 等資料函式庫的實作範例，說明如何運用遞迴 CTE 和日期函式來達成目標。

建立日曆報表

在許多應用場景中，建立日曆報表是一項常見需求。無論是用於排程、時間管理還是資料分析，日曆報表都能夠提供直觀且有用的資訊。本篇文章將介紹如何使用 SQL 來建立一個日曆報表，涵蓋 Oracle、MySQL、PostgreSQL 和 SQL Server 等多種資料函式倉管理系統。

Oracle 解決方案

首先，我們以 Oracle 為例。我們的目標是生成一個包含當前月份每天日期的日曆報表，並將其按照週次（ISO 週）進行分組。

步驟一：生成當前月份的日期

WITH x AS (
  SELECT *
  FROM (
    SELECT TRUNC(SYSDATE, 'mm') + LEVEL - 1 dy,
           TO_CHAR(TRUNC(SYSDATE, 'mm') + LEVEL - 1, 'iw') wk,
           TO_CHAR(TRUNC(SYSDATE, 'mm') + LEVEL - 1, 'dd') dm,
           TO_NUMBER(TO_CHAR(TRUNC(SYSDATE, 'mm') + LEVEL - 1, 'd')) dw,
           TO_CHAR(TRUNC(SYSDATE, 'mm') + LEVEL - 1, 'mm') curr_mth,
           TO_CHAR(SYSDATE, 'mm') mth
    FROM dual
    CONNECT BY LEVEL <= 31
  )
  WHERE curr_mth = mth
)
SELECT *
FROM x;

#### 內容解密：

  • 使用 TRUNC(SYSDATE, 'mm') 取得當前月份的第一天。
  • CONNECT BY LEVEL <= 31 生成從 1 到 31 的數字序列，用於遍歷當前月份的每一天。
  • TO_CHAR 和 TO_NUMBER 函式用於格式化日期，提取所需的資訊，如星期幾（dw）、日期（dm）和 ISO 週（wk）。

步驟二：轉換日期為日曆格式

WITH x AS (
  -- 與步驟一相同
)
SELECT wk,
       CASE dw WHEN 2 THEN dm END AS Mo,
       CASE dw WHEN 3 THEN dm END AS Tu,
       CASE dw WHEN 4 THEN dm END AS We,
       CASE dw WHEN 5 THEN dm END AS Th,
       CASE dw WHEN 6 THEN dm END AS Fr,
       CASE dw WHEN 7 THEN dm END AS Sa,
       CASE dw WHEN 1 THEN dm END AS Su
FROM x;

#### 內容解密：

  • 使用 CASE 表示式根據 dw（星期幾）將 dm（日期）分配到對應的星期欄位中。

步驟三：聚合結果按週顯示

WITH x AS (
  -- 與步驟一相同
)
SELECT MAX(CASE dw WHEN 2 THEN dm END) Mo,
       MAX(CASE dw WHEN 3 THEN dm END) Tu,
       MAX(CASE dw WHEN 4 THEN dm END) We,
       MAX(CASE dw WHEN 5 THEN dm END) Th,
       MAX(CASE dw WHEN 6 THEN dm END) Fr,
       MAX(CASE dw WHEN 7 THEN dm END) Sa,
       MAX(CASE dw WHEN 1 THEN dm END) Su
FROM x
GROUP BY wk
ORDER BY wk;

#### 內容解密：

  • 使用 MAX 聚合函式和 GROUP BY wk 將每週的日期聚合成一行。
  • ORDER BY wk 確保結果按 ISO 周次順序排列。

其他資料函式倉管理系統的解決方案

對於 MySQL、PostgreSQL 和 SQL Server，可以採用類別似的方法，但需要使用各自的日期函式。例如，在 SQL Server 中，可以使用 DATEADD、DAY 和 DATEPART 等函式來實作類別似的功能。

WITH x(dy, dm, mth, dw, wk) AS (
  SELECT dy,
         DAY(dy) dm,
         DATEPART(m, dy) mth,
         DATEPART(dw, dy) dw,
         CASE WHEN DATEPART(dw, dy) = 1 THEN DATEPART(ww, dy) - 1 ELSE DATEPART(ww, dy) END wk
  FROM (
    SELECT DATEADD(day, -DAY(GETDATE()) + 1, GETDATE()) dy
    FROM t1
  ) x
  UNION ALL
  SELECT DATEADD(d, 1, dy), DAY(DATEADD(d, 1, dy)), mth,
         DATEPART(dw, DATEADD(d, 1, dy)),
         CASE WHEN DATEPART(dw, DATEADD(d, 1, dy)) = 1 THEN DATEPART(wk, DATEADD(d, 1, dy)) - 1 ELSE DATEPART(wk, DATEADD(d, 1, dy)) END
  FROM x
  WHERE DATEPART(m, DATEADD(d, 1, dy)) = mth
)
SELECT MAX(CASE dw WHEN 2 THEN dm END) Mo,
       MAX(CASE dw WHEN 3 THEN dm END) Tu,
       MAX(CASE dw WHEN 4 THEN dm END) We,
       MAX(CASE dw WHEN 5 THEN dm END) Th,
       MAX(CASE dw WHEN 6 THEN dm END) Fr,
       MAX(CASE dw WHEN 7 THEN dm END) Sa,
       MAX(CASE dw WHEN 1 THEN dm END) Su
FROM x
GROUP BY wk
ORDER BY wk;

#### 圖表翻譯：

此查詢生成一個日曆報表，其中包含當前月份的日期，按週次分組。每行代表一個星期，列代表星期幾（Mo 到 Su）。透過使用遞迴公用表表達式（CTE）和日期函式，能夠動態地生成所需的報表。

日期處理技術：產生季度起始與結束日期

在處理日期相關的資料時，經常需要計算特定年份中各季度的起始與結束日期。本文將介紹如何使用SQL查詢來實作這一功能，並提供不同資料函式倉管理系統（RDBMS）的具體實作範例。

問題描述

給定一個年份，需要傳回該年份中四個季度的起始與結束日期。

解決方案

目標結果

查詢結果應包含每個季度的起始日期（Q_START）與結束日期（Q_END），如以下範例所示：

| QTR | Q_START | Q_END | |

–|

-|

-| | 1 | 2020-01-01 | 2020-03-31 | | 2 | 2020-04-01 | 2020-06-30 | | 3 | 2020-07-01 | 2020-09-30 | | 4 | 2020-10-01 | 2020-12-31 |

不同RDBMS的實作方法

DB2

使用ROW_NUMBER() OVER函式生成四個行，並利用日期函式計算每個季度的起始與結束日期。

SELECT QUARTER(dy-1 DAY) QTR,
       dy-3 MONTH Q_start,
       dy-1 DAY Q_end
FROM (
  SELECT (CURRENT_DATE -
          (DAYOFYEAR(CURRENT_DATE)-1) DAY +
          (rn*3) MONTH) dy
  FROM (
    SELECT ROW_NUMBER()OVER() rn
    FROM emp
    FETCH FIRST 4 ROWS ONLY
  ) x
) y

PostgreSQL

使用遞迴CTE（Common Table Expression）生成四個季度起始日期，並計算對應的結束日期。

WITH RECURSIVE x (dy, cnt) AS (
  SELECT CURRENT_DATE - CAST(EXTRACT(DAY FROM CURRENT_DATE) AS INTEGER) + 1,
         id
  FROM t1
  UNION ALL
  SELECT CAST(dy + INTERVAL '3 months' AS DATE),
         cnt + 1
  FROM x
  WHERE cnt + 1 <= 4
)
SELECT CAST(dy - INTERVAL '3 months' AS DATE) AS Q_start,
       dy - 1 AS Q_end
FROM x;
MySQL

同樣使用遞迴CTE生成四個季度起始日期，並利用ADDDATE函式計算結束日期。

WITH RECURSIVE x (dy, cnt) AS (
  SELECT ADDDATE(CURRENT_DATE, (-DAYOFYEAR(CURRENT_DATE)) + 1),
         id
  FROM t1
  UNION ALL
  SELECT ADDDATE(dy, INTERVAL 3 MONTH),
         cnt + 1
  FROM x
  WHERE cnt + 1 <= 4
)
SELECT QUARTER(ADDDATE(dy, -1)) QTR,
       DATE_ADD(dy, INTERVAL -3 MONTH) Q_start,
       ADDDATE(dy, -1) Q_end
FROM x
ORDER BY 1;
SQL Server

使用遞迴CTE生成四個季度起始日期，並利用DATEADD函式計算起始與結束日期。

WITH x (dy, cnt) AS (
  SELECT DATEADD(d, -(DATEPART(dy, GETDATE()) - 1), GETDATE()),
         1
  FROM t1
  UNION ALL
  SELECT DATEADD(m, 3, dy),
         cnt + 1
  FROM x
  WHERE cnt + 1 <= 4
)
SELECT DATEPART(q, DATEADD(d, -1, dy)) QTR,
       DATEADD(m, -3, dy) Q_start,
       DATEADD(d, -1, dy) Q_end
FROM x
ORDER BY 1;

程式碼解析

DB2實作解析

首先，使用ROW_NUMBER() OVER函式生成四個連續的行。

SELECT ROW_NUMBER()OVER() rn
FROM emp
FETCH FIRST 4 ROWS ONLY;

接著，計算每個季度的起始日期。

SELECT (CURRENT_DATE -
        (DAYOFYEAR(CURRENT_DATE)-1) DAY +
        (rn*3) MONTH) dy
FROM (...) x;

最後，計算每個季度的起始與結束日期。

SELECT QUARTER(dy-1 DAY) QTR,
       dy-3 MONTH Q_start,
       dy-1 DAY Q_end
FROM (...) y;

PostgreSQL實作解析

使用遞迴CTE生成四個季度起始日期，並計算對應的結束日期。

WITH RECURSIVE x (dy, cnt) AS (
  -- anchor query
  SELECT CURRENT_DATE - CAST(EXTRACT(DAY FROM CURRENT_DATE) AS INTEGER) + 1,
         id
  FROM t1
  UNION ALL
  -- recursive query
  SELECT CAST(dy + INTERVAL '3 months' AS DATE),
         cnt + 1
  FROM x
  WHERE cnt + 1 <= 4
)
SELECT CAST(dy - INTERVAL '3 months' AS DATE) AS Q_start,
       dy - 1 AS Q_end
FROM x;

季度日期的計算與實作

在處理財務報表或時間序列分析時，季度日期的計算是一個常見的需求。本文將介紹如何在不同的資料函式庫系統中（包括DB2、Oracle、PostgreSQL、MySQL和SQL Server）計算季度的起始和結束日期。

問題描述

給定一個年份和季度（格式為YYYYQ），需要傳回該季度的起始和結束日期。

解決方案

關鍵步驟

  1. 提取年份和季度：從輸入的YYYYQ格式中提取年份和季度。
  2. 計算季度結束月份：將季度乘以3，得到季度的結束月份。
  3. 計算季度起始和結束日期：根據季度的結束月份，計算季度的起始和結束日期。

各資料函式庫系統的實作

DB2
select (q_end-2 month) q_start,
       (q_end+1 month)-1 day q_end
from (
  select date(substr(cast(yrq as char(4)),1,4) ||'-'||
              rtrim(cast(mod(yrq,10)*3 as char(2))) ||'-1') q_end
  from (
    select 20051 yrq from t1 union all
    select 20052 yrq from t1 union all
    select 20053 yrq from t1 union all
    select 20054 yrq from t1
  ) x
) y;

內容解密：

  • 首先，透過SUBSTR函式提取年份，並使用MOD函式計算季度。
  • 然後，根據年份和季度結束月份，構建季度結束日期。
  • 最後，透過日期運算，計算出季度的起始日期。
Oracle
select add_months(q_end,-2) q_start,
       last_day(q_end) q_end
from (
  select to_date(substr(yrq,1,4)||mod(yrq,10)*3,'yyyymm') q_end
  from (
    select 20051 yrq from dual union all
    select 20052 yrq from dual union all
    select 20053 yrq from dual union all
    select 20054 yrq from dual
  ) x
) y;

內容解密：

  • 使用SUBSTR函式提取年份，並使用MOD函式計算季度。
  • 使用TO_DATE函式將年份和季度結束月份轉換為日期格式。
  • 使用ADD_MONTHS函式計算季度的起始日期，並使用LAST_DAY函式計算季度的結束日期。
PostgreSQL
select date(q_end-(2*interval '1 month')) as q_start,
       date(q_end+interval '1 month'-interval '1 day') as q_end
from (
  select to_date(substr(yrq,1,4)||mod(yrq,10)*3,'yyyymm') as q_end
  from (
    select 20051 as yrq from t1 union all
    select 20052 as yrq from t1 union all
    select 20053 as yrq from t1 union all
    select 20054 as yrq from t1
  ) x
) y;

內容解密：

  • 使用SUBSTR函式提取年份，並使用MOD函式計算季度。
  • 使用TO_DATE函式將年份和季度結束月份轉換為日期格式。
  • 使用日期間隔運算，計算季度的起始和結束日期。
MySQL
select date_add(
         adddate(q_end,-day(q_end)+1),
         interval -2 month) q_start,
       q_end
from (
  select last_day(
           str_to_date(
             concat(
               substr(yrq,1,4),mod(yrq,10)*3),'%Y%m')) q_end
  from (
    select 20051 as yrq from t1 union all
    select 20052 as yrq from t1 union all
    select 20053 as yrq from t1 union all
    select 20054 as yrq from t1
  ) x
) y;

內容解密：

  • 使用SUBSTR函式提取年份，並使用MOD函式計算季度。
  • 使用STR_TO_DATE函式將年份和季度結束月份轉換為日期格式。
  • 使用LAST_DAY函式計算季度的結束日期，並透過日期運算，計算季度的起始日期。
SQL Server
select dateadd(m,-2,q_end) q_start,
       dateadd(d,-1,dateadd(m,1,q_end)) q_end
from (
  select cast(substring(cast(yrq as varchar),1,4)+'-'+
              cast(yrq%10*3 as varchar)+'-1' as datetime) q_end
  from (
    select 20051 as yrq from t1 union all
    select 20052 as yrq from t1 union all
    select 20053 as yrq from t1 union all
    select 20054 as yrq from t1
  ) x
) y;

內容解密：

  • 使用SUBSTRING函式提取年份，並使用取模運算子（%）計算季度。
  • 將年份和季度結束月份轉換為日期時間格式。
  • 使用DATEADD函式計算季度的起始和結束日期。
玄貓 BlackCat

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。