如何用SQL查找包含客户端连接时段的会话OID？

需求说明：客户端连接表C与会话表S无直接关联，需为每个C.ID匹配唯一OID，要求C的CONNECT到DISCONNECT时段完全落在该OID的整体START与STOP范围内。

示例表结构 #

表C（客户端连接表） #

ID      CONNECT         DISCONNECT
  1  2022-09-01T00:07  2022-09-01T00:25

表S（会话表） #

ID OID       START            FINISH        STATE
  1   1  2022-09-01T00:00  2022-09-01T00:02   STOP
  2   2  2022-09-01T00:05  2022-09-01T00:08  PAUSE
  3   2  2022-09-01T00:10  2022-09-01T00:18  PAUSE
  4   2  2022-09-01T00:25  2022-09-01T00:38   STOP
  5   5  2022-09-01T00:45  2022-09-01T00:58   STOP

处理步骤与SQL实现 #

1. 聚合会话表S得到S' #

先按OID分组，提取每个OID的最早启动时间、最晚结束时间，同时保留最终的STOP状态：

WITH s_agg AS (
    SELECT 
        OID,
        MIN(START) AS START,
        MAX(FINISH) AS STOP,
        MAX(CASE WHEN STATE = 'STOP' THEN STATE END) AS STATE
    FROM S
    GROUP BY OID
)

2. 关联表C与聚合后的S' #

将表C和聚合后的s_agg关联，筛选出满足时间范围条件的记录：

SELECT 
    C.ID,
    s_agg.OID,
    C.CONNECT,
    C.DISCONNECT,
    s_agg.START AS OID_START,
    s_agg.STOP AS OID_STOP
FROM C
JOIN s_agg 
    ON s_agg.START <= C.CONNECT 
    AND s_agg.STOP >= C.DISCONNECT;

完整SQL语句 #

整合后的完整代码如下：

WITH s_agg AS (
    SELECT 
        OID,
        MIN(START) AS START,
        MAX(FINISH) AS STOP,
        MAX(CASE WHEN STATE = 'STOP' THEN STATE END) AS STATE
    FROM S
    GROUP BY OID
)
SELECT 
    C.ID,
    s_agg.OID,
    C.CONNECT,
    C.DISCONNECT,
    s_agg.START AS OID_START,
    s_agg.STOP AS OID_STOP
FROM C
JOIN s_agg 
    ON s_agg.START <= C.CONNECT 
    AND s_agg.STOP >= C.DISCONNECT;

执行后会得到匹配结果，示例输出为：

ID OID      CONNECT         DISCONNECT    OID_START      OID_STOP
  1   2  2022-09-01T00:07  2022-09-01T00:25 2022-09-01T00:05 2022-09-01T00:38

内容的提问来源于stack exchange，提问作者Jan Nielsen