如何在PostgreSQL中基于上期变化对团队进行分类？

核心思路 #

通过窗口函数LAG()获取每个团队每期的上期评级，计算相邻两期的评级变化量，再依据最近两期的变化情况，将团队归类到对应的状态中。

分步实现SQL代码 #

以下是通用SQL写法，适用于支持窗口函数的数据库（如PostgreSQL、MySQL 8.0+、SQL Server等）：

-- 第一步：计算每个团队每期的评级变化
WITH team_rating_changes AS (
    SELECT
        team,
        date,
        rating,
        -- 获取上一期的评级
        LAG(rating) OVER (PARTITION BY team ORDER BY date) AS prev_rating,
        -- 计算当前期与上期的评级差值（正为上升，负为下降）
        rating - LAG(rating) OVER (PARTITION BY team ORDER BY date) AS rating_change
    FROM team_ratings
),
-- 第二步：筛选每个团队最近两期的评级变化（排除无上期数据的第一期）
latest_changes AS (
    SELECT
        team,
        rating_change,
        -- 按日期倒序编号，最近一期为1，上一期为2
        ROW_NUMBER() OVER (PARTITION BY team ORDER BY date DESC) AS rn
    FROM team_rating_changes
    WHERE rating_change IS NOT NULL
)
-- 第三步：根据最近两期的变化判断团队状态
SELECT
    team,
    CASE
        -- 连续两期上升：最近两期变化均为正
        WHEN SUM(CASE WHEN rn IN (1,2) AND rating_change > 0 THEN 1 ELSE 0 END) = 2 
            THEN '连续两期上升（Increasing for two consecutive periods）'
        -- 较上期上升：仅最新一期变化为正
        WHEN MAX(CASE WHEN rn = 1 THEN rating_change END) > 0 
            THEN '较上期上升（Increased compared to the last period）'
        -- 连续两期下降：最近两期变化均为负
        WHEN SUM(CASE WHEN rn IN (1,2) AND rating_change < 0 THEN 1 ELSE 0 END) = 2 
            THEN '连续两期下降（Decreasing for two consecutive periods）'
        -- 较上期下降：仅最新一期变化为负
        WHEN MAX(CASE WHEN rn = 1 THEN rating_change END) < 0 
            THEN '较上期下降（Decreased compared to the last period）'
        -- 处理无变化的特殊情况（用户需求未提及，可选保留）
        ELSE '无变化'
    END AS status
FROM latest_changes
GROUP BY team
ORDER BY team;

代码说明 #

1. LAG()窗口函数：按team分组、date排序，获取当前记录的上一条评级数据，是计算相邻变化的核心。
2. CTE分层处理：
   • team_rating_changes：生成包含每期评级变化的中间表；
   • latest_changes：筛选出每个团队有对比数据的记录，并标记最近两期的序号。
3. CASE条件判断：通过聚合最近两期的变化结果，精准匹配四种状态分类。

运行上述代码后，将得到与示例一致的结果。

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