找回密码
立即注册
搜索
热搜: Java Python Linux Go
云栈社区»论坛 › 技术文档「 Note & Doc 」 › SQL高级查询技巧：10个实战方法优化数据分析效率 ...
发回帖 发新帖
马卖麻皮

2590

积分

1

好友

359

主题

SQL高级查询技巧：10个实战方法优化数据分析效率

发表于 昨天 10:52 | 查看: 5| 回复: 0

在数据处理中，SQL 查询的复杂性常常带来挑战。嵌套子查询难以理解，计算环比或处理层级数据时容易出错。本文将介绍 10 个实用的高级 SQL 技巧，涵盖从简化查询到处理特殊场景的方法，每个技巧都配有具体示例，帮助您提升查询效率和代码可读性。

1. 公共表表达式（CTEs）

CTEs 通过 WITH 子句创建临时命名的结果集，将复杂查询模块化，提升可读性和复用性。它类似于临时视图，仅存在于查询生命周期内。

代码示例：

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 p.name, p.salary
FROM People p
WHERE p.name IN (SELECT name FROM toronto_ppl)
  AND p.salary >= (SELECT avgSalary FROM avg_female_salary);

说明：

  • 使用表别名 p 使列引用更清晰。
  • 子查询直接引用 CTE 名称，逻辑更直观。
  • DISTINCT 确保数据唯一性。

模拟示例：

# population 表
| name    | country | city    |
|---------|---------|---------|
| Alice   | Canada  | Toronto |
| Bob     | Canada  | Toronto |
| Charlie | USA     | New York|

# salaries 表
| salary | gender |
|--------|--------|
| 50000  | Female |
| 60000  | Female |
| 70000  | Male   |

# People 表
| name    | salary |
|---------|--------|
| Alice   | 60000  |
| Bob     | 50000  |
| Charlie | 80000  |

# 输出结果
| name  | salary |
|-------|--------|
| Alice | 60000  |  -- 满足多伦多居民且薪资>=女性平均薪资(55000)

关键点：CTEs 将多层嵌套子查询拆解为独立模块（如 toronto_ppl 和 avg_female_salary），提升可维护性，且不占用存储空间。

2. 递归 CTEs

递归 CTE 用于处理分层数据，例如组织结构。它包含锚点查询、递归成员和终止条件三部分。

代码示例：

WITH RECURSIVE org_structure AS (
    -- 锚点：顶层管理者
    SELECT id, manager_id, 1 AS level
    FROM staff_members
    WHERE manager_id IS NULL
    UNION ALL
    -- 递归：逐级向下查找
    SELECT sm.id, sm.manager_id, os.level + 1
    FROM staff_members sm
    INNER JOIN org_structure os ON os.id = sm.manager_id
)
SELECT * FROM org_structure;

说明：

  • 使用 WITH RECURSIVE 语法支持无限递归，直到无新记录产生。
  • level 列动态记录层级深度，从 1 开始递增。
  • 需确保表中无循环引用，否则可能导致无限递归。

模拟示例：

# staff_members 表
| id | manager_id |
|----|------------|
| 1  | NULL       |  -- CEO
| 2  | 1          |  -- 直属CEO
| 3  | 1          |  -- 直属CEO
| 4  | 2          |  -- 直属id=2

# 输出结果
| id | manager_id | level |
|----|------------|-------|
| 1  | NULL       | 1     |  -- 层级1
| 2  | 1          | 2     |  -- 层级2
| 3  | 1          | 2     |  -- 层级2
| 4  | 2          | 3     |  -- 层级3

关键点：UNION ALL 合并迭代结果，适用于无限深度的树状结构查询。

3. 临时函数

临时函数封装复杂逻辑，提升代码复用性。在 BigQuery 等数据库中通过 CREATE TEMPORARY FUNCTION 定义。

代码示例：

CREATE TEMPORARY FUNCTION get_seniority(tenure INT64) 
RETURNS STRING 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;

说明：RETURNS STRING 显式声明返回类型，提高代码可读性和兼容性。

模拟示例：

# employees 表
| name  | tenure |
|-------|--------|
| Alice | 0      |
| Bob   | 2      |
| Carol | 4      |
| Dave  | 6      |

# 输出结果
| name  | seniority |
|-------|-----------|
| Alice | analyst   |
| Bob   | associate |
| Carol | senior    |
| Dave  | vp        |

关键点：函数仅当前会话有效，可简化 CASE WHEN 逻辑，并支持多参数。

