化学の技術開発現場で使うSQL

はじめに

私は化学メーカーの現場で技術開発を行っています。
これまで業務の一環で、部署の実験データを集約するデータベースの構築にも関わりました。

ただ、化学メーカーの現場では、

• Excelで実験データを管理している
• SQLは「情報系の人が使うもの」というイメージがある
• データベースを見ると「なぜこんな複雑な形にするの？」と感じる

という方も多いのではないでしょうか。私自身も最初はそうでした（笑）

そこで、今回は化学メーカーの現場技術者のような方にとっても、なじみやすいテーマでSQLを勉強をしていけたらと考えています。

今回のシチュエーションとデータ

この記事では、「複数の樹脂素材をコンパウンドさせて新しい素材を作る」という現場をモデルにして考えてみましょう。
そこで、簡単で小規模なデータを使って、SQLを書いてみましょう。
※本記事でのテーブルデータは架空のものであり、化学的事実に基づいておりません。

実際、化学の現場でよく見かけるのはこのようなテーブルだと思います。
各Material_IDごとに、使ったポリマー材料種とその比率がテーブル化されていると考えてください。

テーブル①

例えば、機械学習をするにはテーブル①の形の方が解析がしやすいでしょう。
しかし、データベース構築の観点では、このテーブル①の形態は推奨されづらいです。

理由は主に長期運用・拡張・分析・実務変更に弱くなるからです。
具体的には

1.ポリマー種の追加に弱い

例えば新しく、ポリマー種ABS、PET、PBT、、、のようなものを追加するとなると、そのたびにデータベースに列を追加し、更新しなくてはいけません。
詳細は、こちらの記事をご覧ください。
https://qiita.com/MicroCheese/items/8df6a08b02ce1ca47832

2.NULLだらけになる

新しく追加したポリマー種ABS、PET、PBT、、、等は、ほかのMaterial_IDではNULLになりやすいことが予想されます。
これは

• 見づらい
• 保守しにくい＝テーブル変更・SQL修正・運用管理がどんどん複雑になる
• 無駄が多い

と言えます

3.正規化に違反している

データベースでは本来、

• 列：属性
• 行：データ

であるのが基本と言えます。
よって、PE、PP、PAなどは、本来「polymer」という属性に入る値であり、行に対応するため不自然となります。

例えば、ここにABSを追加する場合、本来は「ABSを新たに使った」というデータの話をしているのに

ALTER TABLE ADD COLUMN ABS

と、データベース構造変更になってしまい不自然となるイメージです。

どんなテーブルにするべきか

推奨するのは、以下のようなテーブルです

テーブル②

私も化学の技術開発の現場にいるので、この構造に違和感を感じる気持ちはよくわかります（笑）。
しかし、データベースの観点で考えれば、こちらの方が極めて自然です。

先ほど申し上げたテーブル①のような横持ちのテーブルの弱点は解消しています。
そして、このテーブル②のような縦持ちのテーブルでは、ポリマーの情報をきちんとデータで扱うことができています。

例えば、テーブル①の場合だと、「PAを含んでいるか」の検索には

WHERE PA IS NOT NULL

のような式を使わなくてはなりません。

しかし、テーブル②の場合だと、PAの情報は行として入っているので、

WHERE polymer = 'PA'

と、データの検索を行うことができます。

さらに、SQLを使えば縦持ちのテーブルから横持ちのテーブルに変換は容易にできます。
そこで、この記事の本題として、その方法について学んでいきましょう。

①縦持ちの組成テーブルを、横持ちに変換してみよう

データベースの箱の作り方については、こちらの記事をご参照ください。
https://qiita.com/MicroCheese/items/8acb216d4831b4fa7f1c

箱を作った後は、基本的なINSERT文を使ったSQLを実行することでデータベースにデータを格納できます。
（DBeaverでもVSCodeでも実行できます。）
テーブルのカラム名は、それぞれ

• material_id：キー
• polymer：ポリマーの種類
• content:含有比率

です。

INSERT INTO polymer_compound_basic
    (material_id, polymer, content)
VALUES
    ('M001', 'PE', 40),
    ('M001', 'PP', 60),

    ('M002', 'PE', 90),
    ('M002', 'PA', 10),

    ('M003', 'PE', 40),
    ('M003', 'PP', 40),
    ('M003', 'PA', 20),

    ('M004', 'PE', 30),
    ('M004', 'PP', 30),
    ('M004', 'PA', 10),
    ('M004', 'EVA', 30);

すると以下のようなデータができます。

さて、いよいよ縦持ちから横持ちテーブルに変換してみましょう。
方法としては、以下のようなCASE式を使うことによって実現できます。
CASE式の詳しい説明は省略しますが、簡単に言えばSQLでの条件分岐式です。

SELECT
    material_id,

    MAX(CASE WHEN polymer = 'PE'  THEN content END) AS "PE",
    MAX(CASE WHEN polymer = 'PP'  THEN content END) AS "PP",
    MAX(CASE WHEN polymer = 'PA'  THEN content END) AS "PA",
    MAX(CASE WHEN polymer = 'EVA' THEN content END) AS "EVA"

