如何使用SQL（Impala/Hive）按指定频率进行无聚合数据采样

需求说明 #

有一张包含Unix时间戳字段的数据表，数据频率不固定（大致每分钟一条），需要按自定义频率（例如15分钟）采样，每个时间窗口内最多保留一条数据，且保留的是离该窗口时间点最近的记录，不做聚合操作。

示例输入 #

row dt              dt_parsed           value
1   1684449236166   2023-05-18 22:33    241.0
2   1684449854529   2023-05-18 22:44    240.0
3   1684450070779   2023-05-18 22:47    240.0
4   1684450684766   2023-05-18 22:58    241.0
5   1684450868360   2023-05-18 23:01    241.0
6   1684451027112   2023-05-18 23:03    241.0
7   1684452361787   2023-05-18 23:26    241.0

期望输出（15分钟采样） #

row dt              dt_parsed           value
1   1684449236166   2023-05-18 22:33    241.0 --> 离22:30最近
2   1684449854529   2023-05-18 22:44    240.0 --> 离22:45最近
5   1684450868360   2023-05-18 23:01    241.0 --> 离23:00最近
6   1684451027112   2023-05-18 23:03    241.0 --> 离23:15最近
7   1684452361787   2023-05-18 23:26    241.0 --> 离23:30最近

实现方案 #

核心思路是：先将每条数据映射到对应的采样窗口时间，再计算每条数据与窗口时间的时间差，最后在每个窗口内筛选出时间差最小的记录。以下是具体SQL：

WITH sampled_data AS (
    SELECT 
        *,
        -- 将时间对齐到最近的15分钟窗口（单位：秒）
        from_unixtime(
            round(unix_timestamp(dt_parsed) / (15*60)) * (15*60)
        ) AS sample_window,
        -- 计算当前记录与窗口时间的时间差绝对值（毫秒）
        abs(
            dt - (round(unix_timestamp(dt_parsed) / (15*60)) * (15*60)*1000)
        ) AS time_diff_ms
    FROM your_table_name
),
ranked_data AS (
    SELECT 
        *,
        -- 按窗口分组，按时间差从小到大排序
        row_number() OVER (PARTITION BY sample_window ORDER BY time_diff_ms ASC) AS rn
    FROM sampled_data
)
SELECT row, dt, dt_parsed, value
FROM ranked_data
WHERE rn = 1
ORDER BY dt;

代码解释 #

1. 采样窗口计算：

   • unix_timestamp(dt_parsed) 将格式化时间转成秒级时间戳
   • 除以15*60（15分钟的秒数）后取整，再乘回15*60，得到最近的15分钟整点时间
   • from_unixtime 转成可读时间格式作为分组依据

2. 时间差计算：

   • 将窗口时间转成毫秒级（乘1000），与原Unix毫秒时间戳dt计算绝对值差，用于判断哪个记录离窗口最近

3. 筛选最近记录：

   • 使用row_number()窗口函数，按采样窗口分组，对每组内的记录按时间差升序排序
   • 取每组中排序为1的记录，即为该窗口内离采样点最近的数据

自定义频率调整 #

如果需要调整采样频率（比如5分钟、30分钟），只需修改15*60这个值：

• 5分钟：替换为5*60
• 30分钟：替换为30*60
• 1小时：替换为3600

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