11ヶ月におよぶSQL開発現場での学び

こんにちは、まっきーです。

現在は某メーカーの生産技術部門で生産現場のDXを目的としたソフト開発に関わっているのですが、 その一環として、事業部やグループ会社からのさまざまな委託業務も行なっています。

本記事では、グループ会社の情報システム部からの依頼である「基幹システムデータ移行のサポート」を昨年8月〜今年6月の11ヶ月にわたり行いようやく先日終わりを迎えたので、そこでの学びなどを書いてみたいと思います。

事前準備

事前知識が乏しい状況だったので、評判のよかった以下の本（すべてミック氏著）で学びました。 中でも３冊目の「達人SQL」は高度な内容が多くすべては読み切れませんでしたが、業務で難しい要件を満たす上でもっとも役に立ったのはこの本でした。 （CASE文、NOT EXISTS、ウインドウ関数、自己結合、NULLの扱いなど）

SQL 第2版 ゼロからはじめるデータベース操作

達人に学ぶDB設計 徹底指南書

達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ

このほか、PL/SQLも使うとのことだったので、プロシージャとは何かについて以下で概要を学びました。

PL/SQLとは何か　プログラムの特徴と基本構造を理解する：超入門「PL/SQL」（1）（1/2 ページ） - ＠IT

プロとしてのOracle PL/SQL入門 【第3版】（Oracle 12c、11g、10g対応）

業務の概要

今回、10以上の仕様からなるSQLを書きましたが、その多くは以下の図のように、

1. 元となる（不完全な）テーブルを用いる
2. 元テーブルの指定のカラムの値を使い、対象のテーブルから該当のレコードをLEFT JOIN
3. 2を繰り返す
4. できあがった横に長ーいテーブルから特定のカラムのみを抽出して新たなテーブルを作成

という流れで進めていくものでした。 副問い合わせで書こうとするとネストが10とかになってしまうので、「with句」や「ビュー」を積極的に使って細かく分けることで保守性を意識しました。

業務の詳細

要件の例１：複数ヒットしたときはNULLにする

主キーがしっかりしているテーブルであれば、1つのレコードに対しJOIN句でテーブルを結合しても1レコードのままですが、今回任された環境ではそれがされてないために、1レコードに対するJOINで2レコード以上になるケースもままありました。 そのため、要件の例１〜３のような仕様に応じた実装が必要でした。

要件の例１では、以下のように書くことで要件を満たしました。

SELECT
    a.*
   ,CASE
     WHEN COUNT(a.元テーブルの結合カラム) > 1 THEN NULL
     ELSE MAX(b.対象テーブルのカラムX)
    END AS カラムX
FROM 元テーブル a
LEFT JOIN 対象テーブル b
ON a.元テーブルの結合カラム = b.対象テーブルの結合カラム
GROUP BY （元テーブルの全カラム）

これは、JOIN後に複数レコードに膨れ上がると元テーブルの結合カラムのCOUNT数が2以上になるため、その場合は対象テーブルから参照したカラムの値に"NULL"をセットするという処理をしています。

ここでハマりやすいポイントとして、以下があります。 1. 集合関数（COUNT）を使うので、対象カラムについても「MAX」を使う（今回は1件のみなのでMINでもAVEでも可） 2. GROUP BY句を忘れない。カラムは残したいすべてのカラムをひとつ残さずカンマ区切りで記述する

要件の例２：複数ヒットしたときは○○カラムの最大値（最小値）を取る

こちらは「要件の例１」とは変わり、ウインドウ関数を使用して最大値を抽出します。

ウインドウ関数のひとつであるROW_NUMBER関数を使い、パーティション毎（ここでは元テーブルの1レコードに対し1件の対象テーブルの値を取ってきたいので、PARTITION BY句の引数には元テーブルの主キーを選択）に対象テーブルのカラムを大きい順に1,2,3,...と降っていきます。

ここまでの処理を副問い合わせにして、その外側から1のデータだけをWHERE句で残すことで要件を満たしました。

SELECT
    a.*
   ,b.対象テーブルのカラムX AS カラムX
FROM (
    SELECT
        a.*
       ,b.*
       ,ROW_NUMBER() OVER (PARTITION BY a.元テーブルの主キー ORDER BY b.対象テーブルのカラムX DESC) AS rownum
    FROM 元テーブル a
    LEFT JOIN 対象テーブル b
    ON a.元テーブルの結合カラム = b.対象テーブルの結合カラム
)
WHERE rownum = 1

要件の例３：複数ヒットしたときは○○カラムの値が「△から□」の範囲にあるときはその中の最小値、ないときは最小値を取る

