十三、专业解析高级 SQL 必用函数

专业解析高级 SQL 必用函数

SQL（Structured Query Language）作为数据库操作的核心语言，其完备的函数库为数据处理与分析赋予了强大的工具支撑。在复杂的数据情境中，熟练掌握高级 SQL 函数能够显著提升数据处理效率以及查询的灵活性。本文将深入剖析一些高级 SQL 必用函数及其典型应用场景，助力读者深刻理解并运用这些函数来解决实际问题。

一、窗口函数（Window Functions）

窗口函数是 SQL 中用于对查询结果进行复杂分析的有力工具，它能够在不改变结果集行数的前提下，针对每一行数据执行聚合、排名等操作。

1. 基本语法

<窗口函数名>(<参数>) OVER (
[PARTITION BY <分区列>]
[ORDER BY <排序列> [ASC|DESC]]
[ROWS|RANGE BETWEEN <起始范围> AND <结束范围>]
)

• PARTITION BY：将结果集依据指定列进行分区，窗口函数在各个分区内独立进行计算。
• ORDER BY：确定窗口内数据的排序顺序，这对于排名函数等至关重要。
• ROWS|RANGE BETWEEN：进一步限定窗口的范围，例如 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 表示从分区起始行至当前行的窗口范围。

2. 常用窗口函数实例

• 排名函数（RANK、DENSE_RANK、ROW_NUMBER）

-- 示例数据
CREATE TABLE employees (
id INT,
name VARCHAR(50),
salary DECIMAL(10, 2),
department VARCHAR(50)
);
INSERT INTO employees VALUES
(1, 'Alice', 5000, 'HR'),
(2, 'Bob', 6000, 'IT'),
(3, 'Charlie', 5000, 'HR'),
(4, 'David', 7000, 'IT'),
(5, 'Eve', 4500, 'Finance');
-- 使用 RANK 函数按 salary 降序排名，在每个部门内进行排名
SELECT id, name, salary, department,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;
-- 使用 DENSE_RANK 函数实现相同功能，但排名是连续的
SELECT id, name, salary, department,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_salary_rank
FROM employees;
-- 使用 ROW_NUMBER 函数为每个部门内的员工分配唯一连续序号
SELECT id, name, salary, department,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

在此示例中，RANK 函数在遇到相同 salary 值时会分配相同排名，并跳过后续相应数量的排名；DENSE_RANK 函数则会分配连续排名；ROW_NUMBER 函数会为每一行分配一个唯一的连续序号，无论 salary 值是否相同。

• 聚合窗口函数（SUM、AVG、MAX、MIN 等）

-- 计算每个部门员工的累计薪资和平均薪资
SELECT id, name, salary, department,
SUM(salary) OVER (PARTITION BY department ORDER BY id) AS cumulative_salary,
AVG(salary) OVER (PARTITION BY department) AS average_salary
FROM employees;

这里，SUM 函数计算了每个部门内从第一行到当前行的薪资累计值，AVG 函数计算了每个部门的平均薪资，借助窗口函数可以在无需使用子查询的情况下轻松实现这些复杂的聚合计算。

二、公用表表达式（CTE - Common Table Expressions）

CTE 是一个临时的命名结果集，它能够在单个 SQL 查询中被多次引用，使得复杂查询的编写更加清晰且模块化。

1. 基本语法

WITH <cte_name> AS (
-- CTE 的查询定义
SELECT <列名>
FROM <表名>
WHERE <条件>
)
-- 主查询，可引用 CTE
SELECT <列名>
FROM <cte_name>
WHERE <条件>;

2. 实例：分层查询

假设我们拥有一个公司组织结构表 organization，包含 id（员工 ID）、name（员工姓名）、manager_id（上级经理 ID）和 level（层级）字段，我们可以运用 CTE 来实现分层查询，获取每个员工及其下属的信息：

-- 创建示例数据
CREATE TABLE organization (
id INT,
name VARCHAR(50),
manager_id INT,
level INT
);
INSERT INTO organization VALUES
(1, 'CEO', NULL, 1),
(2, 'VP1', 1, 2),
(3, 'VP2', 1, 2),
(4, 'Manager1', 2, 3),
(5, 'Manager2', 2, 3),
(6, 'Employee1', 4, 4),
(7, 'Employee2', 4, 4),
(8, 'Employee3', 5, 4);
-- 使用 CTE 进行分层查询
WITH RECURSIVE OrgHierarchy AS (
-- 递归的初始查询，选择顶层经理（CEO）
SELECT id, name, manager_id, level
FROM organization
WHERE manager_id IS NULL
UNION ALL
-- 递归查询，连接下属员工
SELECT o.id, o.name, o.manager_id, o.level
FROM organization o
JOIN OrgHierarchy oh ON o.manager_id = oh.id
)
SELECT *
FROM OrgHierarchy;

在这个例子中，RECURSIVE 关键字用于实现递归查询，通过不断连接下属员工，构建出完整的公司组织结构层次。CTE 使得这种复杂的分层查询逻辑更加清晰易懂，便于维护和扩展。

三、PIVOT 和 UNPIVOT 操作

PIVOT 和 UNPIVOT 用于在行列之间转换数据，使数据以不同的方式呈现，以便于数据分析和报表生成。

1. PIVOT 语法与实例

