iT邦幫忙

9

從問題理解與活用SQL語法-補充:取得多組日期區間內的所有日期 (CTE 遞迴查詢)

回想距離上次鐵人賽到現在已經快一年了
後來工作經常用到蠻多 CTE 遞迴查詢
剛好在新的鐵人賽開始前另外筆記一下最近遇到蠻好用的需求:取得多組日期區間內的所有日期
可惜找不到方法補充在原系列文

一、最近工作遇到的需求實例

有一個活動資料表,有三個欄位:活動名稱、活動開始日、活動結束日
活動頁面有一個行事曆,需要標示某個日期區間裡,有活動的所有日期

例如活動資料表當中,八月有這些活動:

活動名稱 活動開始日期 活動結束日期
test01 2020-08-05 2020-08-09
test02 2020-08-07 2020-08-11
test03 2020-08-14 2020-08-16

八月期間,所有需要標示有舉辦活動的日期:

日期
2020-08-05
2020-08-06
2020-08-07
2020-08-08
2020-08-09
2020-08-10
2020-08-11
2020-08-14
2020-08-15
2020-08-16

二、SQL

playground

1.表結構與資料

CREATE TABLE Item (
  Item_No int PRIMARY KEY IDENTITY ,
  ActivityName NVARCHAR(200),
  StartDate DATETIME,
  EndDate DATETIME,
)

INSERT INTO Item 
(ActivityName, StartDate, EndDate)
VALUES
('test01', '2020-08-05', '2020-08-09'),
('test02', '2020-08-07', '2020-08-11'),
('test03', '2020-07-29', '2020-08-03'),
('test04', '2020-08-29', '2020-09-03'),
('test05', '2020-09-10', '2020-09-13'),
('test06', '2020-08-14', '2020-08-16')

2.取得八月所有活動,舉辦的所有日期

WITH dates AS (
        SELECT StartDate AS [Date], ActivityName, StartDate, EndDate
        FROM Item
        WHERE (StartDate >= '2020-08-01' AND StartDate <= '2020-08-31') OR
              (EndDate >= '2020-08-01' AND EndDate <= '2020-08-31' )
        UNION ALL
        SELECT [Date] = DATEADD(DAY, 1, [Date]), ActivityName, StartDate, EndDate
        FROM dates
        WHERE Date < EndDate
) 

SELECT ActivityName, 
       CAST([Date] AS DATE) AS [Date], 
       CAST(StartDate AS DATE) AS StartDate,
       CAST(EndDate AS DATE) AS EndDate
FROM dates
WHERE [Date] >= '2020-08-01' AND [Date] <= '2020-08-31'
ORDER BY StartDate, [Date];
ActivityName Date StartDate EndDate
test03 2020-08-01 2020-07-29 2020-08-03
test03 2020-08-02 2020-07-29 2020-08-03
test03 2020-08-03 2020-07-29 2020-08-03
test01 2020-08-05 2020-08-05 2020-08-09
test01 2020-08-06 2020-08-05 2020-08-09
test01 2020-08-07 2020-08-05 2020-08-09
test01 2020-08-08 2020-08-05 2020-08-09
test01 2020-08-09 2020-08-05 2020-08-09
test02 2020-08-07 2020-08-07 2020-08-11
test02 2020-08-08 2020-08-07 2020-08-11
test02 2020-08-09 2020-08-07 2020-08-11
test02 2020-08-10 2020-08-07 2020-08-11
test02 2020-08-11 2020-08-07 2020-08-11
test06 2020-08-14 2020-08-14 2020-08-16
test06 2020-08-15 2020-08-14 2020-08-16
test06 2020-08-16 2020-08-14 2020-08-16
test04 2020-08-29 2020-08-29 2020-09-03
test04 2020-08-30 2020-08-29 2020-09-03
test04 2020-08-31 2020-08-29 2020-09-03

3. 取得八月所有舉辦活動的日期

