はじめてのSQL：SELECT/WHERE/GROUP BYを最短で理解【コピペOK】

-- DB作成（sqlite3 ターミナル例）
-- sqlite3 retail.sqlite
CREATE TABLE IF NOT EXISTS sales (
  order_date TEXT,
  store      TEXT,
  product    TEXT,
  qty        INTEGER,
  price      REAL
);
CREATE TABLE IF NOT EXISTS stores (
  store     TEXT PRIMARY KEY,
  area      TEXT,
  open_date TEXT
);
-- 例データ（簡略）
INSERT INTO stores VALUES
 ('S01','Tokyo','2023-01-01'),('S02','Osaka','2023-02-01');
INSERT INTO sales VALUES
 ('2024-04-01','S01','A',2,500),('2024-04-02','S01','B',1,1200),
 ('2024-04-02','S02','A',3,480),('2024-05-01','S01','A',5,500);

-- 売上データの基本列と計算列（amount=qty*price）
SELECT order_date, store, product,
       qty, price,
       qty * price AS amount
FROM sales
WHERE qty > 0 AND price > 0
ORDER BY order_date ASC
LIMIT 10;

-- 月×店舗の売上合計/数量
SELECT strftime('%Y-%m', order_date) AS ym,
       store,
       SUM(qty*price) AS sales,
       SUM(qty)       AS qty
FROM sales
WHERE qty>0 AND price>0
GROUP BY ym, store
HAVING SUM(qty) > 0
ORDER BY ym, store;

-- 売上に店舗のエリアを付与（LEFTで欠落防止）
SELECT s.order_date, s.store, t.area,
       s.product, s.qty, s.price,
       s.qty*s.price AS amount
FROM sales AS s
LEFT JOIN stores AS t
  ON s.store = t.store;

-- CTEで月次集計→上位店舗を抽出
WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS ym, store,
         SUM(qty*price) AS sales
  FROM sales
  GROUP BY ym, store
)
SELECT ym, store, sales
FROM (
  SELECT ym, store, sales,
         ROW_NUMBER() OVER(PARTITION BY ym ORDER BY sales DESC) AS rn
  FROM monthly
)
WHERE rn <= 3
ORDER BY ym, rn;

-- 店舗別の売上累積と前月比
WITH m AS (
  SELECT strftime('%Y-%m', order_date) AS ym, store,
         SUM(qty*price) AS sales
  FROM sales
  GROUP BY ym, store
)
SELECT ym, store, sales,
       SUM(sales) OVER(PARTITION BY store ORDER BY ym) AS sales_cum,
       LAG(sales, 1) OVER(PARTITION BY store ORDER BY ym) AS sales_prev,
       ROUND( (sales - LAG(sales,1) OVER(PARTITION BY store ORDER BY ym))
              / NULLIF(LAG(sales,1) OVER(PARTITION BY store ORDER BY ym),0), 4) AS yoy
FROM m
ORDER BY store, ym;

SELECT product,
       CASE
         WHEN price >= 1000 THEN 'High'
         WHEN price >= 500  THEN 'Mid'
         ELSE 'Low'
       END AS price_band,
       COUNT(*) AS cnt
FROM sales
GROUP BY product, price_band;

-- 重複除去
SELECT DISTINCT store FROM sales;

\-- NULLを0に
SELECT IFNULL(SUM(qty), 0) FROM sales;

\-- 文字列整形（SQLite）
SELECT TRIM(REPLACE(product, ' ', '' )) AS product\_norm FROM sales;

-- 2期間の共通/差分店舗（例）
WITH s1 AS (
  SELECT DISTINCT store FROM sales WHERE order_date BETWEEN '2024-04-01' AND '2024-04-30'
), s2 AS (
  SELECT DISTINCT store FROM sales WHERE order_date BETWEEN '2024-05-01' AND '2024-05-31'
)
SELECT 'common' AS kind, store FROM (
  SELECT store FROM s1 INTERSECT SELECT store FROM s2
)
UNION ALL
SELECT 'only_apr', store FROM (
  SELECT store FROM s1 EXCEPT SELECT store FROM s2
)
UNION ALL
SELECT 'only_may', store FROM (
  SELECT store FROM s2 EXCEPT SELECT store FROM s1
);

-- 実行計画（SQLite）
EXPLAIN QUERY PLAN
SELECT *
FROM sales
WHERE store='S01' AND order_date BETWEEN '2024-04-01' AND '2024-04-30';

\-- 複合インデックス（フィルタ順に）
CREATE INDEX IF NOT EXISTS idx\_sales\_store\_date ON sales(store, order\_date);

BEGIN TRANSACTION;
  INSERT INTO sales VALUES('2024-06-01','S02','B',4,1200);
  UPDATE stores SET area='Kansai' WHERE store='S02';
COMMIT;

import sqlite3, pandas as pd
con = sqlite3.connect("retail.sqlite")
q = """
WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS ym, store,
         SUM(qty*price) AS sales, SUM(qty) AS qty
  FROM sales
  WHERE qty>0 AND price>0
  GROUP BY ym, store
)
SELECT * FROM monthly;
"""
monthly = pd.read_sql_query(q, con)
monthly.to_excel("report.xlsx", index=False)
con.close()

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS ym,
         store,
         SUM(qty*price) AS sales
  FROM sales
  WHERE qty>0 AND price>0
  GROUP BY ym, store
), ranked AS (
  SELECT ym, store, sales,
         ROW_NUMBER() OVER(PARTITION BY ym ORDER BY sales DESC) AS rn,
         LAG(sales,1) OVER(PARTITION BY store ORDER BY ym) AS prev
  FROM monthly
)
SELECT ym, store, sales, rn,
       ROUND( (sales - prev) / NULLIF(prev,0), 4) AS yoy
FROM ranked
WHERE rn <= 3
ORDER BY ym, rn;

-- 1) Top-N per group
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY group_col ORDER BY score DESC) AS rn
  FROM t
) WHERE rn<=N;

\-- 2) ピボット風（集計を列化）
SELECT ym,
SUM(CASE WHEN store='S01' THEN sales ELSE 0 END) AS S01,
SUM(CASE WHEN store='S02' THEN sales ELSE 0 END) AS S02
FROM monthly GROUP BY ym;

\-- 3) EXISTS で高速な存在判定
SELECT \* FROM t1 WHERE EXISTS (
SELECT 1 FROM t2 WHERE t2.key=t1.key
);

\-- 4) 日付関数（SQLite）
SELECT date(order\_date,'start of month') AS first\_day FROM sales;