基于最近2个月按员工ID统计月薪环比变动的SQL实现问题

可以实现按employee_id统计最近2个月的月薪环比变动，先分析你现有代码的问题，再给出修正方案：

现有代码的问题 #

• WHERE条件语法错误：Dateadd(month,-2,_date)中的_date是无效变量，应该用GETDATE()作为基准日期，正确的最近2个月筛选逻辑应为date >= DATEADD(month, -2, GETDATE())（假设date为薪资对应的月份日期）。
• 排序字段错误：用fiscal_year排序无法区分月份，LAG函数无法取到上月薪资数据，导致previous_salary无有效值，最终环比计算结果全为0。
• 性能问题：缺少employee_id和date的联合索引，查询触发全表扫描，导致耗时过长。

修正后的SQL实现（以SQL Server为例） #

假设date字段为薪资对应的月份日期（如每月发薪日），以下代码会统计每个员工最近2个月的薪资及环比变动：

SELECT 
    employee_id,
    -- 提取月份，明确对应周期
    FORMAT(date, 'yyyy-MM') AS salary_month,
    salary,
    -- 获取上月薪资
    LAG(salary) OVER (
        PARTITION BY employee_id 
        ORDER BY YEAR(date), MONTH(date)
    ) AS previous_month_salary,
    -- 计算环比变动率（保留2位小数）
    CASE 
        WHEN LAG(salary) OVER (PARTITION BY employee_id ORDER BY YEAR(date), MONTH(date)) IS NOT NULL
        THEN ROUND((salary - LAG(salary) OVER (PARTITION BY employee_id ORDER BY YEAR(date), MONTH(date))) 
                   / LAG(salary) OVER (PARTITION BY employee_id ORDER BY YEAR(date), MONTH(date)) * 100, 2)
        ELSE NULL -- 无上月数据时显示NULL
    END AS mom_change_rate
FROM
    basic_pays
WHERE
    -- 筛选最近2个月的数据，若需取完整月份可调整为DATEADD(month, DATEDIFF(month,0,GETDATE())-2, 0)
    date >= DATEADD(month, -2, GETDATE())
ORDER BY
    employee_id, salary_month;

性能优化建议 #

创建employee_id和date的联合索引，避免全表扫描：

CREATE NONCLUSTERED INDEX idx_basic_pays_emp_date ON basic_pays(employee_id, date) INCLUDE (salary);

注意：如果date字段存储的是具体发薪日期而非月份，可直接按date排序；若使用MySQL、PostgreSQL等其他数据库，需调整日期函数（如MySQL用DATE_FORMAT，PostgreSQL用TO_CHAR）。

内容的提问来源于stack exchange，提问作者rita_jo