聚合与分组

-- 创建销售数据表
CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    product VARCHAR(100),
    category VARCHAR(50),
    amount NUMERIC(10,2),
    quantity INTEGER,
    sale_date DATE,
    salesperson VARCHAR(50)
);

INSERT INTO sales (product, category, amount, quantity, sale_date, salesperson) VALUES
    ('笔记本电脑', '电子产品', 5999, 2, '2025-01-01', '小陈'),
    ('手机', '电子产品', 3999, 5, '2025-01-02', '小陈'),
    ('耳机', '电子产品', 299, 10, '2025-01-03', '小李'),
    ('运动鞋', '服饰', 599, 3, '2025-01-05', '小王'),
    ('T恤', '服饰', 99, 20, '2025-01-06', '小陈'),
    ('咖啡机', '家居', 1299, 2, '2025-01-07', '小李'),
    ('台灯', '家居', 199, 8, '2025-01-08', '小王'),
    ('手机', '电子产品', 3999, 3, '2025-01-10', '小李'),
    ('笔记本电脑', '电子产品', 5999, 1, '2025-01-12', '小王'),
    ('运动鞋', '服饰', 599, 5, '2025-01-15', '小陈'),
    ('耳机', '电子产品', 299, 15, '2025-01-18', '小李'),
    ('台灯', '家居', 199, 12, '2025-01-20', '小陈'),
    ('咖啡机', '家居', 1299, 1, '2025-01-22', '小王'),
    ('T恤', '服饰', 99, 30, '2025-01-25', '小李'),
    ('手机', '电子产品', 3999, 4, '2025-01-28', '小陈');

SELECT 
    COUNT(*) AS 总记录数,
    COUNT(DISTINCT product) AS 不同商品数,
    SUM(amount) AS 总金额,
    AVG(amount) AS 平均金额,
    MAX(amount) AS 最大金额,    
    MIN(amount) AS 最小金额
FROM sales;

-- COUNT(*) 统计行数（包括 NULL）
SELECT COUNT(*) FROM sales;

-- COUNT(column) 统计非 NULL 值的数量
SELECT COUNT(product) FROM sales;

-- COUNT(DISTINCT column) 统计不重复的非 NULL 值数量
SELECT COUNT(DISTINCT product) FROM sales;

-- 三者的区别
SELECT 
    COUNT(*) AS 总行数,
    COUNT(salesperson) AS 非空姓名数,
    COUNT(DISTINCT salesperson) AS 不同姓名数
FROM sales;

-- 按产品分组
SELECT 
    product,
    COUNT(*) AS 订单数,
    SUM(amount) AS 总销售额,
    SUM(quantity) AS 总销量,
    ROUND(AVG(amount), 2) AS 平均金额
FROM sales
GROUP BY product
ORDER BY 总销售额 DESC;

-- 按类别和产品分组（SQL 中，SELECT 的非聚合列必须出现在 GROUP BY 中）
SELECT 
    category,
    product,
    SUM(amount) AS 销售额,
    COUNT(*) AS 订单数
FROM sales
GROUP BY category, product
ORDER BY category, 销售额 DESC;

SELECT 
    category,
    product,
    SUM(amount) AS 销售额
FROM sales
GROUP BY 1, 2  -- 等同于 GROUP BY category, product
ORDER BY 1, 3 DESC;

-- 按日期范围分组
SELECT 
    EXTRACT(YEAR FROM sale_date) AS 年,
    EXTRACT(MONTH FROM sale_date) AS 月,
    SUM(amount) AS 月销售额
FROM sales
GROUP BY EXTRACT(YEAR FROM sale_date), EXTRACT(MONTH FROM sale_date)
ORDER BY 年, 月;

-- 错误：WHERE 不能使用聚合函数
SELECT product, SUM(amount) AS 总销售额
FROM sales
WHERE SUM(amount) > 10000;   -- ❌ 会报错

-- 正确：用 HAVING 过滤聚合结果
SELECT product, SUM(amount) AS 总销售额
FROM sales
GROUP BY product
HAVING SUM(amount) > 10000
ORDER BY 总销售额 DESC;

-- 只统计 1 月份的销售，然后找出销售额 > 5000 的商品
SELECT 
    product,
    COUNT(*) AS 订单数,
    SUM(amount) AS 总销售额
FROM sales
WHERE sale_date >= '2025-01-01' AND sale_date < '2025-02-01'
GROUP BY product
HAVING SUM(amount) > 5000
ORDER BY 总销售额 DESC;

-- 各种条件聚合一次查询
SELECT 
    category,
    COUNT(*) AS 总订单,
    COUNT(*) FILTER (WHERE amount > 1000) AS 大额订单,
    SUM(amount) AS 总金额,
    SUM(amount) FILTER (WHERE quantity >= 10) AS 批发的金额
FROM sales
GROUP BY category;

SELECT 
    category,
    SUM(amount) AS 总金额,
    SUM(CASE WHEN quantity >= 10 THEN amount ELSE 0 END) AS 批发的金额
FROM sales
GROUP BY category;

-- 多种分组维度一次查询
SELECT 
    COALESCE(category, '全部') AS 类别,
    COALESCE(product, '全部') AS 商品,
    SUM(amount) AS 销售额
FROM sales
GROUP BY GROUPING SETS (
    (category),          -- 按类别汇总
    (category, product), -- 按类别+商品汇总
    ()                   -- 全部汇总
)
ORDER BY category NULLS LAST, product NULLS LAST;

-- ROLLUP：从高到低逐层汇总
SELECT 
    category,
    product,
    SUM(amount) AS 销售额
FROM sales
GROUP BY ROLLUP (category, product)
ORDER BY category, product;

-- 性能对比：先过滤再聚合 vs 先聚合再过滤
-- ✅ 好的做法
SELECT category, SUM(amount)
FROM sales
WHERE sale_date >= '2025-01-15'  -- 先过滤
GROUP BY category;

-- ❌ 低效做法（不适用本例，但展示原则）
SELECT category, SUM(amount)
FROM sales
GROUP BY category
HAVING MIN(sale_date) >= '2025-01-15';  -- 全表聚合后再过滤

-- 1. 各类别的销售额和销量占比
SELECT 
    category,
    SUM(amount) AS 销售额,
    ROUND(SUM(amount) / SUM(SUM(amount)) OVER () * 100, 2) AS 销售占比,
    SUM(quantity) AS 总销量,
    ROUND(SUM(quantity) * 1.0 / SUM(SUM(quantity)) OVER () * 100, 2) AS 销量占比
FROM sales
GROUP BY category
ORDER BY 销售额 DESC;

-- 2. 每个销售员负责的商品种类数
SELECT 
    salesperson,
    COUNT(DISTINCT product) AS 覆盖商品数,
    COUNT(*) AS 订单数,
    SUM(amount) AS 总业绩
FROM sales
GROUP BY salesperson
ORDER BY 总业绩 DESC;

-- 3. 找出日均销售额最高的商品（按 1 月份计算）
SELECT 
    product,
    SUM(amount) AS 总销售额,
    COUNT(DISTINCT sale_date) AS 销售天数,
    ROUND(SUM(amount) / COUNT(DISTINCT sale_date), 2) AS 日均销售额
FROM sales
WHERE sale_date >= '2025-01-01' AND sale_date < '2025-02-01'
GROUP BY product
ORDER BY 日均销售额 DESC
LIMIT 5;