涉及区间的查询

1.找出一系列连续的值

　　问题：判断哪些行表示一系列连续的项目。即某一行项目开始时间和前一行的项目结束时间是一致的。

　　示例表：

　　解决方案：利用窗函数 LEAD OVER 来查找下一行的项目开始时间，从而避免使用自链接。需要按照项目ID进行排序。

select * from (
    select 
     ProjId 项目ID,ProjStart 项目开始时间,projEnd 项目结束时间,lead(ProjStart) over(order by ProjId ) 下一行项目开始时间 from projs
) V

where 项目结束时间 = 下一行项目开始时间

　　注意：使用窗口函数时，别忘了它们是在 From 和 Where 子句之后执行的。

　　使用自连接：

select * from (
    select 
     a.ProjId 项目ID,a.ProjStart 项目开始时间,a.projEnd 项目结束时间,b.ProjStart 下一行项目开始时间 from projs a
     join projs b on a.ProjId = b.ProjId-1
) V

2.找出同一个分组或分区中相邻行的差

　　问题：返回每位员工的部门编号，姓名，薪水以及与当前部门中（部门编号相同）下一位员工的薪水差。这里的下一位员工是通过获聘时间确定的。对于每个部门最后获聘的员工，将薪水差设置为 N/A。

　　示例表：

　　解决方案：同样使用窗函数 LEAD OVER 访问下一行数据。因为比较的同部门的薪水，所以需要使用 PARTITION BY 部门编号进行分区。

with next_sal_table (DeptNo,EName,SAL,HireDate,Next_Sal)
as
(
    select DeptNo,EName,SAL,HireDate,lead(SAL) over(partition by DeptNo order by HireDate) as Next_Sal
    from emps
)

select DeptNo,EName,SAL,HireDate,coalesce(cast(SAL-Next_Sal as char),'N/A') from next_sal_table

　　为了展示解决方案的多样性，这里使用了 CTE （公用表表达式）。这里为了将 NULL 设置为 ‘N/A’ 使用了 COALESCE 函数，返回NULL列表中的第一个非值。

　　在使用函数 LEAD OVER 时需要考虑存在重复值的情况。如果在上述表中存在相同部门相同获聘时间的员工数据，那么上述SQL语句就不正确了。因为需要的事当前员工与不同获聘日期的下一个员工的薪水差。这里我们往上数表中插入部门编号为10 获聘日期都是 ‘2022-01-02’ 的员工数据。

　　这种情况需要跳过相同获聘日期的数据，函数 LEAD支持传入跳过的行数。

　　解决方案：先计算出相同部门相同获聘日期员工的数量以及当前数据在这个分组的序号，cnt-rn+1 便是需要跳过的行数。

select DeptNo,EName,SAL,HireDate,coalesce(cast(SAL-Next_Sal as char),'N/A') from (
    select DeptNo,EName,SAL,HireDate,
    lead(SAL,cnt-rn+1) over(partition by DeptNo order by HireDate) as Next_Sal
    from (
        select DeptNo,EName,SAL,HireDate,count(*) over(partition by DeptNo,HireDate) as cnt,row_number() over(partition by DeptNo,HireDate order by Sal) rn
        from emps
    ) a

) next_sal_table

3.找出连续值构成的区间的起点和终点

　　问题：找出连续值构成的区间，并只返回区间的起点和终点。跟上面第一个示例不同，如果某行并非一组连续值的一部分，依然要返回它。因为其自身构成区间的起点和终点。

　　解决方案：首先找出所有的区间。使用窗函数 LAG Over 判断当前行的项目开始日期是否与上一行的项目结束日期是否一致，如果一致则属于分组的一部分。如果某行的项目开始日期不与上一行一致，项目结束日期也不与下一行一直，则它自己构成一组。

　　　　找出所有分组后，给各组编号。最后找出每组的开始日期和结束日期。

select proj_group,min(ProjStart),max(projEnd) from 
(
select *,sum(flag) over(order by ProjId) as proj_group 
from (
SELECT ProjId ,ProjStart ,projEnd ,
case when lag(projEnd) over(order by ProjId ) = ProjStart 
    then 0 else 1
    end  flag
    from projs ) a
    ) b
    group by proj_group

　　下面分步讲解：

　　1.找出各个组，并给每个组的第一行做标记

SELECT ProjId ,ProjStart ,projEnd ,
case when lag(projEnd) over(order by ProjId ) = ProjStart 
    then 0 else 1
    end  flag
    from projs

　　2.使用滑动小记 sum(flag) over(order by ProjId) as proj_group 将每个组内各行设置为同一个标记。

　　3.最后根据上面的组标记进行分组聚合，取最小开始日期为连续项目的开始日期，最大结束日期为连续项目的结束日期。

4.填补值区间空隙

　　问题：返回10年间每一年聘请的员工数，但其中有些年份并没有聘请任何员工。

　　解决方案：创建一张包含 1到10 数字的透视表，也可以通过 CTE 的递归功能动态创建。根据透视表生成一个每个年份的临时表，然后外连接根据员工表聘请年份分组统计的表。使用 coalesce 函数将没有聘请数据的值设置为0。

WITH RECURSIVE Ta10 (id)
as
(
    select 1 as id
    union all
    select id+1 from Ta10
    where id <10
)

select * from Ta10

select y.yr,coalesce(x.cnt,0) as cnt from (
select   min_year +rn as yr
 from (
    select (
        select min(extract(year from HireDate)) from emps) as min_year,id-1 as rn from t10 
) a ) y
left join (
    select extract(year from HireDate) as yr,count(*) as cnt from emps 
    group by extract(year from HireDate)
) x on x.yr = y.yr

5.生成连续的数字值

　　上面已经实现了这个问题，使用 CTE (公用表表达式) 的递归功能可以解决这个问题。　　

posted @ 2022-08-30 20:38 Ruby_Lu 阅读(474) 评论(0) 编辑收藏举报

刷新页面返回顶部

阿飞

涉及区间的查询

公告