2025年07月06日玄貓（BlackCat）

資料函式庫日期計算技巧大全

本文探討如何在各種資料函式庫系統中進行日期計算，包含計算星期出現次數、日期差異以及使用視窗函式進行日期分析等技巧。文章涵蓋 Oracle、PostgreSQL、SQL Server 和 MySQL 等主流資料函式庫，並提供詳細的 SQL 範例和解說，幫助讀者掌握日期計算的核心概念和實務應用。

資料函式庫 SQL

SQL 日期計算視窗函式遞迴查詢資料函式倉管理系統

在資料函式庫應用中，日期計算是常見且重要的操作。不同資料函式庫系統提供的日期函式和語法略有不同，本文將介紹如何在 Oracle、PostgreSQL、SQL Server 和 MySQL 中有效地進行日期計算，包含計算星期幾出現次數、計算日期差異以及使用視窗函式進行日期分析等技巧。這些技巧能協助開發者更有效率地處理日期相關資料，並應用於各種報表統計和資料分析場景。

統計一年中各個星期幾的出現次數

在多數資料函式倉管理系統中，可以利用遞迴查詢或特定的函式來產生一年中的每一天，並計算每個星期幾的出現次數。

PostgreSQL

利用 generate_series 函式產生一年中的每一天，然後使用 to_char 函式取得星期幾的名稱，最後進行分組統計。

SELECT 
    to_char((date_trunc('year', current_date) + gs.id - 1), 'DAY') AS weekday,
    COUNT(*) AS count
FROM 
    generate_series(1, (date_trunc('year', current_date + interval '1 year') - date_trunc('year', current_date))) gs(id)
GROUP BY 
    to_char((date_trunc('year', current_date) + gs.id - 1), 'DAY')

內容解密：

date_trunc('year', current_date) 用於取得當年的第一天。
generate_series 函式用於產生從1到當年天數的序列，代表每一天。
to_char 函式將日期轉換成星期幾的名稱。
最後根據星期幾的名稱進行分組並統計出現次數。

SQL Server

使用遞迴CTE（Common Table Expression）來產生一年中的每一天，並利用 DATENAME 函式取得星期幾的名稱。

WITH x (start_date, end_date) AS (
    SELECT 
        start_date,
        DATEADD(year, 1, start_date) AS end_date
    FROM 
        (
            SELECT 
                CAST(CAST(YEAR(GETDATE()) AS VARCHAR) + '-01-01' AS DATETIME) AS start_date
            FROM 
                t1
        ) tmp
    UNION ALL
    SELECT 
        DATEADD(day, 1, start_date),
        end_date
    FROM 
        x
    WHERE 
        DATEADD(day, 1, start_date) < end_date
)
SELECT 
    DATENAME(dw, start_date) AS weekday,
    COUNT(*) AS count
FROM 
    x
GROUP BY 
    DATENAME(dw, start_date)
OPTION (MAXRECURSION 366)

內容解密：

使用遞迴CTE x 來產生從當年第一天到下一年第一天前的每一天。
DATENAME(dw, start_date) 用於取得日期對應的星期幾名稱。
最後根據星期幾的名稱進行分組並統計出現次數。
OPTION (MAXRECURSION 366) 用於設定遞迴的最大深度，以避免遞迴次數過多導致錯誤。

MySQL

利用一個具有足夠多行的表格（如t500）來產生一年中的每一天，並使用 DATE_FORMAT 函式取得星期幾的名稱。

SELECT 
    DATE_FORMAT(DATE_ADD(CAST(CONCAT(YEAR(CURRENT_DATE), '-01-01') AS DATE), INTERVAL t500.id - 1 DAY), '%W') AS weekday,
    COUNT(*) AS count
FROM 
    t500
WHERE 
    t500.id <= DATEDIFF(CAST(CONCAT(YEAR(CURRENT_DATE) + 1, '-01-01') AS DATE), CAST(CONCAT(YEAR(CURRENT_DATE), '-01-01') AS DATE))
GROUP BY 
    DATE_FORMAT(DATE_ADD(CAST(CONCAT(YEAR(CURRENT_DATE), '-01-01') AS DATE), INTERVAL t500.id - 1 DAY), '%W')

內容解密：

