SQL: 実践：高度な機能の統合

-- 部門テーブル
CREATE TABLE IF NOT EXISTS departments (
    id INTEGER PRIMARY KEY,
    department_name TEXT NOT NULL,
    manager_id INTEGER,
    location TEXT
);

-- 従業員テーブル
CREATE TABLE IF NOT EXISTS employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department_id INTEGER,
    position TEXT,
    salary REAL,
    hire_date TEXT,
    manager_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- 商品テーブル
CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    category TEXT,
    price REAL,
    stock_quantity INTEGER
);

-- 注文テーブル
CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY,
    customer_name TEXT,
    product_id INTEGER,
    quantity INTEGER,
    amount REAL,
    order_date TEXT,
    status TEXT,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- テストデータを挿入
INSERT OR IGNORE INTO departments VALUES
(1, 'Tech', 1, 'Beijing'),
(2, 'Marketing', 4, 'Shanghai'),
(3, 'Finance', 6, 'Beijing');

INSERT OR IGNORE INTO employees VALUES
(1, 'Alice', 1, 'Senior Engineer', 15000, '2020-01-15', NULL),
(2, 'Bob', 1, 'Mid-level Engineer', 12000, '2021-03-20', 1),
(3, 'Charlie', 1, 'Junior Engineer', 8000, '2022-06-10', 1),
(4, 'Diana', 2, 'Marketing Director', 18000, '2019-05-01', NULL),
(5, 'Eve', 2, 'Marketing Specialist', 9000, '2021-08-15', 4),
(6, 'Frank', 3, 'Finance Manager', 16000, '2020-02-28', NULL),
(7, 'Grace', 3, 'Accountant', 10000, '2021-11-05', 6),
(8, 'Henry', 1, 'Intern Engineer', 5000, '2023-07-01', 2);

INSERT OR IGNORE INTO products VALUES
(1, 'Laptop', 'Electronics', 6999, 50),
(2, 'Wireless Mouse', 'Electronics', 199, 200),
(3, 'Mechanical Keyboard', 'Electronics', 599, 100),
(4, 'Office Chair', 'Furniture', 1299, 30),
(5, 'Monitor', 'Electronics', 2499, 80);

INSERT OR IGNORE INTO orders VALUES
(1, 'CustomerA', 1, 2, 13998, '2024-01-10', 'completed'),
(2, 'CustomerB', 2, 5, 995, '2024-01-15', 'completed'),
(3, 'CustomerA', 3, 3, 1797, '2024-02-01', 'completed'),
(4, 'CustomerC', 1, 1, 6999, '2024-02-15', 'pending'),
(5, 'CustomerB', 4, 2, 2598, '2024-03-01', 'completed'),
(6, 'CustomerD', 5, 4, 9996, '2024-03-10', 'completed'),
(7, 'CustomerA', 2, 10, 1990, '2024-03-15', 'completed'),
(8, 'CustomerC', 3, 1, 599, '2024-04-01', 'cancelled');

-- さまざまなランキングクエリ
WITH salary_ranking AS (
    SELECT 
        e.id,
        e.name,
        d.department_name,
        e.salary,
        e.hire_date,
        -- 全社給与ランキング
        RANK() OVER (ORDER BY e.salary DESC) AS company_rank,
        -- 全社給与ランキング（同順位なし）
        ROW_NUMBER() OVER (ORDER BY e.salary DESC) AS company_row_num,
        -- 部門別給与ランキング
        RANK() OVER (
            PARTITION BY e.department_id 
            ORDER BY e.salary DESC
        ) AS dept_rank,
        -- 給与パーセンタイルランク
        PERCENT_RANK() OVER (ORDER BY e.salary DESC) AS percentile,
        -- 給与グルーピング（4グループ）
        NTILE(4) OVER (ORDER BY e.salary DESC) AS salary_quartile
    FROM employees e
    JOIN departments d ON e.department_id = d.id
)
SELECT 
    name,
    department_name,
    salary,
    company_rank AS "全社ランク",
    dept_rank AS "部門ランク",
    CASE salary_quartile
        WHEN 1 THEN 'トップティア'
        WHEN 2 THEN 'アッパーミドル'
        WHEN 3 THEN 'ローワーミドル'
        WHEN 4 THEN 'ボトムティア'
    END AS "給与バンド",
    ROUND(percentile * 100, 1) || '%' AS "パーセンタイル"
FROM salary_ranking
ORDER BY company_rank;

-- 商品の売上と収益ランキング
WITH product_sales AS (
    SELECT 
        p.id,
        p.product_name,
        p.category,
        p.price,
        COALESCE(SUM(o.quantity), 0) AS total_quantity,
        COALESCE(SUM(o.amount), 0) AS total_revenue,
        COUNT(o.id) AS order_count
    FROM products p
    LEFT JOIN orders o ON p.id = o.product_id AND o.status = 'completed'
    GROUP BY p.id, p.product_name, p.category, p.price
)
SELECT 
    product_name AS "商品名",
    category AS "カテゴリ",
    price AS "単価",
    total_quantity AS "合計数量",
    total_revenue AS "合計収益",
    order_count AS "注文数",
    RANK() OVER (ORDER BY total_revenue DESC) AS "収益ランク",
    RANK() OVER (
        PARTITION BY category 
        ORDER BY total_quantity DESC
    ) AS "カテゴリ数量ランク",
    DENSE_RANK() OVER (ORDER BY total_quantity DESC) AS "売上ランク"