WITH dates AS (
        SELECT StartDate AS [Date], ActivityName, StartDate, EndDate
        FROM Item
        WHERE (StartDate >= '2020-08-01' AND StartDate <= '2020-08-31') OR
              (EndDate >= '2020-08-01' AND EndDate <= '2020-08-31' )
        UNION ALL
        SELECT [Date] = DATEADD(DAY, 1, [Date]), ActivityName, StartDate, EndDate
        FROM dates
        WHERE Date < EndDate
) 
SELECT DISTINCT CAST([Date] AS DATE) AS [Date]
FROM dates
WHERE [Date] >= '2020-08-01' AND [Date] <= '2020-08-31'
ORDER BY [Date]
Date
2020-08-01
2020-08-02
2020-08-03
2020-08-05
2020-08-06
2020-08-07
2020-08-08
2020-08-09
2020-08-10
2020-08-11
2020-08-14
2020-08-15
2020-08-16
2020-08-29
2020-08-30
2020-08-31

三、CTE 遞迴查詢結構

WITH recursive_sql (column_list)
AS
(
    -- Anchor member
    初始化 SELECT ...
    UNION ALL
    -- Recursive member that references recursive_sql.
    遞迴查詢 SELECT...FROM recursive_sql
)

-- 呼叫遞迴查詢
SELECT *
FROM   recursive_sql

四、說明

  1. 第一次呼叫遞迴查詢時,會先執行上方的初始化 SELECT,取得所有的活動資料
  2. 接著將初始化 SELECT得到的資料,將活動開始日+1代入遞迴查詢 SELECT,開始遞迴查詢
  3. 直到[Date] = DATEADD(DAY, 1, [Date]) 帶入遞迴查詢 SELECT時,[Date] < EndDate,遞迴查詢結果為空,結束遞迴查詢

如果其他大大有更好的寫法,歡迎指教與提出建議


1 則留言

7
一級屠豬士
iT邦大師 1 級 ‧ 2020-08-12 23:59:00

寫得不錯,例子也蠻好的.但是我有注意到,你的SQL Command 結束時都沒有使用 ;
似乎一些使用SQL Server 的,常會有此習慣.

我用 PostgreSQL 的 Set Returning Functions 其中
generate_series(start, stop, step interval)
https://www.postgresql.org/docs/current/functions-srf.html

注意到他是使用 timestamp or timestamptz, 所以我有使用轉型運算子 ::
這也是一種方式,提供補充參考.

create table it200812 (
  id integer not null generated always as identity primary key
, actname text not null
, sdate date not null
, edate date not null
);

insert into it200812 (actname, sdate, edate) values
('test01', '2020-08-05', '2020-08-09'),
('test02', '2020-08-07', '2020-08-11'),
('test03', '2020-09-10', '2020-09-13'),
('test02', '2020-08-14', '2020-08-16');

select distinct generate_series(sdate::timestamp, edate, '1 day')::date as dt
  from it200812
 where sdate >= date '2020-08-01'
   and sdate <= date '2020-08-31'
 order by 1;
+------------+
|     dt     |
+------------+
| 2020-08-05 |
| 2020-08-06 |
| 2020-08-07 |
| 2020-08-08 |
| 2020-08-09 |
| 2020-08-10 |
| 2020-08-11 |
| 2020-08-14 |
| 2020-08-15 |
| 2020-08-16 |
+------------+
(10 rows)
通靈亡 iT邦研究生 5 級 ‧ 2020-08-13 08:47:20 檢舉

謝謝屠豬大提供pg的SQL寫法

分號的部分聽大大一說,我去查一下官方文件
才發現T-SQL大多數的陳述式(statement)結尾不需要使用分號

不過依照ANSI SQL-92的標準,結尾是需要加上分號
T-SQL的CTE的語法,也應該在WITH前面加上分號會比較好

這篇的例子,有多了9月部分來做對照,你是有用心安排的.
可以多寫一些來分享.另外PostgreSQLTW 有辦小講堂,
題目不限在PG,歡迎來分享一堂.

至於分號方面,這是隨你個人實際使用.我是有遇到一些不寫分號的,
然後要轉到其他資料庫時,會有些小小麻煩.

通靈亡 iT邦研究生 5 級 ‧ 2020-08-13 16:10:57 檢舉

真的,像之前工作需要,寫PL/SQL的語法有發現語法嚴謹許多
有分號還有斜線

剛才發現還要考慮到跨月的問題(7跨8 與 8跨9),已經補上了
處理日期時間的問題,條件真的要寫的蠻嚴謹的
謝謝屠豬大的回饋

我要留言

立即登入留言