在 postgresql 中查找和汇总具有重叠记录的日期范围

我有一个大型数据集，我想对记录具有重叠时间的计数进行求和。例如，给定数据

[
  {"id": 1, "name": 'A', "start": '2018-12-10 00:00:00', "end": '2018-12-20 00:00:00', count: 34},
  {"id": 2, "name": 'B', "start": '2018-12-16 00:00:00', "end": '2018-12-27 00:00:00', count: 19},
  {"id": 3, "name": 'C', "start": '2018-12-16 00:00:00', "end": '2018-12-20 00:00:00', count: 56},
  {"id": 4, "name": 'D', "start": '2018-12-25 00:00:00', "end": '2018-12-30 00:00:00', count: 43}
]

您可以看到有 2 个活动重叠的时期。我想根据重叠涉及的活动返回这些“重叠”的总数。所以上面的输出会是这样的：

[
  {start:'2018-12-16', end: '2018-12-20', overlap_ids:[1,2,3], total_count: 109},
  {start:'2018-12-25', end: '2018-12-27', overlap_ids:[2,4], total_count: 62},
]

问题是，如何通过 postgres 查询生成这个？正在研究generate_series，然后计算出每个间隔内有哪些活动，但这不太正确，因为数据是连续的——我确实需要确定确切的重叠时间，然后对重叠活动进行求和。

EDIT Have added another example. As @SRack pointed out, since A,B,C overlap, this means B,C A,B and A,C also overlap. This doesn’t matter since the output I’m looking for is an array of date ranges that contain overlapping activities rather than all the unique combinations of overlaps. Also note the dates are timestamps, so will have millisecond precision and won’t necessarily all be at 00:00:00. If it helps, there would probably be a WHERE condition on the total count. For example only want to see results where total count > 100

（使用具有重叠 A-B 部分的旧数据集）

免责声明：这适用于天间隔，不适用于时间戳。对ts的要求是后来才出现的。

SELECT
    s.acts,
    s.sum,
    MIN(a.start) as start,
    MAX(a.end) as end
FROM (
    SELECT DISTINCT ON (acts)
        array_agg(name) as acts,
        SUM(count)
    FROM
        activities, generate_series(start, "end", interval '1 day') gs
    GROUP BY gs
    HAVING cardinality(array_agg(name)) > 1
) s
JOIN activities a
ON a.name = ANY(s.acts)
GROUP BY s.acts, s.sum

1. generate_series生成开始和结束之间的所有日期。因此，活动存在的每个日期都会有一行包含特定的count
2. 对所有日期进行分组，汇总所有现有活动及其计数总和
3. HAVING过滤掉仅存在一项活动的日期
4. 因为相同的活动在不同的日子里，我们只需要一个代表：过滤所有重复项DISTINCT ON
5. 将此结果与原始表连接起来以获得开始和结束。 （注意“end”是Postgres中的保留字，你最好找到另一个列名！）。以前丢失它们会更舒服，但可以在子查询中获取这些数据。
6. 将此连接分组以获得每个时间间隔的最早和最晚日期。

这是时间戳的版本：

WITH timeslots AS (
    SELECT * FROM (
        SELECT
            tsrange(timepoint, lead(timepoint) OVER (ORDER BY timepoint)),
            lead(timepoint) OVER (ORDER BY timepoint)     -- 2
        FROM (
            SELECT 
                unnest(ARRAY[start, "end"]) as timepoint  -- 1 
            FROM
                activities
            ORDER BY timepoint
        ) s
    )s  WHERE lead IS NOT NULL                            -- 3
)
SELECT 
    GREATEST(MAX(start), lower(tsrange)),                 -- 6
    LEAST(MIN("end"), upper(tsrange)),
    array_agg(name),                                      -- 5
    sum(count)
FROM 
    timeslots t
JOIN activities a
ON t.tsrange && tsrange(a.start, a.end)                   -- 4
GROUP BY tsrange
HAVING cardinality(array_agg(name)) > 1

主要思想是确定可能的时间段。因此，我将每个已知时间（开始和结束）放入排序列表中。因此，我可以获取前两个已知时间（从 A 开始的 17:00 和从 B 开始的 18:00）并检查其中的时间间隔。然后我检查第二个和第三个，然后检查第三个和第四个，依此类推。

第一个时隙中只有 A 适合。在 18-19 的第二个中，B 也很合适。在下一个时段 19-20 中还有 C，从 20 到 20:30 A 不再适合，只有 B 和 C。下一个是 20:30-22，其中只适合 B，最后将 22-23 D 添加到B 和最后但并非最不重要的一点是 D 适合 23-23:30。

因此，我将这个时间列表与时间间隔相交的活动表连接起来。之后，它只是按时间段分组并总结您的计数。

1. 这会将一行的两个 ts 放入一个数组中，该数组的元素被扩展为每个元素一行unnest。所以我把所有时间都放在一列中，可以简单地排序
2. 使用铅窗函数 https://www.postgresql.org/docs/current/tutorial-window.html允许将下一行的值放入当前行。所以我可以用这两个值创建一个时间戳范围tsrange
3. 此过滤器是必要的，因为最后一行没有“下一个值”。这创建了一个NULL值被解释为tsrange作为无穷大。所以这会造成一个令人难以置信的错误时间段。所以我们需要过滤掉这一行。
4. 将时间段与原始表连接起来。这&&运算符检查两个范围类型是否重叠。
5. 按单个时间段分组，聚合名称和计数。使用以下方法筛选出仅包含一项活动的时间段HAVING clause
6. 获得正确的起点和终点有点棘手。因此，起始点要么是活动开始的最大值，要么是时间段的开始（可以使用lower）。例如。以 20-20:30 时段为例：它从 20 点开始，但 B 和 C 都没有在那里开始。结束时间类似。