こんにちは、knsk765 です。
今回は数秘術を題材として SQL パズル的なことに挑戦します。
数秘術というのは占いの一種です。僕が占い好きなので今回の題材に選びましたが、占いの信憑性とかは置いておいて統計情報として数値の分析のみを行います。
前置き
数秘術とは
数秘術（Wikipedia） より引用
数秘術（すうひじゅつ、英: Numerology）とは、西洋占星術や易学等と並ぶ占術の一つで、ピタゴラス式やカバラ等が有名である。「数秘学」とも言う。 一般的な占術の方法は「命術」で、占う対象の生年月日（西暦）や姓名などから、固有の計算式に基づいて運勢傾向や先天的な宿命を占う方法である。
数秘術では生年月日から誕生数という数字を求めます。 1 〜 9 の数字に加えマスターナンバーと呼ばれるゾロ目数字（11、22、33、44）の 13種類が誕生数になります。マスターナンバーについては流派によって 22 までだったり 33 までだったりするそうですが、今回の分析では 44 までを対象とします。
誕生数の求め方はこうです。生年月日の各数字を足し算して、2桁の数字になったら分解して1桁になるまで足し算を繰り返します。ただし、マスターナンバー（11、22、33、44）になった場合はそこで確定です。
例1）マスターナンバー以外の場合 1997年9月29日生まれ 1 + 9 + 9 + 7 + 9 + 2 + 9 = 46 → 4 + 6 = 10 → 1 + 0 = 1 ... 誕生数は 1 例2）マスターナンバーの場合 1980年10月30日生まれ 1 + 9 + 8 + 0 + 1 + 0 + 3 + 0 = 22 ... 誕生数は 22
数秘術について分析したいこと
この数秘術について何を分析したいのか。
僕は誕生数 33 なんですが、マスターナンバーは 1〜9 に比べるとレアだと言われています。でもどれぐらいレアなんだろう？調べてみたい！ということで、今回 SQL を使ってそれを実現してみます。
実現したいイメージ
いきなり答えを出してしまいますが、SQL でこんな結果を取得するのが今回のゴールです。
生まれた年代、特に 2000年より前と後で割合が大きく変わってくる（年に 9 の出てくる数が全然違いますよね）ので、20年ごとに分けたときの割合も算出してみます。単位は % です。
| 誕生数 | 1940-1959 | 1960-1979 | 1980-1999 | 2000-2019 | 2020-2039 | 1940-2039（全期間） |
|---|---|---|---|---|---|---|
| 1 | 11.13 | 11.14 | 11.07 | 11.10 | 11.12 | 11.11 |
| 2 | 1.85 | 0.63 | 0.05 | 6.41 | 7.47 | 3.28 |
| 3 | 11.12 | 11.13 | 11.14 | 11.07 | 11.10 | 11.11 |
| 4 | 7.71 | 9.27 | 10.51 | 6.34 | 4.67 | 7.70 |
| 5 | 11.07 | 11.12 | 11.13 | 11.14 | 11.07 | 11.11 |
| 6 | 5.42 | 4.08 | 3.37 | 11.14 | 11.03 | 7.01 |
| 7 | 11.10 | 11.07 | 11.12 | 11.13 | 11.14 | 11.11 |
| 8 | 11.13 | 10.88 | 10.42 | 11.12 | 11.14 | 10.94 |
| 9 | 11.14 | 11.10 | 11.07 | 11.12 | 11.13 | 11.11 |
| 11 | 9.27 | 10.51 | 11.05 | 4.67 | 3.64 | 7.83 |
| 22 | 3.39 | 1.85 | 0.63 | 4.76 | 6.41 | 3.41 |
| 33 | 5.65 | 7.02 | 7.75 | 0.00 | 0.07 | 4.10 |
| 44 | 0.01 | 0.19 | 0.68 | 0.00 | 0.00 | 0.18 |
実行環境
PostgreSQL 14.5
ポイント
今回の SQL チャレンジのポイントです。
ゴールに至るまでの手順
- 1940年〜2039年までの日付を並べる
- 各日付に対する誕生数を割り出す
- 20年ごとに分けて各誕生数の割合を算出する
制限事項
- テーブルは作らない
- 関数は作成しない
- パフォーマンスは考慮しない
- SQL一発で最後の結果まで導く
という SQL パズル的な感じでこの課題を解いていきます。
キーワード
以下の技術を使って実現します。
- 共通テーブル式（Common Table Expression）
- 文字列分割
- substr
- regexp_split_to_table
- CASE WHEN 〜 ELSE 〜 END
SQL を組み立てよう
Step 0. （準備運動）数字を並べよう
まずは準備運動。制限事項に挙げたとおり、テーブルは作りません。じゃあ、どうやって行列のデータを作り上げるのか？
列を取得する
まず、列はこんな感じで作れますよね。
SELECT 'ヅョセフ' AS first_name, 'ヅョースター' AS last_name
| first_name | last_name |
|---|---|
| ヅョセフ | ヅョースター |
行を取得する
複数行はこんな感じ。
SELECT 'ヅョナサソ' AS first_name, 'ヅョースター' AS last_name UNION ALL SELECT 'ヅョセフ' AS first_name, 'ヅョースター' AS last_name UNION ALL SELECT 'ヅョルノ' AS first_name, 'ヅョバァーナ' AS last_name
| first_name | last_name |
|---|---|
| ヅョナサソ | ヅョースター |
| ヅョセフ | ヅョースター |
| ヅョルノ | ヅョバァーナ |
では、1〜100 までの数値列を持った表を作ろうとしたらどうします？
この方法だと SELECT 1 UNION ALL SELECT 2 UNION ALL ... とすればできますが、しんどいしプログラマー的ではないですね。
行を取得する（繰り返し）
そこで CTE (Common Table Expression) - 共通テーブル式を使います。まずは下の SQL を見てください。
WITH RECURSIVE numbers (n) AS ( SELECT 1 AS n UNION ALL SELECT n + 1 AS n FROM numbers WHERE n < 5 ) SELECT * FROM numbers
これを実行した結果はこうなります。
| n |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
1から5までの数値が取得できました。
ここでは詳細な解説は割愛するので、上記URLや他の技術ブログなどをご覧ください。
簡単に言うと下記が CTE の機能です。
WITHで指定した SELECT 結果に名前をつけて参照できるWITH RECURSIVEの中で再帰処理（自己結果の参照）ができる
もっと単純な SQL でいうと
WITH numbers (n) AS ( SELECT 1 AS n ) SELECT * FROM numbers
↓
| n |
|---|
| 1 |
これだけなら下記サブクエリと変わりません。
SELECT * FROM ( SELECT 1 AS n ) AS numbers
しかし、先ほどの例のように再帰処理できるのが CTE のすごいところです。
WITH RECURSIVE numbers (n) AS ( SELECT 1 AS n UNION ALL SELECT n + 1 AS n FROM numbers WHERE n < 5 ) SELECT * FROM numbers
SELECT 1 AS n で取得した（1）という結果 numbers をもとに
SELECT n + 1 AS n FROM numbers という結果（1 + 1 = 2）を結合（UNION ALL）します。
その（1, 2）という結果もまた numbers となります。
これを WHERE n < 5 を満たすあいだ + 1 ずつ並べると 1 〜 5 の行になるわけです。
WHERE n < 100 とすれば 1 〜 100 が取得できますね。
というわけで、この要領でテーブルに登録したデータからの SELECT をせずにダイレクトにデータを表現していきます。
Step 1. 日付を並べよう
では本格的に SQL を組み立てていきましょう。まずは日付を並べてみます。期間として 1940年1月1日〜2039年12月31日の日付を取得します。
WITH RECURSIVE dates_core (d, ymd) AS ( SELECT cast('1940-01-01' as date) AS d, '19400101' AS ymd UNION ALL SELECT d + 1 AS d, to_char(d + 1, 'YYYYMMDD') AS ymd FROM dates_core WHERE d < '2039-12-31' ) SELECT * FROM dates_core
| d | ymd |
|---|---|
| 1940-01-01 | 19400101 |
| 1940-01-02 | 19400102 |
| 1940-01-03 | 19400103 |
| ： | ： |
| 2039-12-29 | 20391229 |
| 2039-12-30 | 20391230 |
| 2039-12-31 | 20391231 |
さっきの数値を並べるのと同じ要領ですね。あとで計算に使うための日付文字列を ymd 列として一緒に並べています。
Step 2. 日付各桁の和を計算しよう
20391229 なら 2 + 0 + 3 + 9 + 1 + 2 + 2 + 9 = 28 となるように日付を1桁ずつ分割して加算しましょう。
-- 指定期間（1940年〜2039年）の日付を並べます WITH RECURSIVE dates_core (d, ymd) AS ( SELECT cast('1940-01-01' as date) AS d, '19400101' AS ymd UNION ALL SELECT d + 1 AS d, to_char(d + 1, 'YYYYMMDD') AS ymd FROM dates_core WHERE d < '2039-12-31' ), -- YYYYMMDD の日付文字列を1文字ずつ分解して和を求めます dates (d, added_digits) AS ( SELECT d, cast(substr(ymd, 1, 1) as integer) + cast(substr(ymd, 2, 1) as integer) + cast(substr(ymd, 3, 1) as integer) + cast(substr(ymd, 4, 1) as integer) + cast(substr(ymd, 5, 1) as integer) + cast(substr(ymd, 6, 1) as integer) + cast(substr(ymd, 7, 1) as integer) + cast(substr(ymd, 8, 1) as integer) AS added_digits FROM dates_core ) SELECT * FROM dates
WITH 句の SELECT 文が複数あるときは、, で続けて書くことができます。2つ目の dates では 1つ目の dates_core を参照しています。
| d | added_digits |
|---|---|
| 1940-01-01 | 16 |
| 1940-01-02 | 17 |
| 1940-01-03 | 18 |
| ： | ： |
| 2039-12-29 | 28 |
| 2039-12-30 | 20 |
| 2039-12-31 | 21 |
さて、結果は出たのですが、1文字ずつ分割して加算しているところ（added_digits）がすっきりしませんね。
これをすっきりさせられる関数が PostgreSQL にはあるので、使ってみましょう。
文字列を分割して表形式で返す
SELECT c FROM regexp_split_to_table('abcd', '') AS c
regexp_split_to_table 関数は、正規表現で指定したセパレーター（第2引数）で文字列（第1引数）を分割して表形式で返します。セパレーターを空文字 '' にすると 1桁ずつ分割されます。
| c |
|---|
| a |
| b |
| c |
| d |
分割されて返ってくるのは文字列なので、加算するには数値にしておかないとダメですね。
SELECT cast(i as smallint) FROM regexp_split_to_table('1234', '') AS i
| i |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
というわけで日付文字列を1文字ずつ加算するにはこう。
SELECT sum(cast(i as smallint)) FROM regexp_split_to_table('20391229', '') AS i
| sum |
|---|
| 28 |
これを踏まえてシンプルにしたのがこちら↓。あわせて dates_core の中で日付 YYYYMMDD 文字列取得していたのを dates の中でダイレクトに文字列化することにしました。
-- 指定期間（1940年〜2039年）の日付を並べます WITH RECURSIVE dates_core (d) AS ( SELECT cast('1940-01-01' as date) AS d UNION ALL SELECT d + 1 AS d FROM dates_core WHERE d < '2039-12-31' ), -- YYYYMMDD の日付文字列を1文字ずつ分解して和を求めます dates (d, added_digits) AS ( SELECT d, ( SELECT sum(cast(i as smallint)) FROM regexp_split_to_table(to_char(d, 'YYYYMMDD'), '') i ) AS added_digits FROM dates_core ) SELECT * FROM dates
Step 3. 誕生数を算出しよう
もう一度おさらいしますが、誕生数の算出方法は下記です。
西暦の数字を1桁ずつ足し算する（例. 1993年4月23日の場合） 1 + 9 + 9 + 3 + 4 + 2 + 3 = 31 和が 2桁の数字を超える場合はさらに 1桁ずつ分解して足す 3 + 1 = 4 ※ただし、2桁の数字になってもゾロ目（11, 22, 33, 44）の場合はそこでストップ
Step 2. で日付各桁の単純加算値は割り出せたので、2桁越えの場合を考慮して算出した誕生数の対比表を作りましょう。具体的にはこんな感じです。
| n | birth_number |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| 10 | 1 |
| 11 | 11 |
| 12 | 3 |
| 13 | 4 |
| 14 | 5 |
| : | : |
日付各桁の和（n）に対して 2桁以上のときの再加算ルールを適用して算出したのが、誕生数（birth_number)。
第一段階
-- 1から順番に数値を並べます WITH RECURSIVE numbers (n) AS ( SELECT 1 AS n UNION ALL SELECT n + 1 AS n FROM numbers WHERE n < 44 ), -- 日付各桁の和から誕生数を求めます birth_numbers_core (n, birth_number) AS ( SELECT n, CASE WHEN n IN (11, 22, 33, 44) THEN n WHEN n <= 9 THEN n ELSE ( SELECT sum(cast(i as smallint)) FROM regexp_split_to_table(cast(n as varchar), '') AS i ) END AS birth_number FROM numbers ) SELECT * FROM birth_numbers_core
| n | birth_number |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| 10 | 1 |
| 11 | 11 |
| 12 | 3 |
| 13 | 4 |
| 14 | 5 |
| 15 | 6 |
| 16 | 7 |
| 17 | 8 |
| 18 | 9 |
| 19 | 10 |
| 20 | 2 |
| 21 | 3 |
| 22 | 22 |
| 23 | 5 |
| 24 | 6 |
| 25 | 7 |
| 26 | 8 |
| 27 | 9 |
| 28 | 10 |
| 29 | 11 |
| 30 | 3 |
| 31 | 4 |
| 32 | 5 |
| 33 | 33 |
| 34 | 7 |
| 35 | 8 |
| 36 | 9 |
| 37 | 10 |
| 38 | 11 |
| 39 | 12 |
| 40 | 4 |
| 41 | 5 |
| 42 | 6 |
| 43 | 7 |
| 44 | 44 |
numbers のところは 1 からとりあえず 44 まで並べています。「とりあえず」については後でちゃんと決めます。
計算のところは CASE 文で実現しています。
CASE WHEN n IN (11, 22, 33, 44) THEN n WHEN n <= 9 THEN n ELSE ( SELECT sum(cast(i as smallint)) FROM regexp_split_to_table(cast(n as varchar), '') AS i ) END AS birth_number
- まずゾロ目（11, 22, 33, 44）ならそのまま
n IN (11, 22, 33, 44) THEN n - 1桁ならそのまま
n <= 9 THEN n - 以外の 2桁はもう一度 1桁ずつ加算
SELECT sum(cast(i as smallint)) FROM regexp_split_to_table(cast(n as varchar), '') AS i
さて、2桁超えの場合やゾロ目の場合の処理がちゃんとできているかと思いきや、少し様子がおかしいですね。
| n | birth_number |
|---|---|
| 27 | 9 |
| 28 | 10 |
| 29 | 11 |
| 30 | 3 |
2 + 8 = 10 はもう一度 1 + 0 = 1 としなければいけませんね。困った。
第二段階
-- 1から順番に数値を並べます WITH RECURSIVE numbers (n) AS ( SELECT 1 AS n UNION ALL SELECT n + 1 AS n FROM numbers WHERE n < 44 ), -- 日付各桁の和から誕生数を求めます（ただし10以上のときの加算は1回のみ） birth_numbers_core (n, birth_number) AS ( SELECT n, CASE WHEN n IN (11, 22, 33, 44) THEN n WHEN n <= 9 THEN n ELSE ( SELECT sum(cast(i as smallint)) FROM regexp_split_to_table(cast(n as varchar), '') AS i ) END AS birth_number FROM numbers ), -- 日付各桁の和から誕生数を求めます（完全版 - 1桁になるまで分割・加算を繰り返す） birth_numbers (n, birth_number) AS ( SELECT b1.n, b2.birth_number FROM birth_numbers_core b1 INNER JOIN birth_numbers_core b2 ON b2.n = b1.birth_number ) SELECT * FROM birth_numbers
| n | birth_number |
|---|---|
| : | : |
| 27 | 9 |
| 28 | 1 |
| 29 | 11 |
| 30 | 3 |
| 31 | 4 |
| 32 | 5 |
| 33 | 33 |
| 34 | 7 |
| 35 | 8 |
| 36 | 9 |
| 37 | 1 |
| 38 | 11 |
| 39 | 3 |
| 40 | 4 |
| 41 | 5 |
| 42 | 6 |
| 43 | 7 |
| 44 | 44 |
これでOK。
先ほどの birth_numbers_core.birth_number を元（n）にしてもう一度計算する処理を INNER JOIN で実現しました（birth_numbers）。
途中経過でいうとこういイメージですね。
| b1.n | (b1.birth_number = | = b2.n) | b2.birth_number |
|---|---|---|---|
| 36 | 9 | 9 | 9 |
| 37 | 10 | 10 | 1 |
| 38 | 11 | 11 | 2 |
| 39 | 12 | 12 | 3 |
| 40 | 4 | 4 | 4 |
関数をつくればもっと簡単に再帰処理で算出できるのですが、今回は自作関数使わないという縛りを設けているのでこんなところで。
Step 4. 日ごとの誕生数をもとめよう
先ほど
numbers のところは 1 からとりあえず 44 まで並べています。「とりあえず」については後でちゃんと決めます。
と書いたこの部分を決めましょう。↓の WHERE n < 44 のことですね。
-- 1から順番に数値を並べます WITH RECURSIVE numbers (n) AS ( SELECT 1 AS n UNION ALL SELECT n + 1 AS n FROM numbers WHERE n < 44 ),
この 44 にあたるのは日付各桁の和で一番大きな値です。今回範囲としている 1940年1月1日〜2039年12月31日で一番大きな数字を求めます。
-- 指定期間（1940年〜2039年）の日付を並べます WITH RECURSIVE dates_core (d) AS ( SELECT cast('1940-01-01' as date) AS d UNION ALL SELECT d + 1 AS d FROM dates_core WHERE d < '2039-12-31' ), -- YYYYMMDD の日付文字列を1文字ずつ分解して和を求めます dates (d, added_digits) AS ( SELECT d, ( SELECT sum(cast(i as smallint)) FROM regexp_split_to_table(to_char(d, 'YYYYMMDD'), '') AS i ) AS added_digits FROM dates_core ) SELECT max(added_digits) FROM dates
なので、この SELECT max(added_digits) FROM dates で求められます。
numbers の WHERE のところに反映して、こう。
-- 指定期間（1940年〜2039年）の日付を並べます WITH RECURSIVE dates_core (d) AS ( SELECT cast('1940-01-01' as date) AS d UNION ALL SELECT d + 1 AS d FROM dates_core WHERE d < '2039-12-31' ), -- YYYYMMDD の日付文字列を1文字ずつ分解して和を求めます dates (d, added_digits) AS ( SELECT d, ( SELECT sum(cast(i as smallint)) FROM regexp_split_to_table(to_char(d, 'YYYYMMDD'), '') AS i ) AS added_digits FROM dates_core ), -- 1から順番に数値を並べます numbers (n) AS ( SELECT 1 AS n UNION ALL SELECT n + 1 AS n FROM numbers WHERE n < (SELECT max(added_digits) FROM dates) ),
さらに続けて日付と誕生数をくっつけましょう。
WITH RECURSIVE dates_core (d) AS ( SELECT cast('1940-01-01' as date) AS d UNION ALL SELECT d + 1 AS d FROM dates_core WHERE d < '2039-12-31' ), -- YYYYMMDD の日付文字列を1文字ずつ分解して和を求めます dates (d, added_digits) AS ( SELECT d, ( SELECT sum(cast(i as smallint)) FROM regexp_split_to_table(to_char(d, 'YYYYMMDD'), '') AS i ) AS added_digits FROM dates_core ), -- 1から順番に数値を並べます numbers (n) AS ( SELECT 1 AS n UNION ALL SELECT n + 1 AS n FROM numbers WHERE n < (SELECT max(added_digits) FROM dates) ), -- 日付各桁の和から誕生数を求めます（ただし10以上のときの加算は1回のみ） birth_numbers_core (n, birth_number) AS ( SELECT n, CASE WHEN n IN (11, 22, 33, 44) THEN n WHEN n <= 9 THEN n ELSE ( SELECT sum(cast(i as smallint)) FROM regexp_split_to_table(cast(n as varchar), '') AS i ) END birth_number FROM numbers ), -- 日付各桁の和から誕生数を求めます（完全版 - 1桁になるまで分割・加算を繰り返す） birth_numbers (n, birth_number) AS ( SELECT b1.n, b2.birth_number FROM birth_numbers_core b1 INNER JOIN birth_numbers_core b2 ON b2.n = b1.birth_number ), -- 日付に対する誕生数を取得します date_and_birth_numbers (d, birth_number) AS ( SELECT d, birth_numbers.birth_number FROM dates INNER JOIN birth_numbers ON dates.added_digits = birth_numbers.n ) SELECT * FROM date_and_birth_numbers
date_and_birth_numbers として、今までの結果をくっつけました。
長いですね。そろそろみなさんイヤになってきたでしょうか。
| d | birth_number |
|---|---|
| 1940-01-01 | 7 |
| 1940-01-02 | 8 |
| 1940-01-03 | 9 |
| 1940-01-04 | 1 |
| 1940-01-05 | 2 |
| 1940-01-06 | 3 |
| 1940-01-07 | 22 |
| 1940-01-08 | 5 |
| 1940-01-09 | 6 |
| 1940-01-10 | 7 |
| : | : |
いい感じです。集計するための元データができたので、ゴールまであと少し。 今回はここまでにして、次回へ続く...
