「JOIN」「GROUP BY」「COUNT()」を使った美容成分と肌悩み分析SQL

おはようございます。Yuinaです☀️

今日は、美容記事の成分データを使って、「成分 × 肌悩み」の関係を SQLで分析していきます📊✨

スキンケア選びでよくある悩みの一つに、
「成分はたくさん書いてあるけど、結局どれが自分の肌に合ってるのか分からない…🤔」ということがありますよね。

化粧品のパッケージや説明文には「セラミド配合💧」「保湿効果あり💖」などと書かれていますが、
その成分が実際にどう使われていて、どんな肌悩みに効果があるのかを、私たちはあまり見たことがありません👀。

そこで今回は、乾燥肌・敏感肌・シミ・くすみなど、よくある肌悩み別に
「どんな成分がどれくらい使われているのか？」を客観的に、数値で可視化してみます📈！

※このブログで扱う成分情報や分析結果は、ChatGPTによる自動生成データを含みます。
実際の成分効果や医学的根拠とは異なる場合があるため、参考程度にお楽しみください🎀。

テーブル作成〜データインポート
何の成分がどの肌悩みにどれくらい使われている？（JOIN + 集計）
まとめ

テーブル作成〜データインポート

テーブル概要は以下のとおりです。

1. articles

役割: 美容記事の基本情報を格納
主なカラム:
- id: 記事の一意ID（主キー）
- title: 記事タイトル
- category: 記事カテゴリ（例：保湿、美白、抗炎症）
- publish_date: 記事公開日

2. ingredients

役割: 美容成分の詳細情報を格納
主なカラム:
- id: 成分の一意ID（主キー）
- name: 成分の日本語名
- inci_name: 国際化粧品原料名称（INCI名）
- origin: 成分の由来（例：合成、植物由来）

3. skin_concerns

役割: 肌の悩み・課題を管理
主なカラム:
- id: 肌悩みの一意ID（主キー）
- concern_name: 肌悩みの名前（例：乾燥肌、敏感肌、くすみ）

4. article_ingredients

役割: 記事と成分の関連を多対多で管理
主なカラム:
- article_id: articlesテーブルのID（外部キー）
- ingredient_id: ingredientsテーブルのID（外部キー）

5. article_concerns

役割: 記事と肌悩みの関連を多対多で管理
主なカラム:
- article_id: articlesテーブルのID（外部キー）
- concern_id: skin_concernsテーブルのID（外部キー）

SQL：

-- テーブル作成
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  publish_date DATE NOT NULL
);

CREATE TABLE ingredients (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  inci_name TEXT NOT NULL,
  origin TEXT NOT NULL
);

CREATE TABLE skin_concerns (
  id SERIAL PRIMARY KEY,
  concern_name TEXT NOT NULL
);

CREATE TABLE article_ingredients (
  article_id INTEGER NOT NULL,
  ingredient_id INTEGER NOT NULL,
  FOREIGN KEY (article_id) REFERENCES articles(id),
  FOREIGN KEY (ingredient_id) REFERENCES ingredients(id)
);

CREATE TABLE article_concerns (
  article_id INTEGER NOT NULL,
  concern_id INTEGER NOT NULL,
  FOREIGN KEY (article_id) REFERENCES articles(id),
  FOREIGN KEY (concern_id) REFERENCES skin_concerns(id)
);

