如何用Hive Query查询次日未访问用户的最后一条记录

需求说明 #

基准日期为2023-05-06，需要找出在基准日期前一天（2023-05-05）未进行访问的用户，并获取这些用户的最后一条访问记录。

源数据 #

期望结果 #

解决方案 #

通过CTE（公共表达式）结合窗口函数实现需求，具体步骤如下：

1. 为每个用户的访问记录按时间倒序编号，标记出每个用户的最后一条访问记录；
2. 提取每个用户最后访问记录的日期；
3. 筛选出最后访问日期早于基准日期前一天的用户，获取他们的最后一条记录。

Hive SQL 查询语句 #

WITH user_last_access AS (
    SELECT 
        user_id,
        mission,
        time,
        -- 按用户分组，时间倒序编号，rn=1为该用户最后一条记录
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY time DESC) AS rn
    FROM user_access_log
),
user_last_date AS (
    SELECT 
        user_id,
        DATE(time) AS last_access_date
    FROM user_last_access
    WHERE rn = 1
)
SELECT ula.user_id, ula.mission, ula.time
FROM user_last_access ula
INNER JOIN user_last_date uld 
    ON ula.user_id = uld.user_id
WHERE ula.rn = 1
  -- 筛选最后访问日期早于基准日期前一天的用户
  AND uld.last_access_date < DATE('2023-05-06') - INTERVAL 1 DAY;

结果验证 #

• 用户A的最后访问日期为2023-05-01，早于2023-05-05，符合条件；
• 用户C的最后访问日期为2023-05-04，早于2023-05-05，符合条件；
• 用户B的最后访问日期为2023-05-05，不符合“次日未访问”要求，被排除。

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