この要件はいまだにパッとすぐには浮かばず、これを書いている今でも考えるのですが、以下のように書くことで実現しました。 ポイントは、 - MIN関数の引数にCASE文を使っていること - CASE文を使うことで、値が範囲外のときはNULLを返すようにして、それをCOALESCE関数と組み合わせて第二引数に移るようにしていること

SELECT
    a.*
   ,COALESCE(
        MIN(
            CASE
             WHEN △ <= b.対象テーブルのカラムX AND b.対象テーブルのカラムX <= □ THEN b.対象テーブルのカラムX
             ELSE NULL
            END
        )
       ,MIN(b.対象テーブルのカラムX)
    )
FROM 元テーブル a
LEFT JOIN 対象テーブル b
ON a.元テーブルの結合カラム = b.対象テーブルの結合カラム
)
GROUP BY （元テーブルの全カラム）

要件の例４：自身の親のカラムAが△のときはxxxをセット、そうでないときはXXXをセット

これは階層問い合わせの機能を使い実現していきました。

Oracleの階層問い合わせ（1）　（start with句、connect by句） (1/2)|CodeZine（コードジン）

などを参考に、

• ルートの親を定義（START WITH 条件式）
• 親と子の関係を定義（CONNECT BY id ＝ parent_id）
• 階層問い合わせ（SYS_CONNECT_BY_PATH(探索したいカラムA, 区切り文字)）
• PATHの値から文字列を探索し、存在するかを確認(INSTR(自身のPATHカラム, 探索したい文字列△) > 0)

要件の例５：自分の子のカラムZを集計

これも階層問い合わせ（と自己結合）を使用することで実装できることを学びました。

SQLは割愛しますが、以下の手順で実装しました。

• 自己結合（同じテーブルを２つ結合しa,bとエイリアス）を行う
• 結合条件は、b.PATH LIKE a.PATH% AND b.PATH <> a.PATH　←自身は集計対象外とする
• 子どもの数だけレコード数が膨れ上がるので、GROUP BY句を使いSUMで集計

現場入り当時はGROUP BYの動きにだいぶ苦しみましたが、今では集合としてどう操作されるのかのイメージにだいぶ頭が慣れてきているのを実感できました。

要件の例６：元テーブルのカラムAの先頭文字がAのとき対象テーブルのカラム1と結合、同Bのとき同カラム2と結合

これはCASE文を結合式の右辺で使うことですっきり書けました。

SELECT
    a.*
   ,b.対象テーブルのカラムX
FROM 元テーブル a
LEFT JOIN 対象テーブル b
ON a.元テーブルのカラムA = CASE
                                                    WHEN SUBSTR(a.元テーブルの結合カラム, 1, 1) THEN b.対象テーブルの結合カラム1 
                                                    WHEN SUBSTR(a.元テーブルの結合カラム, 1, 1) THEN b.対象テーブルの結合カラム2
                                                    WHEN SUBSTR(a.元テーブルの結合カラム, 1, 1) THEN b.対象テーブルの結合カラム3
                                                    ELSE b.対象テーブルの結合カラム4
                                                   END 

要件の例７：テーブルのカラムAがゼロだったらカラムBの最大値を取り、カラムAがゼロでなければカラムA＝カラムBとなるレコードを取る。ただし、存在しない場合や複数存在する場合はカラムBはNULLとする

この要件が一番難しかったです。 全レコードを「カラムAがゼロのもの」「カラムAがゼロでないもの」の2つのテーブルに分けるところから行うのですが、

前者のテーブルについては、上の要件例２の要領でやっていくのですが、最大値となるレコードが「複数存在するときにNULLとする」のが厄介でした。 手としては、ROW_NUMBERでなくRANKを使い、複数レコードを残した上でさらに上の要件例１でCASE文を使うやり方を取りました。

後者のテーブルはさらに厄介で、以下の手順を地道に集合を操作していきました。

1. 新たにカラムを用意し、「カラムA＝カラムB」であるレコードにフラグを管理
2. パーティション毎に1で確認したフラグの数を取得
3. CASE文を使い、新たにつぎのカラムを作成する。カラムA＝カラムBでありかつ2で取得したカウントの値が1であれば値を取得し、なければNULLを取得する。
4. 3の処理を行ったのちに、GROUP BYで3のカラムの最大値をとる

丸一日以上うんうん唸っていたり散歩してアイデアが閃くの待つくらいきつかったですが、無事に動いてくれてよかったです。

学んだこと

CASE文をかなり使いこなせるようになった

CASE文自体は値を返すという点に慣れることができたと思います。 なので、条件式の一方に使用できたり（上の要件例６）、MINの引数に使えたりする（上の要件例３）のは面白いなと思いました。

COALESCEを使いこなせるようになった

