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

Question

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

10

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

[
  {"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}
]

您可以看到有两个时期的活动重叠。我想根据涉及到的重叠活动返回这些“重叠”的总数。因此，以上内容将输出类似以下的内容：

[
  {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然后计算每个区间的活动，但那不是很正确，因为数据是连续的 - 我真的需要确定重叠时间，然后对重叠的活动进行求和。

编辑已添加另一个示例。正如@SRack指出的那样，由于A、B、C重叠，这意味着B、C A、B和A、C也重叠。这并不重要，因为我要找的输出是包含重叠活动的日期范围数组，而不是所有重叠组合的唯一组合。还请注意，日期是时间戳，因此将具有毫秒精度，并且不一定全部在00:00:00。如果有帮助的话，可能会对总计数设置WHERE条件。例如，只想看到总计数> 100的结果

- Dave

你有一些JSON数据还是这些是表格行？ - S-Man

开始时，A和B的交集是什么（不包括C）？ - S-Man

以上只是 JSON 数据的一个简化示例，是的，这些将是表格行（请注意，日期是完整的时间戳，而不仅仅是日期）。 - Dave

好主意@s-man - 将会更新。 - Dave

2个回答

0

由于这是标记为Ruby on Rails，我也为此提供了一个Rails解决方案。我已更新数据，使它们不会全部重叠，并使用以下内容进行了处理：

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

(2..data.length).each_with_object({}) do |n, hash|
  data.combination(n).each do |items|
    combination = items.dup
    first_item = combination.shift
    first_item_range = (Date.parse(first_item[:start])..Date.parse(first_item[:end]))

    if combination.all? { |i| (Date.parse(i[:start])..Date.parse(i[:end])).overlaps?(first_item_range) }
      hash[items.map { |i| i[:name] }.sort] = items.sum { |i| i[:count] }
    end
  end
end

我已更新数据，使它们不再重叠，这将生成以下结果：

# => {["B", "C"]=>75, ["B", "D"]=>62, ["C", "D"]=>99, ["B", "C", "D"]=>118}

...所以您可以看到项目B，C和D重叠，总计118个。（自然地，这也意味着B，C，B，D和C，D重叠。）

以下是它的步骤：

获取数据条目的每个组合，长度从2到4（数据的长度）
遍历这些组合并将第一个元素与其他元素进行比较
如果它们都重叠，则将其存储在哈希表中

这样，我们就可以得到唯一的数据名称条目，并存储其计数。

希望这对您有用-乐意接受任何改进意见。让我知道您的进展如何！

- SRack

谢谢 - 这是一个有趣的解决方案，绝对有用，我会考虑一下，但基于性能原因可能无法在Rails中实现，实际数据集可能非常庞大。请注意，重点不在于哪些项目重叠，而在于哪些日期有重叠，以及相应的计数。 - Dave

感谢您的反馈，@Dave - 老实说，我很喜欢这个项目，所以很高兴能够完成它。很高兴你得到了你想要的答案！ - SRack

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- S-Man · Accepted Answer

演示：db<>fiddle（使用旧数据集与重叠的A-B部分）。

免责声明：此方法适用于日间隔，而非时间戳。对时间戳的要求是后来才提出的。

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

generate_series 生成在开始和结束之间的所有日期，因此每个存在活动的日期都会有一个具有特定计数的行。
将所有日期分组，聚合所有现有活动和它们的计数总和。
HAVING 筛选出仅存在一个活动的日期。
由于存在相同活动的不同日期，我们只需要一个代表：使用DISTINCT ON过滤所有重复项。
将此结果与原始表连接以获取开始和结束时间。(请注意，“end”是Postgres中的保留字，最好找到另一个列名!)。虽然在子查询中失去这些数据更舒适，但可以获得这些数据。
对此连接进行分组，以获取每个区间的最早和最晚日期。

以下是时间戳版本：

演示：db<>fiddle

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。

因此，我将这个时间列表与活动表连接起来，其中时间间隔相交。之后只需要按时间段分组并计算总数即可。

这将两行的ts放入一个数组中，其元素会扩展为每个元素的一行，使用unnest。所以我得到所有时间都在一个列中，可以简单地排序
使用lead窗口函数允许将下一行的值带入当前行。因此，我可以使用tsrange将这两个值创建为时间戳范围
此过滤器是必需的，因为最后一行没有“下一个值”。这会创建一个由NULL值组成的无限值，tsrange将其解释为无穷大。因此，我们需要过滤掉此行。
将时间段与原始表连接起来。&&操作符检查两个区间类型是否重叠。
按单个时间段分组，聚合名称和计数。使用HAVING子句过滤掉只有一个活动的时间段
有点棘手，需要获取正确的起始点和结束点。因此，起始点要么是活动开始的最大值，要么是时间段的开始（可以使用lower获得）。例如，取20-20:30插槽：它开始于20点，但B和C都没有在那里开始。类似的结束时间。