告别嵌套子查询！MySQL窗口函数让报表统计效率提升80%（附避坑）

函数名([参数]) OVER (
    [PARTITION BY 分组列]  -- 可选，按指定列分组（类似GROUP BY，但不合并行）
    [ORDER BY 排序列 [ASC/DESC]]  -- 可选，对分组内的数据排序
    [ROWS/RANGE BETWEEN 窗口范围]  -- 可选，定义窗口的行范围（比如前N行、后N行）
)

-- 创建员工薪资表
CREATE TABLE emp_salary (
    emp_id INT PRIMARY KEY COMMENT '员工ID',
    dept_name VARCHAR(50) COMMENT '部门名称',
    emp_name VARCHAR(50) COMMENT '员工姓名',
    salary DECIMAL(10,2) COMMENT '月薪',
    hire_date DATE COMMENT '入职日期'
);
-- 插入测试数据
INSERT INTO emp_salary VALUES
(1, '研发部', '张三', 20000.00, '2020-01-10'),
(2, '研发部', '李四', 18000.00, '2020-03-15'),
(3, '研发部', '王五', 22000.00, '2019-11-01'),
(4, '市场部', '赵六', 15000.00, '2021-02-20'),
(5, '市场部', '钱七', 16000.00, '2020-08-08'),
(6, '市场部', '孙八', 14000.00, '2021-05-30'),
(7, '财务部', '周九', 19000.00, '2019-09-05');

SELECT 
    dept_name,
    emp_name,
    salary,
    -- 连续排名（即使薪资相同，排名也不同）
    ROW_NUMBER() OVER (PARTITION BY dept_name ORDER BY salary DESC) AS row_num,
    -- 跳跃排名（薪资相同排名相同，后续排名跳过）
    RANK() OVER (PARTITION BY dept_name ORDER BY salary DESC) AS rank_num,
    -- 连续排名（薪资相同排名相同，后续排名不跳过）
    DENSE_RANK() OVER (PARTITION BY dept_name ORDER BY salary DESC) AS dense_rank_num
FROM emp_salary;

-- 方案：窗口函数+子查询（MySQL8.0+支持）
WITH emp_rank AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY dept_name ORDER BY salary DESC) AS row_num
    FROM emp_salary
)
SELECT dept_name, emp_name, salary ,row_num 
FROM emp_rank 
WHERE row_num <= 2;

-- 销售额表
CREATE TABLE sales (
    month VARCHAR(10) PRIMARY KEY COMMENT '月份',
    amount DECIMAL(10,2) COMMENT '月度销售额'
);
INSERT INTO sales VALUES
('2024-01', 10000.00),
('2024-02', 12000.00),
('2024-03', 15000.00),
('2024-04', 13000.00);

SELECT 
    month,
    amount,
    -- 累计销售额（从第一行到当前行）
    SUM(amount) OVER (ORDER BY month) AS total_amount,
    -- 移动平均值（当前行+前1行）
    AVG(amount) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg_amount
FROM sales;

SELECT 
    dept_name,
    emp_name,
    hire_date,
    -- 获取同部门上一个员工的入职日期（偏移1行）
    LAG(hire_date, 1) OVER (PARTITION BY dept_name ORDER BY hire_date) AS prev_hire_date,
    -- 计算时间差（天）
    DATEDIFF(hire_date, LAG(hire_date, 1) OVER (PARTITION BY dept_name ORDER BY hire_date)) AS date_diff
FROM emp_salary;

-- 针对场景1的索引（部门+薪资）
CREATE INDEX idx_dept_salary ON emp_salary(dept_name, salary DESC);

-- 错误：省略ORDER BY，累计求和变成全表总和
SELECT month, amount, SUM(amount) OVER () AS total_amount FROM sales;

-- 正确：显式指定ORDER BY和窗口框架
SELECT 
    month,
    amount,
    SUM(amount) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_amount
FROM sales;

SELECT 
    dept_name,
    emp_name,
    hire_date,
    COALESCE(LAG(hire_date, 1) OVER (PARTITION BY dept_name ORDER BY hire_date), hire_date) AS prev_hire_date,
    DATEDIFF(hire_date, COALESCE(LAG(hire_date, 1) OVER (PARTITION BY dept_name ORDER BY hire_date), hire_date)) AS date_diff
FROM emp_salary;

set global tmp_table_size = 1G; 
set global max_heap_table_size = 1G;