— WITH句で月次売上を集計 WITH monthly_sales AS ( SELECT strftime(‘%Y-%m’, order_date) as year_month, — 年月形式に変換 SUM(amount) as total_sales, — 売上合計 COUNT(*) as order_count — 注文件数 FROM orders WHERE order_date >= date(‘now’, ‘-13 months’) — 過去13ヶ月（前年同月比計算用） GROUP BY strftime(‘%Y-%m’, order_date) — 年月でグループ化 )

— 前月比を計算 ROUND( (total_sales – LAG(total_sales, 1) OVER (ORDER BY year_month)) * 100.0 / LAG(total_sales, 1) OVER (ORDER BY year_month), 1 ) as mom_growth — MoM = Month over Month（前月比）

— 前年同月比を計算 ROUND( (total_sales – LAG(total_sales, 12) OVER (ORDER BY year_month)) * 100.0 / LAG(total_sales, 12) OVER (ORDER BY year_month), 1 ) as yoy_growth — YoY = Year over Year（前年同月比）

— 月次売上推移（前月比・前年同月比付き） WITH monthly_sales AS ( SELECT strftime(‘%Y-%m’, order_date) as year_month, SUM(amount) as total_sales, COUNT(*) as order_count FROM orders WHERE order_date >= date(‘now’, ‘-13 months’) GROUP BY strftime(‘%Y-%m’, order_date) ) SELECT year_month, total_sales, order_count, — 前月比 ROUND( (total_sales – LAG(total_sales, 1) OVER (ORDER BY year_month)) * 100.0 / LAG(total_sales, 1) OVER (ORDER BY year_month), 1 ) as mom_growth, — 前年同月比 ROUND( (total_sales – LAG(total_sales, 12) OVER (ORDER BY year_month)) * 100.0 / LAG(total_sales, 12) OVER (ORDER BY year_month), 1 ) as yoy_growth FROM monthly_sales ORDER BY year_month DESC LIMIT 12;

— 週ごとの売上を集計 WITH weekly_sales AS ( SELECT strftime(‘%Y-W%W’, order_date) as year_week, — 年-週番号形式 SUM(amount) as total_sales, COUNT(*) as order_count FROM orders WHERE order_date >= date(‘now’, ‘-91 days’) — 13週間 = 91日 GROUP BY strftime(‘%Y-W%W’, order_date) )

— 4週間移動平均 ROUND( AVG(total_sales) OVER ( ORDER BY year_week ROWS BETWEEN 3 PRECEDING AND CURRENT ROW — 直近4週間 ), 0 ) as moving_avg_4weeks

— 週次売上と4週間移動平均 WITH weekly_sales AS ( SELECT strftime(‘%Y-W%W’, order_date) as year_week, SUM(amount) as total_sales, COUNT(*) as order_count FROM orders WHERE order_date >= date(‘now’, ‘-91 days’) GROUP BY strftime(‘%Y-W%W’, order_date) ) SELECT year_week, total_sales, order_count, — 4週間移動平均 ROUND( AVG(total_sales) OVER ( ORDER BY year_week ROWS BETWEEN 3 PRECEDING AND CURRENT ROW ), 0 ) as moving_avg_4weeks FROM weekly_sales ORDER BY year_week DESC;

— 日ごとの売上を集計 WITH daily_sales AS ( SELECT order_date, SUM(amount) as total_sales, COUNT(*) as order_count FROM orders WHERE order_date >= date(‘now’, ‘-30 days’) GROUP BY order_date )

— 月初からの累積売上（MTD） SUM(total_sales) OVER ( PARTITION BY strftime(‘%Y-%m’, order_date) — 月ごとにリセット ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — 最初から現在まで ) as cumulative_sales_mtd

— 日次売上と累積売上 WITH daily_sales AS ( SELECT order_date, SUM(amount) as total_sales, COUNT(*) as order_count FROM orders WHERE order_date >= date(‘now’, ‘-30 days’) GROUP BY order_date ) SELECT order_date, total_sales, order_count, — 月初からの累積売上（MTD） SUM(total_sales) OVER ( PARTITION BY strftime(‘%Y-%m’, order_date) ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) as cumulative_sales_mtd, — 全期間の累積売上 SUM(total_sales) OVER ( ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) as cumulative_sales_total FROM daily_sales ORDER BY order_date DESC;

— 商品別売上を集計 SELECT p.category, — カテゴリ p.product_id, — 商品ID p.product_name, — 商品名 SUM(od.quantity * od.unit_price) as total_sales, — 売上金額 SUM(od.quantity) as total_quantity — 販売数量 FROM products p JOIN order_details od ON p.product_id = od.product_id JOIN orders o ON od.order_id = o.order_id WHERE o.order_date >= date(‘now’, ‘start of month’) — 今月の注文のみ GROUP BY p.category, p.product_id, p.product_name

