用 SQL 製作當月月曆

曾經閑得無聊用 SQL (MySQL & Oracle) 製作過月曆。分享一下這個小作品吧。效果圖如下：

2022年3月的月曆

初見或許難以想像其製作過程，但其實相當簡易，僅需用 MySQL 寫約二十行程式碼即可完成。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


with recursive seq (dt) as (
select  last_day(curdate() - interval 1 month) + interval 1 day as dt
union all
select  seq.dt + interval 1 day
  from  seq
 where  seq.dt < last_day(curdate())
)
select
        max(case when dayofweek(dt) = 1 then dayofmonth(dt) else '' end) as SUN,
        max(case when dayofweek(dt) = 2 then dayofmonth(dt) else '' end) as MON,
        max(case when dayofweek(dt) = 3 then dayofmonth(dt) else '' end) as TUE,
        max(case when dayofweek(dt) = 4 then dayofmonth(dt) else '' end) as WED,
        max(case when dayofweek(dt) = 5 then dayofmonth(dt) else '' end) as THU,
        max(case when dayofweek(dt) = 6 then dayofmonth(dt) else '' end) as FRI,
        max(case when dayofweek(dt) = 7 then dayofmonth(dt) else '' end) as SAT
  from  seq
 group  by week(seq.dt, 0)
;

我也曾用 Oracle 寫過，但遺憾地丟失了。然而，基於這樣的思維，不同的 DBMS 都可以實現。我寫 SQL 的時候，通常優先遵從 ANSI Standard，以便在需要時，能較簡單地在其他 DBMS 上重現。

以上的 Query 主要分為兩步驟：

1. 建立數列 `1, 2, 3, 4, 5 ...`

這是 Recursive CTE 的經典用法，且符合 ANSI Standard，因此我選擇採用。但要注意你所使用的 DBMS 版本是否支援，例如 MySQL 需要版本 ≥ 8.0，Oracle 需要版本 ≥ 11gR2。

如果不支援，可以選擇其他方法，例如 Oracle 可以用 connect by，PostgreSQL 可以用 generate_series()。

只要能得到連續數列，就能輕易地轉換為日期序列。日子 + 1, 2, 3, 4, 5 ... 日，即可轉換為日期序列。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


with recursive seq (dt) as (
select  last_day(curdate() - interval 1 month) + interval 1 day as dt
union all
select  seq.dt + interval '1' day
  from  seq
 where  seq.dt < last_day(curdate())
)
select  *
  from  seq
;

step1

2. 行列轉換

利用 CASE 來建立新的 columns，表示星期 日, 一, 二 ... 六。使用 function dayofweek(dt) 來獲得星期幾，並檢查是否與 column 名稱相符。若相符，value 則是日子 dayofmonth(dt)，否則就為 NULL（為了美觀，我使用了空字串 ''）。

若你覺得我解釋得不夠清楚，可以稍微修改 Query 並重新截圖。

這是 group by 前的情況：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


with recursive seq (dt) as (
select  last_day(curdate() - interval 1 month) + interval 1 day as dt
union all
select  seq.dt + interval '1' day
  from  seq
 where  seq.dt < last_day(curdate())
)
select  dt,
        week(dt, 0) as `week`,
        dayofweek(dt) as `dayofweek`,
        case when dayofweek(dt) = 1 then dayofmonth(dt) else '' end as SUN,
        case when dayofweek(dt) = 2 then dayofmonth(dt) else '' end as MON,
        case when dayofweek(dt) = 3 then dayofmonth(dt) else '' end as TUE,
        case when dayofweek(dt) = 4 then dayofmonth(dt) else '' end as WED,
        case when dayofweek(dt) = 5 then dayofmonth(dt) else '' end as THU,
        case when dayofweek(dt) = 6 then dayofmonth(dt) else '' end as FRI,
        case when dayofweek(dt) = 7 then dayofmonth(dt) else '' end as SAT
  from  seq
;

step2

如上所示，week() 可以得知該日是一年內的第幾週（我們需要的主要特性是「同一週的日期會得到相同的數值」！）

在同一個 week() 中，最多只有一個星期日、星期一、星期二……等。如果我們 group by week()，那麼像上圖的前五行，都是 week = 9，就會合併成一行。

再選擇適當的聚合函數，這裡我使用了 max()，例如 column TUE 經過 max(1, ‘’, ‘’, ‘’, ‘’) ⇒ 1。

最後，就完成了我們想要的月曆。

result

雖然製作月曆的實用性不高，但我認為以上提到的兩點都是做 SQL 報表或資料倉儲的重要技巧。

如果你有興趣，也可以嘗試改進上述的 SQL，例如簡化 SQL，或將當日（執行 SQL 的日期）以星號 * 標示。

1. 建立數列 1, 2, 3, 4, 5 ...

2. 行列轉換

1. 建立數列 `1, 2, 3, 4, 5 ...`