如何在BigQuery中检测10分钟间隔内交易数是否≥3？

检测各商家是否存在10分钟间隔内交易数超过3的情况（返回true/false）。

源数据 #

SELECT 1 AS transaction_id, 2 AS business_id,  '2023-01-16 14:30:00' as transaction_date UNION ALL
SELECT 2, 3 ,  '2023-01-16 14:30:00'UNION ALL
SELECT 3, 3 ,  '2023-01-16 14:32:00'UNION ALL
SELECT 4, 3 ,  '2023-01-16 14:33:00'UNION ALL
SELECT 5, 2 ,  '2023-01-16 14:41:00'UNION ALL
SELECT 5, 2 ,  '2023-01-16 14:45:00'UNION ALL
SELECT 6, 2 ,  '2023-01-16 15:01:00'UNION ALL
SELECT 7, 1 ,  '2023-01-16 15:41:00'UNION ALL
SELECT 7, 1 ,  '2023-01-16 15:43:00'UNION ALL
SELECT 7, 1 ,  '2023-01-16 15:46:00'UNION ALL
SELECT 8, 1,  '2023-01-16 17:30:00' 

期望输出 #

尝试与困惑 #

曾尝试用GENERATE_TIMESTAMP_ARRAY生成时间数组，但不知道如何后续检查所有10分钟间隔，求BigQuery中的实现方法。

可以利用窗口函数结合时间范围统计每个交易时间点前后10分钟内的交易数量，再判断商家是否符合条件，具体实现如下：

完整SQL语句 #

WITH sorted_transactions AS (
  SELECT 
    business_id,
    transaction_date,
    -- 去重重复的交易ID（源数据存在重复transaction_id）
    DISTINCT transaction_id
  FROM (
    -- 这里替换成你的实际表，或者直接使用源数据
    SELECT 1 AS transaction_id, 2 AS business_id,  '2023-01-16 14:30:00' as transaction_date UNION ALL
    SELECT 2, 3 ,  '2023-01-16 14:30:00'UNION ALL
    SELECT 3, 3 ,  '2023-01-16 14:32:00'UNION ALL
    SELECT 4, 3 ,  '2023-01-16 14:33:00'UNION ALL
    SELECT 5, 2 ,  '2023-01-16 14:41:00'UNION ALL
    SELECT 5, 2 ,  '2023-01-16 14:45:00'UNION ALL
    SELECT 6, 2 ,  '2023-01-16 15:01:00'UNION ALL
    SELECT 7, 1 ,  '2023-01-16 15:41:00'UNION ALL
    SELECT 7, 1 ,  '2023-01-16 15:43:00'UNION ALL
    SELECT 7, 1 ,  '2023-01-16 15:46:00'UNION ALL
    SELECT 8, 1,  '2023-01-16 17:30:00' 
  )
  ORDER BY business_id, transaction_date
),
transaction_counts AS (
  SELECT
    business_id,
    transaction_date,
    -- 统计当前交易时间往前10分钟内的交易数量
    COUNT(transaction_id) OVER (
      PARTITION BY business_id
      ORDER BY UNIX_SECONDS(transaction_date)
      RANGE BETWEEN 600 PRECEDING AND CURRENT ROW -- 600秒=10分钟
    ) AS transactions_in_last_10min
  FROM sorted_transactions
)
SELECT
  business_id,
  -- 判断该商家是否存在任意10分钟间隔内交易数≥3的情况
  LOGICAL_OR(transactions_in_last_10min >= 3) AS 3_or_more_transactions_in_10_minutes
FROM transaction_counts
GROUP BY business_id
ORDER BY business_id;

逻辑说明 #

1. 数据预处理：先对交易数据按商家ID和交易时间排序，同时去重重复的交易ID，避免重复统计。
2. 时间窗口统计：通过COUNT()窗口函数，基于UNIX时间戳的范围（600秒即10分钟），计算每个交易时间点对应的10分钟内交易数。
3. 结果判断：用LOGICAL_OR()聚合函数，只要商家存在任意一个时间点的10分钟交易数≥3，就返回true，否则返回false。

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