【SQL×美容】ウィンドウ関数で成分の使われ方を肌悩み別にランキング分析してみた

こんばんは、Yuinaです🌸

今日はSQLの力を借りて、美容成分と肌悩みの関係を深掘りします！

スキンケア製品の成分はたくさん並んでいるけど、「結局どれが自分に合っているの？」と迷うこと、ありませんか？

そこで今回は、成分ごとの肌悩み別の使われ方や、季節ごとの人気成分ランキングをデータで見える化しました!

自分に合うケア選びのヒントや、季節に合わせた成分の変化も感じてもらえたら嬉しいです。

なお、今回使用するデータは、ChatGPTにデータを作ってもらってます！

なので、実際の成分効果や医学的な根拠とは違う場合がある(ていうかほぼ違うと思います)のでご注意ください🙏

あくまで「こんな見方ができるんだな〜」という参考として楽しんでいただけると嬉しいです。

実際のスキンケアは、必ず専門家や医師のアドバイスも合わせて検討してくださいね。

それではさっそく見ていきましょう！

よろしくお願いいたします🙏✨

テーブル作成〜データインポート
成分ごとに肌悩み別の使用回数ランキングを作ろう
「シーズン別」人気成分のランキング推移
まとめ

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

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

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）

成分ごとに肌悩み別の使用回数ランキングを作ろう

各成分が、どの肌悩みに対して頻繁に使われている（言及されている）かを見える化し、

出現回数の多い順に順位をつけていきます。

ステップ１：テーブル結合と成長x肌悩みの使用回数の集計

まずは必要なテーブルを結合して、成長と肌悩みの組み合わせごとの出現数（=使用回数）を集計します。詳しくはこちらの記事をご確認ください。

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;

実行結果：

ステップ2：ウィンドウ関数でランキングを追加

ここでウィンドウ関数 RANK() を使い、成分ごとに、肌悩みの出現回数でランキングを付けていきます。

SELECT
  i.name AS ingredient,
  sc.concern_name,
  COUNT(*) AS usage_count,
  RANK() OVER (PARTITION BY i.name ORDER BY COUNT(*) DESC) AS rank_within_ingredient
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, rank_within_ingredient;

RANK() OVER (PARTITION BY i.name ORDER BY COUNT(*) DESC) AS rank_within_ingredientがウィンドウ関数となります。

解説：

・PARTITION BY i.name → 「成分（ingredient）ごとに区切る」

・ORDER BY usage_count DESC → 「使用回数の多い順に順位を付ける」

・RANK() →同じ使用回数の肌悩みには同じ順位を付ける（例：1位が2つある場合、次は3位）

結果を確認してみましょう。

アスコルビン酸は、「くすみ」と一緒に6回出てきていて、それが一番多いのでランクは1位です。
続く「敏感肌」と「シミ」はどちらも1回だけなので、同率で2位です。

グリチルリチン酸2Kは、「乾燥肌」と一緒に5回出てきていて、最も多いのでランクは1位となります。続きまして、「敏感肌」が3回出てきていますので、こちらは2位に値します。

これにより、「この成分は主にどんな肌悩みに対して使われている（言及されている）か」がわかります。

「シーズン別」人気成分のランキング推移

春夏秋冬ごとに、どのスキンケア成分が多く使われていたかをランキングで可視化し、

成分の「季節ごとの人気の変化」を追ってみます。

ステップ1:カラム追加とデータのインポート

分析に必要なので、投稿日時のカラムとデータを追加します。

ALTER TABLE articles
ADD COLUMN published_at TIMESTAMP;

