如何从数据集中筛选出一段时间内价值增幅最大的记录？

我整理了一个最简示例来明确需求：

给定包含行号、ID、Year、Value列的表格：

需求是找出价值增幅最大的ID，并新增一列显示增幅值，期望输出如下：

解决方案 #

场景1：固定时间范围（如示例中的2012-2013年）

通过分组计算每个ID的价值增幅，再筛选出增幅最大的记录：

WITH id_value_gain AS (
    SELECT 
        ID,
        MAX(Value) AS Latest_Value,
        MAX(Value) - MIN(Value) AS Value_Gained
    FROM your_table_name
    WHERE Year BETWEEN 2012 AND 2013
    GROUP BY ID
)
SELECT 
    ID,
    Latest_Value AS Value,
    Value_Gained
FROM id_value_gain
WHERE Value_Gained = (SELECT MAX(Value_Gained) FROM id_value_gain);

场景2：动态时间范围（取每个ID最早和最晚年份的价值差）

如果数据中每个ID的时间跨度不固定，需取其最早记录和最晚记录的价值差：

WITH id_year_value AS (
    SELECT 
        ID,
        Year,
        Value,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Year) AS rn_first,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Year DESC) AS rn_last
    FROM your_table_name
),
id_value_gain AS (
    SELECT 
        iv.ID,
        MAX(CASE WHEN iv.rn_last = 1 THEN iv.Value END) AS Latest_Value,
        MAX(CASE WHEN iv.rn_last = 1 THEN iv.Value END) - MAX(CASE WHEN iv.rn_first = 1 THEN iv.Value END) AS Value_Gained
    FROM id_year_value iv
    WHERE iv.rn_first = 1 OR iv.rn_last = 1
    GROUP BY iv.ID
)
SELECT 
    ID,
    Latest_Value AS Value,
    Value_Gained
FROM id_value_gain
WHERE Value_Gained = (SELECT MAX(Value_Gained) FROM id_value_gain);

注意事项 #

• 替换代码中的your_table_name为实际表名；
• 若Value字段带有千分位逗号，需先转换为数值类型再计算（如REPLACE(Value, ',', '')::DECIMAL）。

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