基于时间窗口，用Spark/Scala分组查询上次登录尝试时间

针对你需要按用户+设备分组，查询每个登录尝试的上一次登录尝试时间的需求，我结合你给出的示例数据集来梳理解决方案：

示例数据集 #

首先咱们先明确原始数据的结构（补全截断的记录）：

核心解决方案：使用SQL窗口函数LAG() #

这类分组内的“前一条记录”查询，最常用的就是SQL的LAG()窗口函数，它可以在同一分组内，获取当前行的前一行指定字段的值。

基础版：获取分组内的上一次尝试时间 #

下面的SQL会按username和device分组，对每个分组内的记录按登录时间排序，然后提取上一次登录的时间：

SELECT 
  username,
  device,
  attempt_at,
  stat,
  -- 提取同一用户+设备分组内的上一次登录尝试时间
  LAG(attempt_at) OVER (
    PARTITION BY username, device 
    ORDER BY attempt_at ASC
  ) AS previous_attempt_at
FROM login_attempts;

执行结果说明

以user1的pc设备为例：

• 时间最早的那条记录（2018-01-02 07:44:10）没有前一次尝试，所以previous_attempt_at为NULL
• 第二条记录（2018-01-02 07:44:27）的previous_attempt_at就是前一次的登录时间2018-01-02 07:44:10

进阶版：限定时间窗口内的上一次尝试 #

如果需要只统计当前登录尝试N小时/天内的前一次尝试，可以在窗口函数中添加时间范围限制：

比如只查询当前记录前1小时内的上一次登录尝试：

SELECT 
  username,
  device,
  attempt_at,
  stat,
  LAG(attempt_at) OVER (
    PARTITION BY username, device 
    ORDER BY attempt_at ASC
    -- 限定范围：当前记录的前1小时到当前记录
    RANGE BETWEEN INTERVAL '1 HOUR' PRECEDING AND CURRENT ROW
  ) AS previous_attempt_within_1hour
FROM login_attempts;

执行结果说明

以user2的iphone设备为例：

• 第三条记录（2017-12-23 17:05:12）和前一条记录（16:58:30）间隔超过1小时，所以previous_attempt_within_1hour为NULL
• 前两条记录间隔22分钟，在1小时内，所以第二条记录的previous_attempt_within_1hour会是第一条的时间

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