select cphm ,count(*) from ( select cphm, lag(to_date(gcsj)) over(partition by cphm order by to_date(gcsj) desc)-to_date(gcsj) d_minus from hk_clkk ) t1 where d_minus*24*60*60<10 group by cphm having count(*)>2;
select cphm ,count(*) from ( select cphm, lag(to_date(gcsj,'yyyy-mm-dd hh24:mi:ss')) over(partition by cphm order by to_date(gcsj,'yyyy-mm-dd hh24:mi:ss') desc)-to_date(gcsj,'yyyy-mm-dd hh24:mi:ss') d_minus from hk_clkk ) t1 where d_minus*24*60*60<10 group by cphm having count(*)>2;刚刚 to_date 格式漏了,这个写法的话 表扫一遍,没测性能,可以试试
1.贴上表结构:如果HK_CLKK表结构就是你上面的截图,那就不需要了。
2.附上建表语句和测试语句,即:Create语句+insert语句,毕竟与人方便,就是给己方便。
3.说明下要实现的效果,最好是有图像展示效果。
select
cphm,
lag(to_date(gcsj)) over(partition by cphm order by to_date(gcsj) desc)-to_date(gcsj) d_minus
from hk_clkk
) t1
where d_minus*24*60*60<10
group by cphm
having count(*)>2;
select
cphm,
lag(to_date(gcsj,'yyyy-mm-dd hh24:mi:ss')) over(partition by cphm order by to_date(gcsj,'yyyy-mm-dd hh24:mi:ss') desc)-to_date(gcsj,'yyyy-mm-dd hh24:mi:ss') d_minus
from hk_clkk
) t1
where d_minus*24*60*60<10
group by cphm
having count(*)>2;刚刚 to_date 格式漏了,这个写法的话 表扫一遍,没测性能,可以试试