SQLiteと闘う：第二弾

こんにちは、和美です。

仕事で、Excel関数とSQLiteというデータベースシステムが使えるソフトを使って開発をしています。
SQLは不得意な上に覚えられなくて調べたメモ書きです。
去年2020年の三月頃に臨時上司から受けたAccessの講座で言われた、「 SQLからは逃げられない 」 という言葉を胸に頑張っています。

vs SQLiteの第二弾は複数テーブルの結合です。
使用したデータベースの環境はSQLite 3.24.0。

【 目次 】

1. 実現したいこと
2. 試したこと1
3. 試したこと2
4. 解決策
5. 最後に

1. 実現したいこと

以下のようなA、B、Cのテーブルを結合する時の話です。

テーブルA：題名テーブル

通し番号（主キー）	題名
0001	あいうえお
0002	かきくけこ

テーブルB：通し番号別記号テーブル

通し番号（主）	No.（主）	記号
0001	1	AAA
0001	2	BBB
0001	3	CCC
0002	1	BBB
0002	2	DDD

テーブルC：名称テーブル

記号（主）	名称
AAA	いろは
BBB	にほへ
CCC	とちり
DDD	ぬるを

最終的に表示させたいテーブルのイメージはこちら。
テーブルAの通し番号をキーにして、行数はテーブルAと同じ二行のままテーブルBを横に並べて結合、テーブルCはテーブルBの記号をキーにして結合です。
…… 例では省略しましたが、テーブルBのNo.は実際には最大で十件ありました ……。

テーブルA+No.1のB+No.1のC+No.2のB+No.2のC+No.3のB+No.3のC

通し番号	名前	No.1記号	No.1名称	No.2記号	No.2名称	No.3記号	No.3名称
0001	あいうえお	AAA	いろは	BBB	にほへ	CCC	とちり
0002	かきくけこ	BBB	にほへ	DDD	ぬるを

途中経過はいらないから最後のコードだけを見たいという方はこちら

2. 試したこと1

最初は別々の条件を元にテーブルを結合する方法が分からず、SQLが得意な職場の方に教えて頂きました。

テーブルA+B+C

通し番号	名前	No.	記号	名称
0001	あいうえお	1	AAA	いろは
0001	あいうえお	2	BBB	にほへ
0001	あいうえお	3	CCC	とちり
0002	かきくけこ	1	BBB	にほへ
0002	かきくけこ	2	DDD	ぬるを

まずはここまで。
最終的に欲しいテーブルは次章まで持ち越しです。

SELECT
 A.通し番号,
 A.題名,
 temp.No.,
 temp.記号,
 temp.名称
FROM
 テーブルA AS A, -- 題名テーブル
 (   SELECT
         B.通し番号,
         B.No.,
         C.名称,
     FROM
         テーブルB AS B, -- 通し番号別記号テーブル
         テーブルC AS C  -- 名称テーブル
     WHERE
         B.No.= C.No.
 ) AS temp
WHERE
 temp.通し番号 = A.通し番号
;

改めて書き直すと「 なんでこんな事に悩んでいたのか 」 と思う内容でしたが、分からない時は分からないのです。

FROMの後にある括弧内は、「 副問い合わせ 」 や 「 サブクエリ 」 と呼ばれ、和美がもっとも苦手な領域です。
…… 苦手な割には今の開発で多用していますが ……。
副問い合わせの中でテーブルBとテーブルCを剥ぎ合わせて 『 temp 』 という名前の仮テーブルを作成し、その仮テーブルとテーブルAを剥ぎ合わせています。
しかも書き直す時に気づきましたが、副問い合わせ内のテーブルBとテーブルCの間にINNER JOINが省略されていますね。

教えてくださった職場の方からは 「 副問い合わせを使わなくても書けるかも 」 と言われましたが、修正は最終章に後述します。

3. 試したこと2

この章で一旦は完成です。
前章で三つのテーブルを接ぎ合せるのも苦労しましたが、縦持ちから横持ちに書き換える方法は、検索ワードから難航しました ……。

縦にデータが並んでいる正規化された状態を「縦持ち」、横にデータが並んでいる状態を「横持ち」と呼び、縦持ちから横持ちに書き換える事を『pivot（ピボット）』、逆を『 unpivot（アンピボット）』と呼ぶそうです。

参考リンク1：[SQL]データの縦持ち、横持ちを入れ替える：Developers.IO
参考リンク2：SQL: CASE 句を使って縦持ちのデータを横持ちに変換する：CUBE SUGAR CONTAINER

