oracle 连续日期统计

select t2.user_id,t2.start_time, t2.score ,t2.win_coins from (

 select d.user_id,d.start_time, d.score ,d.win_coins  from (
     select user_id ,to_char(start_time,'yyyymmdd')as start_time,score,win_coins
   , ROW_NUMBER() over (partition by user_id,to_char(start_time,'yyyymmdd') order by to_date(to_char(start_time,'yyyymmdd'),'yyyymmdd')asc,score asc) as rn
    from drivereport where to_char(start_time,'yyyy-mm-dd') between '2015-05-01' and '2015-05-11'
     ) d where rn=1) t2,
     
     ( SELECT distinct(c.user_id) as user_id  FROM
(SELECT B.user_id, (MAX (to_char(B.start_time,'yyyymmdd')) - MIN (to_char(B.start_time,'yyyymmdd'))+1) as DAYS
FROM (SELECT A.*, TO_NUMBER(to_char(A.start_time,'yyyymmdd') - ROWNUM)as DAYS
FROM (
   SELECT user_id,  to_date(to_char(start_time,'yyyymmdd'),'yyyymmdd') as start_time ,min(score)as MI
 FROM drivereport where to_char(start_time,'yyyymmdd') >='20150501' and to_char(start_time,'yyyymmdd') <= '20150511'
  GROUP BY user_id, to_char(start_time,'yyyymmdd') ORDER BY user_id, to_char(start_time,'yyyymmdd')
   ) A  ) B
GROUP BY B.user_id, B.DAYS) C
WHERE C.DAYS > 2 order by c.user_id asc) t3

where  t3.user_id = t2.user_id

======================================

  -- 获得至少连续3天开启驾驶记录 例如 用户1：1号～3号的记录，用户2：2号～4号的记录，用户3： 4号～6号的记录 ；用户4：7号～10号的记录
     -- 下面的SQL主要获得至少连续3天的有哪些用户的ID

SELECT distinct(c.user_id) AS user_id
                   FROM (SELECT b.user_id
                               ,(MAX(to_char(b.start_time, 'yyyymmdd')) -
                                MIN(to_char(b.start_time, 'yyyymmdd')) + 1) AS days
                           FROM (SELECT a.user_id
                                       ,a.start_time
                                       ,to_number(to_char(a.start_time, 'yyyymmdd') -
                                                  rownum) AS days
                                   FROM (SELECT user_id
                                               ,to_date(to_char(start_time, 'yyyymmdd'), 'yyyymmdd') AS start_time
                                         
                                           FROM drivereport
                                          WHERE to_char(start_time, 'yyyy-mm-dd') >=
                                                '2015-05-01'
                                            AND to_char(start_time, 'yyyy-mm-dd') <=
                                                '2015-05-11'
                                          GROUP BY user_id
                                                  ,to_char(start_time, 'yyyymmdd')
                                          ORDER BY user_id
                                                  ,to_char(start_time, 'yyyymmdd')) a) b
                          GROUP BY b.user_id
                                  ,b.days) c
                  WHERE c.days > 2
                  ORDER BY c.user_id ASC