时间戳

首页 > 解决方案 > oracle 如何根据值变化对数据进行分组

sql - oracle 如何根据值变化对数据进行分组

问题描述

我已经为此苦苦挣扎了一段时间。我有下表，其中存储了有关登录和退出系统的人员的数据：

USERID       STATUSDATETIME         CHANGEDLOGGEDIN      LOGGEDIN      ENDDATETIME            STATEDURATION      SEQNO
johndoe      25-08-19 01:39:32      1                    1             25-08-19 01:39:32      0                  0
johndoe      25-08-19 01:39:32      0                    1             25-08-19 01:40:19      47                 0
johndoe      25-08-19 01:40:19      0                    1             25-08-19 01:40:26      7                  1
johndoe      25-08-19 01:40:26      0                    1             25-08-19 01:40:34      8                  2
johndoe      25-08-19 01:40:34      0                    1             25-08-19 01:40:50      16                 0
johndoe      25-08-19 01:40:50      0                    1             25-08-19 01:43:57      187                0
johndoe      25-08-19 01:43:57      1                    0             25-08-19 01:43:57      0                  1

johndoe      25-08-19 01:48:14      1                    1             25-08-19 01:48:14      0                  0
johndoe      25-08-19 01:48:14      0                    1             25-08-19 01:48:48      34                 0
johndoe      25-08-19 01:48:48      0                    1             25-08-19 01:48:53      5                  1
johndoe      25-08-19 01:48:53      0                    1             25-08-19 01:49:00      7                  2
johndoe      25-08-19 01:49:00      0                    1             25-08-19 01:49:08      8                  3
johndoe      25-08-19 01:49:08      0                    1             25-08-19 01:50:26      78                 0
johndoe      25-08-19 01:50:26      0                    1             25-08-19 01:50:33      7                  0
johndoe      25-08-19 01:50:33      1                    0             25-08-19 01:50:33      0                  1

我想要完成的是，基于按用户 ID、状态日期时间和 seqno 排序的选择，获取登录 = 1 的每个“块”的第一个状态日期时间、最后一个结束日期时间、总和（状态持续时间）。此示例中的最终结果将是像这样的东西：

select userid, min(statusdatetime), max(enddatetime), sum(stateduration)
from login_table
group by userid,block_of_loggedin

USERID    min(STATUSDATETIME)   max(ENDDATETIME)    sum(STATEDURATION)
johndoe   25-08-19 01:39:32     25-08-19 01:43:57   265
johndoe   25-08-19 01:48:14     25-08-19 01:50:33   139

由于 SQL 不是我的强项，而且比 Oracle 更习惯于 MSSQL，这已经让我痛苦了好几天。

我已经尝试过使用 partition by、rollup、connect by 和其他许多方法，但我无法完成我所期望的任何事情。

关于如何实现的任何想法？

谢谢你们！

标签： sqloraclegaps-and-islands

解决方案

这是一个典型的差距和孤岛问题。您可以考虑以下查询：

select 
    userid,
    min(statusdatetime),
    max(enddatetime),
    sum(stateduration)
from (
    select 
        t.*,
        row_number() over(partition by userid order by statusdatetime, enddatetime) rn1,
        row_number() over(partition by userid, loggedin order by statusdatetime, enddatetime) rn2
    from mytable t
) x
where loggedin = 1
group by 
    userid,
    rn1 - rn2
order by 1, 2

这通过在整个表中按日期对每个用户的记录进行排名，并将其与具有相同值的记录组的排名进行比较loggedin。当等级之间的差异发生变化时，就会开始一个新的岛屿（组）。

DB Fiddle上的这个演示与您的示例数据返回：

用户名 | MIN(STATUSDATETIME) | 最大（结束日期时间）| 总和（状态）
:-------- | :----------------- | :---------------- | -----------------:
约翰多 | 25-08-19 01:39:32 | 25-08-19 01:43:57 | 265
约翰多 | 25-08-19 01:48:14 | 25-08-19 01:50:33 | 139

推荐阅读