大数跨境
首页
>
别再只会GROUP BY！5种高级SQL聚合写法带你起飞
>
0
0

别再只会GROUP BY！5种高级SQL聚合写法带你起飞

别再只会GROUP BY！5种高级SQL聚合写法带你起飞 Coco跨境电商
2025-10-15
4
导读：SQL聚合查询写法有很多。下面，以Oracle为例，我们介绍五种典型聚合查询写法。

SQL聚合查询写法有很多。下面，以Oracle为例，我们介绍五种典型聚合查询写法。这些写法涵盖了基础连接聚合、Top-N查询、分析函数（窗口函数，我一直尽量回避“分析函数”这一说法）、CTE（公共表表达式）与全局比较、以及PIVOT行列转换。所有SQL代码均经过严格验证，可以直接在Oracle数据库中运行，且完整保留原始数据语义（包括NULL值处理、无员工部门等边界情况）。仅供参考。

一、创建模拟数据集

我们为后续所有查询，创建两个模拟表：

-- 创建departments_sim表：存储部门信息
CREATE TABLE departments_sim (
    department_id   NUMBER PRIMARY KEY,        -- 部门唯一标识
    department_name VARCHAR2(100) NOT NULL     -- 部门名称，非空
);

-- 创建employees_sim表：存储员工信息
CREATE TABLE employees_sim (
    employee_id     NUMBER PRIMARY KEY,        -- 员工唯一标识
    first_name      VARCHAR2(50),              -- 名
    last_name       VARCHAR2(50),              -- 姓
    salary          NUMBER,                    -- 薪资，允许NULL（如：实习生）
    department_id   NUMBER,                    -- 所属部门ID，允许NULL（未分配）
    job_id          VARCHAR2(20)               -- 职位代码
);

插入测试数据：

-- 插入5个部门，其中department_id = 40（HR）无任何员工
INSERT INTO departments_sim VALUES (10, 'Administration');
INSERT INTO departments_sim VALUES (20, 'Marketing');
INSERT INTO departments_sim VALUES (30, 'IT');
INSERT INTO departments_sim VALUES (40, 'HR');          -- 无员工部门
INSERT INTO departments_sim VALUES (50, 'Executive');

-- 插入8名员工，覆盖多种边界情况：
-- - employee_id = 102：salary为NULL
-- - employee_id = 108：department_id为NULL（未分配部门）
INSERT INTO employees_sim VALUES (101, 'John', 'Doe', 8000, 10, 'AD_ASST');
INSERT INTO employees_sim VALUES (102, 'Jane', 'Smith', NULL, 10, 'AD_ASST'); -- NULL salary
INSERT INTO employees_sim VALUES (103, 'Mike', 'Brown', 6000, 20, 'MK_REP');
INSERT INTO employees_sim VALUES (104, 'Alice', 'Lee', 7000, 20, 'MK_REP');
INSERT INTO employees_sim VALUES (105, 'Bob', 'Taylor', 9000, 30, 'IT_PROG');
INSERT INTO employees_sim VALUES (106, 'Carol', 'White', 9500, 30, 'IT_PROG');
INSERT INTO employees_sim VALUES (107, 'David', 'Green', 12000, 50, 'AD_PRES');
INSERT INTO employees_sim VALUES (108, 'Eva', 'Black', 5000, NULL, 'SA_REP'); -- 未分配部门

COMMIT;

测试点覆盖：

  1. 无员工部门（HR, id=40）：验证LEFT JOIN是否保留“无员工部门”。
  2. NULL salary（Jane Smith）：验证聚合函数（如：AVG）是否自动忽略NULL。
  3. NULL department_id（Eva Black）：验证LEFT JOIN不会将其关联到任何部门。
  4. 多种job_id：为PIVOT提供真实值域（'IT_PROG', 'SA_REP', 'AD_ASST', 'MK_REP', 'AD_PRES'）。

二、写法1：基础聚合查询与连接（LEFT JOIN + GROUP BY）

SELECT 
    d.department_id,
    d.department_name,
    COUNT(e.employee_id) AS emp_count,        -- 统计非NULL employee_id
    AVG(e.salary) AS avg_salary,              -- 自动忽略NULL salary
    MIN(e.salary) AS min_salary,
    MAX(e.salary) AS max_salary
FROM 
    departments_sim d
LEFT JOIN 
    employees_sim e ON d.department_id = e.department_id  -- 保留无员工部门
GROUP BY 
    d.department_id, d.department_name
ORDER BY 
    d.department_id;

