Redshift如何转换时间戳获取秒/毫秒值解决同时间戳取数问题

现有如下测试数据：

id      state   city     time
123.04      ny      1    01-10-2021 12:30
123.05      ny      2    01-10-2021 12:30

需求为按state字段分组，获取每组最新时间关联的id，初始SQL写法如下：

select id, state 
from data a 
join (select state, max(time) as most_recent 
      from data group by 1) b on a on a.state = b.state and a.time = b.most_recent)

运行时遇到同一分组下多条记录时间戳取值完全相同的问题，已知可通过取最大ID的方式兜底，但更希望基于时间戳精度判断：因ID按顺序分配，若能直接获取时间戳对应的秒/毫秒级精度值，即可准确定位最新记录对应的ID，询问Redshift是否支持直接获取时间戳的秒/毫秒值，是否必须额外编写查询实现。

Redshift原生支持直接提取时间戳的秒、毫秒级精度值，无需额外编写多层嵌套查询，直接调用内置函数即可：

• 秒级精度时间戳（从1970-01-01 00:00:00 UTC起算的秒数，带亚秒级小数位）：EXTRACT(EPOCH FROM time)
• 毫秒级精度时间戳：EXTRACT(EPOCH FROM time) * 1000

针对分组取最新记录的场景，更推荐直接使用窗口函数实现，代码更简洁且性能更好，同时可以直接复用时间戳的完整精度，搭配ID作为兜底排序规则，完全避免同时间戳重复值的问题：

SELECT id, state
FROM (
    SELECT
        id,
        state,
        ROW_NUMBER() OVER (
            PARTITION BY state
            ORDER BY time DESC, id DESC
        ) AS row_rank
    FROM data
) ranked
WHERE row_rank = 1;

补充说明：如果time字段本身存储精度只有分钟级（和示例数据一致，仅精确到分），即便提取毫秒值也无法恢复已经丢失的精度，这种场景下排序规则中追加id DESC兜底是最稳妥的实现方式，比单独计算时间戳数值效率更高。
另外原SQL存在两处语法错误：JOIN条件后多写了冗余的on a，语句末尾多了一个不匹配的右括号，保留原JOIN逻辑的修正版本如下：

SELECT a.id, a.state 
FROM data a 
JOIN (
    SELECT state, max(time) as most_recent 
    FROM data 
    GROUP BY 1
) b 
ON a.state = b.state AND a.time = b.most_recent;

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