データ分析のためのSQL実践：ビジネス要件に応じたデータ抽出・加工テクニック

-- 2024年1月以降の売上データで、購入金額が1000円以上のレコードを抽出
SELECT
    order_id,
    customer_id,
    order_date,
    amount
FROM
    orders
WHERE
    order_date >= '2024-01-01' AND amount >= 1000;

-- 地域ごとの総売上と平均購入金額を計算
SELECT
    region,
    SUM(amount) AS total_sales,
    AVG(amount) AS average_purchase
FROM
    orders
GROUP BY
    region
HAVING
    SUM(amount) > 100000; -- 総売上が10万円以上の地域のみ抽出

-- 顧客情報と注文情報を結合し、顧客名と注文金額を表示
SELECT
    c.customer_name,
    o.amount,
    o.order_date
FROM
    customers AS c
JOIN
    orders AS o ON c.customer_id = o.customer_id
WHERE
    o.order_date >= '2024-01-01';

-- 購入金額に応じて顧客をセグメント分け
SELECT
    customer_id,
    amount,
    CASE
        WHEN amount >= 5000 THEN 'High Value'
        WHEN amount >= 1000 THEN 'Medium Value'
        ELSE 'Low Value'
    END AS customer_segment
FROM
    orders;
-- 日付から曜日を抽出 (PostgreSQLの例)
SELECT
    order_date,
    TO_CHAR(order_date, 'Day') AS day_of_week
FROM
    orders;

-- 顧客ごとの購入金額のランキングと、各注文の顧客内での累積購入金額
SELECT
    customer_id,
    order_date,
    amount,
    RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rank_by_amount,
    SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS cumulative_amount
FROM
    orders
ORDER BY
    customer_id, order_date;

-- CTEを使って、まず月ごとの総売上を計算し、その結果から平均売上を計算
WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) AS sales_month,
        SUM(amount) AS total_monthly_sales
    FROM
        orders
    GROUP BY
        sales_month
)
SELECT
    sales_month,
    total_monthly_sales,
    AVG(total_monthly_sales) OVER (ORDER BY sales_month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS three_month_moving_avg
FROM
    monthly_sales
ORDER BY
    sales_month;

-- 平均購入金額以上の注文を抽出
SELECT
    order_id,
    customer_id,
    amount
FROM
    orders
WHERE
    amount > (SELECT AVG(amount) FROM orders);

-- 月ごとの製品カテゴリ別売上を横持ちに変換 (PostgreSQLの例)
SELECT
    DATE_TRUNC('month', order_date) AS sales_month,
    SUM(CASE WHEN product_category = 'Electronics' THEN amount ELSE 0 END) AS electronics_sales,
    SUM(CASE WHEN product_category = 'Books' THEN amount ELSE 0 END) AS books_sales,
    SUM(CASE WHEN product_category = 'Clothing' THEN amount ELSE 0 END) AS clothing_sales
FROM
    orders
GROUP BY
    sales_month
ORDER BY
    sales_month;

-- RFM指標の計算
SELECT
    customer_id,
    MAX(order_date) AS last_purchase_date, -- Recency
    COUNT(DISTINCT order_id) AS purchase_frequency, -- Frequency
    SUM(amount) AS total_monetary -- Monetary
FROM
    orders
GROUP BY
    customer_id;

-- ユーザーの初回購入月を特定し、月ごとの定着率を計算する準備
WITH first_purchase AS (
    SELECT
        customer_id,
        MIN(order_date) AS cohort_month
    FROM
        orders
    GROUP BY
        customer_id
)
SELECT
    fp.cohort_month,
    DATE_TRUNC('month', o.order_date) AS purchase_month,
    COUNT(DISTINCT fp.customer_id) AS cohort_size,
    COUNT(DISTINCT o.customer_id) AS active_customers
FROM
    first_purchase AS fp
JOIN
    orders AS o ON fp.customer_id = o.customer_id
WHERE
    DATE_TRUNC('month', o.order_date) >= fp.cohort_month
GROUP BY
    fp.cohort_month, purchase_month
ORDER BY
    fp.cohort_month, purchase_month;