JavaScriptやReactでも出てくる『NULL合体演算子』と同様、NULLを0に置き換えたりすることでその後の計算を行えるようにする方法として重宝したのはもちろん、 それだけでなく今回の仕様にあったような、

ルート１の条件に合致しなければルート２へ、さらに合致しなければルート３へと進む

といった要件に対して、一度ルート１、ルート２、ルート３で使う対象テーブルをすべてLEFT JOINで結合（がっちゃんこ）し、そのときに自身のレコードの対象ルート以外のカラムの値をNULLとしておくことで、COALESCE(ルート１のカラム, ルート２のカラム, ルート３のカラム)として値を適切に取ってくるような処理をとてもすっきり書くことができました。

主キーがないテーブルがいかに扱いに大変かを知ることができた

これはもう言わずもがなですが、主キーを設定すること、あと表記揺れが生じやすい CHAR や VARCHAR ではなくなるべく番号やUUIDで管理しなければいけないことを体感できました。

関数型プログラミングの勘所を（少しだけ）理解できた & 面白く感じられるようになった

関数型プログラミングの強みである「集合をそのまま扱う考え方」にだいぶ慣れた気がします。 SQLでのGROUP BYやウインドウ関数を使っていく上で、集合に対する考え方がとても大事であり、最初は理解するのに苦労しましたが、いまではそのよさがわかるような気がします。

苦労した点

データ品質が低い

主キーがない、または複合主キーなのにその一部しか使われていない

主キーがないテーブルが今回多くさらにはまったく同一のレコードが含まれることを考慮に入れた実装を求められたのは面倒でした。 主キーが適切に付与されていれば、上の要件１や２などを仕様として考えるまでもないので、今後自分がテーブル設計するときの反面教師にしようと思いました。

同じ意味のデータがさまざまなデータ型で存在している

たとえば日付はDATE型であってほしいのに、文字列（'20230709'だったり'2307'だったり様々・・・）、さらには数値型も存在していると日付なのに【101】など格納されていて驚かされました・・・ （2000年については20000101 → 000101 →(NUMBER型)→ 101）

これについては、NUMBER型からDATE型に一発変換できないので、

TO_DATE(LPAD(101,'0',6),　'YYMMDD')

として直してあげてから「=」でつないであげる必要があります。

表記揺れやデータが複数カラムに分割されている、さらにそれらのデータが結合条件に使用されている

• 単位を示す'M'などの文字が半角だったり全角だったり　←TO_SINGLE_BYTEで変換が必要
• 一部テーブルで㈱や㈲などとして格納されている
• 2カラムにまたがってデータが格納されている、さらにそれが結合条件に使われているというカオスぶり・・・
• 必要以上にNULLが多い　←NOT NULL制約の大切さを学びました

パフォーマンス面

今回数百万行単位のテーブルを扱う必要があったため、少しでも効率の悪い書き方をするととたんにパフォーマンスが悪化（結果が出るのに10分以上かかるなど）してしまい、パフォーマンス悪化の勘所が少しつかめた気がします。

具体的には、

• JOINする際には、JOINするテーブルを事前に結合カラムでGROUP BYして行数を減らしておく
• 処理に使わないカラムを極力SELECT句で省く
• 複数テーブル結合時はWHERE句はなるべくLEFT JOIN句＋ON句で少ない行数のテーブルを基準にするようにする

素のSQLでのデバッグが難しい

Oracle特有かもしれませんが、デバッグがとてもしんどかったです・・・ エラーがあいまいすぎる、かつ何行目かも示してくれないのはかなり厳しかったです。

おわりに

今回、いち会社の基幹データベース内の数百万件、数千万件に及ぶデータ（社員情報や顧客情報などのマスターデータや注文や在庫などのトランザクションデータも）が格納されたデータベースを触る機会を通じて貴重な経験ができました。

SQL作成自体はRailsのActiveRecordなどのORMで代用できるとはいえ、それぞれのORM機能がどういうSQLを生成しているかについての理解の助けになると感じています。

またデータの再整形やクレンジングに強くなったこと、パフォーマンス面やトランザクション処理について実際に実行して学ぶことができたこともよかったです。

今月からは新たに社内アプリ（生産装置トラブル対策共有システム）のEOL対応と機能追加を3ヶ月かけて進める予定となっています。

アプリで使われている技術はVue.js、TypeScript、C#、PostgreSQLで、 EOLの内容は

• Vue.js　2.6 ⇒ 3以上
• ASP.NET Core 2.2 ⇒ 6.0
• PostgreSQL 11 ⇒ 15

となかなか重い感じですが、なんとかこなして転職活動でのアピール材料にできるよう今月からまた気持ち新たにがんばろうと思います。