获取组中每天的最后记录

Question

我正在尝试获取每天的最后/最新记录。12月14日有两条记录，我想获得下午5点的记录，因为这是当天的最后一条记录，但如果你看到row_number(rn = 2 & 3)，它不会选择这条记录，只给我今天的时间点记录(12月15日为其rn=1(。

ITCFID  ControlId   ResourceId  DateTime    rn
P05.01.03   CloudFront.1    AWS:::Acount:1111111111111  12/14/2021 5:00 PM  2
P05.01.03   CloudFront.1    AWS:::Acount:1111111111111  12/14/2021 06:01 AM 3

这是我正在使用的查询：

WITH
Pointintimesecurityfindings AS (
SELECT
*
, ROW_NUMBER() OVER (PARTITION BY ResourceId,ControlId,itcfid ORDER BY DateTime DESC) rn
FROM itcf_final_summary_dashboard
)    
SELECT *
FROM Pointintimesecurityfindings
WHERE rn = 1

对于特定的ITCFID->可以有多个控件ID，控件ID可以有多条资源ID。我想为特定的itcfid->唯一控件ID->唯一资源ID->获取当天的最新记录。

Answer 1

如果你想要每天最新的，你需要按天分区，例如

最初标记的SQL Server解决方案：

ROW_NUMBER() OVER (PARTITION BY
ResourceId
, ControlId
, itcfid
, DATEPART(year,[DateTime])
, DATEPART(dayofyear,[DateTime]
) ORDER BY [DateTime] DESC) rn

更新问题的Presto解决方案：

ROW_NUMBER() OVER (PARTITION BY
, ResourceId
, ControlId
, itcfid
, DATE_FORMAT(Datetime,'%Y') 
, DATE_FORMAT(Datetime,'%j')
ORDER BY DateTime DESC
) rn