关于连续天数的几个思路。

上个月做了个需求,要求统计一张表中某ID连续出现5天(时间连续5天,也不让新建表之类的,就是要求纯sql来做)的具体信息。一开始就是一脸懵逼,没思路然后是百度,找到了第一种解法:
1.采用左连接,把单表每次都作为一张新表,和自身进行左连接(直接看代码)

select distinct ID from 表A t1 join 表A t2 on  t1.ID =t2.ID and  t1.date=t2.date+1                                                                          
  join 表A t3 on t2.ID=t3.ID and t2.date=t3.date+1
  join 表A t4 on t3.ID=t4.ID and t3.date=t4.date+1
  join 表A t5 on t4.ID=t5.ID and t4.date=t5.date+1

1.5.当时我测试的时候,我的表中有163条数据,其中连续5天的有3个ID满足,所以这个做法可以。但是我项目到服务器后,服务器上的数据有6万多条,满足条件的有50几个,然后我的sql跑起来,服务器瞬间连接挂了,(由于连接资源是有限制的)5次左连接不停的占用资源,也没有释放,然后其他的连接就全部掉了,所以数据过多,这个方法不行。

2.采用oracle中自带的函数解决LAG和LEAD—偷偷去其他地方抄了个简单的解释(lag 和lead 可以 获取结果集中,按一定排序所排列的当前行的上下相邻若干offset 的某个行的某个列(不用结果集的自关联);
lag ,lead 分别是向前,向后;
lag 和lead 有三个参数,第一个参数是列名,第二个参数是偏移的offset,第三个参数是 超出记录窗口时的默认值)
)–说白了就是找相邻的第几值。上代码。

其中id是查找的字段,time是时间字段
> SELECT id,
         LAG(time, 4) OVER(PARTITION BY id ORDER BY time) prev_time,time
          from (SELECT id, TRUNC(time) time FROM 表A t2
                         GROUP BY output_id, TRUNC(time))
         where prev_time IS NOT NULL
           AND (time - prev_time) = 4

在服务器上面跑,没问题。

3.在网上还看到过一种方法,用递归。(大神写的代码有点长,看不下去,我贴上原代码,有兴趣的可以看看)(连续打卡,需求差不多)。

with t1(id,rq) as (
  select distinct 人员, date fromwhere date>='2013-11-01' and date<'2013-12-01' ),
--t1求出指定月的人员编号及不同的打卡日期
t2 as (select s2.* from t1 s1 join t1 s2 on s1.id=s2.id and s1.rq=s2.rq-1),
--t2求出所有上一日也打过卡的日期
t3 as (select * from t1 except select * from t2),
--t3求出所有上一日未打过卡的日期
t as ( 
  select id,rq,1 days from t3 
  union all 
  select t1.id,t1.rq,t.days+1 from t1 join t on t1.id=t.id and t1.rq=t.rq+1
)
--t4递归调用,每连续一日days+1,就是求每一打卡时间是连续的第几天
select id
from t
group by id
having max(days)>=5
order by id
以上就不删了,以下可以改短点吧
with t as (
  select 人员 id, date rq, 1 days from 表 t1 
    where not exists(select * from 表 t2 where t2.date=t1.date-1) 
  union all
  select t1.id,t1.rq,t.days+1 from 表 t1 join t on t1.id=t.id and t1.rq=t.rq+1

)
select id
from t
group by id
having max(days)>=5
order by id

总结,有时候适当的的使用oracle自带的函数,可以很轻易的解决问题。而且在sql中效率会比较高。

  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值