ひよっこのひよっこによるひよっこのための SQL TIPS

はじめに

最近 SQL を触り始めた初心者が、TOPSIC SQL CONTEST を通して学んだちょっとしたテクニックを掘り下げてシェアしたいと思います。タイトルはふざけてますが、内容はいたって真面目です。

本記事は、初心者向けですが、ORDER BY, GROUP BY, CASE, JOIN, UNIONなどをすでに知っている前提で進めています。

本記事は、目的の結果を得るためのロジックに注目しており、パフォーマンス的に優れた書き方か否かを全く考慮していません。ご容赦ください。

目次

• 任意の文字列の並び替え
• 再帰構造
• 縦持ちと横持ち
• 行間比較

任意の文字列の並び替え

これは、会社の偉い人順に並び替えたいときなどに使えるテクニックで、「文字列ごとに数値を割り当て、その数値を並び替える」 という発想で実装します。具体的な実装方法のうち、最も簡単なのはORDER BY句にCASE式を組み込むことです。次の単純なテーブルを想定してください。（COMPANY　テーブルとでも命名しておきます）

KIND	DESCRIPTION
社長	1番偉い
課長	3番目に偉い
部長	2番目に偉い

ここで、偉い人順に並び替える SQL は次のように実現できます。

SELECT
    *
FROM
    COMPANY
ORDER BY
    CASE KIND
        WHEN '社長' THEN 1
	    WHEN '部長' THEN 2
	    WHEN '課長' THEN 3
        ELSE 100
    END;

得られる結果は次の通りです。

KIND	DESCRIPTION
社長	1番偉い
部長	2番目に偉い
課長	3番目に偉い

再帰構造

一般的には、会社における上司と部下の関係を考慮した売り上げを取得するといった階層構造で表されるデータを扱う例が多いようですが、今回遭遇したのは以下のようなパターンです。

WITH RECURSIVE SEQ_TBL(SEQ_NO) AS ( 
    SELECT
        1
    UNION ALL 
    SELECT
        SEQ_NO + 1 
    FROM
        SEQ_TBL 
    WHERE
        SEQ_NO < 6
) 
-- 結果確認
SELECT * FROM SEQ_TBL;

これは、WITH RECURSIVE句と呼ばれ、SQL で再帰を実現する構文です。

PostgreSQL 以外では、RECURSIVE は省略可能なようですが、より汎用的な書き方にするため、今回はあえて省略していません。

動作原理を理解しないことには何がなんだかさっぱりだと思うので、まずはどのように動くかを確認していきましょう。基本構文は次の通りです。

WITH RECURSIVE テーブル名(カラム1, カラム2, ...) AS (
	非再帰項
	UNION ALL
	再帰項
)

以下は厳密な内部動作とは異なりますが、今回は再帰に特化した記事ではないため、どのようなテーブルが作成されるかをイメージするのに必要な手順に絞って確認します：

1. 非再帰項を評価し、WITH RECURSIVE句で定義したテーブルに追加
2. 再帰項を評価し、WITH RECURSIVE句で定義したテーブルにUNION ALL
3. 再帰項で得られる結果が空になるまで 2 を実行（空であれば追加されない）

実際には作業テーブル、WITHテーブル、結果テーブルの 3 つのテーブルを保持することで再帰を実現しているようです。具体的なアルゴリズムに興味がある方はこのあたりが参考になると思われます。

では、上記の手順を最初に示したスクリプトを用いて確認していきます。

1. 非再帰項を評価し、WITH RECURSIVE句で定義したテーブルに追加

