时间戳

首页 > 解决方案 > 计算 BigQuery 中时间戳之间的平均延迟

sql - 计算 BigQuery 中时间戳之间的平均延迟

问题描述

我的数据大致有以下结构：

client_id | visit_number | session_start_time | hit_count

我目前正在使用：

SELECT client_id, visit_number, SUM(hit_count) OVER (PARTITION BY client_id ORDER BY visit_number), 
session_start_time - LAG(session_start_time) OVER (PARTITION by client_id ORDER BY visit_number)
FROM session_table

理想情况下，我希望获得客户点击次数的滚动总和（这似乎工作正常）。连续会话之间的平均增量也很方便。希望我为当前会话计算一个增量的方法是正确的，但我不确定计算平均增量的合理方法。

一个想法是将上面的查询包装到 CTE 中，然后在另一个窗口函数中计算平均值，但我相信它可以在一个查询中完成。

标签： sqlgoogle-bigquerywindow-functions

解决方案

如果您想要会话之间的平均时间到本次会话，那么您可以通过将当前时间减去第一次并除以小于会话数的时间来计算它：

SELECT client_id, visit_number,
       SUM(hit_count) OVER (PARTITION BY client_id ORDER BY visit_number), 
       session_start_time - LAG(session_start_time) OVER (PARTITION by client_id ORDER BY visit_number) as delta,
       (session_start_time - 
        MIN(session_start_time) OVER (PARTITION by client_id)
       ) / NULLIF(ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY session_start_time) - 1, 0) as avg_delta       
FROM session_table;

推荐阅读