如何优化统计存在两次交易间隔≤7天的用户数的SQL语句

现有逻辑疏漏 #

• 仅筛选了交易次数≥2的用户，完全没有对「相邻交易间隔在7天以内」的核心规则做判断，统计结果是所有多交易用户，和需求不符
• 仅查询了2017年8月1日单日的会话表，单日范围内的交易不存在跨7天的对比可能，统计范围严重缺失
• 没有提取交易发生的时间字段，根本无法计算交易间隔

优化后SQL代码 #

WITH user_transactions AS (
  -- 提取所有用户的有效交易记录，转换得到交易日期
  SELECT DISTINCT
    fullvisitorid,
    DATE(TIMESTAMP_SECONDS(visitStartTime + CAST(hits.time/1000 AS INT64))) AS transaction_date
  FROM
    `bigquery-public-data.google_analytics_sample.ga_sessions_*`, -- 用通配符匹配所有日期的会话表
    UNNEST(hits) AS hits
  WHERE
    hits.TRANSACTION.transactionid IS NOT NULL -- 过滤非交易的hit
),
transaction_intervals AS (
  -- 计算每个用户相邻两笔交易的日期间隔
  SELECT
    fullvisitorid,
    transaction_date,
    LAG(transaction_date) OVER (PARTITION BY fullvisitorid ORDER BY transaction_date) AS prev_trans_date
  FROM user_transactions
)
-- 统计至少存在一次相邻交易间隔≤7天的去重用户数
SELECT COUNT(DISTINCT fullvisitorid) AS number_of_users_matching_crit
FROM transaction_intervals
WHERE DATE_DIFF(transaction_date, prev_trans_date, DAY) <= 7

逻辑说明 #

1. 第一个CTE先拉取全量日期的会话数据，提取所有带有效交易ID的记录，转换得到每笔交易的实际发生日期，对同一个用户同一天的多笔交易做去重，避免同一天的多笔交易被重复计算间隔
2. 第二个CTE用LAG窗口函数，按用户ID分组、交易日期升序排序，获取每个用户上一笔交易的发生日期
3. 最后筛选出相邻交易间隔≤7天的记录，对用户ID去重后统计总数，得到符合需求的结果

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