— カテゴリ内での順位を付ける ROW_NUMBER() OVER ( PARTITION BY p.category — カテゴリごとに区切る ORDER BY SUM(od.quantity * od.unit_price) DESC — 売上順 ) as rank_in_category

— TOP10だけを抽出 WHERE rank_in_category <= 10

— カテゴリ別売上TOP10 WITH product_sales AS ( SELECT p.category, p.product_id, p.product_name, SUM(od.quantity * od.unit_price) as total_sales, SUM(od.quantity) as total_quantity, ROW_NUMBER() OVER ( PARTITION BY p.category ORDER BY SUM(od.quantity * od.unit_price) DESC ) as rank_in_category FROM products p JOIN order_details od ON p.product_id = od.product_id JOIN orders o ON od.order_id = o.order_id WHERE o.order_date >= date(‘now’, ‘start of month’) GROUP BY p.category, p.product_id, p.product_name ) SELECT category, rank_in_category as rank, product_name, total_sales, total_quantity FROM product_sales WHERE rank_in_category <= 10 ORDER BY category, rank_in_category;

— 今月のランキング WITH this_month AS ( SELECT p.product_id, p.product_name, SUM(od.quantity * od.unit_price) as total_sales, ROW_NUMBER() OVER ( ORDER BY SUM(od.quantity * od.unit_price) DESC ) as rank FROM products p JOIN order_details od ON p.product_id = od.product_id JOIN orders o ON od.order_id = o.order_id WHERE o.order_date >= date(‘now’, ‘start of month’) GROUP BY p.product_id, p.product_name )

— 先月のランキング last_month AS ( SELECT p.product_id, ROW_NUMBER() OVER ( ORDER BY SUM(od.quantity * od.unit_price) DESC ) as rank FROM products p JOIN order_details od ON p.product_id = od.product_id JOIN orders o ON od.order_id = o.order_id WHERE o.order_date >= date(‘now’, ‘start of month’, ‘-1 month’) AND o.order_date < date('now', 'start of month') GROUP BY p.product_id )

— 順位変動を表示 CASE WHEN lm.rank IS NULL THEN ‘NEW’ — 先月なし = 新商品 WHEN lm.rank > tm.rank THEN ‘↑’ || (lm.rank – tm.rank) — 順位上昇 WHEN lm.rank < tm.rank THEN '↓' || (tm.rank - lm.rank) -- 順位下降 ELSE '→' -- 変化なし END as rank_change

— 売上ランクの変動 WITH this_month AS ( SELECT p.product_id, p.product_name, SUM(od.quantity * od.unit_price) as total_sales, ROW_NUMBER() OVER (ORDER BY SUM(od.quantity * od.unit_price) DESC) as rank FROM products p JOIN order_details od ON p.product_id = od.product_id JOIN orders o ON od.order_id = o.order_id WHERE o.order_date >= date(‘now’, ‘start of month’) GROUP BY p.product_id, p.product_name ), last_month AS ( SELECT p.product_id, ROW_NUMBER() OVER (ORDER BY SUM(od.quantity * od.unit_price) DESC) as rank FROM products p JOIN order_details od ON p.product_id = od.product_id JOIN orders o ON od.order_id = o.order_id WHERE o.order_date >= date(‘now’, ‘start of month’, ‘-1 month’) AND o.order_date < date('now', 'start of month') GROUP BY p.product_id ) SELECT tm.rank as current_rank, tm.product_name, tm.total_sales, lm.rank as last_month_rank, CASE WHEN lm.rank IS NULL THEN 'NEW' WHEN lm.rank > tm.rank THEN ‘↑’ || (lm.rank – tm.rank) WHEN lm.rank < tm.rank THEN '↓' || (tm.rank - lm.rank) ELSE '→' END as rank_change FROM this_month tm LEFT JOIN last_month lm ON tm.product_id = lm.product_id WHERE tm.rank <= 20 ORDER BY tm.rank;

— 顧客ごとのRFM値を計算 WITH customer_rfm AS ( SELECT c.customer_id, c.customer_name, — Recency: 最終購入からの日数 julianday(‘now’) – julianday(MAX(o.order_date)) as recency_days, — Frequency: 購入回数 COUNT(o.order_id) as frequency, — Monetary: 総購入金額 SUM(o.amount) as monetary FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_date >= date(‘now’, ‘-1 year’) — 過去1年間 GROUP BY c.customer_id, c.customer_name )

