Oracle 查找记录相隔一定时间

嗨，

我有一个下表:

create table t ( user_id varchar2(10), clinic varchar2(50), visit_dt date );

INSERT INTO T VALUES ( 012, 'oncology', TO_DATE( '08-APR-2008') );
INSERT INTO T VALUES ( 012, 'oncology', TO_DATE( '21-APR-2008') );
INSERT INTO T VALUES ( 012, 'oncology', TO_DATE( '08-JAN-2009') );
INSERT INTO T VALUES ( 012, 'oncology', TO_DATE( '26-JAN-2009') );
INSERT INTO T VALUES ( 012, 'oncology', TO_DATE( '29-JAN-2009') );
INSERT INTO T VALUES ( 012, 'oncology', TO_DATE( '24-FEB-2009') );
INSERT INTO T VALUES ( 012, 'oncology', TO_DATE( '17-MAR-2009') );
INSERT INTO T VALUES ( 012, 'oncology', TO_DATE( '30-MAR-2009') );
INSERT INTO T VALUES ( 012, 'oncology', TO_DATE( '04-MAY-2009') );
INSERT INTO T VALUES ( 012, 'oncology', TO_DATE( '19-OCT-2009') );
INSERT INTO T VALUES ( 012, 'oncology', TO_DATE( '18-NOV-2009') );
INSERT INTO T VALUES ( 012, 'oncology', TO_DATE( '25-NOV-2009') );
复制

我们希望选择两次或两次以上且至少相隔3个月访问诊所的用户。有没有办法只选择相隔> = 3个月的访问？第一次合格访问是对诊所的第一次访问，下一次合格访问必须> = 3个月后。可能是3个月内的多次访问，但我们只对3个月内的访问感兴趣。每次排位赛后，时钟都会重新开始。用户id可以链接到多个诊所，但是合格的访问需要到同一诊所。

从上表中我需要以下记录:

user_id  clinic  visit_dt
---------------------------------
12 oncology 08-APR-2008
12 oncology 08-JAN-2009
12 oncology 04-MAY-2009
12 oncology 19-OCT-2009      
复制

任何帮助都非常感谢!谢谢。

可惜你不在12c上。模式匹配使这很容易 :)

只需定义一个始终真实的初始变量。然后另一个匹配所有这些在3个月内:

alter session set nls_date_format = ' DD Mon YY ';

select * from t
match_recognize (
  order by visit_dt
  measures
    match_number() visit,
    first(st.visit_dt) stdt,
    coalesce(last(visit.visit_dt), st.visit_dt) endt,
    user_id usr,
    clinic  clin
  one row per match
  pattern ( st visit* )
  define
    visit as months_between ( visit_dt, st.visit_dt ) < 3
);

VISIT   STDT          ENDT          USR   CLIN       
      1  08 Apr 08     21 Apr 08    12    oncology   
      2  08 Jan 09     30 Mar 09    12    oncology   
      3  04 May 09     04 May 09    12    oncology   
      4  19 Oct 09     25 Nov 09    12    oncology   
复制

在11.2上，你可以得到你想要的使用递归。有点笨重!

在基本条件下，为患者选择第一行。然后，在递归部分，检查下一次访问的日期是否在最初的三个月内。如果是，则返回上一个日期。如果不是，则返回当前日期。

然后采取独特的方式进行访问:

with vals as (
  select t.*, 
         row_number() over (order by visit_dt) rn,
         min(visit_dt) over () mn
  from   t
), visits (
  user_id, clinic, visit_strt, rn
) as (
  select user_id, clinic, visit_dt, rn
  from   vals
  where  visit_dt = mn
  union all
  select s.user_id, s.clinic,
         case
           when months_between(s.visit_dt, v.visit_strt) < 3 then
             v.visit_strt
           else
             s.visit_dt
         end, s.rn
  from   visits v
  join   vals s
  on     v.rn + 1 = s.rn
)
  select distinct user_id, clinic, visit_strt
  from   visits
  order  by visit_strt;

USER_ID   CLINIC     VISIT_STRT    
12        oncology    08 Apr 08    
12        oncology    08 Jan 09    
12        oncology    04 May 09    
12        oncology    19 Oct 09
复制