今回は、これまでにご紹介したSQLの基本文法やテクニックを組み合わせて、実務でよく利用されるデータ分析について見ていきましょう。

年齢層ごとの製品購入傾向の分析

データ分析の一例として、ユーザーの年齢層ごとにどの製品がよく購入されるかを分析することを考えてみましょう。これは、ターゲットマーケティングの戦略を立てる際に非常に有用な情報です。

以下のSQL文は、ユーザーの年齢層ごとに最も人気のある製品を表示します。

WITH age_groups AS (
    SELECT user_id, 
    CASE 
        WHEN YEAR(CURDATE()) - YEAR(birth_date) BETWEEN 20 AND 29 THEN '20s'
        WHEN YEAR(CURDATE()) - YEAR(birth_date) BETWEEN 30 AND 39 THEN '30s'
        ELSE 'other'
    END AS age_group
    FROM Users
),
product_orders AS (
    SELECT age_groups.age_group, Products.product_name, COUNT(*) as order_count
    FROM Orders
    JOIN Products ON Orders.product_id = Products.product_id
    JOIN age_groups ON Orders.user_id = age_groups.user_id
    GROUP BY age_groups.age_group, Products.product_name
),
max_orders AS (
    SELECT age_group, MAX(order_count) as max_order_count
    FROM product_orders
    GROUP BY age_group
)
SELECT po.age_group, po.product_name
FROM product_orders po
JOIN max_orders mo ON po.age_group = mo.age_group AND po.order_count = mo.max_order_count;

まず、WITH句を用いてサブクエリを作成し、ユーザーを年齢層に分けます。次に、各年齢層での製品ごとの注文数を計算します。最後に、各年齢層で最も多く注文された製品を取得します。

こうした複雑な分析もSQLを用いて行うことができます。SQLの力を活用することで、あなたのビジネスにとって重要な洞察を得ることができます。

顧客の購入パターンの分析

データ分析の一例として、顧客の購入パターンを分析することを考えてみましょう。これは、顧客の購入行動を理解し、マーケティングや販売戦略を立てる際に非常に有用な情報です。

以下のSQL文は、各顧客が最初に購入した製品と最後に購入した製品を表示します。

SELECT user_id,
       FIRST_VALUE(product_id) OVER (PARTITION BY user_id ORDER BY order_date) AS first_purchase,
       LAST_VALUE(product_id) OVER (PARTITION BY user_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_purchase
FROM Orders;

ここで、FIRST_VALUEとLAST_VALUEはWINDOW関数で、各顧客の最初の注文と最後の注文を取得しています。PARTITION BY句は各顧客ごとにデータを分け、ORDER BY句は注文日付でデータをソートします。さらに、ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGは、全ての行を対象とするウィンドウフレームを指定しています。

補足説明：FIRST_VALUEとLAST_VALUEはSQLFiddleでは使えないので、下記を代替で利用することでデータを抽出することができます。

SELECT u.user_id,
       (SELECT product_id FROM Orders o1 WHERE o1.user_id = u.user_id ORDER BY order_date LIMIT 1) AS first_purchase,
       (SELECT product_id FROM Orders o2 WHERE o2.user_id = u.user_id ORDER BY order_date DESC LIMIT 1) AS last_purchase
FROM Users u;

このようなWINDOW関数を活用することで、SQLのみで複雑な分析を実現することができます。WINDOW関数は集計関数と異なり、各行の個別の値を計算できるため、より柔軟な分析が可能となります。

商品間の類似度の分析

データ分析の別の一例として、商品間の類似度を分析することを考えてみましょう。これは、顧客がある商品を購入した場合に他にどの商品を薦めるべきかを理解し、レコメンデーションエンジンを構築する際に非常に有用な情報です。

まず、商品の特徴量として考えられるのが、商品の評価（rating）と価格（price）です。これらを用いて、コサイン類似度に基づいた分析を実施します。コサイン類似度は、ベクトル間の角度のコサイン値を用いて、その類似度を計算する手法であり、一般的にテキスト解析やレコメンデーションエンジンなどでよく用いられます。

今回は、SQLを用いてコサイン類似度を計算する一例を示します。ただし、一般的にはSQLよりもPythonやRなどのプログラミング言語で行われることが多いです。以下のSQL文は、商品1と他の商品の類似度を計算します。

SELECT P1.product_id AS product1,
       P2.product_id AS product2,
       (P1.rating * P2.rating + P1.price * P2.price) / (SQRT(P1.rating * P1.rating + P1.price * P1.price) * SQRT(P2.rating * P2.rating + P2.price * P2.price)) AS cosine_similarity
FROM Products P1, Products P2
WHERE P1.product_id = 1
ORDER BY cosine_similarity DESC;

このSQL文は、すべての商品と商品1とのコサイン類似度を計算しています。この計算では、商品の評価と価格を特徴量として利用しています。

今回のサンプルデータは3つしか製品ないので、コサイン類似度が1に近く同じベクトルという結果が出ていますが、データ量が多いデータを分析すると製品毎の組み合わせでコサイン類似度が大きく異なります。この結果を用いてレコメンドをすることで購買の確率を高めることができます。AmazonなどECサイトでよく出てくるレコメンド機能は、コサイン類似度を用いています。

このような手法を用いることで、類似性の高い商品を特定し、個々の顧客に適切な商品を推薦することが可能となります。SQLを活用して、顧客のニーズに応じた商品推薦やマーケティング戦略を立てることができるようになるでしょう。

データ分析でお困りの企業様の課題解決をサポートします。お気軽に下記よりお問い合わせください。

    お名前