按时间间隔选取数据库记录的问题

Question

时间 间隔 取记录

Answer 1

本帖最后由 wildwave 于 2014-09-29 14:51:03 编辑

  

Answer 2

start with connect by 是树形的写法
利用start with 设定根节点的起始条件(每个用户每天的第一条记录)
connect by 利用上一级节点，查找下一级节点的规则 (查找比当前记录登录时间晚，且间隔一小时以上的最早时间)
设定条件level<=5 限定查找前5次奖励的记录

Answer 3

Oracle start with.connect by prior子句实现递归查询

Answer 4

小灰狼的不错,呵~
如果楼主的版本是11g的,可以考虑使用with as ,比start with实现递归要清晰一点.--建测试数据
create table userLoginTable
as
select 1 id,  200  user_id, to_date('2014-9-28 12:00:00','yyyy-mm-dd hh24:mi:ss') login_time from dual
union all select 1 ,  200  ,  to_date('2014-9-28 12:40:00','yyyy-mm-dd hh24:mi:ss') from dual
union all select 1 ,  200 ,   to_date('2014-9-28 13:05:00','yyyy-mm-dd hh24:mi:ss') from dual
union all select 1 ,  200 ,   to_date('2014-9-28 13:30:00','yyyy-mm-dd hh24:mi:ss') from dual
union all select 1 ,  200 ,   to_date('2014-9-28 14:20:00','yyyy-mm-dd hh24:mi:ss') from dual
union all select 1 ,  200 ,   to_date('2014-9-28 15:10:00','yyyy-mm-dd hh24:mi:ss') from dual
union all select 1 ,  200  ,  to_date('2014-9-28 17:30:00','yyyy-mm-dd hh24:mi:ss') from dual;
insert into userLoginTable
select 2,201,login_time from userLoginTable
union all select 2,201,login_time+1 from userLoginTable
union all select 2,201,login_time+2+2/24 from userLoginTable;
commit;--以下实现
with wa(id,user_id,login_day,login_time) as
(
select id,user_id,login_day,login_time from (select id,user_id,trunc(login_time) login_day, login_time,
row_number() over (partition by id,user_id,trunc(login_time) order by login_time) rn  from userLoginTable
--如果加where条件限制日期,加这里
) where rn=1
union all
select a.id,a.user_id,trunc(a.login_time_a) login_day, a.login_time_b
from (select id,user_id,login_time_a,login_time_b from
(select a.id,a.user_id,a.login_time login_time_a,b.login_time login_time_b,
row_number() over (partition by a.id,a.user_id,a.login_time  order by b.login_time) rn
from userLoginTable a,userLoginTable b
where a.id=b.id and a.user_id=b.user_id and trunc(a.login_time)=trunc(b.login_time)
and a.login_time<=b.login_time-1/24
--如果加and条件限制日期,加这里
) a
where rn=1) a,wa
where a.id=wa.id and a.user_id=wa.user_id and trunc(a.login_time_a)=wa.login_day
and a.login_time_a=wa.login_time
)
--select * from wa order by id,user_id,login_time;--这句可以查明细
select id,user_id,login_day,case when count(*)>5 then 5 else count(*) end jl from wa
group by id,user_id,login_day
order by id,user_id,login_day;

Answer 5

可以解释下嘛语句应该写得比较直观
用了一个树形查询，从每天的第一条登录记录开始，做为树的根节点，下一个节点是从该时间往后推1个小时以后的第一条记录，对应语句中的connect by login_time=prior ....，依次类推。到了第五个节点，不再往下找了语句有个漏洞在于，如果login_time出现重复，比如某用户9月29日 18:01:22登录了两次，则结果集也将出现重复，可能性不大，但有可能发生。如果该表中有个可以唯一标识记录的主键，就可以避免这个问题

Answer 6

楼上的都不错，其实用分析函数就可以。
select  t1.id,t1.user_id,login_time
from 
(select  id,user_id,login_time,
        row_number() over(partition by id,user_id,
                                       to_char(login_time,'yyyymmdd')
                          order by login_time) px    
                   ----这里对同一天内连续两次登录在1小时之内的记录
                   ----按照user_id和当天日期分组，按照login_time进行排序  
from                          
  (select id,user_id,login_time,
         CASE  WHEN 
          LAG(TO_DATE(login_time, 'yyyy-mm-dd hh24:mi:ss'))
          OVER(PARTITION BY id,user_id,
                        to_char(login_time,'yyyymmdd')
               ORDER BY login_time DESC)   ---当天同一账号前一次登录记录
          -login_time> 1 / 24             ---当天同一本次登录记录
         THEN  ROW_NUMBER() OVER(PARTITION BY id,user_id,
                                              to_char(login_time,'yyyymmdd') 
                                 ORDER BY login_time DESC )   --如果两次登录在1小时之外，否则是null
         END RN                                               
    from   userLoginTable)  t
where rn is not null)     t1        ----这里只取同一天内连续两次登录在1小时之内的记录
where t1.px<6;    ------最后取满足条件的前5条

Answer 7

上面有个地方写错了。假设你的login_time是date类型的。
TO_DATE(login_time, 'yyyy-mm-dd hh24:mi:ss')  应该改成login_time。
这个语句我在别的地方使用过。

Answer 8

你这个语句错误和我1#写的问题应该差不多，查询出的结果应该只有下面这两条，具体原因你应该能分析出来，呵呵
1   200   2014-9-28 12:00:00
1   200   2014-9-28 17:30:00腻害，被看穿了。我是意思也理解错了，语句也写错了。我试了下，改成这样可以得到结果。测试真是浪费时间。
select  t1.id,t1.user_id,login_time
from 
(select  id,user_id,login_time,
        row_number() over(partition by id,user_id,
                                       to_char(login_time,'yyyymmdd')
                          order by login_time) px    
 from                          
  (select id,user_id,login_time,
           CASE  WHEN 
          login_time-
          lead(login_time)
          OVER(PARTITION BY id,user_id,
                        to_char(login_time,'yyyymmdd')
               ORDER BY login_time DESC)   
          > 1 / 24        
         or 
         lead(login_time)
          OVER(PARTITION BY id,user_id,
                        to_char(login_time,'yyyymmdd')
               ORDER BY login_time DESC) is null
         THEN  ROW_NUMBER() OVER(PARTITION BY id,user_id 
                                 ORDER BY login_time DESC )   
         END RN                                               
    from   
        (select id,user_id,login_time
         from ( select id,user_id,login_time,
                row_number()  OVER(PARTITION BY id,user_id,
                        to_char(login_time,'yyyymmdd hh24')
                ORDER BY login_time) rn0             
         from   TBL01TEST20141009) 
         where rn0=1))  t
where rn is not null)     t1        
where t1.px<6;