SparkSQL 2.3.1实现过去1小时每15分钟请求数统计（取最近3区间）

需求说明 #

统计过去1小时内每15分钟收到的请求数量，返回最近的3个15分钟时间区间，使用SparkSQL 2.3.1版本。

数据样例 #

log_date, time
2022-11-18, 2022-11-18 17:00:00 UTC
2022-11-18, 2022-11-18 17:10:00 UTC
2022-11-18, 2022-11-18 17:20:00 UTC
2022-11-18, 2022-11-18 17:40:00 UTC

原查询问题分析 #

原查询存在以下关键问题：

• 时间筛选错误：原条件筛选的是前一天及以后的数据，不符合“过去1小时”的需求
• 分组逻辑错误：按time分组会将每个单独时间点作为一组，无法聚合15分钟区间内的请求
• 区间格式不符合要求：返回的intervals是数值型时间戳分段，不是期望的格式化时间区间
• 排序取数逻辑不准确：无法确保获取到最近的3个15分钟区间

修正后的SparkSQL查询 #

SELECT 
    log_date,
    from_unixtime(FLOOR(unix_timestamp(time) / (15 * 60)) * (15 * 60), 'yyyy-MM-dd HH:mm:ss UTC') AS time_interval,
    COUNT(*) AS count
FROM data_log
WHERE 
    time >= date_sub(current_timestamp(), INTERVAL 1 HOUR)
    AND requests_recieved = true
GROUP BY log_date, time_interval
ORDER BY time_interval DESC
LIMIT 3

语句说明 #

1. 时间筛选：用time >= date_sub(current_timestamp(), INTERVAL 1 HOUR)精准筛选过去1小时内的数据，替代原有的日期筛选逻辑
2. 15分钟区间生成：
   • 将time转换为时间戳unix_timestamp(time)
   • 除以15分钟的秒数（15*60）后取整，得到该时间所属的15分钟区间编号
   • 再乘以15*60还原为区间起始时间戳，最后用from_unixtime格式化为指定时间格式
3. 分组统计：按log_date和生成的time_interval分组，统计每个区间的请求数
4. 排序取数：按区间时间降序排序，取前3个结果，确保返回最近的3个15分钟区间

期望输出 #

log_date, time_interval, count
2022-11-18, 2022-11-18 17:30:00 UTC, 1
2022-11-18, 2022-11-18 17:15:00 UTC, 1
2022-11-18, 2022-11-18 17:00:00 UTC, 2

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