需求:找出表中每天8点到第二天8点中N_T字段中的最大值,最小值,平均值(天数不按夜里凌晨0点为一天来算,按8点来算)
create table T_DMSPRODUCT_N_T
aswith jb_0 as
(
select dt.tod,to_char(a.D_REGULARTIME,'yyyy-mm-dd hh24:mi:ss') as time,a.N_T,a.c_stationid
from DMSOBSE.T_FOREN_OBSE_AWS_ALL a
left join (select to_char(sysdate-(level-1),'yyyy-mm-dd')||' 08:00:00' as tod,
to_char(sysdate-(level-0),'yyyy-mm-dd')||' 08:00:00' as yest from dual
connect by level<=140) dt
on a.D_REGULARTIME>=to_date(dt.yest,'yyyy-mm-dd hh24:mi:ss') and a.D_REGULARTIME<to_date(dt.tod,'yyyy-mm-dd hh24:mi:ss')
where a.D_REGULARTIME>=to_date('2016-05-01','yyyy-mm-dd') and
a.N_T is not null and a.N_T<'266.4'
and dt.tod is not null
), --jb_0实现时间要求,connect by控制了regulartime
jb_1 as
(
select b.tod,b.time,b.N_T,b.C_STATIONID,
row_number() OVER(PARTITION BY b.tod,b.C_STATIONID ORDER BY b.N_T desc,time asc) RN
from jb_0 b
),
jb_3 as
(
select b.tod,b.time,b.N_T,b.C_STATIONID,
row_number() OVER(PARTITION BY b.tod,b.C_STATIONID ORDER BY b.N_T asc,time desc) RN
from jb_0 b
), --这里的row_number()over(partition by...) 是对相同的日期进行分组排序
jb_2 as
(
select c.tod,c.C_STATIONID,round(avg(c.N_T),2) as AVG_N_T,count(1) as sl
from jb_0 c
group by c.tod,c.C_STATIONID
)
insert into T_DMSPRODUCT_N_T
(select dt.tod,to_char(a.D_REGULARTIME,'yyyy-mm-dd hh24:mi:ss') as time,a.N_T,a.c_stationid
from DMSOBSE.T_FOREN_OBSE_AWS_ALL a
left join (select to_char(sysdate-(level-1),'yyyy-mm-dd')||' 14:00:00' as tod,
to_char(sysdate-(level-0),'yyyy-mm-dd')||' 14:00:00' as yest from dual
connect by level<=140) dt
on a.D_REGULARTIME>=to_date(dt.yest,'yyyy-mm-dd hh24:mi:ss') and a.D_REGULARTIME<to_date(dt.tod,'yyyy-mm-dd hh24:mi:ss')
where a.D_REGULARTIME>=to_date('2016-05-01','yyyy-mm-dd') and
a.N_T is not null and a.N_T<'266.4'
and dt.tod is not null
)
select d.tod,d.C_STATIONID,d.time as max_time,d.N_T as max_n_t,g.time as min_time,g.N_T as min_n_t,f.AVG_N_T,f.sl
from jb_1 d
left join jb_2 f on d.tod=f.tod and d.C_STATIONID=f.C_STATIONID
left join jb_3 g on d.tod=g.tod and d.C_STATIONID=g.C_STATIONID
where d.rn=1 and g.rn=1 and d.N_T>g.N_T ;