SQL ORACLE MYSQL 最大连续日期计算

数据库 waitig 846℃ 百度已收录 0评论

需求:最大连续日期

不知道大家有没有遇到类似这样的问题?如果认为本文校验方法和观点不错的话,记得收藏生气生气

针对目前的最大连续日期,处理的难点如下几个所示:
      1.连续性。连续性决定了日期的跳跃和不可以把控。中断的连续日期如何重新开始新的日期计数。
      2.同一个日期里存在多个数据,需要对相同数据日期打上同一个标签。

优点:可以看到不同区间段的时间连续性,在其他用户习惯行为分析比较贴切。
缺点:有一个业务情景可能会冲突,就是在多个相同连续天数,这个地方处理目前还不是很合理。
      (建议对最大的连续天数再次group by 分组,取出组内的最大日期和最小日期。)

如有其它补充,欢迎留言。

方法一:
利用 dense_rank函数 对连续的日期进行去重排序 得到一个日期对应的序号。
–用日期减去序号得到一个假定的起始连续开始日期(这里的日期不一定是真实意义的连续开始日期)。
相同序号出现最多的日期为最大连续的时间段。
真实的连续起始日期为序号出现最多次数的最小日期。
只支持dense_rank的数据能用这个方法(SQLSERVER ORACLE)。

with t_lianxu as
(
select c.id,c.first_lianxu_time,count(distinct c.rn) lianxu_times,min(c.load_date) real_lianxu_time 
from (
      select b.*,dateadd(dd,-b.rn,b.load_date) first_lianxu_time 
      from (
           SELECT a.load_date,a.id,dense_rank()over(partition by a.id order by a.load_date ) rn 
           from (
                 select cast(load_date as date) load_date
                        ,id
                 from  table_test
                ) a
           ) b
     ) c
group by c.id,c.first_lianxu_time
)
select t1.* from 
t_lianxu t1
inner join
(
select id,max(lianxu_times) max_lianxu_times from t_lianxu group by id
) t2 on t1.id=t2.id and t1.lianxu_times=t2.max_lianxu_times

id
first_lianxu_time
lianxu_times
real_lianxu_time

1
2017-09-01
        4
                2017-09-04

1
2017-09-03
        4
                2017-09-10




方法二:
利用变量保留上一个日期的方法,和当前的日期进行对比。
1.日期间隔=1 天数增加
2.日期间隔=0 为同一日期 天数不变
3.如果日期间隔>1 连续中断,重新计数

通过初始化一个累计天数,和遇到中断日期能够归零重新计算。
这个方法仅用于mysql

SELECT * FROM(
             SELECT id,max(days) lianxu_days,min(login_day) start_date,max(login_day) end_date FROM
                (
                 SELECT id,@count_day := (CASE WHEN ( @same_id = id AND DATEDIFF(load_date, @last_date) = 1 ) THEN (@count_day + 1)
                                              WHEN ( @same_id = id AND DATEDIFF(load_date, @last_date) =0 ) THEN (@count_day + 0)
                                              ELSE 1 END ) AS days,
                         (@count_id := (@count_id + IF (@count_day = 1, 1, 0))) AS count_id,
                         @same_id := id,
                         @last_date := load_date login_day
                 FROM
                 (SELECT id,cast(load_date as date ) load_date FROM table_test ) AS t1,
                 (SELECT @same_id := ”,@last_date := ”,@count_id := 0,@count_day := 0) AS t2
                 where load_date>@last_date
               ) AS a
            GROUP BY id,count_id 
)b

这个写法没有取最大值,偷懒下~~~


 id, lianxu_days, start_date, end_date
‘1’, ‘2’, ‘2017-09-01’, ‘2017-09-02’
‘1’, ‘4’, ‘2017-09-04’, ‘2017-09-07’
‘1’, ‘4’, ‘2017-09-10’, ‘2017-09-13’

附:
数据准备
create table table_test
(
id int,
load_date datetime
)

insert into table_test values(1,’20170901′)
insert into table_test values(1,’20170902′)
insert into table_test values(1,’20170904′)
insert into table_test values(1,’20170905′)
insert into table_test values(1,’20170906′)
insert into table_test values(1,’20170907′)
insert into table_test values(1,’20170905′)
insert into table_test values(1,’20170902′)
insert into table_test values(1,’20170910′)
insert into table_test values(1,’20170910′)
insert into table_test values(1,’20170911′)
insert into table_test values(1,’20170912′)
insert into table_test values(1,’20170913′)

感谢您的阅读,如果觉得本文方法可行,欢迎转载。大笑大笑


本文由【waitig】发表在等英博客
本文固定链接:SQL ORACLE MYSQL 最大连续日期计算
欢迎关注本站官方公众号,每日都有干货分享!
等英博客官方公众号
点赞 (0)分享 (0)