聚合函数与分组查询

-- 明细:8 行数据
SELECT balance FROM users;

-- 聚合:1 个数字
SELECT SUM(balance) FROM users;  -- 所有用户余额之和

-- 用户总数
SELECT COUNT(*) FROM users;  -- 8

-- 用户平均余额
SELECT AVG(balance) FROM users;  -- 3068.81(精确到分)

-- 最贵和最便宜的商品
SELECT MIN(price) AS cheapest, MAX(price) AS most_expensive FROM products;
-- 88.00, 14999.00

-- 所有已完成订单的总金额
SELECT SUM(total) FROM orders WHERE status = 'done';  -- 50543.00

-- 每个城市有多少用户?
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city;

-- 结果:
-- 北京  3
-- 上海  2
-- 广州  2
-- 深圳  1

-- 每个订单状态各有多少单?总金额是多少?
SELECT status, COUNT(*) AS cnt, SUM(total) AS total_amount
FROM orders
GROUP BY status
ORDER BY total_amount DESC;

-- done       7   50543.00
-- paid       1   14999.00
-- shipped    1   6999.00
-- pending    1   1799.00
-- cancelled  1   99.00

-- 哪些城市的用户数 >= 2?
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city
HAVING COUNT(*) >= 2;

-- 北京  3
-- 上海  2
-- 广州  2

-- 在 VIP 用户中,哪些城市有 2 个及以上的 VIP?
SELECT city, COUNT(*) AS vip_count
FROM users
WHERE is_vip = true       -- WHERE: 先筛选出 VIP 用户(行级过滤)
GROUP BY city
HAVING COUNT(*) >= 2;     -- HAVING: 再筛选用户数 >= 2 的城市(组级过滤)

-- 结果:北京 3(alice, carol, grace 都是 VIP 且在北京)
-- 等等,grace 是北京的.alice, carol, grace 三个是北京 VIP -- 输出 北京 3

-- 每个城市中,VIP 和非 VIP 用户各有多少?平均余额多少?
SELECT city, is_vip, COUNT(*) AS cnt, ROUND(AVG(balance), 2) AS avg_balance
FROM users
GROUP BY city, is_vip
ORDER BY city, is_vip DESC;

-- 北京  true   3   2666.67
-- 上海  true   1   15000.00
-- 上海  false  1   1200.50
-- 广州  false  2   375.00
-- 深圳  false  1   600.00

-- 每个分类下,各商品的平均评分
SELECT c.name AS category, p.name AS product,
       ROUND(AVG(r.rating), 1) AS avg_rating,
       COUNT(r.id) AS review_count
FROM products p
JOIN categories c ON c.id = p.category_id
LEFT JOIN reviews r ON r.product_id = p.id
GROUP BY c.name, p.name
HAVING COUNT(r.id) > 0
ORDER BY category, avg_rating DESC;

-- 每个用户:总订单数,已完成订单数,已取消订单数
SELECT
  u.username,
  COUNT(o.id) AS total_orders,
  COUNT(o.id) FILTER (WHERE o.status = 'done') AS done_orders,
  COUNT(o.id) FILTER (WHERE o.status = 'cancelled') AS cancelled_orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.username
ORDER BY total_orders DESC;

-- CASE WHEN 等价写法
SELECT
  u.username,
  COUNT(o.id) AS total_orders,
  SUM(CASE WHEN o.status = 'done' THEN 1 ELSE 0 END) AS done_orders,
  SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.username;

-- 统计每个分类的:商品数,均价,最高价,库存总量
SELECT
  c.name AS category,
  COUNT(p.id) AS product_count,
  ROUND(AVG(p.price), 2) AS avg_price,
  MAX(p.price) AS max_price,
  SUM(p.stock) AS total_stock
FROM categories c
LEFT JOIN products p ON p.category_id = c.id
GROUP BY c.name
ORDER BY avg_price DESC;

-- 电子产品  3  7932.33  14999.00  750
-- 服装      2  349.00   599.00    1200
-- 食品      2  128.00   168.00    800
-- 图书      3  118.67   139.00    240

-- 有多少个不同城市的用户下过单?
SELECT COUNT(DISTINCT u.city) AS city_count
FROM users u
JOIN orders o ON o.user_id = u.id;
-- 4(北京,上海,广州,深圳都有用户下单)

-- 每个分类被多少个不同用户购买过?
SELECT c.name AS category, COUNT(DISTINCT o.user_id) AS buyer_count
FROM categories c
JOIN products p ON p.category_id = c.id
JOIN order_items oi ON oi.product_id = p.id
JOIN orders o ON o.id = oi.order_id
GROUP BY c.name
ORDER BY buyer_count DESC;

-- 每个用户的总消费金额和平均订单金额(只统计已完成的订单)
SELECT
  u.username,
  u.city,
  COUNT(o.id) AS order_count,
  SUM(o.total) AS total_spent,
  ROUND(AVG(o.total), 2) AS avg_order
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'done'
GROUP BY u.id, u.username, u.city
ORDER BY total_spent DESC NULLS LAST;

-- eve     上海  2  23064.00  11532.00
-- alice   北京  2  18597.00   9298.50
-- grace   北京  1   7266.00   7266.00
-- bob     上海  1    228.00    228.00
-- dave    广州  1    256.00    256.00
-- carol   北京  1    139.00    139.00
-- henry   广州  1    168.00    168.00
-- frank   深圳  0    (null)    (null)

-- 每个商品分类的平均评分和评价数
SELECT
  c.name AS category,
  COUNT(r.id) AS review_count,
  ROUND(AVG(r.rating), 2) AS avg_rating
FROM categories c
JOIN products p ON p.category_id = c.id
LEFT JOIN reviews r ON r.product_id = p.id
GROUP BY c.name
ORDER BY avg_rating DESC NULLS LAST;

-- 图书      3  5.00
-- 电子产品  7  4.14
-- 食品      2  4.50
-- 服装      0  (null)

-- 错误示例:想统计每个用户的订单数 AND 评价数
-- 如果一个用户有 2 单 × 3 条评价 = JOIN 后产生 6 行 → 两个 COUNT 都偏大

-- 正确做法:分别聚合后再 JOIN
SELECT u.username,
       COALESCE(oc.cnt, 0) AS order_count,
       COALESCE(rc.cnt, 0) AS review_count
FROM users u
LEFT JOIN (SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id) oc
  ON oc.user_id = u.id
LEFT JOIN (SELECT user_id, COUNT(*) AS cnt FROM reviews GROUP BY user_id) rc
  ON rc.user_id = u.id;

-- 假设有 5 个用户,其中 2 个 balance 为 NULL
-- AVG(balance) 的分母是 3(非 NULL 行数),不是 5
-- 如果希望 NULL 当 0 计算:
SELECT AVG(COALESCE(balance, 0)) FROM users;

-- 错误:WHERE 中不能用 COUNT
SELECT city FROM users WHERE COUNT(*) > 1 GROUP BY city;
-- ERROR: aggregate functions are not allowed in WHERE

-- 正确:用 HAVING
SELECT city FROM users GROUP BY city HAVING COUNT(*) > 1;