在数据处理与分析领域，高级SQL技巧是提升效率与准确性的关键。本文将结合参考资料，对高级SQL技巧进行系统的整理与解读，并通过实例展示其应用。
一、窗口函数
窗口函数是一种在SQL中执行复杂计算的强大工具，它们允许用户在一组行（称为窗口）上执行计算，而不会将这些行合并成单个结果行。窗口函数在处理排名、累计和运行总和等场景中非常有用。
基本语法：
<窗口函数> OVER([PARTITION BY <分区列>][ORDER BY <排序列>])
常见窗口函数：
- ROW_NUMBER()：为每一行分配一个唯一的序号。
- RANK()：为每一行分配一个序号，但序号间可能有跳跃（如有两行数据相同，则它们共享同一序号，下一行的序号将跳过）。
- DENSE_RANK()：为每一行分配一个序号，序号间无跳跃（即使两行数据相同，也会为下一行分配连续的序号）。
- LEAD() 和 LAG()：用于访问同一窗口中前一行或后一行的数据。
实例：
SELECT employee_id, department_id, salary,
ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY salary DESC) AS row_num,
RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS dense_rank,
LAG(salary, 1) OVER(PARTITION BY department_id ORDER BY salary DESC) AS previous_salary
FROM employees;
二、递归查询
递归查询允许用户在一个查询中多次引用同一个表，这在处理树形结构数据（如组织架构、目录结构）时非常有用。
基本语法：
WITH RECURSIVE cte_name AS (
初始查询
UNION ALL
递归查询
)
SELECT * FROM cte_name;
实例：
WITH RECURSIVE EmployeeCTE AS (
SELECT employee_id, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, ecte.level + 1
FROM employees e
INNER JOIN EmployeeCTE ecte ON e.manager_id = ecte.employee_id
)
SELECT employee_id, employee_name, level
FROM EmployeeCTE
ORDER BY level, employee_id;
三、公共表表达式（CTEs）
CTE是一种临时的结果集，只在单个查询的执行周期内有效。它有助于使复杂查询更加易读和易维护。
基本语法：
WITH cte_name AS (
查询语句
)
SELECT * FROM cte_name;
实例：
WITH SalesCTE AS (
SELECT employee_id, SUM(amount) AS total_sales
FROM sales
GROUP BY employee_id
)
SELECT employee_id, total_sales
FROM SalesCTE
WHERE total_sales > 10000;
四、子查询
子查询是嵌套在另一个查询中的查询，常用于筛选条件和数据过滤。
实例：
SELECT employee_id, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
五、集合操作
集合操作允许用户将两个或多个查询结果集进行合并或比较。常见的集合操作符包括UNION、INTERSECT和EXCEPT。
实例：
-- 合并两个查询结果集
SELECT name FROM customers
UNION
SELECT name FROM suppliers;
-- 找出两个查询结果集的交集
SELECT name FROM customers
INTERSECT
SELECT name FROM suppliers;
-- 找出只在第一个查询结果集中存在的记录
SELECT name FROM customers
EXCEPT
SELECT name FROM suppliers;
六、其他高级技巧
- 临时函数：在支持的数据库中（如PostgreSQL），可以定义临时函数来封装复杂的逻辑，增强代码重用性。
- 日期时间操作：包括日期加减、日期格式转换等。
- 索引优化：创建适当的索引可以显著提高查询性能。
- 自联结：一个表与自身进行联结，常用于处理相对数据。
- 分页：使用LIMIT子句进行分页查询，减少大偏移量