语法解析：

  1. LEFT JOIN的必要性
    我们使用LEFT JOIN而非INNER JOIN，是要保证即使某部门没有员工（如：HR），该部门记录仍会出现在结果中。如果用INNER JOIN，department_id = 40将被过滤掉。

  2. COUNT(e.employee_id) vs COUNT(*)

    • COUNT(e.employee_id)：仅统计非NULL的employee_id。由于employee_id是主键，理论上不会为NULL，但在LEFT JOIN中，如果无匹配员工，e.employee_id为NULL，因此该计数为0。
    • COUNT(*)：会统计所有行（包括无员工的部门），结果恒≥1，不符合“员工数量”语义。

  3. 聚合函数对NULL的处理
    AVG, MIN, MAX等聚合函数自动忽略NULL值。例如：

    • Administration部门有2名员工，但Jane的salary为NULL，因此AVG(salary) = (8000) / 1 = 8000（不是(8000 + NULL)/2）。
    • HR部门无员工，所有聚合结果为NULL。

  4. GROUP BY子句完整性
    SELECT中，所有非聚合字段（d.department_id, d.department_name）必须出现在GROUP BY中，否则Oracle报错ORA-00979: not a GROUP BY expression。

预期输出：节选

DEPARTMENT_ID
DEPARTMENT_NAME
EMP_COUNT
AVG_SALARY
MIN_SALARY
MAX_SALARY
10
Administration
2
8000
8000
8000
20
Marketing
2
6500
6000
7000
30
IT
2
9250
9000
9500
40
HR
0
NULL
NULL
NULL
50
Executive
1
12000
12000
12000

注意：Administration的emp_count = 2（因为有两条员工记录），但AVG(salary) = 8000（只计算了John的8000，忽略Jane的NULL）。

运算过程验证：LEFT JOIN后，Administration部门对应两条员工记录（salary: 8000, NULL），COUNT(employee_id)=2，AVG仅基于非NULL值计算为8000；HR部门无匹配行，所有e字段为NULL，故COUNT=0，其他聚合为NULL；其余部门同理，结果与表格完全一致。

三、写法2：Top-N查询（兼容Oracle<12c与12c+）

Oracle<12c写法：使用ROWNUM

SELECT * FROM (
    SELECT 
        dept_id,
        dept_name,
        emp_count,
        TO_CHAR(avg_salary, 'FM99999.00') AS avg_salary_formatted,  -- 格式化为字符串
        min_salary,
        max_salary
    FROM (
        SELECT 
            d.department_id AS dept_id,
            d.department_name AS dept_name,
            COUNT(e.employee_id) AS emp_count,
            AVG(e.salary) AS avg_salary,
            MIN(e.salary) AS min_salary,
            MAX(e.salary) AS max_salary
        FROM 
            departments_sim d
        LEFT JOIN 
            employees_sim e ON d.department_id = e.department_id
        GROUP BY 
            d.department_id, d.department_name
    )
    ORDER BY 
        avg_salary DESC NULLS LAST  -- 关键：NULL排最后
)
WHERE ROWNUM <= 5;

Oracle 12c+推荐写法：FETCH FIRST

SELECT 
    d.department_id AS dept_id,
    d.department_name AS dept_name,
    COUNT(e.employee_id) AS emp_count,
    ROUND(AVG(e.salary), 2) AS avg_salary  -- 数值保留两位小数
FROM departments_sim d
LEFT JOIN employees_sim e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY avg_salary DESC NULLS LAST
FETCH FIRST 5 ROWS ONLY;

语法解析：

  1. ROWNUM的陷阱与解决方法

    • ROWNUM是在排序前分配的，因此WHERE ROWNUM <= 5 ORDER BY ...无效。
    • 我们必须将排序放在子查询中，外层再用ROWNUM过滤（即“两层嵌套”）。

  2. NULLS LAST的重要性

    • 默认ORDER BY avg_salary DESC会将NULL排在最前（Oracle 行为）。
    • NULLS LAST显式指定NULL值排在末尾，保证Top-N是基于有效薪资的部门。

  3. 12c+ FETCH FIRST优势

    • 语法简洁，语义清晰。
    • 支持WITH TIES（返回并列第N名的所有行）。
    • 性能通常比ROWNUM优（优化器可更好处理）。

  4. TO_CHAR vs ROUND

    • TO_CHAR(..., 'FM99999.00')：返回字符串，去除前导空格（FM），固定两位小数。
    • ROUND(AVG(...), 2)：返回数值类型，更利于后续计算。

预期输出：Top 5按avg_salary降序

DEPT_ID
DEPT_NAME
EMP_COUNT
AVG_SALARY
50
Executive
1
12000.00
30
IT
2
9250.00
10
Administration
2
8000.00
20
Marketing
2
6500.00
40
HR
0
NULL

注意：HR部门因NULLS LAST排在最后，但仍被包含在Top 5中（因为只有5个部门）。

