MariaDB 窗口函数怎么用？排名、累计和同比计算详解

窗口函数是 SQL 里做数据分析最好用的工具——不用窗口函数，计算排名、累计、同比这些需求得写各种子查询和自连接，代码又长又慢。MariaDB 从 10.2 开始支持窗口函数，基本覆盖了 SQL 标准的核心功能。

语法结构

每个窗口函数都遵循同一套语法：

sql
函数名(表达式) OVER (
    PARTITION BY 分组字段
    ORDER BY 排序字段
    ROWS/RANGE 窗口范围
)

OVER 子句定义了"窗口"——函数在这个范围内计算。PARTITION BY 把数据分组，每组独立计算；ORDER BY 决定组内排序；ROWS/RANGE 进一步约束参与计算的行范围。

排名函数：ROW_NUMBER、RANK、DENSE_RANK

三个排名函数的区别在处理并列值时的行为：

• ROW_NUMBER：严格递增，1-2-3-4，不管值是否相同
• RANK：并列同名，跳号，1-1-3-4
• DENSE_RANK：并列同名，不跳号，1-1-2-3

典型场景——每个部门薪资前三名：

sql
SELECT * FROM (
    SELECT name, department, salary,
           DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
    FROM employees
) t WHERE rnk <= 3;

用 DENSE_RANK 而不是 RANK，因为如果前三名有并列，RANK 会跳号，导致实际返回的记录少于 3 条。

聚合函数做累计和移动平均

窗口函数让 SUM/AVG/COUNT 不再只是"一组一个数"，而是逐行累计：

sql
-- 累计销售额
SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

-- 7 天移动平均
SELECT order_date, amount,
       AVG(amount) OVER (ORDER BY order_date
                         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d
FROM orders;

ROWS BETWEEN ... AND ... 定义了参与计算的行范围。6 PRECEDING AND CURRENT ROW 表示当前行和前 6 行，总共 7 行做平均。

LAG 和 LEAD：访问前后行

LAG 取前 N 行的值，LEAD 取后 N 行的值。算环比增长率靠它们：

sql
SELECT month, revenue,
       LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
       ROUND((revenue - LAG(revenue, 1) OVER (ORDER BY month)) 
             / LAG(revenue, 1) OVER (ORDER BY month) * 100, 2) AS growth_pct
FROM monthly_sales;

LAG 的第二个参数是偏移量，第三个参数是默认值（缺省返回 NULL）。算同比就改成 LAG(revenue, 12)，往前取 12 个月。

FIRST_VALUE 和 LAST_VALUE 的坑

FIRST_VALUE 取分组内第一个值，LAST_VALUE 取最后一个值。但 LAST_VALUE 有个常见陷阱——默认窗口范围是 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，不是整个分区。所以如果你想取部门最低薪资，必须显式指定窗口范围：

sql
-- 正确写法：指定完整窗口范围
SELECT name, department, salary,
       LAST_VALUE(salary) OVER (
           PARTITION BY department ORDER BY salary DESC
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS lowest_in_dept
FROM employees;

不加 ROWS BETWEEN ... UNBOUNDED FOLLOWING，LAST_VALUE 每行返回的值都不一样——因为窗口只到当前行为止。

ROWS 和 RANGE 的区别

• ROWS：按物理行号计算，窗口大小固定
• RANGE：按逻辑值范围计算，同一排序值的行作为一个整体

sql
-- ROWS：固定 3 行窗口
SUM(amount) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)

-- RANGE：同一天的行一起算
SUM(amount) OVER (ORDER BY date RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW)

RANGE 适合按时间窗口聚合，同一时间点的所有行会被包含在同一个窗口内。ROWS 更精确，适合固定行数的滑动窗口。