— Rスコア（最近購入ほど高い） CASE WHEN recency_days <= 30 THEN 5 -- 30日以内 WHEN recency_days <= 60 THEN 4 -- 60日以内 WHEN recency_days <= 90 THEN 3 -- 90日以内 WHEN recency_days <= 180 THEN 2 -- 半年以内 ELSE 1 -- それ以上前 END as r_score, -- Fスコア（購入回数が多いほど高い） CASE WHEN frequency >= 20 THEN 5 — 20回以上 WHEN frequency >= 10 THEN 4 — 10回以上 WHEN frequency >= 5 THEN 3 — 5回以上 WHEN frequency >= 2 THEN 2 — 2回以上 ELSE 1 — 1回 END as f_score, — Mスコア（購入金額が大きいほど高い） CASE WHEN monetary >= 100000 THEN 5 — 10万円以上 WHEN monetary >= 50000 THEN 4 — 5万円以上 WHEN monetary >= 20000 THEN 3 — 2万円以上 WHEN monetary >= 10000 THEN 2 — 1万円以上 ELSE 1 — それ未満 END as m_score

— セグメント分類 CASE WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN ‘VIP’ WHEN r_score >= 4 AND f_score >= 3 THEN ‘ロイヤル顧客’ WHEN r_score >= 3 AND m_score >= 4 THEN ‘大口顧客’ WHEN r_score <= 2 AND f_score >= 3 THEN ‘離反リスク’ WHEN r_score >= 4 AND f_score <= 2 THEN '新規顧客' WHEN r_score <= 2 AND f_score <= 2 THEN '休眠顧客' ELSE '一般顧客' END as segment

— RFM分析の完全版 WITH customer_rfm AS ( SELECT c.customer_id, c.customer_name, julianday(‘now’) – julianday(MAX(o.order_date)) as recency_days, COUNT(o.order_id) as frequency, SUM(o.amount) as monetary FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_date >= date(‘now’, ‘-1 year’) GROUP BY c.customer_id, c.customer_name ), rfm_scores AS ( SELECT customer_id, customer_name, recency_days, frequency, monetary, — Rスコア CASE WHEN recency_days <= 30 THEN 5 WHEN recency_days <= 60 THEN 4 WHEN recency_days <= 90 THEN 3 WHEN recency_days <= 180 THEN 2 ELSE 1 END as r_score, -- Fスコア CASE WHEN frequency >= 20 THEN 5 WHEN frequency >= 10 THEN 4 WHEN frequency >= 5 THEN 3 WHEN frequency >= 2 THEN 2 ELSE 1 END as f_score, — Mスコア CASE WHEN monetary >= 100000 THEN 5 WHEN monetary >= 50000 THEN 4 WHEN monetary >= 20000 THEN 3 WHEN monetary >= 10000 THEN 2 ELSE 1 END as m_score FROM customer_rfm ) SELECT customer_id, customer_name, recency_days, frequency, monetary, r_score, f_score, m_score, — セグメント分類 CASE WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN ‘VIP’ WHEN r_score >= 4 AND f_score >= 3 THEN ‘ロイヤル顧客’ WHEN r_score >= 3 AND m_score >= 4 THEN ‘大口顧客’ WHEN r_score <= 2 AND f_score >= 3 THEN ‘離反リスク’ WHEN r_score >= 4 AND f_score <= 2 THEN '新規顧客' WHEN r_score <= 2 AND f_score <= 2 THEN '休眠顧客' ELSE '一般顧客' END as segment FROM rfm_scores ORDER BY r_score DESC, f_score DESC, m_score DESC;

— セグメント別サマリー WITH customer_rfm AS ( — 上記のRFM分析クエリと同様 … ), rfm_with_segment AS ( — セグメント分類まで含めたクエリ … ) SELECT segment, COUNT(*) as customer_count, — 顧客数 SUM(monetary) as total_sales, — 総売上 ROUND(AVG(monetary), 0) as avg_sales_per_customer, — 平均売上 ROUND(AVG(frequency), 1) as avg_purchase_frequency, — 平均購入回数 ROUND(AVG(recency_days), 0) as avg_recency_days, — 平均最終購入日数 — 構成比 ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) as customer_ratio, ROUND(SUM(monetary) * 100.0 / SUM(SUM(monetary)) OVER (), 1) as sales_ratio FROM rfm_with_segment GROUP BY segment ORDER BY total_sales DESC;

EXPLAIN QUERY PLAN SELECT c.customer_name, COUNT(o.order_id) as order_count, SUM(o.amount) as total_amount, MAX(o.order_date) as last_order_date FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE YEAR(o.order_date) = 2024 GROUP BY c.customer_id, c.customer_name ORDER BY total_amount DESC;

