SQL高级查询技巧实战：10个提升数据分析与报表效率的核心方法

随着数据量持续增长，对熟练掌握SQL的数据专业人员的需求也在不断提升。掌握高级查询技巧能够显著提升复杂业务逻辑的处理效率与代码的可维护性。本文将深入探讨10个实用的高级SQL技巧，帮助你应对更复杂的数据分析场景。

1. 利用公共表表达式（CTEs）模块化复杂查询

当查询中嵌套过多子查询时，代码会变得难以阅读和维护。公共表表达式（CTEs）通过创建临时命名结果集，能够像拼装乐高一样将复杂查询分解为多个逻辑清晰的模块。

例如，下面这个包含多重子查询的语句：

SELECT
    name,
    salary
FROM
    People
WHERE
    NAME IN ( SELECT DISTINCT NAME FROM population WHERE country = "Canada" AND city = "Toronto" )
    AND salary >= (
        SELECT
            AVG( salary )
        FROM
            salaries
        WHERE
            gender = "Female"
    )

可以借助CTE进行重构，使其逻辑一目了然：

WITH toronto_ppl AS (
    SELECT DISTINCT name
    FROM population
    WHERE country = "Canada"
        AND city = "Toronto"
),
avg_female_salary AS (
    SELECT AVG(salary) AS avgSalary
    FROM salaries
    WHERE gender = "Female"
)
SELECT name,
    salary
FROM People
WHERE name IN (SELECT name FROM toronto_ppl)
    AND salary >= (SELECT avgSalary FROM avg_female_salary)

通过定义 toronto_ppl 和 avg_female_salary 这两个CTE，查询的意图变得非常明确：筛选出多伦多地区且薪资高于女性平均薪资的员工。CTE不仅提升了代码可读性，也是实现递归查询等高级数据库操作的基础。

2. 递归CTEs处理层次结构数据

递归CTE是一种特殊的CTE，它能够引用自身，非常适用于处理具有层级关系的数据，例如组织架构、文件系统目录或网页链接关系。

一个典型的递归CTE包含三个部分：

• 锚定成员：返回初始结果集的基础查询。
• 递归成员：引用CTE自身的递归查询，通常与锚定成员通过 UNION ALL 连接。
• 终止条件：确保递归能够结束。

以下示例展示了如何查询每个员工及其所有上级管理链：

WITH RECURSIVE org_structure AS (
    -- 锚定成员：找出顶级管理者（没有上级）
    SELECT id,
        manager_id,
        name
    FROM staff_members
    WHERE manager_id IS NULL
    UNION ALL
    -- 递归成员：逐级向下查找下属
    SELECT sm.id,
        sm.manager_id,
        sm.name
    FROM staff_members sm
        INNER JOIN org_structure os ON os.id = sm.manager_id
)
SELECT *
FROM org_structure;

3. 使用临时函数封装复杂逻辑

临时函数允许你将重复或复杂的逻辑块封装起来，类似于编程中的函数，有助于实现代码的复用与清洁。

例如，根据员工在职年限划分职级的CASE WHEN语句：

SELECT name,
    CASE
        WHEN tenure < 1 THEN 'analyst'
        WHEN tenure BETWEEN 1 AND 3 THEN 'associate'
        WHEN tenure BETWEEN 3 AND 5 THEN 'senior'
        WHEN tenure > 5 THEN 'vp'
        ELSE 'n/a'
    END AS seniority
FROM employees

可以封装成一个临时函数：

CREATE TEMPORARY FUNCTION get_seniority(tenure INT64) RETURNS VARCHAR(20) AS (
    CASE
        WHEN tenure < 1 THEN 'analyst'
        WHEN tenure BETWEEN 1 AND 3 THEN 'associate'
        WHEN tenure BETWEEN 3 AND 5 THEN 'senior'
        WHEN tenure > 5 THEN 'vp'
        ELSE 'n/a'
    END
);

SELECT name,
    get_seniority(tenure) AS seniority
FROM employees;

这样，职级判断逻辑被独立出来，主查询更加简洁，并且 get_seniority 函数可以在其他查询中被重复使用。

4. 运用CASE WHEN进行数据透视（行转列）

CASE WHEN 除了用于条件判断，还能巧妙实现数据透视（PIVOT），将行数据转换为列展示，这在制作交叉报表时非常有用。

需求：将月度收入表重构为每个月份作为独立列的形式。
初始表：

+------+---------+-------+
| id   | revenue | month |
+------+---------+-------+
| 1    | 8000    | Jan   |
| 2    | 9000    | Jan   |
| 3    | 10000   | Feb   |
| 1    | 7000    | Feb   |
| 1    | 6000    | Mar   |
+------+---------+-------+

目标结果表：

