分组聚合查询 - 元素码农

发布时间: 2025-04-02 23:08

↑

# 分组聚合查询

本文将详细介绍SQLite中的分组聚合查询，包括GROUP BY子句的使用、聚合函数的应用以及HAVING子句的过滤等内容。

## 基础概念

### GROUP BY子句

GROUP BY子句用于将查询结果按照指定的列进行分组，通常与聚合函数一起使用。

### 常用聚合函数

1. **COUNT()**: 计算行数
2. **SUM()**: 求和
3. **AVG()**: 计算平均值
4. **MAX()**: 获取最大值
5. **MIN()**: 获取最小值

## 基本分组查询

### 单列分组

```sql
-- 按类别统计商品数量
SELECT category, COUNT(*) as product_count
FROM products
GROUP BY category;

-- 按状态统计订单总金额
SELECT status, SUM(total_amount) as total_sales
FROM orders
GROUP BY status;
```

### 多列分组

```sql
-- 按类别和品牌统计商品
SELECT category, brand, COUNT(*) as count,
       AVG(price) as avg_price
FROM products
GROUP BY category, brand;
```

## 高级分组查询

### HAVING子句

```sql
-- 查找平均价格超过1000的类别
SELECT category, AVG(price) as avg_price
FROM products
GROUP BY category
HAVING avg_price > 1000;

-- 查找订单数量大于10的客户
SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id
HAVING order_count > 10;
```

### WHERE与HAVING组合

```sql
-- 查找近30天内销售额超过10000的类别
SELECT category, SUM(total_amount) as sales
FROM orders
WHERE order_date >= date('now', '-30 days')
GROUP BY category
HAVING sales > 10000;
```

## 复杂分组查询

### 子查询结合

```sql
-- 查找销售额高于平均值的类别
SELECT category, SUM(total_amount) as sales
FROM orders
GROUP BY category
HAVING sales > (
    SELECT AVG(category_sales)
    FROM (
        SELECT SUM(total_amount) as category_sales
        FROM orders
        GROUP BY category
    )
);
```

### 多表联合分组

```sql
-- 按类别统计商品和销售信息
SELECT p.category,
       COUNT(DISTINCT p.id) as product_count,
       COUNT(DISTINCT o.id) as order_count,
       SUM(o.quantity) as total_sold
FROM products p
LEFT JOIN order_items o ON p.id = o.product_id
GROUP BY p.category;
```

## 时间分组

### 按日期分组

```sql
-- 按天统计销售额
SELECT date(order_date) as sale_date,
       COUNT(*) as order_count,
       SUM(total_amount) as daily_sales
FROM orders
GROUP BY date(order_date);

-- 按月统计销售额
SELECT strftime('%Y-%m', order_date) as month,
       COUNT(*) as order_count,
       SUM(total_amount) as monthly_sales
FROM orders
GROUP BY month;
```

### 时间段分组

```sql
-- 按时段统计订单
SELECT 
    CASE 
        WHEN strftime('%H', order_time) BETWEEN '09' AND '12' THEN 'morning'
        WHEN strftime('%H', order_time) BETWEEN '13' AND '18' THEN 'afternoon'
        ELSE 'evening'
    END as time_period,
    COUNT(*) as order_count
FROM orders
GROUP BY time_period;
```

## 性能优化

### 索引优化

1. **创建合适的索引**
   ```sql
   -- 为分组列创建索引
   CREATE INDEX idx_category ON products(category);
   CREATE INDEX idx_order_date ON orders(order_date);
   ```

2. **复合索引**
   ```sql
   -- 为多列分组创建复合索引
   CREATE INDEX idx_cat_brand ON products(category, brand);
   ```

### 查询优化

1. **减少分组前数据量**
   ```sql
   -- 使用WHERE子句过滤
   SELECT category, COUNT(*)
   FROM products
   WHERE price > 100
   GROUP BY category;
   ```

2. **避免不必要的分组**
   ```sql
   -- 使用DISTINCT替代单列分组
   SELECT DISTINCT category
   FROM products;
   ```

## 最佳实践

### 代码规范

1. **清晰的格式化**
   ```sql
   SELECT 
       category,
       COUNT(*) as count,
       AVG(price) as avg_price
   FROM products
   WHERE price > 0
   GROUP BY category
   HAVING count > 5
   ORDER BY avg_price DESC;
   ```

2. **使用别名**
   ```sql
   SELECT 
       p.category,
       COUNT(DISTINCT o.customer_id) as customer_count
   FROM products p
   JOIN orders o ON p.id = o.product_id
   GROUP BY p.category;
   ```

### 注意事项

1. **选择合适的分组粒度**
   - 避免过细的分组
   - 考虑数据分布

2. **正确使用聚合函数**
   - COUNT(*)与COUNT(column)的区别
   - 注意NULL值的处理

## 总结

分组查询要点：

- 正确使用GROUP BY
- 合理使用HAVING
- 注意性能优化
- 选择合适的聚合函数

建议：

- 创建必要的索引
- 控制分组粒度
- 注意代码可读性
- 优化查询性能