按HHID分组取最近两个日期，判断最新日期分数是否更高

核心思路 #

1. 给每个HHID分组内的记录按日期倒序排名，最新日期排第1，第二新排第2
2. 筛选出每组排名前2的记录，剔除更早的
3. 对比最新日期与第二新日期的score，判断前者是否更大

具体SQL实现（以常见数据库为例） #

步骤1：生成分组排名的临时数据集

先给每条记录标记组内的日期排名：

WITH ranked_records AS (
    SELECT 
        HHID,
        date,
        score,
        -- 按HHID分组，日期倒序排序，生成排名
        ROW_NUMBER() OVER (PARTITION BY HHID ORDER BY date DESC) AS rank_num
    FROM your_table_name -- 替换成你的表名
)

• 若存在同一HHID下多个记录日期相同的情况，可将ROW_NUMBER()替换为RANK()或DENSE_RANK()，避免遗漏同日期的记录。

步骤2：筛选每组最近的两个日期记录

直接过滤排名≤2的记录，得到每组仅保留最近两个日期的数据：

SELECT HHID, date, score, rank_num
FROM ranked_records
WHERE rank_num <= 2;

步骤3：判断最新日期的score是否大于第二新的

有两种简洁的实现方式：

方式1：自连接对比

通过自连接关联同一HHID下排名1和2的记录，直接对比score：

WITH ranked_records AS (
    SELECT 
        HHID,
        date,
        score,
        ROW_NUMBER() OVER (PARTITION BY HHID ORDER BY date DESC) AS rank_num
    FROM your_table_name
)
SELECT 
    r1.HHID,
    r1.score AS latest_score,
    r2.score AS second_latest_score,
    -- 直接判断并返回结果
    CASE WHEN r1.score > r2.score THEN '是' ELSE '否' END AS is_latest_higher
FROM ranked_records r1
JOIN ranked_records r2 
    ON r1.HHID = r2.HHID 
    AND r1.rank_num = 1 
    AND r2.rank_num = 2;

方式2：用LAG窗口函数直接获取上一条score

通过LAG()函数在排名时直接获取第二新的score，无需自连接：

WITH ranked_records AS (
    SELECT 
        HHID,
        date,
        score,
        ROW_NUMBER() OVER (PARTITION BY HHID ORDER BY date DESC) AS rank_num,
        -- 获取同组内上一条（第二新）的score
        LAG(score) OVER (PARTITION BY HHID ORDER BY date DESC) AS second_latest_score
    FROM your_table_name
)
SELECT 
    HHID,
    score AS latest_score,
    second_latest_score,
    CASE WHEN score > second_latest_score THEN '是' ELSE '否' END AS is_latest_higher
FROM ranked_records
WHERE rank_num = 1; -- 只取最新日期的记录，直接对比

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