UPDATE articles SET published_at = '2025-07-01 10:00:00' WHERE id = 1;
UPDATE articles SET published_at = '2025-10-02 10:00:00' WHERE id = 2;
UPDATE articles SET published_at = '2025-02-03 10:00:00' WHERE id = 3;
UPDATE articles SET published_at = '2025-01-04 10:00:00' WHERE id = 4;
UPDATE articles SET published_at = '2025-04-05 10:00:00' WHERE id = 5;
UPDATE articles SET published_at = '2025-03-06 10:00:00' WHERE id = 6;
UPDATE articles SET published_at = '2025-05-07 10:00:00' WHERE id = 7;
UPDATE articles SET published_at = '2025-02-08 10:00:00' WHERE id = 8;
UPDATE articles SET published_at = '2025-09-09 10:00:00' WHERE id = 9;
UPDATE articles SET published_at = '2025-10-10 10:00:00' WHERE id = 10;
UPDATE articles SET published_at = '2025-12-11 10:00:00' WHERE id = 11;
UPDATE articles SET published_at = '2025-11-12 10:00:00' WHERE id = 12;

ステップ2：月 → 季節に変換🌸🍁⛄

まず、CASE式を使って、投稿日から季節を計算します。

記事がいつ公開されたか（投稿日）を使って、それぞれの季節（春・夏・秋・冬）を判定します。
たとえば、3〜5月なら「春」、6〜8月は「夏」…というふうに分けていきます。

CASE
  WHEN EXTRACT(MONTH FROM a.published_at) IN (3, 4, 5) THEN '春'
  WHEN EXTRACT(MONTH FROM a.published_at) IN (6, 7, 8) THEN '夏'
  WHEN EXTRACT(MONTH FROM a.published_at) IN (9, 10, 11) THEN '秋'
  ELSE '冬'
END AS season

ステップ3：成分 × 季節ごとの出現数を集計

記事ごとに使われているスキンケア成分を集計して、「この季節にはこの成分が◯回使われていた」というデータを作ります。

SELECT
  i.name AS ingredient,
  CASE
    WHEN EXTRACT(MONTH FROM a.published_at) IN (3, 4, 5) THEN '春'
    WHEN EXTRACT(MONTH FROM a.published_at) IN (6, 7, 8) THEN '夏'
    WHEN EXTRACT(MONTH FROM a.published_at) IN (9, 10, 11) THEN '秋'
    ELSE '冬'
  END AS season,
  COUNT(*) AS usage_count
FROM article_ingredients ai
JOIN ingredients i ON ai.ingredient_id = i.id
JOIN articles a ON ai.article_id = a.id
GROUP BY i.name, season

ステップ4:ウィンドウ関数で人気順ランキングをつける

最後に、成分ごとの使用回数をもとに、その季節でどの成分が一番使われていたかをランキング形式で並べます。
「春の人気No.1はセラミド」「夏の注目はビタミンC誘導体」など、季節ごとの傾向が一目で分かるようになります💡

ウィンドウ関数：

RANK() OVER (
  PARTITION BY season          -- 季節ごとにグループ分け
  ORDER BY usage_count DESC    -- 使用回数の多い順に並べる
) AS rank_in_season

季節ごとに成分の使用頻度を降順に並べて順位をつけました。

クエリと実行結果は以下の通りです。

SELECT
  ingredient,
  season,
  usage_count,
  RANK() OVER (PARTITION BY season ORDER BY usage_count DESC) AS rank_in_season
FROM (
  SELECT
    i.name AS ingredient,
    CASE
      WHEN EXTRACT(MONTH FROM a.published_at) IN (3, 4, 5) THEN '春'
      WHEN EXTRACT(MONTH FROM a.published_at) IN (6, 7, 8) THEN '夏'
      WHEN EXTRACT(MONTH FROM a.published_at) IN (9, 10, 11) THEN '秋'
      ELSE '冬'
    END AS season,
    COUNT(*) AS usage_count
  FROM article_ingredients ai
  JOIN ingredients i ON ai.ingredient_id = i.id
  JOIN articles a ON ai.article_id = a.id
  GROUP BY i.name, season
) t
ORDER BY season, rank_in_season;