【序文】
13日の金曜日といえば映画が有名だが、「12日の木曜日」「14日の土曜日」もある。これ豆な。
それぐらい（？）13日といえば金曜日。不吉な組み合わせとされているが実は 13日は金曜日の場合が最も多い。
というのはトリビアネタとして有名か。
プログラムでそれを検証するネタはよくあるが、クエリ版は見たことがないので教材として取り上げてみる。
グレゴリオ暦についても考察する。
【環境】
| SQLServer | SQLCMD | PowerShell |
|---|---|---|
| 2017 | 14.0.1000.169 | 5.1.18362.145 |
| 設定はデフォルト。 |
【400年間全ての13日の列挙】
現在標準として使用されている暦であるところのグレゴリオ暦は 400年周期（後述）。
その中の全ての 13日の曜日を調べると金曜日が最も多い、という話。
総当たりで調べる方法は愚直とされているが、クエリでは寧ろそれが王道なのでその方向性で検証していく。
まず、400年間全ての 13日の列挙。これは CTE 一択だろう。
実装例は以下。開始年は 2000年とする。
-- 400年間全ての13日の列挙
DECLARE @StartYear INT = 2000; -- 開始年
;WITH [CTE_Src]([YMD]) AS ( -- @StartYear/01/13 ～ @StartYear + 399/12/13
SELECT DATEFROMPARTS(@StartYear, 1, 13) -- @StartYear/01/13
UNION ALL
SELECT DATEADD(month, 1, [YMD]) FROM [CTE_Src] -- 1ヶ月ずつ加算
WHERE [YMD] < DATEFROMPARTS(@StartYear + 399, 12, 13) -- ～ @StartYear + 399/12/13
)
SELECT [YMD] FROM [CTE_Src] -- @StartYear/01/13 ～ @StartYear + 399/12/13
OPTION (MAXRECURSION 0)
;
GO
実行結果は以下
YMD
----------------
2000-01-13
2000-02-13
2000-03-13
(中略)
2399-10-13
2399-11-13
2399-12-13
(4800 行処理されました)
開始日を 2000/01/13 とし、1ヶ月ずつ加算し、2399/12/13 まで列挙。
400年 × 12ヶ月 = 4,800レコード。特に問題はないだろう。
因みに、以下のような実装も考えられる。
-- 400年間全ての13日の列挙(400年×12ヶ月)
;WITH [CTE_Year]([Year]) AS ( -- 400年列挙
SELECT @StartYear
UNION ALL
SELECT [Year] + 1 FROM [CTE_Year] WHERE [Year] < @StartYear + 399
)
, [CTE_Month]([Month]) AS ( -- 12ヶ月列挙
SELECT 1
UNION ALL
SELECT [Month] + 1 FROM [CTE_Month] WHERE [Month] < 12
)
SELECT DATEFROMPARTS([Year], [Month], 13)
FROM [CTE_Year], [CTE_Month]
OPTION (MAXRECURSION 0)
;
GO
年（2000～2399）、月（1～12）をそれぞれ列挙し、CROSS JOIN で結合という考え方。
これはこれで SQL らしい実装。
そうなると 1～400年の範囲に限定されるが以下も考えられる。年を月に流用することで少しだけクエリが単純になる
-- 400年間全ての13日の列挙(1～400年限定版)
;WITH [CTE_Year]([Year]) AS ( -- 400年列挙
SELECT 1
UNION ALL
SELECT [Year] + 1 FROM [CTE_Year] WHERE [Year] < 400
)
, [CTE_Month]([Month]) AS ( -- 12ヶ月列挙
SELECT [Year] FROM [CTE_Year] WHERE [Year] < 13
)
SELECT DATEFROMPARTS([Year], [Month], 13)
FROM [CTE_Year], [CTE_Month]
OPTION (MAXRECURSION 0)
;
GO
【年月日を曜日に変換し集計】
年月日から曜日を求める方法は ツェラーの公式 が有名。単純なアルゴリズムなので自力で実装してもそれほどの手間でもない。
しかし 組み込み関数 を使えばもっと簡単、高速なのでそちらを利用する。
SQL に覚えがある人間なら、ここまでくれば着地点はもう見えるだろう。
曜日毎に GROUP BY して、その件数を求める？
はい、その通り。実装例は以下。
-- 400年間全ての13日の曜日を集計
DECLARE @StartYear INT = 2000; -- 開始年
;WITH [CTE_Src]([YMD]) AS ( -- @StartYear/01/13 ～ @StartYear + 399/12/13
SELECT DATEFROMPARTS(@StartYear, 1, 13) -- @StartYear/01/13
UNION ALL
SELECT DATEADD(month, 1, [YMD]) FROM [CTE_Src] -- 1ヶ月ずつ加算
WHERE [YMD] < DATEFROMPARTS(@StartYear + 399, 12, 13) -- ～ @StartYear + 399/12/13
)
SELECT
DATENAME(weekday, [YMD]) [曜日]
, COUNT(DATENAME(weekday, [YMD])) [件数]
FROM [CTE_Src] -- @StartYear/01/13 ～ @StartYear + 399/12/13
GROUP BY
DATENAME(weekday, [YMD]) -- 曜日
ORDER BY
COUNT(DATENAME(weekday, [YMD])) DESC -- 件数
, DATENAME(weekday, [YMD]) -- 曜日
OPTION (MAXRECURSION 0)
;
GO
実行結果以下。金曜日が最も多いことが分かる。
曜日 件数
------------------------------ -----------
金曜日 688
水曜日 687
日曜日 687
火曜日 685
月曜日 685
土曜日 684
木曜日 684
(7 行処理されました)
【答え合わせ】
答え合わせに PowerShell でも実装。
PS C:\Users\user> 2000..2399 | %{ $year = $_; 1..12 | %{ ([DateTime]("{0}/{1}/13" -f $year, $_)).ToString("dddd") } } | group | select Name, Count | sort @{Expression = "Count"; Descending = $true}, @{Expression = "Name"; Descending = $false} | ft * -Autosize
Name Count
---- -----
金曜日 688
水曜日 687
日曜日 687
火曜日 685
月曜日 685
土曜日 684
木曜日 684
PowerShell だと簡単だよなぁ。惜しむらくは Sort の記述が冗長なこと。それを除けば Shellコマンドの有用性が垣間見える一例では。
【応用例1. 今年を含めた過去10年】
13日の金曜日ネタをネットで検索すると、2015年に書かれたものが散見される。
その年は 13金の「当たり年」だったことが理由らしい。
そこで今年を含めて直近 10年間の 13金の件数を集計してみる。
前述のクエリをベースとして以下の変更を行えばよい。
- 集計範囲を今年を含めて過去10年間とする
- 金曜日のレコードのみを抽出し年別で件数を集計
実装例は以下。
-- 今年を含めた過去10年
DECLARE @StartYear INT = DATEPART(year, GETDATE()); -- 開始年(今年)
;WITH [CTE_Src]([YMD]) AS ( -- 今年を含めた過去10年CTE
SELECT DATEFROMPARTS(@StartYear, 12, 13) -- @StartYear/12/13
UNION ALL
SELECT DATEADD(month, -1, [YMD]) FROM [CTE_Src] -- 1ヶ月ずつ減算
WHERE [YMD] > DATEFROMPARTS(@StartYear - 9, 1, 13) -- ～ @StartYear - 9/01/13
)
SELECT
DATENAME(year, [YMD]) [年]
, COUNT(DATENAME(weekday, [YMD])) [件数]
FROM [CTE_Src] -- 今年を含めた過去10年CTE
WHERE DATENAME(weekday, [YMD]) = N'金曜日' -- 金曜日のレコードのみ抽出
GROUP BY DATENAME(year, [YMD]) -- 年
ORDER BY DATENAME(year, [YMD]) DESC -- 年
OPTION (MAXRECURSION 0)
;
GO
実行結果は以下。
年 件数
------------------------------ -----------
2019 2
2018 2
2017 2
2016 1
2015 3
2014 1
2013 2
2012 3
2011 1
2010 1
(10 行処理されました)
確かに2015年が際立っていたことが分かる。そしてここ数年は 2件で安定（？）している傾向も見えてくる。
【応用例2. 今年を含めた未来10年】
そうなると今後の傾向も気になる。
上記のクエリの範囲を未来に置き換えた実装例が以下。
-- 今年を含めた未来10年
DECLARE @StartYear INT = DATEPART(year, GETDATE()); -- 開始年(今年)
;WITH [CTE_Src]([YMD]) AS ( -- 今年を含めた未来10年CTE
SELECT DATEFROMPARTS(@StartYear, 1, 13) -- @StartYear/1/13
UNION ALL
SELECT DATEADD(month, 1, [YMD]) FROM [CTE_Src] -- 1ヶ月ずつ加算
WHERE [YMD] < DATEFROMPARTS(@StartYear + 9, 12, 13) -- ～ @StartYear + 9/12/13
)
SELECT
DATENAME(year, [YMD]) [年]
, COUNT(DATENAME(weekday, [YMD])) [件数]
FROM [CTE_Src] -- 今年を含めた未来10年CTE
WHERE DATENAME(weekday, [YMD]) = N'金曜日' -- 金曜日のレコードのみ抽出
GROUP BY DATENAME(year, [YMD]) -- 年
ORDER BY DATENAME(year, [YMD]) -- 年
OPTION (MAXRECURSION 0)
;
GO
--
実行結果は以下。
年 件数
------------------------------ -----------
2019 2
2020 2
2021 1
2022 1
2023 2
2024 2
2025 1
2026 3
2027 1
2028 1
(10 行処理されました)
次の「当たり年」は2026年の模様。そう考えると2015年ってのは貴重（？）だったんだなぁと。11年も間が空いているし。
【応用例3. 年毎の13金の件数】
こうやって見ていると、13金が 3件より多い年や 0件の年はあるのか？ といった素朴な疑問も湧いてくるし、全般的な傾向が気になる。
そこで、年毎の件数を集計してみる。実装例は以下。
-- 年毎の13金の件数
DECLARE @StartYear INT = 2000; -- 開始年
;WITH [CTE_Src]([YMD]) AS ( -- @StartYear/01/13 ～ @StartYear + 399/12/13
SELECT DATEFROMPARTS(@StartYear, 1, 13) -- @StartYear/01/13
UNION ALL
SELECT DATEADD(month, 1, [YMD]) FROM [CTE_Src] -- 1ヶ月ずつ加算
WHERE [YMD] < DATEFROMPARTS(@StartYear + 399, 12, 13) -- ～ @StartYear + 399/12/13
)
SELECT
DATEPART(year, [YMD]) [年]
, COUNT(DATENAME(weekday, [YMD])) [件数]
FROM [CTE_Src] -- @StartYear/01/13 ～ @StartYear + 399/12/13
WHERE DATENAME(weekday, [YMD]) = N'金曜日' -- 金曜日のレコードのみ抽出
GROUP BY
DATEPART(year, [YMD]) -- 年
ORDER BY
COUNT(DATENAME(weekday, [YMD])) DESC -- 件
, DATEPART(year, [YMD]) -- 年
OPTION (MAXRECURSION 0)
;
GO
実行結果は以下。件数降順、年昇順。
年 件数
----------- -----------
2009 3
2012 3
2015 3
2026 3
2037 3
2040 3
2043 3
2054 3
2065 3
2068 3
2071 3
2082 3
2093 3
2096 3
2099 3
2105 3
2108 3
2111 3
2122 3
2133 3
2136 3
2139 3
2150 3
2161 3
2164 3
2167 3
2178 3
2189 3
2192 3
2195 3
2201 3
(中略)
2394 1
2397 1
2399 1
(400 行処理されました)
件数は必ず 1～3件の範囲であることが分かる。
3件の年は、3年または 11年の間隔である傾向も見てとれる。丁度今は 11年の間であると。
と思いきや、2099年と 2105年は 6年の開きがある。
2195年と 2201年も同様。
考えられる理由としては、どちらも閏年にならない例外的な年（2100年、2200年）をまたぐこと。恐らくそれで周期がずれるのでは。
【応用例4. 直近(未来)の13金】
直近（過去）の 13金は 2019/09/13 が記憶に新しい。では次の 13金は？ ということで直近(未来)の 13金を求めてみる。
当日から未来日に向けて調べる訳だけど、開始月が当月決め打ちでは芸がない。
当日が 13日以下の場合は当月から、14日以降なら次月から調べるものとする。
これは 月度 を求めるアルゴリズムと同じ。従って、開始月は以下で求められる。
(当日 - 13日 + 1ヶ月) の月
実装例は以下。
-- 直近(未来)の13金
DECLARE @StartYear INT = DATEPART(year, GETDATE()); -- 開始年(今年)
DECLARE @StartMonth INT = DATEPART(month, DATEADD(month, 1, DATEADD(day, -13, GETDATE())));
-- 開始月(13日以下なら当月、それ以外は次月)
DECLARE @Max INT = 10; -- 求める件数
;WITH [CTE_Src]([YMD], [Weekday], [Cnt]) AS ( -- 開始年、開始月から未来CTE
SELECT
DATEFROMPARTS(@StartYear, @StartMonth, 13) -- 年月日
, DATENAME(weekday, DATEFROMPARTS(@StartYear, @StartMonth, 13)) -- 曜日
, IIF(DATENAME(weekday, DATEFROMPARTS(@StartYear, @StartMonth, 13)) = N'金曜日', 1, 0) -- 金曜日なら1
UNION ALL
SELECT
DATEADD(month, 1, [YMD])
, DATENAME(weekday, DATEADD(month, 1, [YMD]))
, IIF(DATENAME(weekday, DATEADD(month, 1, [YMD])) = N'金曜日', [Cnt] + 1, [Cnt])
FROM [CTE_Src] -- 開始年、開始月から未来CTE
WHERE [Cnt] < @Max -- 求める件数
)
SELECT
CONVERT(CHAR(10), FORMAT([YMD], 'yyyy/MM/dd')) [直近の13金]
FROM [CTE_Src] -- 開始年、開始月から未来CTE
WHERE [Weekday] = N'金曜日' -- 金曜日のレコードのみ抽出
ORDER BY [YMD] -- 年月日
OPTION (MAXRECURSION 0)
;
GO
実行結果は以下。次回は 2019/12/13 らしい。年の瀬に不吉な。
直近の13金
----------
2019/12/13
2020/03/13
2020/11/13
2021/08/13
2022/05/13
2023/01/13
2023/10/13
2024/09/13
2024/12/13
2025/06/13
(10 行処理されました)
【グレゴリオ暦は何故400年周期？】
それは 400年間の日数を数えてみれば見えてくる。この時、閏年の判断がポイントとなる。
4の倍数年は基本閏年だが、100で割り切れて 400では割り切れない年は例外として平年となる。
この例外は 400年間に 3回出現する。
| 年 | 閏年/平年 |
|---|---|
| 2000 | 閏年 |
| 2100 | 平年 |
| 2200 | 平年 |
| 2300 | 平年 |
| 2400 | 閏年 |
| 2500 | 平年 |
| 2600 | 平年 |
| 2700 | 平年 |
| ・・・ | ・・・ |
400年間で 4の倍数年は 100個あり、その中の例外は 3件。つまり閏年は 97件。
従って 400年間の日数は以下。
PRINT 400 * 365 + 97;
GO
146097
ついでに 400年間の全ての年月日を列挙して件数を確認してみる。実装例は以下。
-- 400年間の日数カウント
;WITH [CTE_Src]([YMD]) AS ( -- 2000/01/01 ～ 2399/12/31 列挙CTE
SELECT CAST('2000/01/01' AS DATE) -- 2000/01/01
UNION ALL
SELECT DATEADD(day, 1, [YMD]) FROM [CTE_Src] -- 1日ずつ加算
WHERE [YMD] < '2399/12/31' -- ～ 2399/12/31
)
SELECT COUNT([YMD]) [400年間の日数] FROM [CTE_Src] -- 2000-01-01 ～ 2399/12/31 列挙CTE
OPTION (MAXRECURSION 0)
;
GO
400年間の日数
-----------
146097
(1 行処理されました)
完全に一致。そして 146,097日は 7 の倍数。従って 400年で曜日がループすることになる。
加えて前述の通り、閏年の判定は例外の年を含めて 400年を周期としているので、グレゴリオ暦は 400年周期となる。
閏年は例外の判定が地味に面倒。しかしもしそれが無く、4の倍数年を必ず閏年としてしまうと、400年間の日数は 7 の倍数にならず、400年周期にはならない。閏年の例外の仕様は丁度 400年周期になる為の調整の役割を果たしていることが分かる。よくできてるよ。
また、400年周期は「ずれ」の視点でも説明ができる。
子供の頃、今年のカレンダーと去年のカレンダーを見比べ、曜日が一つずれる法則性に気が付いた経験がある方も多いと思う。
365日を 7 で割ると 1 余る。
従って、曜日は毎年一つずれるし、閏年は二つずれる。
では 400年でどれだけずれるか。それは以下。
PRINT 400 + 97;
GO
497
497日。これも 7 の倍数。従って 400年でずれは元に戻る。所謂「一周回って」ならぬ、71周回って元に戻ると。
【DATETIME型はグレゴリオ暦型？】
今回年月日処理に DATE型を利用したが（※1）、DATETIME型というものもある。
datetime (Transact-SQL) - SQL Server _ Microsoft Docs
日付範囲 1753 年 1 月 1 日～ 9999 年 12 月 31 日
下限が 1753年と半端。これはグレゴリオ暦が一般化した年に由来するらしい。
通常、コンピュータシステムにおける「型」は単純にメモリサイズに依存する。例えば BYTE型なら 1バイトに収まる範囲、0～255 みたいな。
それに対し、DATETIME型のように歴史的経緯に範囲が依存するというものは珍しいというか興味深い。
ロケールが日本の場合、下限が 1873年（※2）に変化するとか。流石にそれはないか（笑）。
※1）今回利用したシステム関数は以下。全て引数や戻り値の型は DATE型。
| システム関数 | 説明 |
|---|---|
| DATEADD | 入力 date 値の指定 datepart に指定 number 値 (符号付き整数として) を追加し、その変更後の値を返します |
| DATEFROMPARTS | 指定の年月日の値にマッピングされる date 値を返します |
| DATENAME | 指定された date の指定された datepart を表す文字列を返します |
※2）日本にグレゴリオ暦が導入された年