SQL分组数据方法总结

分组方法对比表

分组配合函数表

SQL分组数据详细示例

1. 基础 GROUP BY 分组

-- 按部门分组统计员工信息
SELECT 
    department AS department_name,           -- 分组字段
    COUNT(*) AS employee_count,              -- 统计每部门员工数
    AVG(salary) AS average_salary,           -- 计算每部门平均薪资
    MAX(salary) AS highest_salary,           -- 每部门最高薪资
    MIN(hire_date) AS earliest_hire_date     -- 每部门最早入职日期
FROM employees
GROUP BY department                         -- 按部门分组
ORDER BY employee_count DESC;               -- 按员工数降序排列

-- 多列分组统计销售数据
SELECT 
    region,                                  -- 地区分组
    product_category,                        -- 产品类别分组
    COUNT(*) AS order_count,                 -- 订单数量
    SUM(order_amount) AS total_sales,        -- 销售总额
    AVG(order_amount) AS avg_order_value     -- 平均订单价值
FROM sales_orders
GROUP BY region, product_category           -- 按地区和产品类别双重分组
HAVING SUM(order_amount) > 10000            -- 筛选销售总额大于10000的分组
ORDER BY region, total_sales DESC;

2. GROUP BY ROLLUP 层次化分组

-- 使用ROLLUP生成销售数据的层次化汇总
SELECT 
    region,                                  -- 地区字段
    department,                              -- 部门字段
    COUNT(*) AS sales_count,                 -- 销售笔数
    SUM(amount) AS total_revenue,            -- 总收入
    GROUPING(region) AS region_grouping,     -- 标识地区汇总行
    GROUPING(department) AS dept_grouping    -- 标识部门汇总行
FROM sales_data
GROUP BY ROLLUP(region, department)         -- 生成层次化汇总
ORDER BY 
    GROUPING(region),                       -- 先显示明细行
    GROUPING(department),                   -- 再显示小计行
    region, department;

3. GROUP BY CUBE 多维分组

-- 使用CUBE生成所有可能的分组组合
SELECT 
    region,                                  -- 地区维度
    product_line,                            -- 产品线维度
    customer_type,                           -- 客户类型维度
    COUNT(*) AS transaction_count,           -- 交易次数
    SUM(revenue) AS total_revenue,           -- 总收入
    AVG(revenue) AS avg_revenue              -- 平均收入
FROM sales_transactions
GROUP BY CUBE(region, product_line, customer_type)  -- 生成所有组合
ORDER BY 
    GROUPING(region), 
    GROUPING(product_line), 
    GROUPING(customer_type);

4. GROUP BY GROUPING SETS 自定义分组

-- 使用GROUPING SETS定义特定的分组组合
SELECT 
    region,                                  -- 地区字段
    department,                              -- 部门字段
    product_category,                        -- 产品类别字段
    COUNT(*) AS record_count,                -- 记录数
    SUM(sales_amount) AS total_sales         -- 销售总额
FROM sales_records
-- 自定义分组集合：总计、按地区、按部门、按类别
GROUP BY GROUPING SETS (
    (),                                     -- 总计（空分组）
    (region),                               -- 按地区分组
    (department),                           -- 按部门分组
    (product_category)                      -- 按产品类别分组
)
ORDER BY 
    GROUPING(region),
    GROUPING(department), 
    GROUPING(product_category);

5. 分组配合窗口函数

-- 分组内使用窗口函数进行排名分析
SELECT 
    department,                              -- 部门分组
    employee_name,                           -- 员工姓名
    salary,                                  -- 薪资
    -- 计算部门内薪资排名
    RANK() OVER (
        PARTITION BY department               -- 按部门分组
        ORDER BY salary DESC                  -- 按薪资降序排名
    ) AS salary_rank,
    -- 计算部门内薪资百分位
    PERCENT_RANK() OVER (
        PARTITION BY department
        ORDER BY salary
    ) AS salary_percentile,
    -- 计算部门累计薪资
    SUM(salary) OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS cumulative_salary
FROM employees
ORDER BY department, salary_rank;

使用建议

1. 选择合适的分组方法: 根据业务需求选择基础分组或高级分组功能
2. 性能优化: 大数据量分组时考虑创建适当的索引
3. 结果筛选: 使用 HAVING 子句对分组结果进行筛选
4. NULL值处理: 注意分组字段中NULL值的处理方式
5. 排序控制: 合理使用 ORDER BY 控制分组结果显示顺序