掌握SQL窗口函数，轻松处理复杂数据分析

Java编程爱好者
0 阅读7分钟

SQL 的窗口函数（Window Function）是一种强大的分析型函数，用于在不减少原始行数的情况下对数据进行复杂计算。它允许你对一组相关行（称为“窗口”）执行计算，并将结果返回到每一行中。

窗口函数的主要特点

  1. ‌保持原始行数‌：与传统的聚合函数（如 GROUP BY）不同，窗口函数不会合并行，而是为每一行返回一个计算结果。
  2. ‌基于窗口计算‌：窗口函数的计算基于一个“窗口”范围，这个窗口可以通过 OVER() 子句定义。
  3. ‌支持多种类型函数‌：
    ‌1. 排序类函数（Ranking Functions） ‌
    用于在分组内进行排名，是处理“Top N”问题的核心工具。
    表格
函数详细说明使用场景典型SQL模式
ROW_NUMBER()为每行分配唯一序号，无并列，严格递增分页查询、去重取唯一记录ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)
RANK()并列排名，相同值同名次，后续跳过名次竞赛排名、成绩并列展示RANK() OVER (ORDER BY score DESC)
DENSE_RANK()并列排名，后续不跳名次，保持连续奖项等级划分、梯队分析DENSE_RANK() OVER (PARTITION BY region ORDER BY sales)
NTILE(n)将数据均分为n组，返回组号客户分层、收入五分位分析NTILE(4) OVER (ORDER BY income)

PARTITION BY 可实现分组内排名；若省略，则全表作为一个窗口计算。

‌2. 聚合类函数（Aggregate Functions as Window Functions） ‌
在保留原始行的基础上，实现累计、移动、滑动统计。
表格

函数详细说明使用场景典型SQL模式
SUM()窗口内累计求和月度累计销售额、滚动总额SUM(sales) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AVG()窗口内平均值移动平均、趋势分析AVG(price) OVER (PARTITION BY product ORDER BY date ROWS 2 PRECEDING)
COUNT()统计窗口内行数活跃用户累计计数COUNT(*) OVER (PARTITION BY user_id ORDER BY login_date)
MAX()/MIN()获取窗口内极值历史最高/最低对比MAX(temp) OVER (PARTITION BY city ORDER BY day)

使用 ROWS 或 RANGE 明确窗口范围，避免全表扫描性能问题；UNBOUNDED PRECEDING 表示从分区第一行开始。

‌3. 取值类函数（Value Access Functions） ‌
用于跨行取值，实现环比、同比、趋势预测等分析。
表格

函数详细说明使用场景典型SQL模式
LAG(col, n, default)取当前行前第n行的值与上期对比、环比增长LAG(sales, 1) OVER (ORDER BY month)
LEAD(col, n, default)取当前行后第n行的值预测、下期对比LEAD(price, 1) OVER (PARTITION BY stock ORDER BY date)
FIRST_VALUE(col)取窗口第一行的值初始值对比、基期分析FIRST_VALUE(salary) OVER (PARTITION BY emp_id ORDER BY year)
LAST_VALUE(col)取窗口最后一行的值末期值提取（需配合 RANGE BETWEEN ...）LAST_VALUE(score) OVER (ORDER BY date RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)

LAST_VALUE() 默认窗口为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，需显式调整为 UNBOUNDED FOLLOWING 才能取到最后。

‌4. 分布类函数（Distribution Functions） ‌
用于分析数据在分组中的相对位置和分布密度。
表格

函数详细说明使用场景典型SQL模式
PERCENT_RANK()计算相对排名（0到1），公式：(rank-1)/(总行数-1)成绩百分位、绩效分布PERCENT_RANK() OVER (ORDER BY score)
CUME_DIST()小于等于当前值的比例，公式：≤当前值的行数 / 总行数客户覆盖率、达标率分析CUME_DIST() OVER (ORDER BY revenue)

CUME_DIST() 对重复更改敏感，适合用于“有多少人低于我”的场景。

基本语法结构

sqlCopy Code<窗口函数> OVER (
    [PARTITION BY <分组列>] 
    [ORDER BY <排序列>] 
    [ROWS/RANGE <窗口框架定义>]
)
  • PARTITION BY：用于将数据划分为不同的组，类似于 GROUP BY，但不减少行数。
  • ORDER BY：定义窗口内的排序方式，影响窗口函数的计算顺序。
  • ROWS 或 RANGE：定义窗口的范围，例如当前行前后几行或某个值范围内的行。

注意： [ROWS/RANGE <窗口框架定义>] 默认值是：**

1. 没有指定 ORDER BY‌：窗口函数将应用于整个结果集，相当于对所有行进行计算。  2. 指定了 ORDER BY‌：默认的窗口帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，这意味着窗口从分区的第一行开始，到当前行结束。

