如何简化SQL查询，识别连续活跃≥4小时的IP？

云上老周

2026-6-13

问题描述

现有一张包含ip、datetime、year、month、day、country、region、seen_time字段的数据表，单个IP在同一小时内可能存在多条记录。需要识别出连续活跃至少4小时的IP，期望输出包含原表字段（含hour）及标识字段is_active_min_4hr，用于标记该IP是否处于连续4小时及以上的活跃时段。

当前已构思多步骤实现方案：

提取唯一的ip与hour

CREATE OR REPLACE TEMP VIEW step_1 AS
SELECT distinct ip,hour
FROM input_table

计算行号

CREATE OR REPLACE TEMP VIEW step_2 AS
SELECT ip,hour,
       ROW_NUMBER() OVER (PARTITION BY ip ORDER BY hour) AS rn
FROM step_1

计算分组标识grp

CREATE OR REPLACE TEMP VIEW step_3 AS
SELECT ip, (hour - rn) AS grp
FROM step_2

筛选连续活跃≥4小时的IP时段

CREATE OR REPLACE TEMP VIEW step_4 AS
SELECT ip, MIN(hour) AS start_hour, MAX(hour) AS end_hour
FROM step_3
GROUP BY ip, grp
HAVING COUNT(*) >= 4

关联原表获取完整数据

现询问是否可通过单条SQL查询或更简便的方式实现该需求？

输入数据示例

%sql
WITH input_table AS (
  SELECT * FROM VALUES
    ('192.168.1.1',  TIMESTAMP('2025-05-26 01:15:00'), 2025, 5, 26, 'US', 'California', 15, 1),
    ('192.168.1.1',  TIMESTAMP('2025-05-26 02:10:00'), 2025, 5, 26, 'US', 'California', 10, 2),
    ('192.168.1.1',  TIMESTAMP('2025-05-26 03:20:00'), 2025, 5, 26, 'US', 'California', 20, 3),
    ('192.168.1.1',  TIMESTAMP('2025-05-26 04:25:00'), 2025, 5, 26, 'US', 'California', 25, 4),
    ('192.168.1.1',  TIMESTAMP('2025-05-26 07:05:00'), 2025, 5, 26, 'US', 'California', 5, 7),
    ('10.0.0.2',     TIMESTAMP('2025-05-26 01:00:00'), 2025, 5, 26, 'US', 'Texas',      12, 1),
    ('10.0.0.2',     TIMESTAMP('2025-05-26 03:00:00'), 2025, 5, 26, 'US', 'Texas',      14, 3),
    ('10.0.0.2',     TIMESTAMP('2025-05-26 04:00:00'), 2025, 5, 26, 'US', 'Texas',      8, 4),
    ('172.16.0.5',   TIMESTAMP('2025-05-26 10:00:00'), 2025, 5, 26, 'UA', 'Kyiv',  6, 10),
    ('172.16.0.5',   TIMESTAMP('2025-05-26 11:00:00'), 2025, 5, 26, 'UA', 'Kyiv',  9, 11),
    ('172.16.0.5',   TIMESTAMP('2025-05-26 12:00:00'), 2025, 5, 26, 'UA', 'Kyiv',  15, 12),
    ('172.16.0.5',   TIMESTAMP('2025-05-26 13:00:00'), 2025, 5, 26, 'UA', 'Kyiv',  11, 13),
    ('172.16.0.5',   TIMESTAMP('2025-05-26 14:00:00'), 2025, 5, 26, 'UA', 'Kyiv',  10, 14)
  AS input_table(ip, datetime, year, month, day, country, region, seen_time, hour)
)
SELECT * FROM input_table;

预期输出

在输入数据基础上添加is_active_min_4hr字段，第4、12、13条记录该字段值为TRUE，其余为FALSE：

ip           year  month day country region     seen_time hour  is_active_min_4hr
192.168.1.1  2025  5     26  US      California 15        1     FALSE
192.168.1.1  2025  5     26  US      California 10        2     FALSE
192.168.1.1  2025  5     26  US      California 20        3     FALSE
192.168.1.1  2025  5     26  US      California 25        4     TRUE
192.168.1.1  2025  5     26  US      California 5         7     FALSE
10.0.0.2     2025  5     26  US      Texas      12        1     FALSE
10.0.0.2     2025  5     26  US      Texas      14        3     FALSE
10.0.0.2     2025  5     26  US      Texas      8         4     FALSE
172.16.0.5   2025  5     26  UA      Kyiv       6         10    FALSE
172.16.0.5   2025  5     26  UA      Kyiv       9         11    FALSE
172.16.0.5   2025  5     26  UA      Kyiv       15        12    FALSE
172.16.0.5   2025  5     26  UA      Kyiv       11        13    TRUE
172.16.0.5   2025  5     26  UA      Kyiv       10        14    TRUE

