如何在Athena中查询每个分组的最新记录及对应Amount值

问题背景 #

现有数据表结构如下：

Id   Type   TimeStamp   Amount   Partition  year   Hash
-------------------------------------------------------
1    194I   108          28       Monthly   2022   abc
1    194I   105          56       Monthly   2022   abc
1    194J   106          36       Monthly   2022   abc
2    194K   110          29       Monthly   2022   xyz
1    194J   105          32       Monthly   2022   abc
2    194C   100          20       Monthly   2022   xyz
1    194J   106          36       Monthly   2022   abc
1    194K   110          29       Monthly   2022   abc
1    194J   105          32       Monthly   2022   abc
2    194C   100          20       Monthly   2022   xyz

需求为筛选id=1、Hash=abc、year=2022、Partition=Monthly的数据后，取每个Type分组下TimeStamp最新记录对应的Amount值。

注：给出的预期结果中194C对应的记录id=2、Hash=xyz，不符合基础过滤条件，实际不会出现在最终结果中，符合条件的正确返回结果应为194I、194J、194K三个Type对应的最新Amount值。

原有SQL仅做了条件过滤和全局排序，没有实现分组取Top1的逻辑，因此会返回所有符合过滤条件的记录，原SQL如下：

select type, amount
from table
where id = 1
  and hash = abc
  and financialyear = 2022
  and partition = Monthly
order by timestamp desc

正确查询语句 #

Athena基于Presto引擎，支持窗口函数，使用ROW_NUMBER()窗口函数即可实现分组取最新记录的需求，写法如下：

WITH ranked_data AS (
  SELECT
    Type,
    Amount,
    ROW_NUMBER() OVER (
      PARTITION BY Type
      ORDER BY TimeStamp DESC
    ) AS row_rank
  FROM 替换为你的实际表名
  WHERE
    id = 1
    AND Hash = 'abc'
    AND year = 2022
    AND Partition = 'Monthly'
)
SELECT Type, Amount
FROM ranked_data
WHERE row_rank = 1

说明 #

• 语句首先通过CTE对所有符合基础过滤条件的记录做分组排序：按Type字段分区，每个分区内按TimeStamp从新到旧排序，为每条记录生成序号，每个Type组内时间最新的记录序号固定为1
• 外层查询筛选序号为1的记录，即可得到每个Type对应的最新时间的Amount值
• 原SQL存在两处语法问题需要修正：字符串类型的过滤值需要用单引号包裹；原SQL中写的financialyear字段和表结构中的year字段名不匹配，需要替换为实际表的对应字段名

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