34,590
社区成员
发帖
与我相关
我的任务
分享
Name Type Nullable Default Comments
--------- ------------- -------- ------- --------
PK_ID NUMBER(19)
AREAID VARCHAR2(200) Y
SNOWDEPTH NUMBER(16,2) Y
SNOWDATE DATE Y
insert into t_snowdata values(1,11,1.0,to_date('2012-01-01','yyyy-MM-dd'));
insert into t_snowdata values(2,12,1.0,to_date('2012-01-01','yyyy-MM-dd'));
insert into t_snowdata values(3,12,1.0,to_date('2012-01-02','yyyy-MM-dd'));
insert into t_snowdata values(4,11,0.0,to_date('2012-01-02','yyyy-MM-dd'));
insert into t_snowdata values(5,11,4.0,to_date('2012-01-03','yyyy-MM-dd'));
insert into t_snowdata values(8,11,3.0,to_date('2012-01-04','yyyy-MM-dd'));
insert into t_snowdata values(7,11,1.0,to_date('2012-01-06','yyyy-MM-dd'));
insert into t_snowdata values(6,11,0.0,to_date('2012-01-05','yyyy-MM-dd'));
select max(t2.num) maxNum,areaId from (
select count(diff) num,areaId from
(select areaId,snowDate-row_number()over(partition by areaId order by snowDate asc) as diff from t_snowdata where snowDepth>0)t
group by diff,areaId
)t2
group by areaId