时间间隔分组

bigdata-sb 2018-06-15 10:05:58

-- Create table
create table TEST
(
msg_id VARCHAR2(200),
send_time DATE
)


insert into test (MSG_ID, SEND_TIME)
values ('aaa', to_date('15-06-2018 09:00:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test (MSG_ID, SEND_TIME)
values ('bbb', to_date('15-06-2018 09:01:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test (MSG_ID, SEND_TIME)
values ('ccc', to_date('15-06-2018 09:02:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test (MSG_ID, SEND_TIME)
values ('ddd', to_date('15-06-2018 09:05:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test (MSG_ID, SEND_TIME)
values ('eee', to_date('15-06-2018 09:08:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test (MSG_ID, SEND_TIME)
values ('fff', to_date('15-06-2018 09:09:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test (MSG_ID, SEND_TIME)
values ('ggg', to_date('15-06-2018 09:13:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test (MSG_ID, SEND_TIME)
values ('hhh', to_date('15-06-2018 10:00:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test (MSG_ID, SEND_TIME)
values ('iii', to_date('15-06-2018 10:03:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test (MSG_ID, SEND_TIME)
values ('jjj', to_date('15-06-2018 10:20:00', 'dd-mm-yyyy hh24:mi:ss'));





...全文
759 3 打赏 收藏 转发到动态 举报
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
小丑伍陸柒 2018-06-15
  • 打赏
  • 举报
回复
WITH t AS
 (SELECT row_number() over(ORDER BY send_time) rn, msg_id, send_time
    FROM TEST),
tmp(rn,
msg_id,
send_time,
flag_time) AS
 (SELECT rn, msg_id, send_time, send_time
    FROM t
   WHERE rn = 1
  UNION ALL
  SELECT t.rn
        ,t.msg_id
        ,t.send_time
        ,CASE
           WHEN t.send_time <= tmp.flag_time + 1 / 1440 * 5 THEN
            tmp.flag_time
           ELSE
            t.send_time
         END
    FROM tmp, t
   WHERE t.rn = tmp.rn + 1),
t2 AS
 (SELECT msg_id
        ,send_time
        ,flag_time
        ,row_number() over(PARTITION BY flag_time ORDER BY send_time) f
    FROM tmp)
SELECT msg_id, send_time FROM t2 WHERE f = 1;
小丑伍陸柒 2018-06-15
  • 打赏
  • 举报
回复
WITH t AS (SELECT row_number() over(ORDER BY send_time) rn, msg_id, send_time FROM TEST), tmp(rn, msg_id, send_time, flag_time) AS (SELECT rn, msg_id, send_time, send_time FROM t WHERE rn = 1 UNION ALL SELECT t.rn ,t.msg_id ,t.send_time ,CASE WHEN t.send_time <= tmp.flag_time + 1 / 1440 * 5 THEN tmp.flag_time ELSE t.send_time END FROM tmp, t WHERE t.rn = tmp.rn + 1), t2 AS (SELECT msg_id ,send_time ,flag_time ,row_number() over(PARTITION BY flag_time ORDER BY send_time) f FROM tmp) SELECT msg_id, send_time FROM t2 WHERE f = 1;
jame_tian 2018-06-15
  • 打赏
  • 举报
回复

with a as
 (select msg_id, a_time, b_time
    from (select a.msg_id,
                 a.send_time a_time,
                 b.send_time b_time,
                 row_number() over(partition by a.rowid order by b.send_time) rn
            from test a
            left join test b
              on b.send_time > a.send_time + 1 / 24 / 60 * 5)
   where rn = 1)
select msg_id,a_time
  from a
 start with a.msg_id = 'aaa'
connect by a_time = prior b_time

20,807

社区成员

发帖
与我相关
我的任务
社区描述
Hadoop生态大数据交流社区,致力于有Hadoop,hive,Spark,Hbase,Flink,ClickHouse,Kafka,数据仓库,大数据集群运维技术分享和交流等。致力于收集优质的博客
社区管理员
  • 分布式计算/Hadoop社区
  • 涤生大数据
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