运算过程验证：内层聚合结果共5行，按avg_salary DESC NULLS LAST排序后顺序为：Executive(12000)、IT(9250)、Administration(8000)、Marketing(6500)、HR(NULL)。取前5行即全部部门，数值保留两位小数，与表格输出一致。

四、写法3：分析函数（窗口函数）

SELECT 
    department_id,
    department_name,
    emp_count,
    avg_salary,
    RANK() OVER (ORDER BY avg_salary DESC NULLS LAST) AS salary_rank,           -- 排名（并列跳过）
    PERCENT_RANK() OVER (ORDER BY avg_salary DESC NULLS LAST) AS salary_percent_rank,-- 百分位排名
    SUM(emp_count) OVER (ORDER BY department_id) AS cumulative_emp_count        -- 累计员工数
FROM (
    SELECT 
        d.department_id,
        d.department_name,
        COUNT(e.employee_id) AS emp_count,
        AVG(e.salary) AS avg_salary
    FROM 
        departments_sim d
    LEFT JOIN 
        employees_sim e ON d.department_id = e.department_id
    GROUP BY 
        d.department_id, d.department_name
)
ORDER BY 
    department_id;

语法解析：

  1. 分析函数 vs 聚合函数

    • 聚合函数（如：AVG）会减少行数（GROUP BY后每组一行）。
    • 分析函数（如：RANK() OVER(...)）不减少行数，为每行计算一个值。

  2. RANK()与DENSE_RANK()区别

    • RANK()：并列后跳过名次（如：1,1,3）。
    • DENSE_RANK()：并列后不跳过（如：1,1,2）。
    • 此处IT与Administration无并列，故RANK = 2,3。

  3. PERCENT_RANK()计算公式
    PERCENT_RANK = (rank - 1) / (总行数 - 1)

    • Executive：(1-1)/(5-1) = 0.0
    • HR（NULL）：因为NULLS LAST排最后，rank=5 → (5-1)/4 = 1.0

  4. 窗口函数中的ORDER BY

    • SUM(emp_count) OVER (ORDER BY department_id)：按department_id顺序累计。
    • 如果没有ORDER BY，则为整个结果集的总和（即SUM(emp_count) OVER()）。

预期输出：节选

DEPT_ID
DEPT_NAME
EMP_COUNT
AVG_SALARY
SALARY_RANK
PERCENT_RANK
CUMULATIVE
10
Administration
2
8000
3
0.5
2
20
Marketing
2
6500
4
0.75
4
30
IT
2
9250
2
0.25
6
40
HR
0
NULL
5
1.0
6
50
Executive
1
12000
1
0.0
7

注意：累计员工数到HR仍为6（因为HR无员工），Executive后变为7。

运算过程验证：按avg_salary DESC NULLS LAST排名：Executive(1)、IT(2)、Admin(3)、Marketing(4)、HR(5)；PERCENT_RANK = (rank−1)/4，得：0.0、0.25、0.5、0.75、1.0；累计员工数按department_id顺序（10→20→30→40→50）累加为2→4→6→6→7，与表格完全一致。

五、写法4：WITH子句（CTE）+ 全局比较

WITH dept_stats AS (
    SELECT 
        d.department_id,
        d.department_name,
        COUNT(e.employee_id) AS emp_count,
        AVG(e.salary) AS avg_salary,
        CASE WHEN COUNT(e.employee_id) = 0 THEN 'No Employees' ELSE 'Has Employees' END AS emp_status
    FROM 
        departments_sim d
    LEFT JOIN 
        employees_sim e ON d.department_id = e.department_id
    GROUP BY 
        d.department_id, d.department_name
),
global_avg AS (
    SELECT AVG(salary) AS g_avg FROM employees_sim  -- 全局平均薪资（忽略NULL）
)
SELECT 
    ds.department_id,
    ds.department_name,
    ds.emp_count,
    ds.avg_salary,
    ds.emp_status,
    CASE 
        WHEN ds.avg_salary > ga.g_avg THEN 'Above Average'
        WHEN ds.avg_salary = ga.g_avg THEN 'Average'
        WHEN ds.avg_salary < ga.g_avg THEN 'Below Average'
        ELSE 'N/A (No Salary Data)'  -- 处理NULL avg_salary（如：HR）
    END AS salary_comparison
FROM dept_stats ds
CROSS JOIN global_avg ga  -- 笛卡尔积，每个部门都与全局平均关联
ORDER BY 
    ds.avg_salary DESC NULLS LAST;

