数秘術の誕生数を SQL で分析（後編）

バックエンド

こんにちは、knsk765 です。

こちらの記事の続きです。 blog.ingage.jp

前編では1940年〜2039年全日付の誕生数を SQL で導き出しました。
後編では集計処理に取り組みます。

おさらい

こんなデータを取得するところまではできました。

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
: :

ゴールに至るまでの手順

  • 1940年〜2039年までの日付を並べる
  • 各日付に対する誕生数を割り出す ← ここまでクリア
  • 20年ごとに分けて各誕生数の割合を算出する

今回やること

Step 5. 20年単位での集計処理

1940年から20年単位にわけて集計します。誕生数で GROUP BY した結果を 20年単位の列に分けて表示します。

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
)
-- 誕生数それぞれの登場回数を1940年〜2039年の20年ごと（＆全期間）に集計します
SELECT
  birth_number AS "誕生数",
  sum(
    CASE WHEN d BETWEEN '1940-01-01' AND '1959-12-31' THEN 1 ELSE 0 END
  ) AS "1940-1959",
  sum(
    CASE WHEN d BETWEEN '1960-01-01' AND '1979-12-31' THEN 1 ELSE 0 END
  ) AS "1960-1979",
  sum(
    CASE WHEN d BETWEEN '1980-01-01' AND '1999-12-31' THEN 1 ELSE 0 END
  ) AS "1980-1999",
  sum(
    CASE WHEN d BETWEEN '2000-01-01' AND '2019-12-31' THEN 1 ELSE 0 END
  ) AS "2000-2019",
  sum(
    CASE WHEN d BETWEEN '2020-01-01' AND '2039-12-31' THEN 1 ELSE 0 END
  ) AS "2020-2039",
  count(*) AS "1940-2039"
FROM
  date_and_birth_numbers
GROUP BY
  birth_number
ORDER BY
  birth_number

こんなかんじ。

誕生数 1940-1959 1960-1979 1980-1999 2000-2019 2020-2039 1940-2039
1 813 814 809 811 812 4059
2 135 46 4 468 546 1199
3 812 813 814 809 811 4059
4 563 677 768 463 341 2812
5 809 812 813 814 809 4057
6 396 298 246 814 806 2560
7 811 809 812 813 814 4059
8 813 795 761 812 814 3995
9 814 811 809 812 813 4059
11 677 768 807 341 266 2859
22 248 135 46 348 468 1245
33 413 513 566 0 5 1497
44 1 14 50 0 0 65

年代ごとの振り分け部分について解説。

sum(
  CASE
    WHEN d BETWEEN '1940-01-01' AND '1959-12-31' THEN 1
    ELSE 0
  END
)

CASE 文を使って日付が 1940・50年代なら 1、以外は 0 に変換。
それを sum で加算するわけです。
年間の売上を月ごとに集計して列で並べるみたいなシチュエーションでも使えるテクニックですね。

Step 6. 各年代の日数を算出

Step 5. でほぼ答えは出てしまったのですが、最終的にパーセンテージを表示したいのでもうちょい手を加えます。
割合を出すには母数が必要なので、各年代の日数を算出します。

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
),
-- 誕生数それぞれの登場回数を1940年〜2039年の20年ごと（＆全期間）に集計します
birth_number_sums AS (
  SELECT
    birth_number AS "誕生数",
    sum(
      CASE WHEN d BETWEEN '1940-01-01' AND '1959-12-31' THEN 1 ELSE 0 END
    ) AS "1940-1959",
    sum(
      CASE WHEN d BETWEEN '1960-01-01' AND '1979-12-31' THEN 1 ELSE 0 END
    ) AS "1960-1979",
    sum(
      CASE WHEN d BETWEEN '1980-01-01' AND '1999-12-31' THEN 1 ELSE 0 END
    ) AS "1980-1999",
    sum(
      CASE WHEN d BETWEEN '2000-01-01' AND '2019-12-31' THEN 1 ELSE 0 END
    ) AS "2000-2019",
    sum(
      CASE WHEN d BETWEEN '2020-01-01' AND '2039-12-31' THEN 1 ELSE 0 END
    ) AS "2020-2039",
    count(*) AS "1940-2039"
  FROM date_and_birth_numbers GROUP BY birth_number
)
-- 20年ごと（＆全期間）の日数を集計します
SELECT
  sum("1940-1959") AS "日数1940-1959",
  sum("1960-1979") AS "日数1960-1979",
  sum("1980-1999") AS "日数1980-1999",
  sum("2000-2019") AS "日数2000-2019",
  sum("2020-2039") AS "日数2020-2039",
  sum("1940-2039") AS "日数1940-2039"