FROM product_sales
ORDER BY total_revenue DESC;

-- 月次売上と累積計算
WITH monthly_sales AS (
    SELECT 
        strftime('%Y-%m', order_date) AS month,
        SUM(amount) AS monthly_total,
        COUNT(*) AS order_count
    FROM orders
    WHERE status = 'completed'
    GROUP BY strftime('%Y-%m', order_date)
)
SELECT 
    month AS "月",
    monthly_total AS "月次売上",
    order_count AS "注文数",
    -- 累積売上
    SUM(monthly_total) OVER (ORDER BY month) AS "累積売上",
    -- 累積注文数
    SUM(order_count) OVER (ORDER BY month) AS "累積注文数",
    -- 前月比成長率
    ROUND(
        (monthly_total - LAG(monthly_total) OVER (ORDER BY month)) 
        / LAG(monthly_total) OVER (ORDER BY month) * 100, 
        2
    ) AS "前月比%",
    -- 前月差
    monthly_total - LAG(monthly_total) OVER (ORDER BY month) AS "前月差"
FROM monthly_sales
ORDER BY month;

-- 注文金額の移動平均
WITH order_details AS (
    SELECT 
        o.id,
        o.order_date,
        o.amount,
        o.customer_name,
        -- 3日移動平均
        AVG(o.amount) OVER (
            ORDER BY o.order_date 
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) AS avg_3day,
        -- 5日移動平均
        AVG(o.amount) OVER (
            ORDER BY o.order_date 
            ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
        ) AS avg_5day,
        -- 累積平均
        AVG(o.amount) OVER (ORDER BY o.order_date) AS cumulative_avg,
        -- 前回の注文金額
        LAG(o.amount, 1) OVER (ORDER BY o.order_date) AS prev_amount,
        -- 累積最大注文金額
        MAX(o.amount) OVER (ORDER BY o.order_date) AS running_max
    FROM orders o
    WHERE o.status = 'completed'
)
SELECT 
    order_date AS "注文日",
    amount AS "金額",
    ROUND(avg_3day, 2) AS "3日MA",
    ROUND(avg_5day, 2) AS "5日MA",
    ROUND(cumulative_avg, 2) AS "累積平均",
    running_max AS "累積最大"
FROM order_details
ORDER BY order_date;

-- 組織階層の再帰クエリ
WITH RECURSIVE org_hierarchy AS (
    -- アンカー：トップレベルの管理者（上司がいない従業員）
    SELECT 
        e.id,
        e.name,
        e.position,
        e.manager_id,
        0 AS level,
        e.name AS path,
        e.salary
    FROM employees e
    WHERE e.manager_id IS NULL
    
    UNION ALL
    
    -- 再帰部分：部下を見つける
    SELECT 
        e.id,
        e.name,
        e.position,
        e.manager_id,
        oh.level + 1,
        oh.path || ' -> ' || e.name,
        e.salary
    FROM employees e
    JOIN org_hierarchy oh ON e.manager_id = oh.id
)
SELECT 
    CASE level
        WHEN 0 THEN ''
        WHEN 1 THEN '├─ '
        WHEN 2 THEN '│  ├─ '
        ELSE '│  │  ├─ '
    END || name AS "組織図",
    position AS "役職",
    level AS "レベル",
    path AS "報告パス",
    salary AS "給与"
FROM org_hierarchy
ORDER BY path;

-- 部門ごとの人員階層と給与分布の統計
WITH dept_stats AS (
    SELECT 
        d.id,
        d.department_name,
        COUNT(e.id) AS employee_count,
        COALESCE(SUM(e.salary), 0) AS total_salary,
        COALESCE(AVG(e.salary), 0) AS avg_salary,
        COALESCE(MAX(e.salary), 0) AS max_salary,
        COALESCE(MIN(e.salary), 0) AS min_salary
    FROM departments d
    LEFT JOIN employees e ON d.id = e.department_id
    GROUP BY d.id, d.department_name
)
SELECT 
    department_name AS "部門",
    employee_count AS "従業員数",
    ROUND(total_salary, 2) AS "給与合計",
    ROUND(avg_salary, 2) AS "平均給与",
    max_salary AS "最高給与",
    min_salary AS "最低給与",
    max_salary - min_salary AS "給与格差",
    -- 給与分布
    CASE 
        WHEN avg_salary > 15000 THEN '高給与'
        WHEN avg_salary > 10000 THEN '中給与'
        ELSE '基本給与'
    END AS "給与レベル"
FROM dept_stats
ORDER BY total_salary DESC;

-- トランザクションを使ってバッチ注文を処理
BEGIN TRANSACTION;