FROM polymer_compound_basic
GROUP BY material_id
ORDER BY material_id;

これを実行することによって、縦持ちであった組成テーブルを以下のように横持ちに変換することができます。

【補足】

なぜ、MAX()のような集約関数を書いているのかと思った方もいるかもしれません。
結論を言うと、この場合集約関数は必須です。

試しに、集約関数なしでSQLを実行してみましょう。

SELECT
    material_id,

    CASE WHEN polymer = 'PE'  THEN content END AS "PE",
    CASE WHEN polymer = 'PP'  THEN content END AS "PP",
    CASE WHEN polymer = 'PA'  THEN content END AS "PA",
    CASE WHEN polymer = 'EVA' THEN content END AS "EVA"

FROM polymer_compound_basic
ORDER BY material_id;

すると、このように1行に集約されないテーブルが出来上がってしまいます。

これは、集約関数がないCASE WHENは縦持ちテーブルを1行ずつ処理していくイメージだからです。

この時、重要になるのがMAX()のような集約関数です。
例えば、M001のPE列だけを見ると、CASE WHENによって内部的には以下のような状態になります。

material_id	PE
M001	40
M001	NULL

ここで、

MAX(CASE WHEN polymer = 'PE' THEN content END)

とすることによって、M001グループでは

MAX(40, NULL) 
→ 40

のように、NULL以外の値を取り出すことができます。すなわち、この時のMAX()は最大値を求めるというより、

• グループ内のNULL以外の値を取り出す
• 値を1つにまとめる

という役割を担っています。

その後、

GROUP BY material_id

によって、material_idごとにデータをグループ化しています。

※なお、これに関する説明は後述する参考図書②のP.10-11にも記載があります。

②カラムとデータを追加してみよう

次に、このテーブルを使ってコンパウンド温度を追加することを考えてみましょう。
この時に準備するテーブルは以下のようになります。

特徴的なのは、同じMaterial_IDを持つ行に、すべて同じ値のコンパウンド温度Compounding Temperatureが入るということです。

実際、データベース設計の観点では、この温度を別テーブルに分ける設計もよく行われますが、今回は

• SQLによる横持ち変換
• GROUP BYとMAX()との親和性

を優先するため、あえて同じテーブルに持たせています。

カラムの追加後、データを入れるSQLは以下です。

UPDATE polymer_compound_basic
SET compounding_temperature =
    CASE
        WHEN material_id = 'M001' THEN 200
        WHEN material_id = 'M002' THEN 240
        WHEN material_id = 'M003' THEN 250
        WHEN material_id = 'M004' THEN 240
    END
WHERE material_id IN ('M001', 'M002', 'M003', 'M004');

このようなテーブルが出力されます。

それでは、このテーブルも横持ちに変換してみましょう。
考え方は先ほどと同じで、

• material_idごとにcompounding_temperatureを集約する
• そのためにMAX()を使う

ということになります。

SELECT
    material_id,

    MAX(CASE WHEN polymer = 'PE'  THEN content END) AS "PE",
    MAX(CASE WHEN polymer = 'PP'  THEN content END) AS "PP",
    MAX(CASE WHEN polymer = 'PA'  THEN content END) AS "PA",
    MAX(CASE WHEN polymer = 'EVA' THEN content END) AS "EVA",
    
    MAX(compounding_temperature) AS "comp._temp." ---新しく追加

FROM polymer_compound_basic
GROUP BY material_id
ORDER BY material_id;

これを実行することで、以下のようなテーブルに変換できます。

まとめ

データベース内部では、縦持ちでデータを管理することで、長期運用や拡張性を高めることができます。
一方で、実際の分析・可視化・機械学習などでは、横持ちテーブルの方が扱いやすい場面も多くあります。

そのため実務では、

• 保存は縦持ち
• 利用時に横持ちへ変換

という考え方が非常によく使われます。

この記事を通して、SQLに普段あまり馴染みのない現場技術者の方でも、

• データベースに適したテーブル設計
• SQLを使った縦持ち⇔横持ち変換
• GROUP BY や CASE式、集約関数の考え方

を、化学材料の例を通してイメージしていただけたなら嬉しいです。

参考図書

私は下記の、ミックさんの本を使ってSQLを勉強しています。

• 参考図書①：『SQL ゼロから始めるデータベース操作』（翔泳社）(https://www.amazon.co.jp/dp/4798144452)
• 参考図書②：『達人に学ぶSQL徹底指南書』（翔泳社）(https://www.amazon.co.jp/dp/B07GB4CNKP)

今は生成AIがコーディングを行える時代ですが、生成AIが書いたコードを読んで解釈するためにも、SQLをはじめとしたプログラミング言語の基礎理解が重要と考えています。

特に、現場でデータベースやSQLに関わる技術者にとっては、「自分でゼロから全部書けること」以上に「書かれたSQLを読んで理解できること」が重要になる場面も多いと思います。

参考図書①はSQL初心者でも理解しやすく、これからSQLを学びたい方に非常におすすめです。ぜひ手に取ってみてください。