SQLでカレンダーとかインデックスの付番

一定期間のカレンダーを生成したり、結果に論理番号を付番するメモ

カレンダー

起算日(2025-01-01) + オフセット(0..*)でカレンダーを作成する

オフセットの準備

指定レンジでインクリメンタルなオフセット値を持つテーブルを作成する

• オフセット値は変数に持ち歩かせる
• information_schema.COLUMNSのクエリ結果でレンジ行を確保しつつ、オフセット値を格納
• LIMITでレンジを切る

sql

-- 1行目でレンジ変数初期化
SELECT @_index := 0 AS _index -- 0
-- 2行目以降をUNIONで結合
UNION
-- 行確保のため、information_schema.COLUMNSをクエリして、レンジ変数をインクリメント
SELECT @_index := @_index + 1 AS _index FROM information_schema.COLUMNS -- 1..*
-- レンジの上限=7行
LIMIT 7;

+--------+
| _index |
+--------+
|      0 |
|      1 |
|      2 |
|      3 |
|      4 |
|      5 |
|      6 |
+--------+

カレンダーにする

• 起算日(2025-01-01)にオフセット値を加算する

sql

SELECT '2025-01-01' + INTERVAL _range._index DAY AS DATE FROM (
  -- 1行目でレンジ変数初期化
  SELECT @_index := 0 AS _index
  -- 2行目以降をUNIONで結合
  UNION
  -- 行確保のため、information_schema.COLUMNSをクエリして、レンジ変数をインクリメント
  SELECT @_index := @_index + 1 AS _index FROM information_schema.COLUMNS
  -- レンジの上限=10個
  LIMIT 7
) _range;

+------------+
| DATE       |
+------------+
| 2025-01-01 |
| 2025-01-02 |
| 2025-01-03 |
| 2025-01-04 |
| 2025-01-05 |
| 2025-01-06 |
| 2025-01-07 |
+------------+

論理番号を付番する

クエリ結果に対して、論理番号を付番して意味付けする

こんな科目-点数テーブルがあったとして

+-----------+-------+
| category  | score |
+-----------+-------+
| 国語       |    84 |
| 国語       |    91 |
| 国語       |    77 |
| 数学       |    88 |
| 数学       |    98 |
| 数学       |    81 |
| 理科       |    50 |
| 理科       |    80 |
| 理科       |    68 |
+-----------+-------+

結果に行番号を付番する

• 行番号を変数に持ち歩かせる
• FROM句で変数を初期化する

全教科の点数ランキング

sql

SELECT
  -- 結果順に変数を加算
  @_rank := @_rank + 1 AS rank,
  score.category,
  score.score
FROM
  score,
  -- 変数を初期化。テーブルに用はない
  (SELECT @_rank:= 0) AS row_num
-- score降順
ORDER BY score.score DESC;

+------+----------+-------+
| rank | category | score |
+------+----------+-------+
|    1 | 数学      |    98 |
|    2 | 国語      |    91 |
|    3 | 数学      |    88 |
|    4 | 国語      |    84 |
|    5 | 数学      |    81 |
|    6 | 理科      |    80 |
|    7 | 国語      |    77 |
|    8 | 理科      |    68 |
|    9 | 理科      |    50 |
+------+----------+-------+

教科ごの点数ランキング

• 付番する軸=教科の変わり目で行番号を初期化する

sql

SELECT
  -- 付番する軸=教科の変わり目で行番号を初期化する
  @_rank := IF(@prev_category = score.category, @_rank + 1, 1) AS rank,
  @prev_category := score.category AS category,
  score.score
FROM score
-- カテゴリ×score降順
ORDER BY score.category ASC, score.score DESC

+------+----------+-------+
| rank | category | score |
+------+----------+-------+
|    1 | 国語      |    91 |
|    2 | 国語      |    84 |
|    3 | 国語      |    77 |
|    1 | 数学      |    98 |
|    2 | 数学      |    88 |
|    3 | 数学      |    81 |
|    1 | 理科      |    80 |
|    2 | 理科      |    68 |
|    3 | 理科      |    50 |
+------+----------+-------+