-- セーブポイント：ロールバックしやすくするため
SAVEPOINT before_process;

-- 1. 完了注文の商品在庫を更新
UPDATE products 
SET stock_quantity = stock_quantity - (
    SELECT COALESCE(SUM(o.quantity), 0)
    FROM orders o
    WHERE o.product_id = products.id
    AND o.status = 'completed'
    AND o.order_date >= '2024-01-01'
)
WHERE id IN (
    SELECT DISTINCT product_id 
    FROM orders 
    WHERE status = 'completed' 
    AND order_date >= '2024-01-01'
);

-- 2. 在庫がマイナスになった商品がないかチェック
SELECT 
    CASE 
        WHEN MIN(stock_quantity) < 0 THEN 'エラー'
        ELSE 'OK'
    END AS inventory_check
FROM products;

-- 3. 在庫チェックが通った場合、月次レポートを生成
INSERT INTO monthly_report (month, total_revenue, total_orders)
SELECT 
    strftime('%Y-%m', order_date),
    SUM(amount),
    COUNT(*)
FROM orders
WHERE status = 'completed'
GROUP BY strftime('%Y-%m', order_date);

-- トランザクションをコミット
COMMIT;

-- 結果を表示
SELECT * FROM products ORDER BY id;

-- 包括的な顧客価値分析
WITH customer_analysis AS (
    SELECT 
        customer_name,
        COUNT(*) AS order_count,
        SUM(amount) AS total_spent,
        AVG(amount) AS avg_order_value,
        MIN(order_date) AS first_order,
        MAX(order_date) AS last_order,
        -- 顧客生涯（日数）を計算
        julianday(MAX(order_date)) - julianday(MIN(order_date)) AS customer_lifetime,
        -- 最終購入からの日数
        julianday('now') - julianday(MAX(order_date)) AS days_since_last_order
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_name
),
customer_rfm AS (
    SELECT 
        *,
        -- Rスコア：最新度評価（1-5、5が最新）
        CASE 
            WHEN days_since_last_order <= 30 THEN 5
            WHEN days_since_last_order <= 60 THEN 4
            WHEN days_since_last_order <= 90 THEN 3
            WHEN days_since_last_order <= 180 THEN 2
            ELSE 1
        END AS r_score,
        -- Fスコア：頻度評価
        CASE 
            WHEN order_count >= 5 THEN 5
            WHEN order_count >= 3 THEN 4
            WHEN order_count >= 2 THEN 3
            WHEN order_count >= 1 THEN 2
            ELSE 1
        END AS f_score,
        -- Mスコア：金額評価
        NTILE(5) OVER (ORDER BY total_spent) AS m_score
    FROM customer_analysis
)
SELECT 
    customer_name AS "顧客名",
    order_count AS "注文数",
    ROUND(total_spent, 2) AS "総支出額",
    ROUND(avg_order_value, 2) AS "平均注文金額",
    first_order AS "初回注文",
    last_order AS "最終注文",
    r_score AS "Rスコア",
    f_score AS "Fスコア",
    m_score AS "Mスコア",
    r_score + f_score + m_score AS "RFM合計",
    CASE 
        WHEN r_score + f_score + m_score >= 12 THEN 'VIP顧客'
        WHEN r_score + f_score + m_score >= 9 THEN '重要顧客'
        WHEN r_score + f_score + m_score >= 6 THEN '一般顧客'
        ELSE '低額顧客'
    END AS "顧客ティア"
FROM customer_rfm
ORDER BY r_score + f_score + m_score DESC;

-- 給与バンド分析レポート
WITH salary_bands AS (
    SELECT 
        e.id,
        e.name,
        d.department_name,
        e.salary,
        e.hire_date,
        -- 勤続年数を計算
        (julianday('now') - julianday(e.hire_date)) / 365.25 AS years_employed,
        -- 給与バンド分類
        CASE 
            WHEN e.salary >= 15000 THEN 'バンドA（15000以上）'
            WHEN e.salary >= 12000 THEN 'バンドB（12000-14999）'
            WHEN e.salary >= 8000 THEN 'バンドC（8000-11999）'
            WHEN e.salary >= 5000 THEN 'バンドD（5000-7999）'
            ELSE 'バンドE（5000未満）'
        END AS salary_band
    FROM employees e
    JOIN departments d ON e.department_id = d.id
)
SELECT 
    salary_band AS "給与バンド",
    COUNT(*) AS "従業員数",
    ROUND(AVG(salary), 2) AS "平均給与",
    ROUND(AVG(years_employed), 1) AS "平均勤続年数",
    MIN(salary) AS "最低給与",
    MAX(salary) AS "最高給与",
    GROUP_CONCAT(name, ', ') AS "従業員"
FROM salary_bands
GROUP BY salary_band
ORDER BY salary_band;

WITH monthly AS (
  SELECT DATE_FORMAT(order_date, '%Y-%m') AS m, SUM(total) AS s
  FROM orders GROUP BY m
)
SELECT m, s, LAG(s) OVER (ORDER BY m) AS prev FROM monthly;