顔とメイクの相性バッチリ診断SQL💄✨

こんにちは！Yuinaです。

今日は顔のパーツとメイクの相性をシンプルに数値化するSQLをご紹介します😍

よろしくお願いします！

分析前の準備🥣✨
メイクスタイル分析💄✨
まとめ💖🪞

分析前の準備🥣✨

ステップ①：テーブルを作成する

🎀 ユーザーの顔データ（beauty.user_face_metrics）:

CREATE TABLE beauty.user_face_metrics_t (
  name VARCHAR PRIMARY KEY,
  face_width NUMERIC,
  face_height NUMERIC,
  eye_width NUMERIC,
  lip_thickness NUMERIC,
  eyebrow_thickness NUMERIC
);

💋 メイクスタイル理想値（beauty.makeup_style_metrics）:

CREATE TABLE beauty.makeup_style_metrics_t (
  style_name VARCHAR PRIMARY KEY,
  eye_shadow_width NUMERIC,
  eye_line_length NUMERIC,
  lip_thickness NUMERIC,
  eyebrow_thickness NUMERIC
);

🍑ステップ②：データをインサート

beauty.user_face_metrics:

INSERT INTO beauty.user_face_metrics_t (name, face_width, face_height, eye_width, lip_thickness, eyebrow_thickness) VALUES
('Reina', 14.0, 18.5, 4.2, 2.4, 4.8),
('Mio', 12.5, 17.5, 4.0, 1.9, 5.2),
('Riko', 13.5, 18.0, 4.5, 3.1, 5.1),
('Nao', 14.5, 19.0, 5.0, 1.6, 4.9),
('Yuna', 13.0, 18.0, 4.0, 3.6, 2.0),
('Airi', 12.8, 18.1, 3.8, 2.9, 1.9),
('Hana', 14.2, 19.0, 4.5, 3.2, 2.4),
('Saki', 13.7, 17.8, 4.0, 2.7, 2.0),
('Yui', 13.3, 18.5, 4.1, 2.5, 1.7),
('Haruka', 14.0, 18.9, 4.6, 3.5, 2.2),
('Rina', 13.0, 18.2, 4.2, 2.8, 2.1),
('Miku', 12.7, 17.6, 3.7, 2.6, 1.6),
('Emi', 13.5, 18.3, 4.3, 3.1, 2.3),
('Kana', 13.3, 17.5, 4.0, 3.7, 4.4),
('Eri', 14.2, 18.6, 4.5, 2.9, 4.0),
('Nana', 12.8, 16.4, 3.8, 3.2, 4.1),
('Ayaka', 15.1, 19.2, 5.3, 4.6, 5.1),
('Hina', 13.9, 17.9, 4.4, 3.3, 4.5),
('Mei', 13.0, 18.0, 4.0, 3.6, 2.0),
('Aoi', 14.0, 19.0, 4.2, 2.4, 2.3),
('RikoA', 12.5, 17.5, 4.0, 1.9, 1.8),
('Natsuki', 13.5, 18.5, 4.5, 3.1, 2.1),
('Sayaka', 14.5, 19.5, 5.0, 1.6, 2.5);

beauty.makeup_style_metrics:

INSERT INTO beauty.makeup_style_metrics_t (style_name, eye_shadow_width, eye_line_length, lip_thickness, eyebrow_thickness) VALUES
('Soft Elegant', 8.10, 24.00, 4.80, 5.00),
('Fresh', 7.50, 22.00, 4.30, 4.80),
('Cool', 8.50, 23.50, 4.60, 5.10),
('Cute', 7.80, 21.00, 4.20, 4.90),
('Glamorous', 9.20, 26.00, 5.00, 2.70),
('Natural', 7.00, 20.00, 3.80, 2.00),
('Sharp', 8.80, 25.00, 4.40, 2.60),
('Romantic', 7.60, 21.50, 4.10, 2.20),
('Dreamy', 8.60, 26.00, 4.90, 4.80),
('Chic', 7.20, 22.50, 3.50, 4.30),
('Edgy', 9.30, 31.00, 5.70, 5.30),
('Vintage', 8.00, 24.50, 4.00, 4.50),
('Minimal', 7.00, 20.00, 3.20, 4.00);

ステップ③：データ確認

beauty.user_face_metrics:

beauty.makeup_style_metrics:

🎉これで準備ばっちりですっ！
次はユーザーの顔特徴にいちばん近いスタイルをマッチングするような分析クエリ作ります！✨

メイクスタイル分析💄✨

ステップ①：全ユーザー×全メイクスタイルの組み合わせを作成🎲

ここでは、lip_thickness（唇の厚さ）・eye_shadow_width（アイシャドウ幅)・eye_line_length(アイシャドウの長さ)の類似度を使って、
「ユーザーに一番近いメイクスタイル」を1つ選ぶクエリを用意します！💕

SELECT
  u.name,
  s.style_name,
  u.lip_thickness,
  u.eye_width,
  s.lip_thickness AS style_lip_thickness,
  s.eye_shadow_width,
  s.eye_line_length
FROM
  beauty.user_face_metrics_t u
CROSS JOIN
  beauty.makeup_style_metrics_t s;

実行結果：

🎗️ステップ②：各ユーザー×メイクスタイルごとに「似てる度合い」を計算✨

「ユーザーの顔の特徴」と「メイクの理想的な特徴」の差を計算して、

どのメイクスタイルがユーザーに一番似合うか（差が一番小さいか）を数値で表しています！

SQRT関数：

1.ユーザーの顔の特徴とメイクの特徴の差を出す。

2.差を二乗して足して、平方根を取る(=ユークリッド距離)✨

3.最後に小数点以下2桁に丸める📏

SQL:

WITH scored_styles AS (
  SELECT
    u.name,
    s.style_name,
    ROUND(
      SQRT(
        POWER(s.lip_thickness     - u.lip_thickness,     2) +
        POWER(s.eye_shadow_width - u.eye_width * 1.5,    2) +
        POWER(s.eye_line_length  - u.eye_width * 4.0,    2) +
        POWER(s.eyebrow_width    - u.eyebrow_width,      2) +
        POWER((u.face_width / u.face_height) - 0.75,     2) * 10 -- 黄金比0.75に近づけたい💎
      ), 2
    ) AS similarity_score
  FROM beauty.user_face_metrics_t u
  CROSS JOIN beauty.makeup_style_metrics_t s
),

補足💬：

唇の厚さの差：メイクのリップの厚さとユーザーの唇の厚さがどれだけ近いかチェック💄

アイシャドウ幅：目の幅の1.5倍くらいが可愛いって決めてるので、それに合うかチェック👁️✨

アイライン長さ：目幅の4倍くらいが美人のラインの長さということで比較！

眉の幅の差：眉の形は、超大事なのでしっかり比べます〜👍

顔の黄金比：顔の幅÷顔の高さが0.75に近いのが美人顔の条件！10倍重視に設定しているので、超重要項目です💎

実行結果：

ステップ③：各ユーザーに対して「一番似てる」メイクスタイルだけ抽出🎯

ROW_NUMBER() ：
「行に順番をつける」関数です。1から始まって連番を振っていくイメージでいいと思います。

PARTITION BY name ：
「name」というグループごとに分けて順番をつけます。

ORDER BY similarity_score ASC ：
そのグループの中で、similarity_score の値が小さい順（昇順）に並べて順番を決めます。

ranked_styles AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY name ORDER BY similarity_score ASC) AS rank
  FROM scored_styles
)

SELECT * FROM ranked_styles WHERE rank = 1;

実行結果(rank=1の条件で絞る前)：