4. 使用 CASE WHEN 枢转数据

行转列通过 CASE WHEN 配合聚合函数实现，将分类值转换为新列。

代码示例：

SELECT 
    id,
    SUM(CASE WHEN month = 'Jan' THEN revenue END) AS Jan_Revenue,
    SUM(CASE WHEN month = 'Feb' THEN revenue END) AS Feb_Revenue,
    SUM(CASE WHEN month = 'Mar' THEN revenue END) AS Mar_Revenue
FROM revenue_data
GROUP BY id;

说明：

  • CASE 表达式将纵向月度数据转换为横向列。
  • SUM() 函数对每个 ID 的特定月份收入求和，缺失值返回 NULL。

模拟示例：

# revenue_data 表
| 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 |
|----|-------------|-------------|-------------|
| 1  | 8000        | 7000        | 6000        |
| 2  | 9000        | NULL        | NULL        |
| 3  | NULL        | 10000       | NULL        |

关键点：SUM() 确保多行合并为单行，可用 COALESCE 处理 NULL 值。

5. EXCEPT vs NOT IN

两者用于比较数据集差异，但行为不同：EXCEPT 是集合运算并去重，NOT IN 是单列过滤且需处理 NULL 值。

代码示例：

-- EXCEPT 示例
SELECT name FROM employees 
EXCEPT 
SELECT name FROM managers;  -- 返回在employees但不在managers的名字（去重）

-- NOT IN 示例
SELECT name FROM employees
WHERE name NOT IN (SELECT name FROM managers); -- 可能受NULL值影响

说明：

  • EXCEPT 自动过滤 NULL 值并去重。
  • NOT IN 在子查询含 NULL 时可能返回空集，因为 X NOT IN (NULL, ...) 恒为 UNKNOWN。

模拟示例：

# employees 表       # managers 表
| name    |         | name    |
|---------|         |---------|
| Alice   |         | Bob     |
| Bob     |         | NULL    |
| Charlie |         

# EXCEPT 输出
| name    |
|---------|
| Alice   |
| Charlie |

# NOT IN 输出：无结果（因 managers 含 NULL）

关键点：使用 NOT IN 时，子查询应排除 NULL，或改用 NOT EXISTS。

6. 自联结

自联结将表与自身连接，用于比较同一表内的关联数据，例如员工与经理的薪资。

代码示例：

SELECT
    emp.name AS Employee,
    emp.salary AS Employee_Salary,
    mgr.salary AS Manager_Salary
FROM Employee emp
JOIN Employee mgr ON emp.manager_id = mgr.id
WHERE emp.salary > mgr.salary;

说明：

  • 通过别名区分员工（emp）和管理者（mgr）角色。
  • INNER JOIN 确保只返回有管理者的记录，如需包含无管理者员工，可使用 LEFT JOIN。

模拟示例：

# Employee 表
| id | name  | salary | manager_id |
|----|-------|--------|------------|
| 1  | Joe   | 70000  | 3          |
| 2  | Henry | 80000  | 4          |
| 3  | Sam   | 60000  | NULL       |
| 4  | Max   | 90000  | NULL       |

# 输出结果
| Employee | Employee_Salary | Manager_Salary |
|----------|-----------------|----------------|
| Joe      | 70000           | 60000          |  -- Joe薪资 > 经理Sam

关键点：自联结适用于发现数据异常，如员工薪资高于管理者。在 manager_id 和 id 列上创建索引可优化性能。

7. Rank vs Dense Rank vs Row Number

窗口函数为结果集分配序号，区别在于处理重复值的方式。

代码示例：

SELECT 
    name,
    GPA,
    ROW_NUMBER() OVER (ORDER BY GPA DESC) AS row_num,
    RANK() OVER (ORDER BY GPA DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY GPA DESC) AS dense_rank
FROM student_grades;

说明：

  • ROW_NUMBER() 生成唯一连续序号。
  • RANK() 相同值排名相同，后续序号跳空。
  • DENSE_RANK() 相同值排名相同，后续序号连续。

模拟示例：

# student_grades 表
| name  | GPA |
|-------|-----|
| Alice | 3.9 |
| Bob   | 3.9 |
| Carol | 3.7 |
| Dave  | 3.5 |

# 输出结果
| name  | GPA | row_num | rank | dense_rank |
|-------|-----|---------|------|------------|
| Alice | 3.9 | 1       | 1    | 1          |
| Bob   | 3.9 | 2       | 1    | 1          |
| Carol | 3.7 | 3       | 3    | 2          |  -- RANK() 跳过2
| Dave  | 3.5 | 4       | 4    | 3          |  -- DENSE_RANK() 连续