FROM
  birth_number_sums

というわけでこう。

日数1940-1959 日数1960-1979 日数1980-1999 日数2000-2019 日数2020-2039 日数1940-2039
7305 7305 7305 7305 7305 36525

Step 7. パーセンテージ

これで最後です。長い SQL になりましたね。

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
),
-- 誕生数それぞれの登場回数を1940年〜2039年の20年ごと（＆全期間）に集計します
birth_number_sums AS (
  SELECT
    birth_number AS "誕生数",
    sum(
      CASE WHEN d BETWEEN '1940-01-01' AND '1959-12-31' THEN 1 ELSE 0 END
    ) AS "1940-1959",
    sum(
      CASE WHEN d BETWEEN '1960-01-01' AND '1979-12-31' THEN 1 ELSE 0 END
    ) AS "1960-1979",
    sum(
      CASE WHEN d BETWEEN '1980-01-01' AND '1999-12-31' THEN 1 ELSE 0 END
    ) AS "1980-1999",
    sum(
      CASE WHEN d BETWEEN '2000-01-01' AND '2019-12-31' THEN 1 ELSE 0 END
    ) AS "2000-2019",
    sum(
      CASE WHEN d BETWEEN '2020-01-01' AND '2039-12-31' THEN 1 ELSE 0 END
    ) AS "2020-2039",
    count(*) AS "1940-2039"
  FROM date_and_birth_numbers GROUP BY birth_number
),
-- 20年ごと（＆全期間）の日数を集計します
totals as (
  SELECT
    sum("1940-1959") AS "日数1940-1959",
    sum("1960-1979") AS "日数1960-1979",
    sum("1980-1999") AS "日数1980-1999",
    sum("2000-2019") AS "日数2000-2019",
    sum("2020-2039") AS "日数2020-2039",
    sum("1940-2039") AS "日数1940-2039"
  FROM birth_number_sums
)
-- 誕生数それぞれのパーセンテージを1940年〜2039年の20年ごと（＆全期間）に算出します
SELECT
  "誕生数",
  round(
    "1940-1959" / (SELECT "日数1940-1959" FROM totals) * 100, 2
  ) AS "1940-1959",
  round(
    "1960-1979" / (SELECT "日数1960-1979" FROM totals) * 100, 2
  ) AS "1960-1979",
  round(
    "1980-1999" / (SELECT "日数1980-1999" FROM totals) * 100, 2
  ) AS "1980-1999",
  round(
    "2000-2019" / (SELECT "日数2000-2019" FROM totals) * 100, 2
  ) AS "2000-2019",
  round(
    "2020-2039" / (SELECT "日数2020-2039" FROM totals) * 100, 2
  ) AS "2020-2039",
  round(
    "1940-2039" / (SELECT "日数1940-2039" FROM totals) * 100, 2
  ) AS "1940-2039（全期間）"
FROM
  birth_number_sums
ORDER BY
  "誕生数"

出ました！

誕生数 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

44 がダントツで激レアですね。

数字だけだとわかりにくいので、Excel の条件付き書式でレア度高い順に色をつけました（列単位）。

僕の誕生数 33 は、1900年代にはいるものの2000年代以降はほぼ0 。
全期間では 33 の割合は確かに少ないけど、レア度 2位というわけでもない。
マスターナンバー（ゾロ目）の中でも 11 よりは少ないが 22 よりは多い。
なんなら 2 のほうが 44 の次にレアという意外な結果。
11 と 2 で分散してしまう（11 になった時点で計算が終わる）ので、11 に割合持っていかれてるってことですね。おもしろい。

集計期間をかえれば結果も変わってくるので、みなさんも試してみてください。

というわけで、数秘術では宇宙人みたいと言われる 33 の knsk765 がお送りしました。