+------+-------------+-------------+-------------+-----+-----------+
| id   | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
+------+-------------+-------------+-------------+-----+-----------+
| 1    | 8000        | 7000        | 6000        | ... | null        |
| 2    | 9000        | null        | null        | ... | null        |
| 3    | null        | 10000       | null        | ... | null        |
+------+-------------+-------------+-------------+-----+-----------+

实现SQL：

SELECT id,
    SUM(CASE WHEN month = 'Jan' THEN revenue ELSE NULL END) AS Jan_Revenue,
    SUM(CASE WHEN month = 'Feb' THEN revenue ELSE NULL END) AS Feb_Revenue,
    SUM(CASE WHEN month = 'Mar' THEN revenue ELSE NULL END) AS Mar_Revenue,
    -- ... 重复至12月
FROM monthly_revenue
GROUP BY id;

这种方法能有效应对需要横向展示多维度数据的复杂业务逻辑报表需求。

5. EXCEPT 与 NOT IN 的细微差别

两者都用于查找两个结果集之间的差异，但存在重要区别：

• EXCEPT / MINUS：返回在第一个查询结果中但不在第二个查询结果中的去重后的行。它比较的是所有列的组合。
• NOT IN：用于子查询，根据单个列的值进行过滤。它不会自动去重，且如果子查询返回 NULL，则整个 NOT IN 条件可能返回空结果。

6. 自连接（Self Join）解决关联比较

自连接是指表与自身进行连接，常用于处理同一表内数据间的比较关系。

示例：从员工表中找出薪资高于其直属经理的员工。
员工表：

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+

查询语句：

SELECT
    a.Name AS Employee
FROM
    Employee AS a
    JOIN Employee AS b ON a.ManagerId = b.Id
WHERE
    a.Salary > b.Salary;

这里，表 Employee 被分别别名为 a（员工视角）和 b（经理视角），通过连接条件 a.ManagerId = b.Id 建立关联，从而进行跨行比较。

7. RANK, DENSE_RANK 与 ROW_NUMBER 的区别

这三个窗口函数都用于排名，但处理并列排名的方式不同：

• ROW_NUMBER()：为每一行分配一个唯一的连续序号，即使值相同，序号也不同（顺序不确定）。
• RANK()：排名时，相同值获得相同排名，但后续排名会出现“跳跃”。例如，有两个第1名，则下一个是第3名。
• DENSE_RANK()：排名时，相同值获得相同排名，但后续排名是连续的。例如，有两个第1名，则下一个是第2名。

示例查询与结果：

SELECT Name,
    GPA,
    ROW_NUMBER() OVER (ORDER BY GPA DESC) AS ‘row_number‘,
    RANK() OVER (ORDER BY GPA DESC) AS ‘rank‘,
    DENSE_RANK() OVER (ORDER BY GPA DESC) AS ‘dense_rank‘
FROM student_grades;

8. 使用 LAG/LEAD 计算差值（Delta）

LAG() 和 LEAD() 窗口函数可以访问当前行之前（LAG）或之后（LEAD）指定偏移量的行的值，非常适合计算周期环比或同期对比。

• 计算月环比（本月 vs 上月）：

  SELECT month,
  sales,
  sales - LAG(sales, 1) OVER (ORDER BY month) AS month_over_month_growth
  FROM monthly_sales;

• 计算年同比（本月 vs 去年同月）：

  SELECT month,
  sales,
  sales - LAG(sales, 12) OVER (ORDER BY month) AS year_over_year_growth
  FROM monthly_sales;

9. 计算累积总和（Running Total）

结合 SUM() 与 OVER 子句，可以轻松计算累积值，常用于观察指标随时间的变化趋势。

SELECT Month,
    Revenue,
    SUM(Revenue) OVER (ORDER BY Month) AS Cumulative_Revenue
FROM monthly_revenue;

10. 灵活的日期时间处理

日期时间是SQL查询中的常客，掌握相关函数至关重要。

• EXTRACT(field FROM date)：提取日期特定部分（年、月、日等）。
• DATE_ADD(date, INTERVAL expr unit), DATE_SUB：日期加减。
• DATEDIFF(date1, date2)：计算两个日期之间的天数差。
• DATE_TRUNC(unit, date)：将日期截断到指定精度（如月初）。

示例：查找温度比前一天高的日期。
天气表：

+---------+------------------+------------------+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+---------+------------------+------------------+
|       1 |       2015-01-01 |               10 |
|       2 |       2015-01-02 |               25 |
|       3 |       2015-01-03 |               20 |
|       4 |       2015-01-04 |               30 |
+---------+------------------+------------------+

查询语句：

SELECT
    a.Id
FROM
    Weather a
    CROSS JOIN Weather b
WHERE
    DATEDIFF(a.RecordDate, b.RecordDate) = 1
    AND a.Temperature > b.Temperature;

熟练掌握以上10个高级SQL技巧，能让你在面对复杂数据查询、报表生成和数据分析任务时更加游刃有余，极大提升数据处理效率。