PostgreSQL「ERROR: column must appear in the GROUP BY clause」の原因と対処

-- サンプル
CREATE TABLE customers(id int PRIMARY KEY, name text, region text);
CREATE TABLE orders(id int PRIMARY KEY, customer_id int, amount numeric, created_at timestamp);

-- NG: 非集約列 name をSELECTしているが、GROUP BYに無い
SELECT c.name, SUM(o.amount)
FROM customers c JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;
-- ERROR:  column "c.name" must appear in the GROUP BY clause or be used in an aggregate function

-- OK: name を GROUP BY に追加
SELECT c.id, c.name, SUM(o.amount)
FROM customers c JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

-- SELECTに出す列は全て GROUP BY へ
SELECT c.region, c.name, COUNT(*) AS orders
FROM customers c JOIN orders o ON o.customer_id = c.id
GROUP BY c.region, c.name;

-- 代表値を1つに縮めたいなら集約関数で包む
SELECT c.region,
       COUNT(*)                                   AS orders,
       SUM(o.amount)                              AS revenue,
       MAX(o.amount)                              AS max_amount,
       STRING_AGG(DISTINCT c.name, ', ' ORDER BY c.name) AS customers
FROM customers c JOIN orders o ON o.customer_id = c.id
GROUP BY c.region;

-- 式そのものを GROUP BY
SELECT date_trunc('month', o.created_at) AS ym, SUM(o.amount)
FROM orders o
GROUP BY date_trunc('month', o.created_at)
ORDER BY ym;

-- 別名で GROUP BY（PostgreSQL は可）
SELECT date_trunc('month', o.created_at) AS ym, SUM(o.amount)
FROM orders o
GROUP BY ym
ORDER BY ym;

-- 序数で GROUP BY（1列目＝ym）
SELECT date_trunc('month', o.created_at) AS ym, SUM(o.amount)
FROM orders o
GROUP BY 1
ORDER BY 1;

-- NG: 「顧客ごとの合計」が意図なのに、商品名までSELECTすると粒度が壊れる
SELECT c.id, c.name, p.product_name, SUM(o.amount)
FROM customers c
JOIN orders o   ON o.customer_id = c.id
JOIN products p ON p.id = o.id
GROUP BY c.id, c.name;   -- product_name が未集約 → エラー

-- 対処1: product_name を SELECT しない（粒度を保つ）
SELECT c.id, c.name, SUM(o.amount)
FROM customers c JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

-- 対処2: product_name を集約（たとえば一覧化）
SELECT c.id, c.name, SUM(o.amount),
       STRING_AGG(DISTINCT p.product_name, ', ') AS products
FROM customers c
JOIN orders o   ON o.customer_id = c.id
JOIN products p ON p.id = o.id
GROUP BY c.id, c.name;

-- 1) DISTINCT ON で「各顧客の最新注文」を1件だけ
--    DISTINCT ON (key) ORDER BY key, 条件 … が鉄板
SELECT DISTINCT ON (o.customer_id)
       o.customer_id, o.id AS order_id, o.amount, o.created_at
FROM orders o
ORDER BY o.customer_id, o.created_at DESC;  -- 各customer_idで最新

-- 2) サブクエリで「代表行id」を決めてJOIN
WITH latest AS (
  SELECT customer_id, MAX(created_at) AS mx
  FROM orders GROUP BY customer_id
)
SELECT o.*
FROM orders o JOIN latest l
  ON o.customer_id = l.customer_id AND o.created_at = l.mx;

-- NG: HAVING に非集約列が登場
SELECT c.region, SUM(o.amount)
FROM customers c JOIN orders o ON o.customer_id = c.id
GROUP BY c.region
HAVING c.name = 'Alice';  -- エラー

-- OK: HAVING は集計後の条件。非集約条件は WHERE へ
SELECT c.region, SUM(o.amount)
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE c.name = 'Alice'
GROUP BY c.region;

-- ORDER BY も同様（非集約列を出すなら GROUP BY へ）
SELECT c.region, SUM(o.amount)
FROM customers c JOIN orders o ON o.customer_id = c.id
GROUP BY c.region
ORDER BY c.name;  -- エラー

-- 「状態ごとの件数」を列で出すなら、列を増やさず集計で表現
SELECT c.region,
       COUNT(*) FILTER (WHERE o.amount >= 10000) AS big_orders,
       COUNT(*) FILTER (WHERE o.amount <  10000) AS small_orders,
       SUM(o.amount) AS total
FROM customers c JOIN orders o ON o.customer_id = c.id
GROUP BY c.region;

-- CASE でも同様
SELECT c.region,
       SUM(CASE WHEN o.amount >= 10000 THEN 1 ELSE 0 END) AS big_orders
FROM customers c JOIN orders o ON o.customer_id = c.id
GROUP BY c.region;

-- NG: 集計とウィンドウを同じ層で混在させて非集約列を参照
-- OK: まず集計、その結果に対してウィンドウをかける
WITH agg AS (
  SELECT c.region, SUM(o.amount) AS revenue
  FROM customers c JOIN orders o ON o.customer_id = c.id
  GROUP BY c.region
)
SELECT region,
       revenue,
       revenue / SUM(revenue) OVER () AS share
FROM agg
ORDER BY revenue DESC;

-- 1) SELECT の各列を「集約 or GROUP BY」のどちらかに分類
-- 2) HAVING/ORDER BY にも非集約列が紛れていないかを確認
-- 3) 1対多JOINなら、代表値の設計（集約 or 代表行）を決める
EXPLAIN (VERBOSE, COSTS OFF)
SELECT c.name, SUM(o.amount)
FROM customers c JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;  -- 実行計画とエラー箇所を併読

-- P1: 非集約列がSELECTにある
-- NG: SELECT a, SUM(b) FROM t GROUP BY b;
-- OK: SELECT a, SUM(b) FROM t GROUP BY a;

-- P2: JOIN後に片側の列を出したい
-- NG: SELECT c.name, SUM(o.amount) FROM c JOIN o GROUP BY c.id;
-- OK: SELECT c.id, c.name, SUM(o.amount) FROM c JOIN o GROUP BY c.id, c.name;

-- P3: 商品名まで出したいが「顧客ごと合計」が目的
-- NG: SELECT c.name, p.product_name, SUM(o.amount) ... GROUP BY c.name;
-- OK: SELECT c.name, SUM(o.amount), STRING_AGG(DISTINCT p.product_name, ', ') ...
--     GROUP BY c.name;

-- P4: 月別集計（式を出す）
-- NG: SELECT date_trunc('month', ts) AS m, SUM(x) FROM t GROUP BY ts;
-- OK: SELECT date_trunc('month', ts) AS m, SUM(x) FROM t GROUP BY m;

-- P5: 各グループの最新行
-- NG: SELECT *, MAX(ts) FROM t GROUP BY key;         -- 非集約列だらけ
-- OK: SELECT DISTINCT ON (key) * FROM t ORDER BY key, ts DESC;

-- P6: HAVINGで非集約列を使っている
-- NG: ... GROUP BY region HAVING name = 'A';
-- OK: ... WHERE name = 'A' GROUP BY region;