解决方案

可以将原有多步骤逻辑合并为单条SQL，通过嵌套CTE（公共表表达式）实现，无需创建多个临时视图，逻辑更紧凑：

WITH input_table AS (
  SELECT * FROM VALUES
    ('192.168.1.1',  TIMESTAMP('2025-05-26 01:15:00'), 2025, 5, 26, 'US', 'California', 15, 1),
    ('192.168.1.1',  TIMESTAMP('2025-05-26 02:10:00'), 2025, 5, 26, 'US', 'California', 10, 2),
    ('192.168.1.1',  TIMESTAMP('2025-05-26 03:20:00'), 2025, 5, 26, 'US', 'California', 20, 3),
    ('192.168.1.1',  TIMESTAMP('2025-05-26 04:25:00'), 2025, 5, 26, 'US', 'California', 25, 4),
    ('192.168.1.1',  TIMESTAMP('2025-05-26 07:05:00'), 2025, 5, 26, 'US', 'California', 5, 7),
    ('10.0.0.2',     TIMESTAMP('2025-05-26 01:00:00'), 2025, 5, 26, 'US', 'Texas',      12, 1),
    ('10.0.0.2',     TIMESTAMP('2025-05-26 03:00:00'), 2025, 5, 26, 'US', 'Texas',      14, 3),
    ('10.0.0.2',     TIMESTAMP('2025-05-26 04:00:00'), 2025, 5, 26, 'US', 'Texas',      8, 4),
    ('172.16.0.5',   TIMESTAMP('2025-05-26 10:00:00'), 2025, 5, 26, 'UA', 'Kyiv',  6, 10),
    ('172.16.0.5',   TIMESTAMP('2025-05-26 11:00:00'), 2025, 5, 26, 'UA', 'Kyiv',  9, 11),
    ('172.16.0.5',   TIMESTAMP('2025-05-26 12:00:00'), 2025, 5, 26, 'UA', 'Kyiv',  15, 12),
    ('172.16.0.5',   TIMESTAMP('2025-05-26 13:00:00'), 2025, 5, 26, 'UA', 'Kyiv',  11, 13),
    ('172.16.0.5',   TIMESTAMP('2025-05-26 14:00:00'), 2025, 5, 26, 'UA', 'Kyiv',  10, 14)
  AS input_table(ip, datetime, year, month, day, country, region, seen_time, hour)
),
ip_hour_groups AS (
  SELECT 
    ip, 
    hour,
    hour - ROW_NUMBER() OVER (PARTITION BY ip ORDER BY hour) AS grp
  FROM (SELECT DISTINCT ip, hour FROM input_table)
),
active_periods AS (
  SELECT 
    ip, 
    MIN(hour) AS start_hour, 
    MAX(hour) AS end_hour
  FROM ip_hour_groups
  GROUP BY ip, grp
  HAVING COUNT(*) >=4
)
SELECT 
  t.ip,
  t.year,
  t.month,
  t.day,
  t.country,
  t.region,
  t.seen_time,
  t.hour,
  CASE 
    WHEN EXISTS (
      SELECT 1 FROM active_periods ap 
      WHERE ap.ip = t.ip 
      AND t.hour BETWEEN ap.start_hour AND ap.end_hour
      AND (t.hour - ap.start_hour + 1) >=4
    ) THEN TRUE
    ELSE FALSE
  END AS is_active_min_4hr
FROM input_table t
ORDER BY t.ip, t.hour;

逻辑说明

保留核心识别逻辑：用hour - ROW_NUMBER()生成连续时段的分组标识，这是识别连续小时的经典技巧；
将所有步骤嵌套在CTE中，简化执行流程，避免创建多个临时视图；
通过(t.hour - ap.start_hour +1) >=4判断当前小时是否属于连续时段的第4个及以后的小时，精准匹配预期输出的标记规则；
最终按IP和小时排序，保证结果顺序清晰。

执行该SQL后，输出结果将完全符合预期要求。

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

如何简化SQL查询，识别连续活跃≥4小时的IP？

输入数据示例 #

预期输出 #

逻辑说明 #

输入数据示例

预期输出

逻辑说明