postgresql - 如何在 postgres 中查询并发事件，即使用情况？

Question

从下面我的第一个事件表中，什么查询会给我我的第二个使用表？

开始	结尾
08:42	08:47
08:44	08:50

开始	结尾	数数
08:42	08:44	1
08:44	08:47	2
08:47	08:50	1

如果我应该创建任何索引来加快速度怎么办？

我经常需要的主要事情是峰值使用量和峰值使用时间（即上面的最大计数行），那么还有一种更快的方法来获得其中一个/两个吗？

此外，每秒查询是否更快（我可以想象如何做），例如：

时间	数数
08:42	1
08:43	1
08:44	2
08:45	2
08:46	2
08:47	1
08:48	1
08:49	1

注意我的实际开始/结束时间是带时区的时间戳（6），我有数千条记录，但我希望我上面的例子有用。

标签： postgresql

Answer 1

分步演示：db<>fiddle

SELECT 
    t as start, 
    lead as "end",
    sum as count
FROM (
    SELECT
        t,
        lead(t) OVER (ORDER BY t),     -- 2a
        type,
        SUM(type) OVER (ORDER BY t)    -- 2b
    FROM (
        SELECT                         -- 1
            start as t,
            1 as type
        FROM mytable
        
        UNION 
        
        SELECT
            stop,
            -1 as type
        FROM mytable
    ) s
) s
WHERE sum > 0                          -- 3

1. 将所有时间值放入一列。将值添加1到以前的start值和-1以前的end值
2. a) 将下一次值放入当前记录 b) 使用累计SUM()超过新添加的1/-1值。每个start点增加计数，每个end值减少它。这是你的预期count
3. 无间隔地删除所有记录。

---

以上仅在您的边界不同时才能正常工作。如果您在同一时间点有间隔边界，则必须将其更改UNION为（保持相同的值）并随后将此结果分组以从同一时间段的两个值UNION ALL生成例如：-2-1

分步演示：db<>fiddle

SELECT 
    t as start, 
    lead as "end",
    sum as count
FROM (
    SELECT
        t,
        lead(t) OVER (ORDER BY t),
        type,
        SUM(type) OVER (ORDER BY t)
    FROM (
        SELECT
            t,
            SUM(type) AS type
        FROM (
            SELECT
                start as t,
                1 as type
            FROM mytable
            
            UNION ALL
            
            SELECT
                stop,
                -1 as type
            FROM mytable
        ) s
        GROUP BY t
    ) s
) s
WHERE sum > 0