oracle日期统计近两个月,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