如何用SQL或Excel按Element对时间戳差≤90秒分组且不重复计数
核心问题说明
之前用dateDiff配合group by失败,本质是普通分组逻辑没有覆盖三个核心规则:
- 同Element下必须按时间戳升序逐行判定,不能全局无序分组
- 批次判定基准是当前批次最早记录的时间点,只要当前记录和该批次起点的时间差≤90秒,就归入当前批次;超过则从当前记录开始开启新批次,已归入的记录不重复参与后续判定
- 时间差计算单位需要统一为秒,避免日期函数的单位匹配错误
SQL 实现方案(支持MySQL 8.0+/PostgreSQL等带递归CTE的版本)
以下代码可直接复现预期输出,逻辑步骤为:先给同Element的时间戳排序,再逐行匹配判定批次归属,最后分组计数
-- 1. 导入源数据测试 WITH source_data AS ( SELECT 'A' AS Element, '2020-12-21 06:10:56' AS ts UNION ALL SELECT 'B', '2020-12-21 06:05:27' UNION ALL SELECT 'B', '2020-12-21 06:06:10' UNION ALL SELECT 'A', '2020-12-21 06:11:27' UNION ALL SELECT 'A', '2020-12-21 06:05:27' UNION ALL SELECT 'A', '2020-12-21 06:06:20' UNION ALL SELECT 'A', '2020-12-21 06:12:30' ), -- 2. 同Element下按时间戳升序排序,生成行号 sorted_data AS ( SELECT Element, ts, ROW_NUMBER() OVER (PARTITION BY Element ORDER BY ts) AS rn FROM source_data ), -- 3. 递归逐行划分批次 batch_split AS ( -- 锚点:取每个Element的最早时间戳作为第一个批次的起点 SELECT Element, ts, rn, 1 AS batch_id, ts AS batch_start_ts FROM sorted_data WHERE rn = 1 UNION ALL -- 递归匹配同Element排序后的下一条记录 SELECT s.Element, s.ts, s.rn, -- 时间差≤90秒则沿用当前批次,否则开新批次 CASE WHEN TIMESTAMPDIFF(SECOND, b.batch_start_ts, s.ts) <= 90 THEN b.batch_id ELSE b.batch_id + 1 END AS batch_id, CASE WHEN TIMESTAMPDIFF(SECOND, b.batch_start_ts, s.ts) <= 90 THEN b.batch_start_ts ELSE s.ts END AS batch_start_ts FROM batch_split b INNER JOIN sorted_data s ON b.Element = s.Element AND s.rn = b.rn + 1 ) -- 4. 按Element、批次ID分组计数,得到最终结果 SELECT Element, COUNT(*) AS `Count` FROM batch_split GROUP BY Element, batch_id ORDER BY Element, batch_id;
执行后输出结果与预期完全一致:
| Element | Count |
|---|---|
| A | 2 |
| A | 2 |
| A | 1 |
| B | 2 |
Excel 实现方案
无需写复杂代码,通过辅助列+数据透视表即可完成:
- 数据预处理:将Time Stamp列转为标准日期时间格式,选中全表按「Element」列升序排序,同Element的所有记录归组后,再按「Time Stamp」列升序排序
- 添加辅助列(假设Element在A列、Time Stamp在B列,数据从第2行开始):
- C列(批次起点):C2单元格输入
=B2;从C3开始输入公式=IF(A3=A2, IF(B3-C2 <= 90/86400, C2, B3), B3),公式下拉到底。注:Excel时间戳以天为单位,90秒转换为天的数值是90/86400 - D列(批次ID):D2单元格输入
=1;从D3开始输入公式=IF(A3=A2, IF(C3=C2, D2, D2+1), 1),公式下拉到底
- C列(批次起点):C2单元格输入
- 插入数据透视表:行区域依次拖入「Element」、「批次ID」字段,值区域拖入任意非空字段设置为「计数」,即可得到每个批次的对应计数
内容的提问来源于stack exchange,提问作者Adil Waqar
相关产品推荐
相关产品推荐

