时间戳

首页 > 解决方案 > 如何合并（分组）属于同一会话的行

postgresql - 如何合并（分组）属于同一会话的行

问题描述

访客可以OPEN进入房屋的前门，然后ENTER进入房屋中的几个房间。做完这一切，他将OPEN再次踏上前门，离开家。这给出了以下示例数据：

13:00 John  OPEN
13:00 John  ENTER Hall
13:30 John  ENTER Kitchen
13:45 John  ENTER Living room
14:00 John  OPEN
13:30 Steve OPEN
13:30 Steve ENTER Hall
13:40 Steve ENTER Stairs
14:00 Steve ENTER Bed room
16:00 Steve ENTER Stairs
16:10 Steve OPEN

所以换句话说，我们总是有一个单一的OPEN条目，然后是一个或多个ENTER条目，以一个单一的OPEN条目结束。此外，多个访客可以同时在房子里，他们可以多次访问房子，完全没有限制。

OPEN让我们定义一个从到的序列OPEN作为一个会话。我现在想为每个包含所有发生事件的会话创建一行，如下所示：

[13:00,14:00) John  (13:00,Hall),(13:30,Kitchen),(13:45,Living room)
[13:30,16:10) Steve (13:30,Hall),(13:40,Stairs),(14:00,Bed room),(16:00,Stairs)

如何有效地做到这一点？

我有一个n^2可行的解决方案，它首先获取ENTER每个会话的第一个和最后一个（使用窗口函数lead，lag然后与前一个进行比较），然后在外部循环中搜索所有交错ENTER条目。这显然表现不佳。

有没有办法对数据集进行一次扫描，将ENTER属于同一会话的所有条目标记为唯一的序列号，然后最终按该序列进行分组？我为此头破血流。

标签： postgresqlgroup-bywindow-functionsgaps-and-islands

解决方案

一种可能的解决方案是表SUM中最多出现OPEN当前行（按名称分区并按时间排序），然后将其除以 2 以获得当前访问编号。这可以用来对结果进行分组：

WITH CTE AS (
  SELECT *,
         (1 + SUM(CASE WHEN action = 'OPEN' THEN 1 ELSE 0 END) OVER (PARTITION BY name ORDER BY time)) / 2 AS access
  FROM data
)
SELECT MIN(time), MAX(time), name, ARRAY_AGG(time || ',' || action) AS actions
FROM CTE
GROUP BY name, access
ORDER BY MIN(time), name

输出（我的扩展演示，第二次访问John）：

min     max     name    actions
13:00   14:00   John    ["13:00,OPEN","13:00,ENTER Hall","13:30,ENTER Kitchen","13:45,ENTER Living room","14:00,OPEN"]
13:30   16:10   Steve   ["13:30,OPEN","13:30,ENTER Hall","13:40,ENTER Stairs","14:00,ENTER Bed room","16:00,ENTER Stairs","16:10,OPEN"]
15:00   16:00   John    ["15:00,OPEN","15:00,ENTER Hall","15:30,ENTER Kitchen","15:45,ENTER Living room","16:00,OPEN"]

dbfiddle 上的演示

推荐阅读