-- データ挿入
INSERT INTO articles (id, title, category, publish_date) VALUES
(1, 'セラミドでうるおいチャージ', '保湿', '2024-10-01'),
(2, 'グリチルリチン酸で肌トラブル予防', '抗炎症', '2024-11-10'),
(3, 'ビタミンC美容液の本当の効果', '美白', '2024-09-12'),
(4, '肌のくすみ対策！', '美白', '2024-08-22'),
(5, '乾燥に強い肌を作る', '保湿', '2024-10-20')
(6, 'セラミドで潤う肌へ', '保湿', '2024-01-01'),
(7, '乾燥肌対策の基礎知識', 'スキンケア', '2024-01-02'),
(8, 'シミを防ぐ美白成分', '美白', '2024-02-10'),
(9, '敏感肌に優しい化粧品', '敏感肌', '2024-03-05'),
(10, 'ニキビと毛穴ケアの真実', 'ニキビ', '2024-04-01'),
(11, 'ビタミンCのスキンケア活用', '美白', '2024-04-10'),
(12, '日焼け後の正しいケア', '日焼け', '2024-05-01');

INSERT INTO ingredients (id, name, inci_name, origin) VALUES
(1, 'セラミド', 'Ceramide', '合成'),
(2, 'グリチルリチン酸2K', 'Dipotassium Glycyrrhizate', '植物由来'),
(3, 'アスコルビン酸', 'Ascorbic Acid', '合成')
(4, 'セラミド_2', 'Ceramide NP', '合成'),
(5, 'ヒアルロン酸', 'Sodium Hyaluronate', '天然'),
(6, 'ビタミンC誘導体', 'Ascorbyl Glucoside', '合成'),
(7, 'グリチルリチン酸', 'Glycyrrhizic Acid', '天然'),
(8, 'アラントイン', 'Allantoin', '天然'),
(9, 'ナイアシンアミド', 'Niacinamide', '合成');

INSERT INTO skin_concerns (id, concern_name) VALUES
(1, '乾燥肌'),
(2, '敏感肌'),
(3, 'くすみ')
(4, '超乾燥肌'),
(5, '超敏感肌'),
(6, 'シミ'),
(7, 'ニキビ'),
(8, '毛穴'),
(9, '超くすみ');;

INSERT INTO article_ingredients (article_id, ingredient_id) VALUES
(1, 1),
(2, 2),
(3, 3),
(4, 3),
(5, 1),
(1, 1),
(1, 2),
(2, 2),
(2, 4),
(3, 3),
(3, 6),
(4, 4),
(4, 5),
(5, 5),
(5, 6),
(6, 3),
(6, 6),
(7, 2),
(7, 4);

INSERT INTO article_concerns (article_id, concern_id) VALUES
(1, 1),
(2, 2),
(3, 3),
(4, 3),
(5, 1),
(1, 1),
(2, 1),
(3, 3),
(4, 2),
(5, 4),
(5, 5),
(6, 3),
(6, 6),
(7, 1),
(7, 2)

達人に学ぶSQL徹底指南書第2版（Amazon）

何の成分がどの肌悩みにどれくらい使われている？（JOIN + 集計）

成分と肌悩みの繋がりについて分析していきましょう✨

以下の流れで進めていきます。

ステップ1：テーブルを結合しよう🎗️🔗
必要なテーブル同士をJOINでつなげて、データの関連付けをしましょう。

ステップ2：欲しいカラムを選んでGROUP BYで集計しよう📊
成分名や肌悩み名など、分析したい情報を取得し、グループごとに集計します。

ステップ3：結果を見やすく並べ替えよう🔄🔝
ORDER BYで並び順を指定して、分析結果をわかりやすく表示しましょう。

では、よろしくお願いします!

ステップ１：テーブルを結合しよう🎗️

まずはテーブルを結合していきましょう！🔗✨

今回使うのは、以下の4つのテーブルです。

ingredients（成分）
article_ingredients（記事と成分の関係）
article_concerns（記事と肌悩みの関係）
skin_concerns（肌悩み）

目的は、ingredients（成分） と skin_concerns（肌悩み） をつなげること。

でも直接つなげることはできません。
なぜなら、両者は記事（article）を介して間接的につながっているからです。

そこで、中間テーブルの article_ingredients と article_concerns を間に挟んで、
成分 → 記事 → 肌悩み、という流れで結合していきます。

これで正しい関連付けができ、整合性もバッチリです👌