上記参考リンク1ではrow_number() over(partition というコードが使われていますが、SQLite 3.24.0はrow_number、分析関数のoverとも非対応です。
（ 三ヶ月後に発表されたSQLite 3.25.0より対応 ）
色々やっていると、結局使いませんでした ……。

…… あまりにも読み辛いので修正します。
完成形のテーブルイメージを再掲しておきます。

テーブルA+No.1のB+No.1のC+No.2のB+No.2のC+No.3のB+No.3のC

通し番号	名前	No.1記号	No.1名称	No.2記号	No.2名称	No.3記号	No.3名称
0001	あいうえお	AAA	いろは	BBB	にほへ	CCC	とちり
0002	かきくけこ	BBB	にほへ	DDD	ぬるを

SELECT
 A.通し番号,          -- 通し番号
 A.題名,              -- 題名
 -- ここから下記号
 SUM( CASE
         WHEN temp.No. = 1
     AND temp.通し番号 = A.通し番号 THEN temp.記号
     END ) AS No.1記号,
 MAX( CASE
         WHEN temp.No. = 1
     AND temp.通し番号 = A.通し番号 THEN temp.名称
     END ) AS No.1名称,
 SUM( CASE
         WHEN temp.No. = 2
     AND temp.通し番号 = A.通し番号 THEN temp.記号
     END ) AS No.2記号,
 MAX( CASE
         WHEN temp.No. = 2
     AND temp.通し番号 = A.通し番号 THEN temp.名称
     END ) AS No.2名称,
 SUM( CASE
         WHEN temp.No. = 3
     AND temp.通し番号 = A.通し番号 THEN temp.記号
     END ) AS No.3記号,
 MAX( CASE
         WHEN temp.No. = 3
     AND temp.通し番号 = A.通し番号 THEN temp.名称
     END ) AS No.3名称
 -- ここまで記号
-- ここからFROM
FROM
 TableA AS A,    -- テーブルA：題名テーブル
 (   SELECT
         B.記号,           -- 記号
         B.No.,            -- No.
         B.通し番号,       -- 通し番号
         C.名称            -- 名称
     FROM
        TableB AS B,        -- テーブルB：通し番号別記号テーブル
        TableC AS C         -- テーブルC：名称テーブル
     WHERE
         B.記号 = C.記号
 ) AS temp
GROUP BY
 A.通し番号
;

テーブルにない値の表示が心配でしたが、正しく空白になっていました。
途中の副問い合わせにMAXとSUMが入り混じっているのは、数値と文字列の違いです。
SUMでは名称の文字列が表示されず、何となく試したらMAXでは文字列が正しく表示されました。
最終行のGROUP BYがないと、テーブルBに登録されている通し番号分の行が表示されます。

4. 解決策

最終的に完成したコードは以下の通り。
FROMの中にあった副問い合わせは削除しました。

SELECT
 A.通し番号,          -- 通し番号
 A.題名,              -- 題名
 -- ここから下記号
 SUM( CASE
         WHEN temp.No. = 1
     AND temp.通し番号 = A.通し番号 THEN temp.記号
     END ) AS No.1記号,
 MAX( CASE
         WHEN temp.No. = 1
     AND temp.通し番号 = A.通し番号 THEN temp.名称
     END ) AS No.1名称,
 SUM( CASE
         WHEN temp.No. = 2
     AND temp.通し番号 = A.通し番号 THEN temp.記号
     END ) AS No.2記号,
 MAX( CASE
         WHEN temp.No. = 2
     AND temp.通し番号 = A.通し番号 THEN temp.名称
     END ) AS No.2名称,
 SUM( CASE
         WHEN temp.No. = 3
     AND temp.通し番号 = A.通し番号 THEN temp.記号
     END ) AS No.3記号,
 MAX( CASE
         WHEN temp.No. = 3
     AND temp.通し番号 = A.通し番号 THEN temp.名称
     END ) AS No.3名称
 -- ここまで記号
-- ここからFROM
FROM
 TableA AS A,    -- テーブルA
    INNER JOIN
        TableB AS B        -- テーブルB
ON A.通し番号 = B.通し番号
    INNER JOIN
        TableC AS C        -- テーブルC
ON B.通し番号 = C.B.通し番号
GROUP BY
 A.通し番号
;

SQLを書く時は、とりあえず構文エラーが出ないコードをそのままに、別のところで「実験用」として新しくコードを書いて、正しく動く、構文エラーが出ない事を確認してから元のコードに上書きし、「実験用」は紛らわしいので削除します。
なので試行錯誤の途中経過は不明ですが …… 結局、FROMの中にあるtempの副問い合わせは不要になりました。

5. 最後に

vs SQLite関係の第二弾はテーブル結合、縦持ちと横持ちの変換でした。
この時は難航していましたが、この縦 → 横変換を何度も行う羽目になったので、少しは覚えられたかな …… と思います。
データベースがマイナーなので知らない方も多いかとは思いますが、お役に立ったら嬉しいです。