oracle合并连续数值

最新推荐文章于 2022-09-29 15:27:54 发布

原创最新推荐文章于 2022-09-29 15:27:54 发布 · 950 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#oracle #合并数值 #lag #lead

PL/SQL 专栏收录该内容

13 篇文章

订阅专栏

这篇博客介绍了如何在Oracle数据库中合并连续的数值区间。通过使用`LAG`和`LEAD`函数，配合窗口函数，解决数据中连续数值的合并问题，确保不丢失最后的连续数据段。文中给出了多种SQL解决方案，并考虑了实际业务中可能出现的交集和子集情况。

原始数据
ID PID
1000 1010
1011 1050
1051 1056
1059 1073
1200 1210
1211 1240
1501 1570
1571 1580
1581 1600

效果
startNO endNo
1000 1056
1059 1073
1200 1240
1501 1600

select id, lead(priv,1,pid) over(order by id) - 1 pid
    from
    (
    WITH cat AS
    (
    select '1000' ID, '1010'+1 PID FROM dual union
    SELECT '1011' ID, '1050'+1 PID FROM dual UNION
    SELECT '1051' ID, '1056'+1 PID FROM dual UNION
    SELECT '1059' ID, '1072'+1 PID FROM dual UNION
    SELECT '1200' ID, '1210'+1 PID FROM dual UNION
    SELECT '1211' ID, '1240'+1 PID FROM dual UNION
    SELECT '1501' ID, '1570'+1 PID FROM dual UNION
    SELECT '1571' ID, '1580'+1 PID FROM dual UNION
    SELECT '1581' ID, '1600'+1 PID FROM dual
    )
    SELECT ID, pid, (id - lag(pid,1,1)over(order by id)) diff,  lag(pid,1,1)over(order by id) priv
    FROM cat  
    )  where diff<>0;

其实上面的语句仍然有部分问题，最后的两条diff=0的数据在整合的时候被过滤掉了，如何解决？

1、造一条比max(id),max(pid)还大的id值，然后再进行查询；

2、利用数值连续的作为分组取最大最小值

SQL代码如下：

select groupid, min(id), max(pid)
from 
(
select id, pid,  lead(priv,1,pid) over(order by id), sum(diff) over(order by id) groupid
from
(
with tmp as(
select 1000 as id,  1010 as pid  from dual union all
select 1011 as id,  1050 as pid  from dual union all
select 1051 as id,  1056 as pid  from dual union all
select 1059 as id,  1073 as pid  from dual union all
select 1200 as id,  1210 as pid  from dual union all
select 1211 as id,  1240 as pid  from dual union all
select 1501 as id,  1570 as pid  from dual union all
select 1571 as id,  1580 as pid  from dual union all
select 1581 as id,  1600 as pid  from dual  
)
select id , pid,  lag(pid,1,1) over(order by pid) priv,
       id - lag(pid, 1, 1) over(order by id) -1 diff 
from tmp
) a
)
group by groupid;

或者是

select  groupid, min(id), max(pid)
from
(
select id, pid, sum(low) over(order by id) as groupid 
from 
(
with tmp as(
select 1000 as id,  1010 as pid  from dual union all
select 1011 as id,  1050 as pid  from dual union all
select 1051 as id,  1056 as pid  from dual union all
select 1059 as id,  1073 as pid  from dual union all
select 1200 as id,  1210 as pid  from dual union all
select 1211 as id,  1240 as pid  from dual union all
select 1501 as id,  1570 as pid  from dual union all
select 1571 as id,  1580 as pid  from dual union all
select 1581 as id,  1600 as pid  from dual  
)
select id , pid,  
       MAX(pid) over(order by id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) max_pid,
       (case when MAX(pid+1) over(order by id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) >= id then 0 else 1 end) as Low
from tmp
) 
) group by groupid;

实际业务中，可能还会出现交集、子集等的情况，以下为解决方案：

with tmp as(
select 1005 as id, 1010 as pid from dual union all
select 1003 as id, 1020 as pid from dual union all
select 1000 as id, 1050 as pid from dual union all
select 1051 as id, 1056 as pid from dual union all
select 1059 as id, 1073 as pid from dual union all
select 1200 as id, 1210 as pid from dual union all
select 1211 as id, 1240 as pid from dual union all
select 1501 as id, 1570 as pid from dual union all
select 1571 as id, 1580 as pid from dual union all
select 1581 as id, 1600 as pid from dual
), tmp1 as
(
select id , pid,
(case when max(pid) over(order by id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) >= id+1
then max(pid) over(order by id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) else pid end) new_pid
from tmp
), tmp2 as
(
select id, pid, new_pid,
(case when lag(new_pid,1,1) over(order by id)+1 >= id then 0 else 1 end) diff
from tmp1
), tmp3 as
(
select id, pid, new_pid, diff, sum(diff) over(order by id) groupid
from tmp2
)
select groupid, min(id), max(new_pid)
From tmp3
group by groupid