使用 CONCAT 和 CAST 組合來取得當年第一天的日期。
利用 DATE_ADD 和 t500.id 來產生一年中的每一天。
DATE_FORMAT 函式用於將日期轉換成星期幾的名稱。
最後根據星期幾的名稱進行分組並統計出現次數。

Oracle

同樣可以使用遞迴查詢或連線查詢（CONNECT BY）來達成目的。

WITH x AS (
    SELECT 
        LEVEL AS lvl
    FROM 
        dual
    CONNECT BY 
        LEVEL <= (ADD_MONTHS(TRUNC(SYSDATE, 'y'), 12) - TRUNC(SYSDATE, 'y'))
)
SELECT 
    TO_CHAR(TRUNC(SYSDATE, 'y') + lvl - 1, 'DAY') AS weekday,
    COUNT(*) AS count
FROM 
    x
GROUP BY 
    TO_CHAR(TRUNC(SYSDATE, 'y') + lvl - 1, 'DAY')

內容解密：

使用 CONNECT BY 和 LEVEL 來產生從1到當年天數的序列。
TRUNC(SYSDATE, 'y') 用於取得當年的第一天。
利用產生的序列來計算每一天的日期，並轉換成星期幾的名稱。
最後根據星期幾的名稱進行分組並統計出現次數。

這些查詢都能有效地統計出一年中各個星期幾的出現次數。

統計一年中各個工作日的出現次數

在處理日期相關的資料時，我們經常需要統計一年中各個工作日（星期一至星期日）的出現次數。不同的資料函式庫系統提供了不同的功能來實作這一需求。以下將介紹如何在Oracle、PostgreSQL和SQL Server中完成這項任務。

Oracle

在Oracle中，可以使用遞迴查詢或利用現有的表來生成一年中的所有日期。對於Oracle 9i及以後版本，可以使用遞迴WITH子句來生成日期：

