用 SQL 製作本月月曆

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

也許第一眼很難想像怎樣才做到吧。但其實相當簡單的，用 MySQL 寫大概二十行左右便能寫好。

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, 以便萬一需要時，較易在其他 DB 重現。

以上的 Query 基本上分為兩步：

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

因為這是 Recursive CTE 的經典用法，同時也是 ANSI Standard, 所以便採用了。不過要留意你所使用的 DBMS 版本是否支援，例如 MySQL 要 ≥ 8.0, Oracle 要 ≥11gR2.

如果不支援，那改用其他方法吧。例如 Oracle 可以用 connect by, PostgreSQL 可以用 generate_series()。

只要你得能到數列，那就能輕鬆轉換為 date series 了。日子 + 1, 2, 3, 4, 5 ... 日，就變成了 date series 了。

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
;

2. 行列轉換

用 CASE 建立新 columns 星期 日, 一, 二 ... 六。用 function dayofweek(dt) 獲知是星期幾，是不是和 column 名稱相符合。是的話 value 則是日子 dayofmonth(dt)，不是的話就 NULL (而我為了美觀，所以用 empty string '')

覺得自己可能解釋得不太好呢，那就改一改 Query 再截圖吧。

這是 group by 前的情況：

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
;

從上可見 week() 可以得知該日是一年內的第幾個星期（重點我們是需要它「同屬一個星期的日子都會得到相同的數值」這個特性！）

而同一個 week() 裡，最多只有一個星期日，一個星期一，一個星期二……

若我們 group by week() , 那麼如上圖的首5行，都是 week = 9，便會併成一行 (row)。

再選用恰當的 aggregate function, 這裡用的是 max()， 例如 column TUE 經過 max(1, ‘’, ‘’, ‘’, ‘’) ⇒ 1。

最後，就變成想要的模樣啦。

雖然製作一個月曆出來沒有用，但我覺得以上提到的兩點都算是做 SQL report / Data warehousing 的重要技巧。

有興趣的話，你也可以簡單地改進下上面的 SQL，例如將 SQL 弄得簡潔一些，和將當日（執行 SQL 的日子）附以星號 * 標示。