Athena SQL实现滚动每小时用户搜索记录最新条目查询

需求说明 #

需要从客户搜索历史中，找出每个客户在滚动一小时时间窗口内的最新搜索记录——具体逻辑是：从最晚的记录开始，将每条记录与后续（时间更晚）的记录对比，若后续1小时内有其他记录，则只保留该窗口内的最晚记录；若后续1小时无记录，则保留当前记录。最终结果是每个“独立1小时窗口”的最晚记录集合。

示例输入 #

customerid, query_string, searchrequestat
1, qs1, 2023-05-25 06:38:51.000
1, qs1, 2023-05-25 06:50:51.000
1, qs1, 2023-05-25 07:30:51.000
1, qs1, 2023-05-25 07:52:00.000
1, qs1, 2023-05-25 08:12:00.000
1, qs1, 2023-05-25 09:50:56.000
2, qs2, 2023-05-25 06:23:06.000
2, qs2, 2023-05-25 05:33:46.000

期望输出 #

customerid, query_string, searchrequestat
1, qs1, 2023-05-25 06:50:51.000
1, qs1, 2023-05-25 08:12:00.000
1, qs1, 2023-05-25 09:50:56.000
2, qs2, 2023-05-25 06:23:06.000

原SQL问题分析 #

你提供的自关联SQL仅返回每个客户的单条记录，原因是：

• 自关联生成了大量重复行，但后续仅按customerid分区取rn=1，相当于直接取每个客户的最新记录，完全没有实现滚动窗口的分组逻辑。

正确解决方案 #

采用递归CTE从后往前遍历的方式，精准筛选符合条件的记录：

WITH sorted_desc AS (
    -- 按客户分组，搜索时间从晚到早排序
    SELECT 
        customerid, 
        query_string, 
        searchrequestat,
        ROW_NUMBER() OVER (PARTITION BY customerid ORDER BY searchrequestat DESC) AS row_num
    FROM your_table
),
recursive_keep AS (
    -- 初始化：保留每个客户的最晚记录
    SELECT 
        customerid, 
        query_string, 
        searchrequestat,
        row_num,
        searchrequestat AS last_kept_time
    FROM sorted_desc
    WHERE row_num = 1
    
    UNION ALL
    
    -- 递归判断：仅保留与上一条保留记录时间差超过1小时的记录
    SELECT 
        sd.customerid, 
        sd.query_string, 
        sd.searchrequestat,
        sd.row_num,
        sd.searchrequestat AS last_kept_time
    FROM sorted_desc sd
    JOIN recursive_keep rk 
        ON sd.customerid = rk.customerid 
        AND sd.row_num = rk.row_num + 1
    WHERE DATE_DIFF('hour', sd.searchrequestat, rk.last_kept_time) > 1
)
-- 按客户和时间升序输出结果
SELECT 
    customerid, 
    query_string, 
    searchrequestat
FROM recursive_keep
ORDER BY customerid, searchrequestat;

逻辑说明 #

1. sorted_desc阶段：将每个客户的搜索记录按时间从晚到早排序，给每条记录分配序号，方便递归遍历。
2. recursive_keep阶段：
   • 初始步骤：先保留每个客户的最晚记录（时间最新的那条）。
   • 递归步骤：依次遍历更早的记录，若当前记录与上一条保留记录的时间差超过1小时，则保留该记录；否则跳过（因为它属于上一条记录的1小时窗口内）。
3. 最终输出：将保留的记录按客户和时间升序排列，得到符合需求的结果。

这个方案完美匹配示例中的分组逻辑，能准确筛选出每个独立1小时窗口的最新记录。

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