row_number(partition by..)以及connect by level的使用

需求：找出表中每天8点到第二天8点中N_T字段中的最大值，最小值，平均值（天数不按夜里凌晨0点为一天来算，按8点来算）

create table T_DMSPRODUCT_N_T

    as
    with 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 ;