MariaDB 的窗口函数有哪些？如何使用窗口函数进行数据分析？ - 面试题

MariaDB 的窗口函数（Window Functions）是强大的分析工具，允许在查询结果集上执行复杂的计算，同时保留原始行的详细信息。MariaDB 从 10.2 版本开始支持窗口函数。

1. 窗口函数语法

sql
window_function_name(expression) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression [ASC|DESC]]
    [FRAME_CLAUSE]
)

2. 常用窗口函数

排名函数

sql
-- ROW_NUMBER：为每一行分配唯一的序号
SELECT 
    employee_id,
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

-- RANK：相同值的行获得相同排名，可能有间隔
SELECT 
    employee_id,
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

-- DENSE_RANK：相同值的行获得相同排名，无间隔
SELECT 
    employee_id,
    name,
    department,
    salary,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

聚合函数

sql
-- SUM：计算累计总和
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

-- AVG：计算移动平均值
SELECT 
    order_date,
    amount,
    AVG(amount) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM orders;

-- COUNT：计算累计计数
SELECT 
    order_date,
    amount,
    COUNT(*) OVER (ORDER BY order_date) AS cumulative_count
FROM orders;

偏移函数

sql
-- LAG：访问前一行的值
SELECT 
    order_date,
    amount,
    LAG(amount, 1, 0) OVER (ORDER BY order_date) AS prev_amount,
    amount - LAG(amount, 1, 0) OVER (ORDER BY order_date) AS change
FROM orders;

-- LEAD：访问后一行的值
SELECT 
    order_date,
    amount,
    LEAD(amount, 1, 0) OVER (ORDER BY order_date) AS next_amount
FROM orders;

-- FIRST_VALUE：获取分区的第一个值
SELECT 
    employee_id,
    name,
    department,
    salary,
    FIRST_VALUE(salary) OVER (
        PARTITION BY department 
        ORDER BY salary DESC
    ) AS highest_salary
FROM employees;

-- LAST_VALUE：获取分区的最后一个值
SELECT 
    employee_id,
    name,
    department,
    salary,
    LAST_VALUE(salary) OVER (
        PARTITION BY department 
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_salary
FROM employees;

3. 窗口框架（Window Frame）

sql
-- ROWS：基于物理行
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
    ) AS sum_4_rows
FROM orders;

-- RANGE：基于逻辑值范围
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
        RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW
    ) AS sum_7_days
FROM orders;

-- GROUPS：基于分组
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
        GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
    ) AS sum_2_groups
FROM orders;

4. 实际应用场景

计算同比增长率

sql
SELECT 
    YEAR(order_date) AS year,
    MONTH(order_date) AS month,
    SUM(amount) AS monthly_sales,
    LAG(SUM(amount), 12) OVER (ORDER BY YEAR(order_date), MONTH(order_date)) AS same_month_last_year,
    (SUM(amount) - LAG(SUM(amount), 12) OVER (ORDER BY YEAR(order_date), MONTH(order_date))) / 
    LAG(SUM(amount), 12) OVER (ORDER BY YEAR(order_date), MONTH(order_date)) * 100 AS yoy_growth
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date);

计算移动平均

sql
SELECT 
    order_date,
    amount,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7_days
FROM orders;

找出前 N 名

sql
SELECT * FROM (
    SELECT 
        employee_id,
        name,
        department,
        salary,
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
    FROM employees
) ranked
WHERE rank <= 3;

窗口函数提供了强大的数据分析能力，能够简化复杂的 SQL 查询，提高代码的可读性和维护性。