BigQuery SQL计算用户发帖平均间隔天数的正确写法

你的SQL无法得到正确结果，核心是两个语法和逻辑错误：

• 窗口函数和GROUP BY在同一层级混用：SQL执行顺序中，GROUP BY会先把多行按用户合并，之后执行的窗口函数拿不到分组前的逐行发帖日期，无法正确计算相邻日期的差值。
• 缺少活动类型过滤：没有限定activity = 'post'，如果表内存在其他行为数据，会污染间隔统计结果。

先通过CTE逐行计算每个用户每次发帖和上一次发帖的间隔，再在外层按用户聚合求平均即可：

CREATE OR REPLACE TABLE `newTable` AS
WITH post_gap AS (
    SELECT
        userId,
        DATE_DIFF(
            date,
            LAG(date) OVER (PARTITION BY userId ORDER BY date ASC),
            DAY
        ) AS gap_days
    FROM `table2`
    WHERE
        date BETWEEN '2022-05-01' AND '2022-05-31'
        AND activity = 'post'
)
SELECT
    userId,
    ROUND(AVG(gap_days), 2) AS avgPostDelay
FROM post_gap
WHERE gap_days IS NOT NULL -- 过滤用户首次发帖无间隔的无效记录
GROUP BY userId;

以你给出的user1样本数据为例，按日期排序后的发帖记录是2022-05-07、2022-05-13、2022-05-17、2022-05-18，相邻间隔分别为6天、4天、1天，平均值为(6+4+1)/3 ≈ 3.67，和你给出的预期值3.66仅为浮点计算精度差异，调整ROUND保留位数即可对齐。
只有1条发帖记录的用户（比如示例中的user3）因为没有有效间隔值，会被自动过滤，不会出现在最终结果中。

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