結合イメージ：

では、ここからクエリを書いていきましょう。

SQL:

SELECT
*
FROM article_ingredients ai
JOIN ingredients i ON ai.ingredient_id = i.id
JOIN article_concerns ac ON ai.article_id = ac.article_id
JOIN skin_concerns sc ON ac.concern_id = sc.id

以下はSQLの実行結果です✨

これから1つずつテーブルを増やして結合していくので、
その都度データの動きや挙動を一緒に確認していきましょう🔍👀

実行結果：

ステップ2:カラム取得とGROUPBYをしよう

まずは、成分名（name） と 肌悩み名（concern_name） のみを取得して、
それぞれの組み合わせでグループ化してみましょう📋✨

(青くなっている部分が取得する部分)

SELECT
	i.name AS ingredient,         -- 成分名
  	sc.concern_name,              -- 肌悩み名
FROM article_ingredients ai
JOIN ingredients i ON ai.ingredient_id = i.id
JOIN article_concerns ac ON ai.article_id = ac.article_id
JOIN skin_concerns sc ON ac.concern_id = sc.id
GROUP BY i.name, sc.concern_name

成分 × 肌悩みの組み合わせごとに、
COUNT(*) を使って出現数、つまり「使用回数」を集計します📊✨

イメージ：

SQL:

SELECT
	i.name AS ingredient,         -- 成分名
  	sc.concern_name,              -- 肌悩み名
  	COUNT(*) AS usage_count
FROM article_ingredients ai
JOIN ingredients i ON ai.ingredient_id = i.id
JOIN article_concerns ac ON ai.article_id = ac.article_id
JOIN skin_concerns sc ON ac.concern_id = sc.id
GROUP BY i.name, sc.concern_name

実行結果:

ステップ3:適切な順番に並べよう

成分名でまず並べて、
その中で肌悩みごとの使用回数が多い順（降順）に表示します📋⬇️

SQL：

ORDER BY i.name, usage_count DESC;

実行前：

実行後:

SQL全体：

SELECT
	i.name AS ingredient,         -- 成分名
  	sc.concern_name,              -- 肌悩み名
  	COUNT(*) AS usage_count
FROM article_ingredients ai
JOIN ingredients i ON ai.ingredient_id = i.id
JOIN article_concerns ac ON ai.article_id = ac.article_id
JOIN skin_concerns sc ON ac.concern_id = sc.id
GROUP BY i.name, sc.concern_name
ORDER BY i.name, usage_count DESC;

犯罪から学ぶSQL入門（Amazon）

まとめ

今回のSQL分析では、「どの成分がどんな肌悩みによく使われているか」がはっきり見えてきました✨
(例えば、セラミドは乾燥肌のケアに多く使われている、みたいな感じです)

こうしたデータをもとに、成分の組み合わせや使われ方のパターンを探ったり、

時期ごとのトレンドを追ったりすることで、もっと深く肌悩みと成分の関係が見えてきます📈💡

将来的には、利用者にぴったり合ったスキンケア成分をAIが提案したり、

肌の状態や季節に合わせて最適なケア方法を自動でカスタマイズしたり、

SNSや口コミデータと連携してリアルなユーザー評価を反映したり、

さらに新しい成分や製品の効果を予測・評価できるようなシステムも作れるかもしれません。

ありがとうございました！🙏💖

✨おまけ✨
「乾燥肌に使われる成分トップ5」を抽出するSQLです。
よかったらコピペして実行してみてください。

SELECT
  i.name AS ingredient,
  COUNT(*) AS usage_count
FROM article_ingredients ai
JOIN ingredients i ON ai.ingredient_id = i.id
JOIN article_concerns ac ON ai.article_id = ac.article_id
JOIN skin_concerns sc ON ac.concern_id = sc.id
WHERE sc.concern_name = '乾燥肌'
GROUP BY i.name
ORDER BY usage_count DESC
LIMIT 5;