-- 示例数据：销售数据，包含日期、产品类别和销售额
CREATE TABLE sales (
sale_date DATE,
product_category VARCHAR(50),
sales_amount DECIMAL(10, 2)
);
INSERT INTO sales VALUES
('2024-01-01', 'Electronics', 1000),
('2024-01-01', 'Clothing', 500),
('2024-01-02', 'Electronics', 1200),
('2024-01-02', 'Clothing', 600),
('2024-01-03', 'Electronics', 800),
('2024-01-03', 'Clothing', 400);
-- 使用 PIVOT 将产品类别转换为列，显示每日各类产品销售额
SELECT *
FROM (
SELECT sale_date, product_category, sales_amount
FROM sales
) AS SourceTable
PIVOT (
SUM(sales_amount)
FOR product_category IN ([Electronics], [Clothing])
) AS PivotTable;

上述代码将原始的行数据（日期、产品类别、销售额）转换为以日期为行，产品类别为列的格式，便于查看每日各类产品的销售汇总情况。

2. UNPIVOT 语法与实例

-- 对上面 PIVOT 后的结果进行 UNPIVOT 操作，转回原始格式
SELECT sale_date, product_category, sales_amount
FROM (
SELECT sale_date, [Electronics], [Clothing]
FROM (
SELECT sale_date, product_category, sales_amount
FROM sales
) AS SourceTable
PIVOT (
SUM(sales_amount)
FOR product_category IN ([Electronics], [Clothing])
) AS PivotTable
) AS PivotResult
UNPIVOT (
sales_amount
FOR product_category IN ([Electronics], [Clothing])
) AS UnpivotTable;

UNPIVOT 操作是 PIVOT 的逆过程，将列数据重新转换为行数据，恢复到原始的销售数据格式，这种转换在数据处理中对于满足不同的分析和存储需求非常有用。

四、字符串函数（高级用法）

除了常见的字符串函数（如 CONCAT、SUBSTRING 等），SQL 还提供了一些高级字符串处理函数，用于复杂的文本分析和格式化。

1. REGEXP_REPLACE 函数

REGEXP_REPLACE 函数利用正则表达式进行字符串替换，功能强大且灵活。

-- 示例：清理电话号码格式
CREATE TABLE contacts (
id INT,
name VARCHAR(50),
phone_number VARCHAR(20)
);
INSERT INTO contacts VALUES
(1, 'John Doe', '(123) 456-7890'),
(2, 'Jane Smith', '123-456-7890'),
(3, 'Bob Johnson', '123.456.7890');
-- 使用 REGEXP_REPLACE 统一电话号码格式为 123-456-7890
SELECT id, name,
REGEXP_REPLACE(phone_number, '[\(\)\.]', '') AS cleaned_phone_number
FROM contacts;

这里，正则表达式 [\(\)\.] 匹配括号和点号，并将其从电话号码中删除，实现了电话号码格式的统一清理。

2. STRING_AGG 函数

STRING_AGG 函数用于将字符串列的值按照指定的分隔符合并为一个字符串。

-- 示例：将员工姓名按部门合并为字符串
SELECT department,
STRING_AGG(name, ', ') AS employees_list
FROM employees
GROUP BY department;

在这个例子中，STRING_AGG 函数将每个部门内的员工姓名用逗号和空格连接成一个字符串，方便查看每个部门的员工列表情况。

五、日期和时间函数（高级用法）

SQL 中的日期和时间函数在处理时间序列数据和执行基于时间的计算时非常重要，除了基本的日期格式化和加减运算，还有一些高级用法。

1. EXTRACT 函数

EXTRACT 函数用于从日期或时间值中提取特定的部分，如年、月、日、小时、分钟等。

-- 示例：统计每月的销售总额
SELECT EXTRACT(YEAR FROM sale_date) AS year,
EXTRACT(MONTH FROM sale_date) AS month,
SUM(sales_amount) AS total_sales
FROM sales
GROUP BY EXTRACT(YEAR FROM sale_date), EXTRACT(MONTH FROM sale_date)
ORDER BY year, month;

通过 EXTRACT 函数，我们可以便捷地按年和月对销售数据进行分组统计，获取每月的销售总额情况，以便进行时间序列分析和趋势观察。

2. DATE_TRUNC 函数

DATE_TRUNC 函数用于将日期或时间值截断到指定的精度，如截断到月、季度、年等。

-- 示例：统计每季度的平均销售额
SELECT DATE_TRUNC('quarter', sale_date) AS quarter_start,
AVG(sales_amount) AS average_sales
FROM sales
GROUP BY DATE_TRUNC('quarter', sale_date)
ORDER BY quarter_start;

这里，DATE_TRUNC 函数将销售日期截断到季度开始，然后计算每个季度的平均销售额，有助于从季度层面分析销售数据的趋势和规律。
高级 SQL 函数为数据处理和分析提供了丰富的工具集，能够解决各种复杂的数据处理需求。通过深入理解和熟练运用窗口函数、CTE、PIVOT 和 UNPIVOT 操作以及各种高级字符串和日期时间函数，开发者可以更加高效地查询、分析和转换数据库中的数据，为决策支持、报表生成和数据挖掘等任务提供有力支持，提升数据库应用的价值和性能。在实际应用中，根据具体的数据场景和业务需求，灵活选择和组合这些函数，将能够充分发挥 SQL 的强大功能，实现复杂的数据操作和分析目标。