はじめに
私は化学メーカーの現場で技術開発を行っています。
これまで業務の一環で、部署の実験データを集約するデータベースの構築にも関わりました。
今回も、化学の技術開発現場を想定したSQLの例を学んでいきたいと思います。
化学に限らず、ほかの製造業の業種でも活用できると考えておりますので、ぜひご覧ください。
想定するシチュエーション
今回は、塗料を作っている技術開発部での「塗料に添加する成分の種類や量を変えた実験」に対応できるデータベースを考えていきたいと思います。
具体的には
- 技術開発部では塗料の合成を行い、新製品の開発を目指している
- 実験室で合成してできた塗料サンプルには、機能付与を目的として添加剤を入れる場合がある
- その添加剤の性能を確かめるために、添加剤の種類や量を変えたサンプルを作ることがよくある
- 添加剤には、例えば増粘剤、分散剤、光安定剤など、用途に応じてさまざまな種類がある
- 添加剤を加えることもあれば、加えないサンプルもあり、また複数種類加える場合もある
従って、データベースは以下の技術的要件を満たすことが必要です。
- 複数種類の添加剤の添加に対応できる
- 添加剤を加えない場合にも対応できる
- それらのサンプルを識別できる
です。
ERDを書いてみよう
データベースに慣れていない人は、例えば分散剤の種類と量の列を作り、増粘剤の種類と量の列を作り、、、というイメージをしてしまいがちです。
しかし、そのような横持ちのテーブル設計は避けるべきでしょう。
その理由はこちらをご覧ください。
https://qiita.com/MicroCheese/items/ac004354b125faf75bb0
そこで、次のようなERDを書いてみました。
Sampleテーブル
Sample テーブルは、実験室で合成された塗料サンプルを管理するテーブルです。
添加剤を加えていないサンプルだけでなく、添加剤を加えた後の派生サンプルも同じ「サンプル」として管理します。
| カラム名 | 説明 |
|---|---|
sample_id |
サンプルを一意に識別するID（主キー） |
base_sample_id |
元となったサンプルID。添加剤なしのベースサンプルから派生した場合、その元サンプルの sample_id を格納する（自己参照外部キー） |
sample_name |
サンプル名 |
例えば、S001 をベースサンプルとして、そこに添加剤を加えた S002 や S003 を作成した場合、base_sample_id に S001 を格納することで、「どのサンプルから派生したか」を追跡することができます。
Additiveテーブル
Additive テーブルは、塗料に添加する添加剤のマスタテーブルです。
添加剤名や種類など、添加剤自体の情報を管理します。
| カラム名 | 説明 |
|---|---|
additive_id |
添加剤を一意に識別するID（主キー） |
additive_name |
添加剤名 |
例えば、増粘剤、分散剤、光安定剤などを管理します。
Sample_additiveテーブル
Sample_additive テーブルは、「どのサンプルに、どの添加剤を、どれだけ添加したか」を管理する中間テーブルです。
1つのサンプルに複数の添加剤を添加できるようにするため、多対多関係を解消する目的で作成しています。
主キーは、sample_idと additive_idの複合主キーであることが特徴です。
| カラム名 | 説明 |
|---|---|
sample_id |
添加剤を加えたサンプルID（外部キー） |
additive_id |
添加した添加剤ID（外部キー） |
additive_ratio |
添加剤の添加量・配合比率 |
このテーブルのイメージとしては、以下のようになります。
このようなERDにするメリット
- 複数種類の添加剤に対応できる
- 添加剤がない場合にも対応できる
- 添加剤あり・なしを区別せず、同じ「Sample」という粒度で統一的に管理できる
- 拡張性がよい
等が挙げられます。
特に拡張性について、最初に成分の種類や量を変える実験を想定していなかったりすると、現場では上のERDのSampleテーブルだけが存在しているケースも多いでしょう。
そこから、成分の種類や量を変える実験に対応できるようにするとき、上記のようなデータベース設計をすれば操作がとてもしやすいです。
SQLで可視化を行ってみよう
実際に実務で取り扱う際など、どのようなSQLを書くべきかを学びます。
① テーブルの作成
--------------------------------------------------
-- Sampleテーブル
--------------------------------------------------
CREATE TABLE sample (
sample_id TEXT PRIMARY KEY,
base_sample_id TEXT,
sample_name TEXT NOT NULL,
-- 自己参照外部キー
CONSTRAINT fk_sample_base
FOREIGN KEY (base_sample_id)
REFERENCES sample(sample_id),
-- 自分自身を参照することを防止
CONSTRAINT chk_sample_not_self
CHECK (
base_sample_id IS NULL
OR base_sample_id <> sample_id
)
);
--------------------------------------------------
-- Additiveテーブル
--------------------------------------------------
CREATE TABLE additive (
additive_id TEXT PRIMARY KEY,
additive_name TEXT NOT NULL
);
--------------------------------------------------
-- Sample_additiveテーブル
--------------------------------------------------
CREATE TABLE sample_additive (
sample_id TEXT NOT NULL,
additive_id TEXT NOT NULL,
additive_ratio NUMERIC(5,4) NOT NULL,
-- 複合主キー
PRIMARY KEY (sample_id, additive_id),
-- 外部キー
CONSTRAINT fk_sample_additive_sample
FOREIGN KEY (sample_id)
REFERENCES sample(sample_id),
CONSTRAINT fk_sample_additive_additive
FOREIGN KEY (additive_id)
REFERENCES additive(additive_id)
);
--------------------------------------------------
-- インデックス
--------------------------------------------------
CREATE INDEX idx_sample_base_sample_id
ON sample(base_sample_id);
CREATE INDEX idx_sample_additive_additive_id
ON sample_additive(additive_id);
注目すべきは、
-- 自己参照外部キー
CONSTRAINT fk_sample_base
FOREIGN KEY (base_sample_id)
REFERENCES sample(sample_id),
で自己参照を行っています。ここで、
-
CONSTRAINT fk_sample_base：制約の名前付け（エラー確認や、のちに制約削除がしやすい） -
FOREIGN KEY (base_sample_id)：FKの指定 -
REFERENCES sample(sample_id),：sampleテーブルのsample_idを参照
を意味しています。
最後のインデックスは、検索高速化のための索引作成です。
今回はデモなのでデータ数も少なく効果を実感しづらいですが、大量データになる場合などは作成するとよいでしょう。
② データの挿入
SQLでINSERTを行い、以下のようなテーブルを作成しました。詳しいSQLは省略します。
〇 sampleテーブル
〇 additiveテーブル
〇 sample_additiveテーブル
可視化
これらのテーブルを使って、実際に分析・可視化しやすい形へ変換してみましょう。
実務では、組成情報を一覧比較しやすくするため、縦持ちデータを横持ちテーブルへ変換したいケースがよくあります。
そこで、まずはCASE文を用いて横持ち化を行います。
SELECT
s.sample_id,
s.base_sample_id,
s.sample_name,
MAX(
CASE
WHEN a.additive_name = '増粘剤'
THEN sa.additive_ratio
END
) AS "増粘剤",
MAX(
CASE
WHEN a.additive_name = '光安定剤A'
THEN sa.additive_ratio
END
) AS "光安定剤A",
MAX(
CASE
WHEN a.additive_name = '光安定剤B'
THEN sa.additive_ratio
END
) AS "光安定剤B",
MAX(
CASE
WHEN a.additive_name = '分散剤'
THEN sa.additive_ratio
END
) AS "分散剤"
FROM sample s
LEFT JOIN sample_additive sa
ON s.sample_id = sa.sample_id
LEFT JOIN additive a
ON sa.additive_id = a.additive_id
GROUP BY
s.sample_id,
s.base_sample_id,
s.sample_name
ORDER BY
s.sample_id;
CASE文を使ったこのSQLを実行すると、以下のような横持ちテーブルを得ることができます。
もちろん、単純な組成比較であれば、このテーブルでも十分実用的です。
しかし、化学技術開発の現場では、以下のようなニーズもよく出ることでしょう。
sample_idのS001〜S005は、元をたどるとS001から派生したサンプルである- S004とS005は、S001を基準に考えるとS002で加えた増粘剤分も含まれている
そこで、S001を基準として最終的に含まれる添加剤と量をすべて把握できるようにし、S001〜S005を比較しやすいテーブルへ変換したいと考えます。
これを解決するためには、再帰CTEを使うことで達成できます。
具体的なSQLは以下です。
WITH RECURSIVE sample_tree AS (
-- 基準サンプル：S001
SELECT
sample_id,
base_sample_id,
sample_name,
sample_id AS root_sample_id
FROM sample
WHERE sample_id = 'S001'
UNION ALL
-- S001から派生したサンプルを再帰的に取得
SELECT
s.sample_id,
s.base_sample_id,
s.sample_name,
st.root_sample_id
FROM sample s
JOIN sample_tree st
ON s.base_sample_id = st.sample_id
),
ancestor_path AS (
-- 各サンプル自身
SELECT
sample_id AS target_sample_id,
sample_id AS ancestor_sample_id
FROM sample_tree
UNION ALL
-- 各サンプルの親をたどる
SELECT
ap.target_sample_id,
s.base_sample_id AS ancestor_sample_id
FROM ancestor_path ap
JOIN sample s
ON ap.ancestor_sample_id = s.sample_id
WHERE s.base_sample_id IS NOT NULL
),
cumulative_additive AS (
SELECT
ap.target_sample_id AS sample_id,
a.additive_name,
SUM(sa.additive_ratio) AS additive_ratio
FROM ancestor_path ap
JOIN sample_additive sa
ON ap.ancestor_sample_id = sa.sample_id
JOIN additive a
ON sa.additive_id = a.additive_id
GROUP BY
ap.target_sample_id,
a.additive_name
)
SELECT
st.sample_id,
st.base_sample_id,
st.sample_name,
COALESCE(MAX(CASE WHEN ca.additive_name = '増粘剤' THEN ca.additive_ratio END), 0) AS "増粘剤",
COALESCE(MAX(CASE WHEN ca.additive_name = '光安定剤A' THEN ca.additive_ratio END), 0) AS "光安定剤A",
COALESCE(MAX(CASE WHEN ca.additive_name = '光安定剤B' THEN ca.additive_ratio END), 0) AS "光安定剤B",
COALESCE(MAX(CASE WHEN ca.additive_name = '分散剤' THEN ca.additive_ratio END), 0) AS "分散剤"
FROM sample_tree st
LEFT JOIN cumulative_additive ca
ON st.sample_id = ca.sample_id
WHERE st.sample_id BETWEEN 'S001' AND 'S005'
GROUP BY
st.sample_id,
st.base_sample_id,
st.sample_name
ORDER BY
st.sample_id;
これを実行すると、以下のように目的のテーブルを得ることができます。
再帰CTEを学習しよう
再帰CTE（Common Table Expression）とは、簡単に言えば「親をたどって、さらにその親をたどる」をSQLで繰り返す操作になります。
SELECT *
FROM sample
WHERE base_sample_id = 'S001';
のような通常のSQLでは、'base_sample_id'がS001であるいわば「S001の子供」しかとってくることができません。
しかし、実際は
S001
└ S002
└ S004
のように、子のさらに子もあり、これらをたどりたいのが今回のケースです。
そこで再帰CTEは、「見つけた子に対して、もう一回同じ処理をする」を繰り返します。
CTEとは、一時的な仮想テーブルを作成できるSQL構文です。
--- CTEがあらわすもの
WITH 名前 AS (
SELECT ...
)
--- CTEの具体例（仮想テーブル）
WITH high_gloss AS (
SELECT *
FROM sample
WHERE gloss > 90
)
SELECT *
FROM high_gloss;
今回は
① スタート地点(S001)作成
S001を取得し、これをスタートとしています。
WITH RECURSIVE sample_tree AS (
-- 基準サンプル：S001
SELECT
sample_id,
base_sample_id,
sample_name,
sample_id AS root_sample_id
FROM sample
WHERE sample_id = 'S001'
② 今見つかったサンプルを親として子を探す
UNION ALL
-- S001から派生したサンプルを再帰的に取得
SELECT
s.sample_id,
s.base_sample_id,
s.sample_name,
st.root_sample_id
FROM sample s
JOIN sample_tree st
ON s.base_sample_id = st.sample_id
),
すなわち、以下のようなイメージになります。
sample_tree
最初:
S001
↓ これを使って検索
S002
S003
↓ さらにこれを使って検索
S004
S005
このような構造は「木構造・階層構造」と言われ、再帰CTEは木構造を扱うSQLです。
実際、今回の親子構造は以下のようになっています。
S001
├ S002
│ ├ S004
│ └ S005
└ S003
まとめ
「このようなケースはどのようなデータベースにするべきか」、「SQLで実現できるのか」を遊びながら考えることが、データベースを楽しく勉強するきっかけになると考えています。
今回は、化学技術開発の現場を題材として、
- 添加剤振り実験に対応できるERD設計
- 中間テーブルを用いた多対多関係のモデリング
- CASE文を用いた横持ち変換
- 再帰CTEを用いた派生サンプルの追跡
について学びました。
実務では、最初から完璧な要件が見えていることは少なく、
- 後から添加剤振り実験へ対応したい
- 派生サンプルを追跡したい
- 物性DBと連携したい
など、運用途中で新たな要求が増えることも珍しくありません。
そのような場合でも、
- テーブル責務を適切に分離する
- 中間テーブルを利用する
- 正規化を意識する
ことで、長期運用・拡張・分析に強いデータベースを構築しやすくなります。
また、今回扱った再帰CTEは少し難しかったかもしれませんが、化学分野だけでなくさまざまな場面で活用できる非常に強力なSQLです。
今回の例をもとに、学びが深まりましたら幸いです。