SQLite Group By

SQLite 的 GROUP BY 子句 用于将查询结果按一个或多个列分组，常与聚合函数（COUNT、SUM、AVG、MAX、MIN）配合使用，实现统计分析。

---

基本语法

SELECT column1, aggregate_function(column2), ...
FROM table_name
[WHERE condition]
GROUP BY column1, column2, ...
[HAVING condition]
[ORDER BY ...]
[LIMIT ...];

• GROUP BY 必须出现在 WHERE 之后、ORDER BY 之前
• SELECT 中非聚合列必须出现在 GROUP BY 中
• HAVING 用于过滤分组后的结果（类似 WHERE，但作用于聚合值）

---

示例表结构

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    product TEXT,
    amount REAL,
    order_date DATE,
    status TEXT  -- 'completed', 'pending', 'cancelled'
);

INSERT INTO orders (customer, product, amount, order_date, status) VALUES
('Alice', 'Laptop',     1200, '2025-10-01', 'completed'),
('Alice', 'Mouse',       25, '2025-10-02', 'completed'),
('Bob',   'Keyboard',    80, '2025-10-03', 'completed'),
('Alice', 'Monitor',    350, '2025-10-05', 'pending'),
('Charlie','Webcam',     90, '2025-10-06', 'completed'),
('Bob',   'Headset',    150, '2025-10-07', 'cancelled'),
('Alice', 'USB Hub',     45, '2025-10-08', 'completed'),
('Charlie','Printer',   300, '2025-10-09', 'completed');

---

1. 基础分组统计

-- 每个客户订单总数和总金额
SELECT 
    customer,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
GROUP BY customer;

结果：

customer | order_count | total_amount
---------|-------------|-------------
Alice    | 4           | 1620.0
Bob      | 2           | 230.0
Charlie  | 2           | 390.0

---

2. 多列分组

-- 按客户和状态统计
SELECT 
    customer, 
    status,
    COUNT(*) AS count,
    AVG(amount) AS avg_amount
FROM orders
GROUP BY customer, status;

---

3. 使用 HAVING 过滤分组结果

-- 只显示订单数 ≥ 3 的客户
SELECT customer, COUNT(*) AS orders
FROM orders
GROUP BY customer
HAVING COUNT(*) >= 3;

HAVING vs WHERE：

• WHERE：过滤原始行
• HAVING：过滤分组后的结果

-- WHERE 先过滤，再分组
SELECT customer, SUM(amount)
FROM orders
WHERE status = 'completed'
GROUP BY customer;

---

4. 结合时间函数分组

-- 按月份统计销售额
SELECT 
    STRFTIME('%Y-%m', order_date) AS month,
    COUNT(*) AS orders,
    SUM(amount) AS revenue
FROM orders
GROUP BY STRFTIME('%Y-%m', order_date)
ORDER BY month;

---

5. 分组 + 排序 + 分页

-- 每个产品类别的平均金额（降序），取前 3
SELECT 
    product,
    AVG(amount) AS avg_price,
    COUNT(*) AS sales_count
FROM orders
GROUP BY product
ORDER BY avg_price DESC
LIMIT 3;

---

6. 复杂表达式分组

-- 按金额区间分组（<100, 100-500, >500）
SELECT 
    CASE 
        WHEN amount < 100  THEN 'Low'
        WHEN amount <= 500 THEN 'Medium'
        ELSE 'High'
    END AS price_range,
    COUNT(*) AS count,
    SUM(amount) AS total
FROM orders
GROUP BY price_range;

---

7. 常见错误

错误	正确做法
SELECT name, COUNT(*) FROM ... GROUP BY department	name 必须在 GROUP BY 中
HAVING customer = 'Alice'	应为 WHERE customer = 'Alice'
GROUP BY 1（列位置）	可行但可读性差

-- 错误：name 未分组
SELECT customer, product, COUNT(*) FROM orders GROUP BY customer;

-- 正确
SELECT customer, COUNT(*) FROM orders GROUP BY customer;

---

8. 性能优化

-- 为分组列建索引
CREATE INDEX idx_customer_status ON orders(customer, status);
CREATE INDEX idx_order_date ON orders(order_date);

分组字段有索引 → 减少排序开销

---

快速参考

需求	SQL
每客户订单数	GROUP BY customer + COUNT(*)
每月营收	GROUP BY STRFTIME('%Y-%m', date)
订单数 > 2	HAVING COUNT(*) > 2
最高平均金额	ORDER BY AVG(amount) DESC LIMIT 1

---

最佳实践

1. 非聚合列必须在 GROUP BY 中
2. 用 WHERE 过滤行，用 HAVING 过滤组
3. 为 GROUP BY 列建复合索引
4. 结合 ORDER BY 呈现更清晰

---

需要我帮你写一个针对你表的 GROUP BY 统计吗？
请提供表名、想统计的字段和分组维度！