如何在postgresql表中获得不重叠的不同间隔？

rur96b6h 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(481)

表中有用户会话，我需要打印不同的非重叠会话。

CREATE TABLE SESSIONS(
            id serial NOT NULL PRIMARY KEY, 
            ctn INT NOT NULL, 
            day DATE NOT NULL,
            f_time TIME(0) NOT NULL,
            l_time TIME(0) NOT  NULL
        );     
    INSERT INTO SESSIONS(id, ctn, day, f_time, l_time)
    VALUES
    (1, 707, '2019-06-18', '10:48:25', '10:56:17'),
    (2, 707, '2019-06-18', '10:48:33', '10:56:17'),
    (3, 707, '2019-06-18', '10:53:17', '11:00:49'),
    (4, 707, '2019-06-18', '10:54:31', '10:57:37'),
    (5, 707, '2019-06-18', '11:03:59', '11:10:39'),
    (6, 707, '2019-06-18', '11:04:41', '11:08:02'),
    (7, 707, '2019-06-18', '11:11:04', '11:19:39');

sql小提琴
我的table是这样的：

id  ctn day         f_time      l_time
1   707 2019-06-18  10:48:25    10:56:17
2   707 2019-06-18  10:48:33    10:56:17
3   707 2019-06-18  10:53:17    11:00:49
4   707 2019-06-18  10:54:31    10:57:37
5   707 2019-06-18  11:03:59    11:10:39
6   707 2019-06-18  11:04:41    11:08:02
7   707 2019-06-18  11:11:04    11:19:39

现在我需要不同的不重叠的用户会话，所以它应该给我

1.  start_time: 10:48:25  end_time: 11:00:49  duration: 12min,24 sec
2.  start_time: 11:03:59  end_time: 11:10:39  duration: 6min,40 sec
3.  start_time: 11:11:04  end_time: 11:19:33  duration: 8min,29 sec

sql postgresql window-functions Date gaps-and-islands

来源：https://stackoverflow.com/questions/62904977/how-to-get-non-overlapping-distinct-intervals-in-postgresql-table

1条答案

按热度按时间

mctunoxg1#

这是一个缺口和孤岛问题。下面是一种使用窗口函数的方法：

select
    ctn,
    min(f_ts) start_ts,
    max(l_ts) end_ts,
    max(l_ts) - min(f_ts) duration
from (
    select 
        s.*,
        count(*) filter(where f_ts > lag_l_ts) over(partition by ctn order by f_ts) grp
    from (
        select
            s.*,
            lag(l_ts) over(partition by ctn order by f_ts) lag_l_ts
        from (
            select
                s.*,
                (day + l_time)::timestamp l_ts,
                (day + f_time)::timestamp f_ts
            from sessions s
        ) s
    ) s
) s
group by ctn, grp
order by ctn, start_ts

查询的工作方式如下：
首先，我们从日期和时间部分重建适当的时间戳：以这种方式存储数据会使操作变得不容易（而且它不允许会话在不同的日子中传播）
一个数据是标准化的，我们使用 lag() 获取“上一行”的结束时间戳
然后，我们可以构建“相邻”记录的组：每次开始时间戳大于前一个结束时间戳时，就会启动一个新组
最后一步是聚合
db小提琴演示：

ctn | start_ts            | end_ts              | duration
--: | :------------------ | :------------------ | :-------
707 | 2019-06-18 10:48:25 | 2019-06-18 11:00:49 | 00:12:24
707 | 2019-06-18 11:03:59 | 2019-06-18 11:10:39 | 00:06:40
707 | 2019-06-18 11:11:04 | 2019-06-18 11:19:39 | 00:08:35

展开查看全部

赞(0）回复(0）举报 2021-07-26

我来回答

如何在postgresql表中获得不重叠的不同间隔？

1条答案

相关问题

热门标签

最新问答