ROWS‌：

  • ‌定义‌：ROWS 基于物理行号来定义窗口范围，严格按照行的顺序进行计数。

  • ‌特点

    • 严格按照行的物理位置来确定窗口。

    • 即使排序列的值相同，也会被单独计数。

    • 适用于需要精确控制行数的场景，例如计算最近 N 笔交易或前后 N 天的记录。

  • ‌示例

    • ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING：表示当前行及其前后各一行，共三行。

    • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：表示从第一行到当前行的所有行。

‌RANGE‌：

  • ‌定义‌：RANGE 基于排序列的值范围来定义窗口，将排序值相同的行视为一个逻辑组。

  • ‌特点‌：

    • 将排序列中值相等的所有行视为一个逻辑单元。

    • 适用于处理有重复值的场景，例如按日期或金额计算累积值。

    • 通常与 ORDER BY 子句一起使用。

  • ‌示例

    • RANGE BETWEEN 10 PRECEDING AND CURRENT ROW：表示当前行排序值范围内，前 10 个单位的行。

    • RANGE BETWEEN CURRENT ROW AND CURRENT ROW：仅包含当前行。

‌两者区别总结‌：

特性ROWSRANGE
定义方式基于物理行号基于排序列值范围
适用场景精确控制行数处理重复值、逻辑范围
重复值处理单独计数合并为一个逻辑组

sql例子

本例子是使用mysql8.0版本，进行测试；

初始数据的准备

sql数据

CREATE TABLE business (
    name VARCHAR(255),
    orderdate DATE,
    cost INT
);
​
INSERT INTO business VALUES 
('jack', '2017-01-01', 10),
('tony', '2017-01-02', 15),
('jack', '2017-02-03', 23),
('tony', '2017-01-04', 29),
('jack', '2017-01-05', 46),
('jack', '2017-04-06', 42),
('tony', '2017-01-07', 50),
('jack', '2017-01-08', 55),
('mart', '2017-04-08', 62),
('mart', '2017-04-09', 68),
('neil', '2017-05-10', 12),
('mart', '2017-04-11', 75),
('neil', '2017-06-12', 80),
('mart', '2017-04-13', 94);

相关例子

  1. 查询在2017年4月份购买过的顾客及总人数
SELECT DISTINCT name AS customer_name,
       COUNT(DISTINCT name) OVER() AS total_customers
FROM business 
WHERE SUBSTRING(orderdate, 1, 7) = '2017-04'
ORDER BY name;
  1. 查询顾客的购买明细及月购买总额
SELECT 
    name AS customer_name,
    orderdate AS order_date,
    cost AS purchase_amount,
    SUM(cost) OVER(PARTITION BY name, DATE_FORMAT(orderdate, '%Y-%m')) AS monthly_total,
    COUNT(*) OVER(PARTITION BY name, DATE_FORMAT(orderdate, '%Y-%m')) AS monthly_purchase_count
FROM business 
ORDER BY name, orderdate;

3.求每个顾客的购买明细及起点到当前行的累加 上一行到当前行的累加 当前行到下一行的累加 上一行到下一行的累加 当前行到终点的累加

-- 查询每个顾客的购买明细及各种累加统计
SELECT 
    name AS customer_name,
    orderdate AS order_date,
    cost AS purchase_amount,
    -- 起点到当前行的累加
    SUM(cost) OVER(PARTITION BY name ORDER BY orderdate ROWS UNBOUNDED PRECEDING) AS cumulative_from_start,
    -- 上一行到当前行的累加
    SUM(cost) OVER(PARTITION BY name ORDER BY orderdate ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS sum_prev_current,
    -- 当前行到下一行的累加
    SUM(cost) OVER(PARTITION BY name ORDER BY orderdate ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS sum_current_next,
    -- 上一行到下一行的累加
    SUM(cost) OVER(PARTITION BY name ORDER BY orderdate ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_prev_next,
    -- 当前行到终点的累加
    SUM(cost) OVER(PARTITION BY name ORDER BY orderdate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS cumulative_to_end
FROM business 
ORDER BY name, orderdate;;

4.查询每个顾客上次 和 下次 的购买时间

-- 查询每个顾客的购买明细及上次和下次购买时间
SELECT 
    name AS customer_name,
    orderdate AS current_purchase_date,
    LAG(orderdate) OVER (PARTITION BY name ORDER BY orderdate) AS previous_purchase_date,
    LEAD(orderdate) OVER (PARTITION BY name ORDER BY orderdate) AS next_purchase_date
FROM business
ORDER BY name, orderdate;

  1. 查询前20%时间的订单信息
SELECT
    name AS customer_name,
    orderdate AS order_date,
    cost AS purchase_amount
FROM (
    SELECT
    name,
    orderdate,
    cost,
    ---使用NTILE(5)窗口函数将所有订单按日期分为5个等份(五分位数)
    ---选择第一个五分位数(quintile=1)的数据，即最早20%时间段内的订单
     --最终结果按订单日期升序排列，展示前20%时间范围内的所有订单详情
    NTILE(5) OVER (ORDER BY orderdate) AS quintile
    FROM business
) AS ranked_orders
WHERE quintile = 1
ORDER BY orderdate;
avatar
文章
阅读
粉丝