范围处理

1. 定位连续值的范围

  select proj_id,proj_start,proj_end from(
   select proj_id,proj_start,lead(proj_start) over(order by proj_start) startd,proj_end 

      from dept_obj
   ) where startd = proj_end

 

2.查找同一组或分区中行之间的差

   注:求各部门员工之间工资差额，如果为最新来的员工差额为N/A

  select deptno,ename,sal,hiredate,lpad(nvl(to_char(sal-next_sal),'N/A'),10) next_nvl from (
select deptno,ename,sal,lead(sal) over(partition by deptno order by hiredate) next_sal,hiredate from emp
 group by deptno,ename,sal,hiredate order by deptno,hiredate desc
 )

 

3.定位连续范围内的开始点和结束点

 select proj_grp,min(proj_start),max(proj_end) from (
  select proj_id,proj_start,proj_end,sum(flag) over(order by proj_id) proj_grp from (
    select proj_id,proj_start,proj_end,
           case when lag(proj_end) over(order by proj_id) = proj_start then 0 else 1 end flag
    from dept_obj
    )
   )  group by proj_grp

 

4.补充范围内丢失的值

①

  with temp as(
  select add_months(trunc(to_date('1980-01-01','yyyy-mm-dd'),'Y'),(level-1)*12) bwyear from dual
  connect by level <= 10 group by level order by bwyear
  )
 select to_char(bwyear,'yyyy'),count(hiredate) from temp,emp where bwyear = trunc(hiredate(+),'Y')
 group by to_char(bwyear,'yyyy')
 order by to_char(bwyear,'yyyy')

②

 

select x.yr,coalesce(cnt,0) cnt from(
  select extract(year from min(hiredate) over())
         - mod((extract(year from min(hiredate) over())),10)
         + rownum - 1 yr
   from emp where rownum <= 10
   ) x,
   ( 
      select to_number(to_char(hiredate,'yyyy')) yr,count(*) cnt from emp
      group by to_number(to_char(hiredate,'yyyy'))
    ) y
    where x.yr = y.yr(+) 
    order by yr

 

5.生成连续数字值

 ① select level from dual connect by level < 10

 ②

select array id from dual
model
  dimension by (0 idx)
  measures(1 array)
  rules iterate(10)(
  array[iteration_number] = iteration_number + 1
) 

 

6.查找3秒以上，大于100的数值

with temp as(
select 4681 vtr_id,to_date('2010-8-2 15:07:37','yyyy-mm-dd hh24:mi:ss') rec_time,100 sensor_spd from dual
union all
select 4681 vtr_id,to_date('2010-8-2 15:07:38','yyyy-mm-dd hh24:mi:ss') rec_time,110 sensor_spd from dual
union all
select 4681 vtr_id,to_date('2010-8-2 15:07:39','yyyy-mm-dd hh24:mi:ss') rec_time,120 sensor_spd from dual
union all
select 4681 vtr_id,to_date('2010-8-2 15:07:40','yyyy-mm-dd hh24:mi:ss') rec_time,130 sensor_spd from dual
union all
select 4681 vtr_id,to_date('2010-8-2 15:07:41','yyyy-mm-dd hh24:mi:ss') rec_time,140 sensor_spd from dual
union all
select 4681 vtr_id,to_date('2010-8-2 15:07:42','yyyy-mm-dd hh24:mi:ss') rec_time,150 sensor_spd from dual
union all
select 4681 vtr_id,to_date('2010-8-2 15:07:43','yyyy-mm-dd hh24:mi:ss') rec_time,110 sensor_spd from dual
union all
select 4681 vtr_id,to_date('2010-8-2 15:07:45','yyyy-mm-dd hh24:mi:ss') rec_time,60 sensor_spd from dual
union all
select 4682 vtr_id,to_date('2010-8-2 15:07:37','yyyy-mm-dd hh24:mi:ss') rec_time,110 sensor_spd from dual
union all
select 4682 vtr_id,to_date('2010-8-2 15:07:38','yyyy-mm-dd hh24:mi:ss') rec_time,50 sensor_spd from dual
union all
select 4682 vtr_id,to_date('2010-8-2 15:07:44','yyyy-mm-dd hh24:mi:ss') rec_time,100 sensor_spd from dual
union all
select 4682 vtr_id,to_date('2010-8-2 15:07:45','yyyy-mm-dd hh24:mi:ss') rec_time,70 sensor_spd from dual
union all
select 4682 vtr_id,to_date('2010-8-2 15:07:46','yyyy-mm-dd hh24:mi:ss') rec_time,77 sensor_spd from dual
)
select vtr_id,rec_time,sensor_spd
from
(
select vtr_id,rec_time,sensor_spd,count(1) over(partition by vtr_id,rn1) rt
from(
select vtr_id,rec_time,
rec_time-numtodsinterval(row_number() over(partition by vtr_id order by rec_time),'second') rn1,
sensor_spd from temp
where sensor_spd>=100
)
)
where rt>=3