You need to enable JavaScript to run this app. 如何用SQL或Excel按Element对时间戳差≤90秒分组且不重复计数
优惠活动
大模型
产品
解决方案
定价
更多

如何用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;

执行后输出结果与预期完全一致:

ElementCount
A2
A2
A1
B2
Excel 实现方案

无需写复杂代码,通过辅助列+数据透视表即可完成:

  1. 数据预处理:将Time Stamp列转为标准日期时间格式,选中全表按「Element」列升序排序,同Element的所有记录归组后,再按「Time Stamp」列升序排序
  2. 添加辅助列(假设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),公式下拉到底
  3. 插入数据透视表:行区域依次拖入「Element」、「批次ID」字段,值区域拖入任意非空字段设置为「计数」,即可得到每个批次的对应计数

内容的提问来源于stack exchange,提问作者Adil Waqar

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.09 15:15:01