sql - 在 BigQuery 中查找重叠的时间段

Question

假设我在 BigQuery 中的数据结构如下:

WITH session_log AS (
  SELECT 'ABC' as site_id, 1234 user_id, 12 session_id, '2020-02-10 00:29:59.376000 UTC' start_time, '2020-02-10 01:13:02.817000 UTC' end_time UNION ALL
  SELECT 'ABC' as site_id, 1234 user_id, 13 session_id, '2020-02-10 02:41:56.330000 UTC' start_time, '2020-02-10 02:41:56.389999 UTC' end_time UNION ALL
  SELECT 'ABC' as site_id, 1234 user_id, 14 session_id, '2020-02-10 04:24:46.649999 UTC' start_time, '2020-02-10 05:14:08.243000 UTC' end_time UNION ALL
  SELECT 'ABC' as site_id, 1234 user_id, 15 session_id, '2020-02-10 04:59:21.356999 UTC' start_time, '2020-02-10 15:57:11.501000 UTC' end_time  
  SELECT 'ABC' as site_id, 6789 user_id, 25 session_id, '2020-02-10 02:15:38.560000 UTC' start_time, '2020-02-10 02:56:38.784500 UTC' end_time UNION ALL
  SELECT 'ABC' as site_id, 6789 user_id, 26 session_id, '2020-02-10 04:59:21.356999 UTC' start_time, '2020-02-10 15:57:11.501000 UTC' end_time
)
SELECT site_id, user_id, session_id, start_time, end_time FROM session_log

我要查询session_log这样，如果存在具有相同 site_id 的另一条记录，则 session 会被标记为“并发”。和user_id其时间范围与另一个 session 重叠。 (这里的意思是用户同时在多个设备上访问同一网站。)

理想情况下，我需要一个能够生成以下内容的查询，因为 session 14 和 15 的至少一部分对于该特定用户是重叠的。 session 26 与 session 14 和 15 重叠，但不是并发的，因为它是不同的 user_id .

<表类=“s-表”><标题>site_iduser_idsession_id开始时间结束时间并发 session <正文>ABC1234122020-02-10 00:29:59.376000 世界标准时间2020-02-10 01:13:02.817000 世界标准时间假ABC1234132020-02-10 02:41:56.330000 世界标准时间2020-02-10 02:41:56.389999 世界标准时间假ABC123414 2020-02-10 04:24:46.649999 UTC 2020-02-10 05:14:08.243000 UTC 真实ABC123415 2020-02-10 04:59:21.356999 UTC 2020-02-10 05:57:11.501000 UTC 真实ABC6789252020-02-10 02:15:38.560000 世界标准时间2020-02-10 02:56:38.784500 世界标准时间假ABC6789262020-02-10 04:44:21.356999 世界标准时间2020-02-10 06:57:11.501000 世界标准时间假

我尝试创建一个用户定义的函数，该函数将在表中搜索具有相同 site_id 的 session 。和user_id但不是session_id时间重叠，但它失败得很惨。我几乎不好意思把这个放在这里，但是......到底是什么。

CREATE TEMPORARY FUNCTION getConcurrentSessions(_site_id STRING, _user_id INT64, _session_id INT64, _start_time TIMESTAMP, _end_time TIMESTAMP)
AS 
(
    (
        SELECT count(session_id)
        FROM `session_log`
        WHERE site_id = _site_id
        AND user_id = _user_id
        AND session_id != _session_id
        AND (
            (_start_time BETWEEN start_time AND end_time)
            OR
            (_end_time BETWEEN start_time AND end_time)
        )
    )
);
SELECT site_id, user_id, session_id, start_time, end_time,
IF (
  getConcurrentSessions(site_id, user_id, session_id, start_time, end_time) > 0,
  TRUE,
  FALSE
) AS concurrent_sessions 
FROM session_log

任何和所有的建议表示赞赏。谢谢。

Answer 1

考虑以下方法

select *, 
  ifnull(start_time <= lag(end_time) over win or 
  end_time >= lead(start_time) over win, false) as concurrent_session 
from your_table
window win as (partition by site_id, user_id order by start_time)         

如果应用于您问题中的样本数据 - 输出为