如何用SQL查询识别薪资增减趋势并标记员工整体下降趋势

首先，我先把你的问题和数据整理清楚，方便理解：

你有如下员工薪资数据表：

需求是给每个EmpID统一加一个“Trend Decreasing”（Y/N）标识，同一员工的所有行都显示同一个结果——比如EmpID 100全标记Y，EmpID 101全标记N，而不是每行单独判断趋势。

核心思路 #

要实现这个，关键是先单独算出每个员工的整体趋势，再把这个结果关联回原表，这样同一员工的所有记录就能带上统一的标识。下面我分几种常用数据库给你具体代码，同时说明趋势判断的逻辑，你可以根据自己的实际需求调整。

趋势判断逻辑（匹配你的例子） #

从你的例子来看，EmpID 100的薪资虽然中间有小幅上涨，但整体呈下降走向；EmpID 101先涨后跌，整体趋势不算明显下降。我这里用线性回归计算薪资随时间变化的斜率来判断趋势——斜率为负就是下降（Y），否则为N，这个方法比简单对比首尾薪资更能反映真实的整体趋势。

各数据库实现代码 #

1. PostgreSQL #

PostgreSQL自带统计函数，直接就能计算斜率，非常方便：

WITH emp_trends AS (
    SELECT
        EmpID,
        -- regr_slope(y, x)：y是薪资，x把日期转成时间戳数值来计算变化率
        CASE WHEN regr_slope(Salary, EXTRACT(EPOCH FROM "Paid Date"::DATE)) < 0 
             THEN 'Y' 
             ELSE 'N' 
        END AS "Trend Decreasing"
    FROM your_salary_table
    GROUP BY EmpID
)
-- 把趋势结果关联回原表
SELECT
    t.*,
    et."Trend Decreasing"
FROM your_salary_table t
JOIN emp_trends et ON t.EmpID = et.EmpID
ORDER BY t.EmpID, t."Paid Date";

2. MySQL（8.0+） #

MySQL 8.0以后支持窗口函数，我给你两种方法：一种是精确计算斜率，一种是简化的统计下降次数占比。

方法A：精确斜率计算

WITH emp_dates AS (
    SELECT
        EmpID,
        Salary,
        "Paid Date" AS pay_date,
        -- 把日期转成相对于该员工第一次发薪日的天数，作为连续的x值
        DATEDIFF("Paid Date", MIN("Paid Date") OVER (PARTITION BY EmpID)) AS days_since_first
    FROM your_salary_table
),
emp_trends AS (
    SELECT
        EmpID,
        -- 用线性回归公式计算斜率：(n*Σxy - Σx*Σy) / (n*Σx² - (Σx)²)
        CASE 
            WHEN (COUNT(*) * SUM(days_since_first * Salary) - SUM(days_since_first) * SUM(Salary)) 
                 / (COUNT(*) * SUM(days_since_first * days_since_first) - POWER(SUM(days_since_first), 2)) < 0
            THEN 'Y'
            ELSE 'N'
        END AS "Trend Decreasing"
    FROM emp_dates
    GROUP BY EmpID
)
SELECT
    t.*,
    et."Trend Decreasing"
FROM your_salary_table t
JOIN emp_trends et ON t.EmpID = et.EmpID
ORDER BY t.EmpID, t."Paid Date";

方法B：简化判断（统计下降次数）

如果不需要太精确的斜率，也可以统计该员工每次发薪比上一次少的次数占比，比如超过一半就标记Y：

WITH emp_salary_ordered AS (
    SELECT
        EmpID,
        Salary,
        "Paid Date",
        -- 拿到上一次发薪的薪资
        LAG(Salary) OVER (PARTITION BY EmpID ORDER BY "Paid Date") AS prev_salary
    FROM your_salary_table
),
emp_trends AS (
    SELECT
        EmpID,
        CASE
            -- 统计下降次数（排除第一条没有上一次记录的行）
            WHEN SUM(CASE WHEN Salary < prev_salary THEN 1 ELSE 0 END) * 2 > COUNT(*) - 1
            THEN 'Y'
            ELSE 'N'
        END AS "Trend Decreasing"
    FROM emp_salary_ordered
    GROUP BY EmpID
)
SELECT
    t.*,
    et."Trend Decreasing"
FROM your_salary_table t
JOIN emp_trends et ON t.EmpID = et.EmpID
ORDER BY t.EmpID, t."Paid Date";

3. SQL Server #

SQL Server也支持统计函数，实现和PostgreSQL类似：

WITH emp_trends AS (
    SELECT
        EmpID,
        CASE WHEN REGR_SLOPE(Salary, CAST("Paid Date" AS DATETIME)) < 0
             THEN 'Y'
             ELSE 'N'
        END AS "Trend Decreasing"
    FROM your_salary_table
    GROUP BY EmpID
)
SELECT
    t.*,
    et."Trend Decreasing"
FROM your_salary_table t
INNER JOIN emp_trends et ON t.EmpID = et.EmpID
ORDER BY t.EmpID, t."Paid Date";

自定义趋势规则 #

如果你想用其他判断逻辑，比如只对比第一次和最后一次的薪资，只需要修改emp_trends里的CASE语句就行，比如：

CASE WHEN (SELECT Salary FROM your_salary_table WHERE EmpID = e.EmpID ORDER BY "Paid Date" DESC LIMIT 1) 
          < (SELECT Salary FROM your_salary_table WHERE EmpID = e.EmpID ORDER BY "Paid Date" ASC LIMIT 1)
     THEN 'Y'
     ELSE 'N'
END AS "Trend Decreasing"

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