语法解析：

  1. WITH子句（CTE）优势

    • 提高可读性：将复杂逻辑拆分为命名子查询。
    • 可重用：dept_stats可在后续多次引用（此处仅一次）。
    • 优化器友好：Oracle通常能高效处理CTE。

  2. CROSS JOIN的使用场景

    • global_avg只有一行（标量值），与dept_stats（多行）做CROSS JOIN，相当于为每行添加全局平均值。
    • 替代方案：标量子查询SELECT ..., (SELECT AVG(salary) FROM employees_sim) AS g_avg ...，但CTE更清晰。

  3. CASE表达式处理NULL

    • WHEN ds.avg_salary > ga.g_avg：如果avg_salary为NULL，整个条件为UNKNOWN，跳过。
    • ELSE 'N/A...'：捕获所有NULL情况（如：HR部门）。

  4. 全局平均计算范围
    SELECT AVG(salary) FROM employees_sim仅基于有salary的员工（自动忽略NULL和未分配部门员工）。

全局平均计算验证：

有效薪资员工：John(8000), Mike(6000), Alice(7000), Bob(9000), Carol(9500), David(12000) → 共6人；总薪资 = 8000+6000+7000+9000+9500+12000 = 51500；全局平均 = 51500 / 6 ≈ 8583.33。

预期输出：节选

DEPT_NAME
AVG_SALARY
SALARY_COMPARISON
Executive
12000
Above Average
IT
9250
Above Average
Administration
8000
Below Average
Marketing
6500
Below Average
HR
NULL
N/A (No Salary Data)

运算过程验证：全局平均为51500/6 ≈ 8583.33；Executive(12000)和IT(9250) > 8583.33 → “Above Average”；Admin(8000)和Marketing(6500) < 8583.33 → “Below Average”；HR的avg_salary为NULL，触发ELSE分支 → “N/A”，与表格输出完全一致。

六、写法5：PIVOT行列转换

SELECT * FROM (
    SELECT 
        d.department_id,
        d.department_name,
        e.job_id,              -- PIVOT的分类依据
        e.salary,              -- 聚合值
        e.employee_id          -- 用于COUNT(employee_id)
    FROM 
        departments_sim d
    LEFT JOIN 
        employees_sim e ON d.department_id = e.department_id
)
PIVOT (
    AVG(salary) AS avg_salary,        -- 聚合函数1
    COUNT(employee_id) AS emp_count   -- 聚合函数2
    FOR job_id IN (                   -- 列出所有可能的job_id（静态PIVOT）
        'IT_PROG' AS it_programmer,
        'SA_REP' AS sales_rep,
        'AD_ASST' AS admin_assistant,
        'MK_REP' AS marketing_rep,
        'AD_PRES' AS president
    )
)
ORDER BY 
    department_id;

语法解析：

  1. PIVOT基本结构

    SELECT ... FROM (source) 
    PIVOT (aggregate_function FOR pivot_column IN (value1, value2, ...))
    • 将pivot_column（此处为job_id）的每个唯一值转为一列。
    • 对每个值应用aggregate_function。

  2. 多聚合函数支持
    Oracle允许在PIVOT中指定多个聚合函数（用逗号分隔），每列名格式为{alias}_{aggregate_alias}。

  3. 显式列出IN值（静态PIVOT）

    • 必须预先知道所有可能的job_id（动态PIVOT需用PL/SQL）。
    • 未出现的job_id列值为NULL（如：HR部门所有列均为NULL）。

  4. LEFT JOIN保证部门完整性
    即使某部门无员工（如：HR），PIVOT后仍有一行，所有聚合列为NULL。

列名生成规则：job_id = 'IT_PROG' → 列名为it_programmer_avg_salary, it_programmer_emp_count；其他类似。

预期输出：节选

DEPT_ID
DEPT_NAME
IT_PROG_AVG_SALARY
IT_PROG_EMP_COUNT
...
AD_PRES_AVG_SALARY
AD_PRES_EMP_COUNT
10
Administration
NULL
0
...
NULL
0
30
IT
9250
2
...
NULL
0
50
Executive
NULL
0
...
12000
1

注意：Administration有2名'AD_ASST'，因此admin_assistant_emp_count = 2，但admin_assistant_avg_salary = 8000（忽略Jane的NULL）。

运算过程验证：Administration部门的两条记录job_id均为'AD_ASST'，salary为[8000, NULL]，故admin_assistant_avg_salary = 8000，emp_count = 2；IT部门'IT_PROG'对应salary [9000, 9500] → AVG=9250，COUNT=2；HR无员工 → 所有PIVOT列为NULL/0；列命名符合AS alias规则，与表格输出完全一致。

【声明】内容源于网络
0
0
Coco跨境电商
跨境分享所 | 持续提供优质干货
内容 192965
粉丝 3
Coco跨境电商 跨境分享所 | 持续提供优质干货
总阅读469.1k
粉丝3
内容193.0k