Google Spreadsheet/BigQuery/DataStudio同ID相邻扫描时间差计算方案

1. BigQuery场景实现方案 #

核心使用LAG窗口函数实现同ID相邻行时间戳提取，完整视图创建逻辑如下：

CREATE OR REPLACE VIEW `你的项目名.你的数据集名.laps_with_preceding_ts` AS
SELECT
  Timestamp,
  `QRcode Read` AS ID,
  -- 按ID分组，组内按扫描时间升序排序，取前一行的时间戳
  LAG(Timestamp) OVER (
    PARTITION BY `QRcode Read`
    ORDER BY Timestamp ASC
  ) AS Preceding_Timestamp
FROM
  `你的项目名.你的数据集名.LAPS`

逻辑说明：同一ID的第一条扫描记录没有前序时间，Preceding_Timestamp返回NULL，属于正常情况。

2. Google Spreadsheet场景实现方案 #

假设表格结构为：A列表头为Timestamp、B列表头为ID，需要在C列生成Preceding Timestamp。在C2单元格输入以下ArrayFormula公式，无需逐行拖拽即可自动计算所有行：

=ARRAYFORMULA(IF(A2:A="",,LAG(A2:A) OVER (PARTITION BY B2:B ORDER BY A2:A)))

逻辑说明：空行自动返回空值，仅对有有效扫描记录的行计算同ID上一条扫描时间。

3. Google DataStudio场景实现方案 #

按以下步骤配置自定义阈值滑块即可：

• 第一步：预计算间隔字段：优先在BigQuery侧视图中新增scan_interval_sec字段，公式为TIMESTAMP_DIFF(Timestamp, Preceding_Timestamp, SECOND)，也可直接在DataStudio数据源中创建同名计算字段，公式保持一致。
• 第二步：添加滑块控件：进入DataStudio报表编辑页，点击顶部菜单栏「添加控件」→「滑块」，将滑块的控制字段绑定为scan_interval_sec，可按需设置默认值区间（如默认最小阈值设为10秒，即间隔小于10秒判定为误扫）。
• 第三步：配置过滤规则：给所有需要展示有效记录的图表添加过滤条件：scan_interval_sec >= 滑块值 OR scan_interval_sec IS NULL，其中NULL值对应每个ID的第一条扫描记录，默认判定为有效。

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