时间戳

首页 > 解决方案 > 如何使用 BigQuery 将 date_diff 用于两个相邻的会话？

sql - 如何使用 BigQuery 将 date_diff 用于两个相邻的会话？

问题描述

我正在尝试使用下表中的数据计算两个相邻会话之间的平均小时数：

用户身份 event_timestamp session_num
一个 2021-04-16 10:00:00.000 UTC 1
一个 2021-04-16 11:00:00.000 UTC 2
一个 2021-04-16 13:00:00.000 UTC 3
一个 2021-04-16 16:00:00.000 UTC 4
乙 2021-04-16 12:00:00.000 UTC 1
乙 2021-04-16 14:00:00.000 UTC 2
乙 2021-04-16 19:00:00.000 UTC 3
C 2021-04-16 10:00:00.000 UTC 1
C 2021-04-16 17:00:00.000 UTC 2
C 2021-04-16 18:00:00.000 UTC 3

所以，对于用户 A，我们有

1 hour  between session_num = 2 and session_num = 1,
2 hours between session_num = 3 and session_num = 2,
3 hours between session_num = 4 and session_num = 3.

其他用户也一样：

2, 5用户 B 的小时数；

7, 1用户 C 的小时数。

我期望得到的结果应该是这个 date_diff(HOUR) 的算术平均值。

因此，avg(1,2,3,2,5,7,1)= 3 小时是两个相邻会话之间的平均时间。

任何人都知道可以使用什么查询，以便 date_diff 函数仅适用于相邻会话？

标签： sqlgoogle-bigquerydatediffdate-difference

解决方案

给定用户的会话之间的平均小时数最简单地计算为：

select user_id,
       timestamp_diff(max(event_timestamp), min(event_timestamp), hour) * 1.0 / nullif(count(*) - 1, 0)
from t
group by user_id;

也就是说，用户会话之间的平均时间是最大时间戳减去最小时间戳除以会话数减一。

推荐阅读