SQL Cookbook 系列 - 范围处理

1. 定位连续值的范围
2. 查找同一组或分区中行之间的差
3. 定位连续值范围的开始点和结束点
4. 补充范围内丢失的值
5. 生成连续数字值

 

1.定位连续值的范围

select v1.project_id,v1.project_start,v1.project_end

from V v1,V v2

where v1.project_end=v2.project_start;

Note:这个会用到，应用场景在连续范围上，可以是数字范围，也可以是日期范围

 

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

db2/mysql/postgresql/sqlserver:

select deptno,ename,hiredate,sal,

coalesce(cast(sal-next_sal as char(10)),'N/A') as diff

from (

select e.deptno,e.ename,e.hiredate,e.sal,

(select min(sal) from emp d

where d.deptno=e.deptno and d.hiredate=

(select min(hiredate) from emp d

where e.deptno=d.deptno and d.hiredate>e.hiredate)) as next_sal

from emp e

) x;

oracle:

select deptno,ename,sal,hiredate,

lpad(nvl(to_char(sal-next_sal),'N/A'),10) as diff

from (

select deptno,ename,sal,hiredate,

lead(sal)over(partition by deptno order by hiredate) next_sal

from emp

);

Note:当数据按照某种条件有序的时候，统计连续两行之间测差

 

3.定义连续值范围的开始点和结束点

db2/mysql/postgresql/sqlserver:

create view V2 as

select a.*,case when (

select b.project_id from V b

where a.project_id=b.project_id

) is null then 0 else 1

end as flag from V a;

 

select project_grp,

min(project_start) as project_start,

max(project_end) as project_end

from (

select a.project_id,a.project_start,a.project_end,

(select sum(b.flag) from V2 b where b.project_id<=a.project_id)

as project_grp

from V2 a

) x group by project_grp;

oracle:

select project_grp,min(project_start),max(project_end)

from (

select project_id,project_start,project_end,

sum(flag)over(order by project_id) project_grp

from (

select project_id,project_start,project_end,

case when lag(project_end)over(order by project_id)=project_start

then 0 else 1 end flag

from V

)

) group by project_grp;

Note：统计某个时间段内记录的情况，多少大小

 

4.补充范围内丢失的值

db2:

select x.yr,coalesce(y.cnt,0) cnt from (

select year(min(hiredate)over())-

mod(year(min(hiredate)over()),10)+

row_number()over()-1 yr

from emp fetch first 10 rows only

) x left join (

select year(hiredate) yr1,count(*) cnt

from emp group by year(hiredate)

) y on (x.yr=y.yr1);

oracle:

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 left join (

select to_number(to_char(hiredate,'YYYY')) yr,count(*) cnt

from emp group by to_number(to_char(hiredate,'YYYY'))

) y on (x.yr=y.yr);

postgresql/mysql:

select y.yr,coalesce(x.cnt,0) as cnt from (

select min_year-mod(cast(min_year as int),10)+rn as yr from (

select (select min(extract(year from hiredate)) from emp) as min_year,

id-1 as rn

from t10

) a

) y left join (

select extract(year from hiredate) as yr,count(*) as cnt

from emp group by extract(year from hiredate)

) x on (y.yr=x.yr);

sqlserver:

select x.yr,coalesce(y.cnt,0) cnt from (

select top (10)

(year(min(hiredate)over())-

year(min(hiredate)over())%10)+

row_number()over(order by hiredate)-1 yr

from emp

) x left join (

select year(hiredate) yr,count(*) cnt

from emp group by year(hiredate)

) y on (x.yr=y.yr);

Note：这个没有用过，并不是补充数据，是进行查询

 

5.生产连续数字值

db2/sqlserver:

with x(id) as (

select 1 from t1 union all

select id+1 from x where id+1<=10

)

select * from x;

db2:

with x(id) as (

values(1) union all

select id+1 from x where id+1<=10

)

select * from x;

oracle:

select array id from dual

model dimension by (0 idx)

measures(1 array)

rules iterate (10) (

array[iteration_number]=iteration_number+1

);

postgresql:

select id from generate_series(1,10) x(id);

Note: mysql本身有自增的关键字