WITH x AS (
  SELECT LEVEL lvl
  FROM dual
  CONNECT BY LEVEL <= (ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y'))
)
SELECT TO_CHAR(TRUNC(SYSDATE,'y') + lvl - 1, 'DAY'), COUNT(*)
FROM x
GROUP BY TO_CHAR(TRUNC(SYSDATE,'y') + lvl - 1, 'DAY')

內容解密：

TRUNC(SYSDATE, 'y') 用於取得當前年份的第一天。
ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y') 計算當前年份的天數。
使用CONNECT BY LEVEL遞迴生成從1到當年天數的序列。
將生成的序列加到當年第一天，得到當年每一天的日期。
使用TO_CHAR將日期轉換為星期名稱，並進行分組統計。

對於Oracle 8i及更早版本，可以使用一個至少有366行的表來實作相同的功能：

SELECT TO_CHAR(TRUNC(SYSDATE,'y') + ROWNUM - 1, 'DAY'), COUNT(*)
FROM t500
WHERE ROWNUM <= (ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y'))
GROUP BY TO_CHAR(TRUNC(SYSDATE,'y') + ROWNUM - 1, 'DAY')

內容解密：

t500是一個至少包含366行的表，用於生成行號。
ROWNUM用於限制生成的行數為當年的天數。
其他部分與前面的查詢相同。

PostgreSQL

在PostgreSQL中，可以使用DATE_TRUNC函式來取得當年第一天，然後利用GENERATE_SERIES函式生成當年的每一天：

SELECT TO_CHAR(CAST(DATE_TRUNC('year', CURRENT_DATE) AS DATE) + gs.id - 1, 'DAY'), COUNT(*)
FROM GENERATE_SERIES(1,366) gs(id)
WHERE gs.id <= (CAST(DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '12 month' AS DATE) - 
                CAST(DATE_TRUNC('year', CURRENT_DATE) AS DATE))
GROUP BY TO_CHAR(CAST(DATE_TRUNC('year', CURRENT_DATE) AS DATE) + gs.id - 1, 'DAY')

內容解密：

DATE_TRUNC('year', CURRENT_DATE) 取得當前年份的第一天。
GENERATE_SERIES(1,366) 生成從1到366的序列，用於表示當年的每一天。
將序列中的數字加到當年第一天，生成當年的每一天。
使用TO_CHAR將日期轉換為星期名稱，並進行分組統計。

SQL Server

在SQL Server中，可以使用遞迴CTE（Common Table Expression）來生成當年的每一天：

WITH x(start_date, end_date) AS (
  SELECT start_date, DATEADD(YEAR, 1, start_date) AS end_date
  FROM (
    SELECT CAST(CAST(YEAR(GETDATE()) AS VARCHAR) + '-01-01' AS DATETIME) AS start_date
    FROM t1
  ) tmp
  UNION ALL
  SELECT DATEADD(DAY, 1, start_date), end_date
  FROM x
  WHERE DATEADD(DAY, 1, start_date) < end_date
)
SELECT DATENAME(DW, start_date), COUNT(*)
FROM x
GROUP BY DATENAME(DW, start_date)
OPTION (MAXRECURSION 366)

內容解密：

首先計算當年第一天，並將其與下一年的第一天作為結束日期。
使用遞迴CTE逐日增加日期，直到達到結束日期。
使用DATENAME函式將日期轉換為星期名稱，並進行分組統計。
OPTION (MAXRECURSION 366)用於允許遞迴最多366次，以涵蓋閏年的情況。

資料函式庫中計算當前記錄與下一筆記錄之間的日期差異

在處理資料函式庫中的日期欄位時，經常需要計算當前記錄與下一筆記錄之間的日期差異。本文將介紹如何使用不同的資料函式倉管理系統（DBMS）來實作這一功能。

解決方案

本問題的解決方案是找出當前員工被聘用後的下一個最早的聘用日期（HIREDATE）。然後，使用類別似於「Recipe 8.2」中的技術來計算日期差異（以天為單位）。

DB2

使用標量子查詢來找出相對於當前 HIREDATE 的下一個 HIREDATE。然後，使用 DAYS 函式來計算日期差異（以天為單位）：

SELECT x.*,
       DAYS(x.next_hd) - DAYS(x.hiredate) AS diff
FROM (
  SELECT e.deptno, e.ename, e.hiredate,
         LEAD(hiredate) OVER (ORDER BY hiredate) AS next_hd
  FROM emp e
  WHERE e.deptno = 10
) x

MySQL 和 SQL Server

使用 LEAD 函式來存取下一筆記錄。這裡使用了 SQL Server 版本的 DATEDIFF 函式：

SELECT x.ename, x.hiredate, x.next_hd,
       DATEDIFF(x.next_hd, x.hiredate, DAY) AS diff
FROM (
  SELECT deptno, ename, hiredate,
         LEAD(hiredate) OVER (ORDER BY hiredate) AS next_hd
  FROM emp e
  WHERE deptno = 10
) x

MySQL 使用者可以省略第一個引數（“day”），並交換剩下的兩個引數的順序：

SELECT x.ename, x.hiredate, x.next_hd,
       DATEDIFF(x.next_hd, x.hiredate) AS diff
FROM (
  SELECT deptno, ename, hiredate,
         LEAD(hiredate) OVER (ORDER BY hiredate) AS next_hd
  FROM emp e
) x
WHERE x.deptno = 10

Oracle

使用視窗函式 LEAD OVER 來存取相對於當前記錄的下一個 HIREDATE，從而促進日期相減的運算：

SELECT ename, hiredate, next_hd,
       next_hd - hiredate AS diff
FROM (
  SELECT deptno, ename, hiredate,
         LEAD(hiredate) OVER (ORDER BY hiredate) AS next_hd
  FROM emp
)
WHERE deptno = 10

PostgreSQL

使用標量子查詢來找出相對於當前 HIREDATE 的下一個 HIREDATE。然後，使用簡單的相減運算來計算日期差異（以天為單位）：

SELECT x.*,
       x.next_hd - x.hiredate AS diff
FROM (
  SELECT e.deptno, e.ename, e.hiredate,
         LEAD(hiredate) OVER (ORDER BY hiredate) AS next_hd
  FROM emp e
  WHERE e.deptno = 10
) x

討論

儘管語法有所不同，但所有這些解決方案的方法都是相同的：使用視窗函式 LEAD，然後使用「Recipe 8.2」中描述的技術來計算兩個日期之間的差異（以天為單位）。

處理重複的日期

在 Oracle 中，如果存在重複的 HIREDATE，則需要特別處理。預設情況下，LEAD 函式不會跳過重複的日期。為瞭解決這個問題，可以將 LEAD 函式與 COUNT 和 ROW_NUMBER 函式結合使用，以正確地計算日期差異。

日期運算的深度解析與實務應用

在處理資料函式庫中的日期資料時，我們經常會遇到各種複雜的運算需求。以下將探討如何使用 SQL 進行日期運算，並提供實務上的應用範例。

使用視窗函式進行日期分析

視窗函式是 SQL 中非常強大的工具，可以用於進行複雜的日期分析。以下範例展示如何使用 COUNT OVER 和 ROW_NUMBER OVER 來計算每個部門中員工的入職日期分佈。

select deptno, ename, hiredate,
       count(*) over (partition by hiredate) cnt,
       row_number() over (partition by hiredate order by empno) rn
from emp
where deptno = 10;

內容解密：

視窗函式 COUNT OVER：計算每個 hiredate 出現的次數，並將結果傳回給每一行。
ROW_NUMBER OVER：根據 empno 對每個 hiredate 內的員工進行排序。
結果解讀：對於重複的 hiredate，cnt 將傳回相同的計數，而 rn 將傳回每個員工在該日期內的排序。

結合 LEAD 函式進行日期間隔計算

進一步地，我們可以結合 LEAD 函式來計算每個員工入職日期與下一個入職日期之間的間隔。

select deptno, ename, hiredate,
       cnt - rn + 1 as distance_to_next,
       lead(hiredate, cnt - rn + 1) over (order by hiredate) as next_hd
from (
  select deptno, ename, hiredate,
         count(*) over (partition by hiredate) cnt,
         row_number() over (partition by hiredate order by empno) rn
  from emp
  where deptno = 10
);

內容解密：

distance_to_next 計算：透過 cnt - rn + 1 計算當前員工入職日期與下一個不同入職日期之間的間隔。
LEAD 函式應用：根據計算出的間隔，使用 LEAD 函式取得下一個入職日期。
結果解讀：結果顯示了每個員工入職日期與下一個不同入職日期之間的間隔及下一個入職日期。

日期資料型別的特性與常見問題

日期資料型別具有比簡單的數字型別更複雜的結構，因此在處理時需要特別注意其特性與相關函式的使用。

不同資料函式庫廠商的實作差異：儘管核心功能相似，不同資料函式庫系統在日期函式的實作上仍存在差異。
常見函式與操作：掌握核心的日期函式（如 LAST_DAY、DATE_TRUNC 等）對於處理日期資料至關重要。

日期操作的實務應用

在實際應用中，經常需要進行日期相關的操作，如判斷閏年、計算日期間隔等。以下範例展示如何判斷當前年份是否為閏年。

DB2 與 SQL Server 的實作

-- DB2
with x (dy, mth) as (
  select dy, month(dy)
  from (
    select (current_date - dayofyear(current_date) days + 1 days) + 1 months as dy
    from t1
  ) tmp1
  union all
  select dy + 1 days, mth
  from x
  where month(dy + 1 day) = mth
)
select max(day(dy))
from x;

-- SQL Server
select coalesce(day(cast(concat(year(getdate()), '-02-29') as date)), 28);

PostgreSQL 與 MySQL 的實作

-- PostgreSQL
select max(to_char(tmp2.dy + x.id, 'DD')) as dy
from (
  select dy, to_char(dy, 'MM') as mth
  from (
    select cast(cast(date_trunc('year', current_date) as date) + interval '1 month' as date) as dy
    from t1
  ) tmp1
) tmp2, generate_series(0, 29) x(id)
where to_char(tmp2.dy + x.id, 'MM') = tmp2.mth;

-- MySQL
select day(last_day(date_add(date_add(date_add(current_date, interval -dayofyear(current_date) day), interval 1 day), interval 1 month))) dy
from t1;

Oracle 的實作

select to_char(last_day(add_months(trunc(sysdate, 'y'), 1)), 'DD')
from t1;

內容解密：

核心思路：透過檢查二月的最後一天來判斷是否為閏年。
不同資料函式庫的實作：展示了在不同資料函式庫系統中如何使用各自提供的函式來達成相同的目標。