从重复记录表中识别房产评分随时间提升的特定记录

核心需求 #

从大型数据集生成的重复房产记录表中，筛选出满足以下条件的单套房产：

• 该房产的最早记录评分＜50
• 该房产的最新记录评分＞70
• 示例数据中仅PROPERTY17符合要求，需提取其对应REFERENCE为jygh的行

关键注意事项 #

必须基于时间维度做变化分析，不能仅通过对比Rating的最大值/最小值判断：

若存在房产评分先高后低（如早期评分80、后期评分40），其评分最大值＞70、最小值＜50，但完全不符合需求，因此必须严格对应「最早」和「最新」时间点的评分值。

实现思路（SQL示例） #

步骤1：分组获取每套房产的最早/最新评分 #

通过窗口函数按房产ID分组，提取对应时间最早和最晚的评分及关联字段：

WITH property_rating_timeline AS (
    SELECT
        PROPERTY_ID,
        -- 获取最早记录的评分
        FIRST_VALUE(Rating) OVER (PARTITION BY PROPERTY_ID ORDER BY record_timestamp ASC) AS earliest_rating,
        -- 获取最新记录的评分及对应REFERENCE
        FIRST_VALUE(Rating) OVER (PARTITION BY PROPERTY_ID ORDER BY record_timestamp DESC) AS latest_rating,
        FIRST_VALUE(REFERENCE) OVER (PARTITION BY PROPERTY_ID ORDER BY record_timestamp DESC) AS target_reference
    FROM your_property_dataset
)

步骤2：筛选符合条件的记录 #

基于中间结果，筛选出满足评分条件的房产，并提取目标REFERENCE：

SELECT
    PROPERTY_ID,
    target_reference
FROM property_rating_timeline
WHERE earliest_rating < 50 AND latest_rating > 70
-- 去重，确保每套房产仅返回一条结果
GROUP BY PROPERTY_ID, target_reference;

内容的提问来源于stack exchange，提问作者my-socrates-note