基于Databricks SQL的Hive映射Delta表高效求最大时间查询

现有一张映射为Hive表的Delta表UtilEvents，表数据如下：

-----------------------------------------------------------------------------
SerialNumber    EventTime               UseCase     RemoteHost  RemoteIP
-----------------------------------------------------------------------------
131058          2022-12-02T00:31:29     Send        Host1       RemoteIP1
131058          2022-12-21T00:33:24     Receive     Host1       RemoteIP1
131058          2022-12-22T01:35:33     Send        Host1       RemoteIP1
131058          2022-12-20T01:36:53     Receive     Host1       RemoteIP1
131058          2022-12-11T00:33:28     Send        Host2       RemoteIP2
131058          2022-12-15T00:35:18     Receive     Host2       RemoteIP2
131058          2022-12-12T02:29:11     Send        Host2       RemoteIP2
131058          2022-12-01T02:30:56     Receive     Host2       RemoteIP2

需要按UseCase和RemoteHost字段分组，获取每组中EventTime最大的对应记录，预期结果如下：

----------------------------------------------------------------
SerialNumber    EventTime               UseCase     RemoteHost
----------------------------------------------------------------
131058          2022-12-21T00:33:24     Receive     Host1
131058          2022-12-22T01:35:33     Send        Host1
131058          2022-12-15T00:35:18     Receive     Host2
131058          2022-12-12T02:29:11     Send        Host2

要求提供纯Databricks SQL格式的高效查询语句，禁止使用中间DataFrame结果。

使用窗口函数ROW_NUMBER()按分组字段分区，对每组内的时间字段降序排序后取第一条记录，这种方式无需额外关联操作，执行效率更高：

SELECT SerialNumber, EventTime, UseCase, RemoteHost
FROM (
    SELECT 
        SerialNumber, 
        EventTime, 
        UseCase, 
        RemoteHost,
        ROW_NUMBER() OVER (PARTITION BY UseCase, RemoteHost ORDER BY EventTime DESC) AS rn
    FROM UtilEvents
) t
WHERE rn = 1;

如果同一分组内存在多条EventTime相同的最大时间记录，需要保留所有这类记录时，可将ROW_NUMBER()替换为RANK()或DENSE_RANK()。

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