关键点：结合 PARTITION BY 可分组计算排名，适用于 Top N 查询。

8. 计算 Delta 值

LAG() 和 LEAD() 窗口函数访问前后行数据，用于计算环比或同比差异。

代码示例：

-- 计算月环比增长
SELECT 
    month,
    revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS month_over_month
FROM monthly_revenue;

-- 计算年同比增长
SELECT 
    month,
    revenue,
    revenue - LAG(revenue, 12) OVER (ORDER BY month) AS year_over_year
FROM monthly_revenue;

说明：

  • LAG(revenue, 1) 取上月收入，计算月环比。
  • LAG(revenue, 12) 取去年同月收入，计算年同比。
  • 首月或首年数据因无历史值返回 NULL。

模拟示例：

# monthly_revenue 表
| month   | revenue |
|---------|---------|
| 2023-01 | 100     |
| 2023-02 | 150     |
| 2024-01 | 120     |

# 月环比输出
| month   | revenue | month_over_month |
|---------|---------|------------------|
| 2023-01 | 100     | NULL             |
| 2023-02 | 150     | 50               |  -- 150 - 100
| 2024-01 | 120     | -30              |  -- 120 - 150 (需跨年)

# 年同比输出
| month   | revenue | year_over_year |
|---------|---------|----------------|
| 2023-01 | 100     | NULL           |
| 2024-01 | 120     | 20             |  -- 120 - 100

关键点：确保数据连续，缺失月份可能需补零处理。

9. 计算运行总数

累积统计通过 SUM() OVER (ORDER BY) 实现，动态计算运行总数。

代码示例：

SELECT 
    month,
    revenue,
    SUM(revenue) OVER (ORDER BY month) AS cumulative_revenue
FROM monthly_revenue;

说明：窗口函数按月份排序，逐行累加当前行及之前所有月份的收入。

模拟示例：

# monthly_revenue 表
| month | revenue |
|-------|---------|
| Jan   | 100     |
| Feb   | 150     |
| Mar   | 200     |

# 输出结果
| month | revenue | cumulative_revenue |
|-------|---------|---------------------|
| Jan   | 100     | 100                 |
| Feb   | 150     | 250                 |  -- 100+150
| Mar   | 200     | 450                 |  -- 100+150+200

关键点：结合 PARTITION BY 可分组累计，扩展应用包括计算移动平均值。

10. 日期时间操纵

日期函数处理时间逻辑，常用函数如 DATEDIFF、DATE_ADD 和 EXTRACT。

代码示例：

-- 查找温度高于前一天的日子
SELECT 
    a.id
FROM Weather a
JOIN Weather b 
    ON a.record_date = DATE_ADD(b.record_date, INTERVAL 1 DAY)
WHERE a.temperature > b.temperature;

说明：使用显式 JOIN 和日期函数关联相邻日期的数据。

模拟示例：

# Weather 表
| id | record_date | temperature |
|----|-------------|-------------|
| 1  | 2023-01-01  | 10          |
| 2  | 2023-01-02  | 25          |  -- 比前一天高
| 3  | 2023-01-03  | 20          |
| 4  | 2023-01-04  | 30          |  -- 比前一天高

# 输出结果
| id |
|----|
| 2  |
| 4  |

关键点：日期函数语法因数据库而异，例如 PostgreSQL 使用 b.record_date + INTERVAL '1 day'。索引 record_date 列可提升查询性能。

掌握这些 SQL 高级技巧，能将复杂查询模块化，提升数据分析效率。实践中，理解每个方法的核心用途并多练习，即可灵活应用于实际场景。更多数据库技术讨论，欢迎访问 云栈社区 进行交流。




上一篇：C++ STL循环优化：一行代码为何带来10倍性能提升？汇编视角解析
下一篇：15款红队常用C2框架对比：从Metasploit到Sliver的渗透测试工具盘点
SQL, 数据库, 数据分析, 查询优化, 数据处理

相关帖子
返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

手机版|小黑屋|网站地图|云栈社区 ( 苏ICP备2022046150号-2 )

GMT+8, 2026-1-16 00:34 , Processed in 1.260965 second(s), 44 queries , Gzip On.

Powered by Discuz! X3.5

© 2025-2025 云栈社区.

快速回复 返回顶部 返回列表