BigQuery中如何计算时间戳平均间隔并实现访问量滚动求和

可以不嵌套CTE，直接在单条查询中实现连续会话平均时间差的计算。
你可以直接将计算相邻会话时间差的LAG()逻辑嵌套到AVG()窗口函数的参数中，无需先封装为子查询或CTE，参考写法如下：

SELECT 
    client_id, 
    visit_number, 
    SUM(hit_count) OVER (PARTITION BY client_id ORDER BY visit_number) AS cumulative_hit_count, 
    session_start_time - LAG(session_start_time) OVER (PARTITION by client_id ORDER BY visit_number) AS current_session_gap,
    -- 新增逻辑：计算同一用户下所有连续会话的平均时间差
    AVG(session_start_time - LAG(session_start_time) OVER (PARTITION by client_id ORDER BY visit_number)) 
        OVER (PARTITION BY client_id) AS avg_session_gap
FROM session_table

说明 #

• 首次会话的LAG()返回结果为NULL，AVG()计算时会自动跳过NULL值，不会拉低平均结果，符合业务逻辑预期
• 如果你需要计算全量所有用户的全局平均连续会话时间差，去掉AVG()窗口函数中的PARTITION BY client_id即可
• 该写法支持所有主流支持窗口函数的数据库，包括PostgreSQL 11+、MySQL 8+、SQL Server 2012+、BigQuery等

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