SQL语法之分析函数使用指南与注意事项

1. 聚合类分析函数

常见函数：SUM(), AVG(), COUNT(), MAX(), MIN()

-- OVER() 之后括号里可以什么都不写 → 对整个结果集计算

-- 加上 PARTITION BY 表示分组统计 → 按指定列分组计算

-- 加上 ORDER BY 表示累计统计 → 从分区的开始到当前行的累计值

2. 排名类分析函数

常见函数：RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()

-- 必须要有 ORDER BY → 决定排序方式

-- 可以没有 PARTITION BY → 不分组时对整个结果集排序

3. 位移类分析函数

常见函数：LAG(), LEAD()

-- 必须要有 ORDER BY → 决定行的前后顺序

-- 可以没有 PARTITION BY → 不分组时在整个结果集中移动

简单示例

示例数据准备

CREATE TABLE sales (

sales_id SERIAL PRIMARY KEY,

salesperson VARCHAR(50),

region VARCHAR(50),

sale_date DATE,

amount DECIMAL(10,2)

);

INSERT INTO sales (salesperson, region, sale_date, amount) VALUES

('张三', '华东', '2023-01-05', 1500),

('李四', '华北', '2023-01-12', 2000),

('张三', '华东', '2023-01-18', 3000),

('王五', '华南', '2023-01-22', 2500),

('李四', '华北', '2023-01-25', 1800),

('王五', '华南', '2023-02-02', 3200);

1. 聚合类分析函数示例

-- 所有销售人员的总销售额(OVER空括号)

SELECT salesperson, amount,

SUM(amount) OVER() AS total_sales

FROM sales;

-- 按地区分组的销售额(使用PARTITION BY)

SELECT salesperson, region, amount,

SUM(amount) OVER(PARTITION BY region) AS region_total,

AVG(amount) OVER(PARTITION BY region) AS region_avg

FROM sales;

-- 销售人员的累计销售额(使用ORDER BY)

SELECT salesperson, sale_date, amount,

SUM(amount) OVER(PARTITION BY salesperson ORDER BY sale_date) AS running_total

FROM sales;

2. 排名类分析函数示例

-- 按销售额排名(必须有ORDER BY)

SELECT salesperson, amount,

RANK() OVER(ORDER BY amount DESC) AS sales_rank,

DENSE_RANK() OVER(ORDER BY amount DESC) AS dense_sales_rank,

ROW_NUMBER() OVER(ORDER BY amount DESC) AS row_num

FROM sales;

-- 按地区分组的销售额排名(PARTITION BY + ORDER BY)

SELECT salesperson, region, amount,

RANK() OVER(PARTITION BY region ORDER BY amount DESC) AS region_rank

FROM sales;

3. 位移类分析函数示例

-- 上一条和下一条销售记录的比较(必须有ORDER BY)

SELECT salesperson, sale_date, amount,

LAG(amount, 1) OVER(ORDER BY sale_date) AS prev_amount,

LEAD(amount, 1) OVER(ORDER BY sale_date) AS next_amount

FROM sales;

-- 按销售人员分组的上次销售金额

SELECT salesperson, sale_date, amount,

LAG(amount, 1) OVER(PARTITION BY salesperson ORDER BY sale_date) AS prev_sale

FROM sales;

通用重要提示：

• ORDER BY 在分析函数中和在全局 ORDER BY 子句中意义完全不同。分析函数内的 ORDER BY 只影响窗口内数据的计算顺序，不改变最终结果的展示顺序（除非最外层再用 ORDER BY 排序）。

• 窗口定义还可以支持更高级的 ROWS/RANGE BETWEEN 子句来精确控制窗口范围，例如 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING（当前行的前一行到后一行），但在使用前仍需注意数据库的兼容性。