QUERY PLAN
|--SCAN customers
|--SCAN orders
`--USE TEMP B-TREE FOR ORDER BY
        

— 1. order_dateにインデックス（日付での絞り込み用） CREATE INDEX idx_orders_date ON orders(order_date); — 2. customer_idにインデックス（JOIN用） CREATE INDEX idx_orders_customer ON orders(customer_id);

-- ✅ 最適化後のクエリ SELECT c.customer_name, COUNT(o.order_id) as order_count, SUM(o.amount) as total_amount, MAX(o.order_date) as last_order_date FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id -- LEFT → INNER WHERE o.order_date >= '2024-01-01' -- 関数を使わない（範囲検索） AND o.order_date < '2025-01-01' GROUP BY c.customer_id, c.customer_name ORDER BY total_amount DESC;

-- 顧客ごとに注文を番号付け WITH ranked_orders AS ( SELECT o.order_id, o.customer_id, o.order_date, o.amount, ROW_NUMBER() OVER ( PARTITION BY o.customer_id -- 顧客ごとに区切る ORDER BY o.order_date DESC -- 新しい順 ) as row_num FROM orders o )

-- 最新3件だけを抽出 WHERE ro.row_num <= 3

-- 各顧客の最新3件の注文（N+1問題なし） WITH ranked_orders AS ( SELECT o.order_id, o.customer_id, o.order_date, o.amount, ROW_NUMBER() OVER ( PARTITION BY o.customer_id ORDER BY o.order_date DESC ) as row_num FROM orders o ) SELECT c.customer_id, c.customer_name, ro.order_id, ro.order_date, ro.amount FROM customers c JOIN ranked_orders ro ON c.customer_id = ro.customer_id WHERE ro.row_num <= 3 ORDER BY c.customer_id, ro.row_num;

-- 最適な複合インデックス CREATE INDEX idx_orders_optimal ON orders(customer_id, status, order_date DESC);

EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 101 AND order_date >= '2024-01-01' AND status = 'completed' ORDER BY order_date DESC LIMIT 10;

QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_optimal
        

-- 今月の売上 WITH current_month AS ( SELECT SUM(amount) as sales, COUNT(*) as orders FROM orders WHERE order_date >= date('now', 'start of month') )

-- 先月の売上 last_month AS ( SELECT SUM(amount) as sales, COUNT(*) as orders FROM orders WHERE order_date >= date('now', 'start of month', '-1 month') AND order_date < date('now', 'start of month') )

-- 前年同月の売上 last_year_same_month AS ( SELECT SUM(amount) as sales, COUNT(*) as orders FROM orders WHERE order_date >= date('now', 'start of month', '-1 year') AND order_date < date('now', 'start of month', '-1 year', '+1 month') )

-- 今月の新規顧客数 new_customers AS ( SELECT COUNT(*) as count FROM customers WHERE created_at >= date('now', 'start of month') )

-- 売上TOP5商品 top_products AS ( SELECT p.product_name, SUM(od.quantity * od.unit_price) as sales FROM products p JOIN order_details od ON p.product_id = od.product_id JOIN orders o ON od.order_id = o.order_id WHERE o.order_date >= date('now', 'start of month') GROUP BY p.product_id, p.product_name ORDER BY sales DESC LIMIT 5 )

-- 結果を出力 SELECT -- 基本指標 cm.sales as current_sales, cm.orders as current_orders, -- 前月比 ROUND((cm.sales - lm.sales) * 100.0 / lm.sales, 1) as mom_growth, -- 前年同月比 ROUND((cm.sales - ly.sales) * 100.0 / ly.sales, 1) as yoy_growth, -- 新規顧客 nc.count as new_customers FROM current_month cm, last_month lm, last_year_same_month ly, new_customers nc;

-- 総合ダッシュボードクエリ WITH current_month AS ( SELECT SUM(amount) as sales, COUNT(*) as orders FROM orders WHERE order_date >= date('now', 'start of month') ), last_month AS ( SELECT SUM(amount) as sales, COUNT(*) as orders FROM orders WHERE order_date >= date('now', 'start of month', '-1 month') AND order_date < date('now', 'start of month') ), last_year_same_month AS ( SELECT SUM(amount) as sales, COUNT(*) as orders FROM orders WHERE order_date >= date('now', 'start of month', '-1 year') AND order_date < date('now', 'start of month', '-1 year', '+1 month') ), new_customers AS ( SELECT COUNT(*) as count FROM customers WHERE created_at >= date('now', 'start of month') ), top_products AS ( SELECT p.product_name, SUM(od.quantity * od.unit_price) as sales FROM products p JOIN order_details od ON p.product_id = od.product_id JOIN orders o ON od.order_id = o.order_id WHERE o.order_date >= date('now', 'start of month') GROUP BY p.product_id, p.product_name ORDER BY sales DESC LIMIT 5 ) SELECT cm.sales as current_sales, cm.orders as current_orders, ROUND((cm.sales - lm.sales) * 100.0 / lm.sales, 1) as mom_growth, ROUND((cm.sales - ly.sales) * 100.0 / ly.sales, 1) as yoy_growth, nc.count as new_customers, (SELECT group_concat(product_name || ':' || sales, ', ') FROM top_products) as top_5_products FROM current_month cm, last_month lm, last_year_same_month ly, new_customers nc;