WITH RECURSIVE SEQ_TBL(SEQ_NO) AS ( 
    -- 非再帰項
    SELECT
        1
    --　以下は2に続く

この時点で、WITH RECURSIVE句で定義したテーブルであるSEQ_TBL テーブルの SEQ_NO カラムに 1 が追加されます。

2. 再帰項を評価し、WITH RECURSIVE句で定義したテーブルにUNION ALL

    UNION ALL
    -- 再帰項
    SELECT
        SEQ_NO + 1 
    FROM
        SEQ_TBL 
    WHERE
        SEQ_NO < 6

このSELECT文により、すでに 1 が存在しているSEQ_TBL テーブルの SEQ_NO カラムに +1 しているため、 SEQ_NO カラムに 2 が入っているテーブルが得られます（WHERE句の条件も満たす）。このテーブルとSEQ_TBL テーブルをUNION ALLするため、次のテーブルを得ます。

SEQ_NO
1
2

3. 再帰項で得られる結果が空になるまで 2 を実行（空であれば追加されない）

再帰項が空になるのは、SEQ_NO が 6 、すなわち SEQ_NO + 1 が 7 になるときです。しかしこの時はSEQ_TBL テーブルには追加されないので、最終的には、6 までが追加された次のテーブルを得ることができるとわかります。

SEQ_NO
1
2
3
4
5
6

縦持ちと横持ち

例えば、次のとある人物とその趣味についてまとめたHOBBIES テーブルについて考えてみましょう。
このとき、横持ちとは次のようなデータの持ち方のことです。

NAME	HOBBY1	HOBBY2	HOBBY3
山本	サッカー	チャリ旅	ピアノ
渡辺	少林寺	バイク	NULL

渡辺くんは趣味が若干少ないですね。
これを縦持ちにすると、次のようになります。（NULLを除いた場合）

NAME	HOBBY
山本	サッカー
山本	チャリ旅
山本	ピアノ
渡辺	少林寺
渡辺	バイク

これらを相互に変換し合います。変換のモチベーションとしては、横持ちにすると人間にとって直感的で見やすいものになりますし、縦持ちにするとデータの追加、削除が行いやすく、上記の例のようにNULLを発生させにくくなるといった利点が挙げられます。

横持ち → 縦持ち

以下の2つの書き方が主流なようです：

1. UNION (ALL)で結合する方法
2. ID テーブルをCROSS JOINする方法

ロジックが簡単なのは1の方ですが、カラム数が多くなるほど結合がしんどくなりそうなので、より汎用的に書けそうなのは2の方といった印象を勝手ながらもっています。それぞれで書いてみます。

1. UNION (ALL)で結合

行っていることは、趣味それぞれを1つずつ取得するクエリを書き、それらを縦に結合するだけです。
ロジックは単純ですが、UNION ALLを使用するので、カラム名を揃えておくことを忘れないようにしましょう。

SELECT
    WHO, HOBBY1 AS HOBBY
FROM
    HOBBIES
UNION ALL
SELECT
    WHO, HOBBY2 AS HOBBY
FROM
    HOBBIES
UNION ALL
SELECT
    WHO, HOBBY3 AS HOBBY
FROM
    HOBBIES;

2. CROSS JOINで結合

この方法は先ほどよりも少しややこしいです。

1. 趣味の数分の ID(HOBBY_ID) を持つテーブルを作成する
2. HOBBIES テーブルのレコードと ID の全組み合わせで結合する（CROSS JOIN）
3. CASE式で値を持つ HOBBY に絞る

実際に、2 でCROSS JOINした後に得られるテーブルは以下のようになっているため、HOBBY_ID ごとに場合分けすることで、縦持ちになることが想像できます。

NAME	HOBBY1	HOBBY2	HOBBY3	HOBBY_ID
山本	サッカー	チャリ旅	ピアノ	1
山本	サッカー	チャリ旅	ピアノ	2
山本	サッカー	チャリ旅	ピアノ	3
渡辺	少林寺	バイク	NULL	1
渡辺	少林寺	バイク	NULL	2
渡辺	少林寺	バイク	NULL	3

具体的な実装としては、趣味の数だけ ID を持つ、HOBBY_NUM テーブルを作り、CROSS JOINした後に必要なカラムを場合分けしてやればいいです。ID を持つテーブルはカラムが少なければ直接作れますし、汎用性を持たせたければ、上述した再帰を用いて生成することも可能です。

-- 先ほどの再帰で作る（カラム名 HOBBY_ID は AS 句で生成）
WITH HOBBY_NUM AS (
  SELECT 1 AS HOBBY_ID
  UNION ALL
  SELECT
    HOBBY_ID + 1
  FROM
    HOBBY_NUM
  WHERE
    HOBBY_ID < 3
)

-- 以下がメイン
SELECT
   H.NAME,
   CASE HN.HOBBY_ID
        WHEN 1 THEN HOBBY1
        WHEN 2 THEN HOBBY2
        WHEN 3 THEN HOBBY3
        ELSE NULL
    END AS HOBBY
FROM
    HOBBIES AS H
CROSS JOIN
    HOBBY_NUM AS HN;

後はNULLを排除するために、各SELECT文にWHERE句をつけて排除したり、サブクエリにした後にNULL以外を選択するようにすればいいと思います。

縦持ち → 横持ち

少し複雑ですが、発想しては1レコードにまとめる起点にしたいカラム（ここでは NAME）でグループ化したあとに、趣味番号ごとにCASE式を用います（なければ、ROW_NUMBER等で生成し、結合しておけばいい）。ややこしいのはMAXを使う部分です。

SELECT
   NAME,
   MAX(CASE HOBBY_ID WHEN 1 THEN HOBBY ELSE NULL END) AS HOBBY1,
   MAX(CASE HOBBY_ID WHEN 2 THEN HOBBY ELSE NULL END) AS HOBBY2,
   MAX(CASE HOBBY_ID WHEN 3 THEN HOBBY ELSE NULL END) AS HOBBY3
FROM
    HOBBIES
GROUP BY
    NAME;

実際に、上記のコードから MAXとGROUP BYを省いた形で実行すると、次のようなデータが得られます。

NAME	HOBBY1	HOBBY2	HOBBY3
山本	サッカー	NULL	NULL
山本	NULL	チャリ旅	NULL
山本	NULL	NULL	ピアノ
渡辺	少林寺	NULL	NULL
渡辺	NULL	バイク	NULL
渡辺	NULL	NULL	NULL

つまり、HOBBIES テーブルにおける1レコードに対してすべてのCASEが走っているので、条件不一致の部分はNULLになっています。この NULLを消すために NAME でグループ分けしてから、MAXを使うという必要があるということです。

行間比較

SQL において同一レコード内のカラム同士の比較は簡単ですが、行間比較をするのは相対的に難しいです。これを実現する有名なテクニックとして、「相関サブクエリ」や「ウィンドウ関数」なるものが存在するようですが、今回は、「自己結合による行間比較」に出会ったので共有します。

例えば、次のような月ごとの収入と支出をまとめたTOTAL_EXPENSES テーブルがあるとします。

MONTH	KIND	AMOUNT
MAY	INCOME	100000
MAY	EXPENSE	50000
JUNE	INCOME	30000
JUNE	EXPENSE	60000

ここから INCOME と EXPENSE の差がプラスになる月とその差額を取得したい場合に、自己結合によって差額を得る次の実装が考えられます。

SELECT
  TE1.MONTH,
  TE2.AMOUNT - TE1.AMOUNT AS DIFF
FROM 
  TOTAL_EXPENSES AS TE1
  INNER JOIN TOTAL_EXPENSES AS TE2
    ON TE1.MONTH = TE2.MONTH
    AND TE2.KIND = "EXPENSE"
WHERE
  DIFF > 0;

自己結合した後に、得られるテーブルは以下のようになります。

MONTH	KIND	AMOUNT	MONTH	KIND	AMOUNT
MAY	INCOME	100000	MAY	EXPENSE	50000
MAY	EXPENSE	50000	MAY	EXPENSE	50000
JUNE	INCOME	30000	JUNE	EXPENSE	60000
JUNE	EXPENSE	60000	JUNE	EXPENSE	60000

ここでは、片方のテーブル（TE2）の条件を絞ることで比較したいレコードを複製できるため、列同士の比較にもっていくことができているというわけです。

したがって、最終的に次のテーブルを得ることができます。

MONTH	DIFF
MAY	50000

おわりに

長くなってしまいましたが、お読みいただきありがとうございました。
もし間違い等ございましたら、ご指摘いただけますと幸いです。
こんな SQL を実務で書くのかは定かではないですが、実際に解いてみて思考力を養成することができていると感じています。本記事がもし誰かのお役に立てるのであれば幸いです。

参考