如何用SQL查询按ID分组添加最新值与最大值列？

需求说明 #

现有原始数据表格：

id   date           val
1    10.08.2022     10
1    12.08.2022     11
1    08.08.2022     15
1    16.08.2022     9
2    02.07.2022     2
2    01.07.2022     4
2    30.07.2022     7

需要新增两列：

• last_v：每个id对应最新日期的val值
• max_v：每个id的val最大值

期望输出结果：

id   date           val   last_v   max_v
1    10.08.2022     10      9        15
1    12.08.2022     11      9        15
1    08.08.2022     15      9        15
1    16.08.2022     9       9        15
2    02.07.2022     2       2        7
2    01.07.2022     4       2        7
2    30.06.2022     7       2        7

解决方案 #

使用SQL窗口函数即可实现，核心是按id分组，分别计算最大值和最新日期对应的val值。

实现代码 #

SELECT 
    id,
    date,
    val,
    -- 获取每个id最新日期对应的val
    FIRST_VALUE(val) OVER (
        PARTITION BY id 
        ORDER BY STR_TO_DATE(date, '%d.%m.%Y') DESC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_v,
    -- 获取每个id的val最大值
    MAX(val) OVER (PARTITION BY id) AS max_v
FROM your_table_name;

代码说明 #

1. MAX(val) OVER (PARTITION BY id)：按id分组，直接计算每组的val最大值，对应max_v列。
2. FIRST_VALUE(val) OVER (...)：
   • PARTITION BY id：限定仅在当前id的分组内处理数据
   • ORDER BY STR_TO_DATE(date, '%d.%m.%Y') DESC：将字符串格式的日期转换为日期类型，倒序排列后最新日期会排在分组的首位
   • ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING：确保分组内所有行都能取到排序后的第一个值（即最新日期对应的val）

如果使用的数据库不支持STR_TO_DATE（如PostgreSQL），可替换为对应日期转换函数，例如TO_DATE(date, 'DD.MM.YYYY')。

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