oracle合并连续数值
来源:互联网 发布:android 开启移动数据 编辑:程序博客网 时间:2024/05/14 05:06
原始数据
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
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) groupidfrom(with tmp as(select 1000 as id, 1010 as pid from dual union allselect 1011 as id, 1050 as pid from dual union allselect 1051 as id, 1056 as pid from dual union allselect 1059 as id, 1073 as pid from dual union allselect 1200 as id, 1210 as pid from dual union allselect 1211 as id, 1240 as pid from dual union allselect 1501 as id, 1570 as pid from dual union allselect 1571 as id, 1580 as pid from dual union allselect 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 allselect 1011 as id, 1050 as pid from dual union allselect 1051 as id, 1056 as pid from dual union allselect 1059 as id, 1073 as pid from dual union allselect 1200 as id, 1210 as pid from dual union allselect 1211 as id, 1240 as pid from dual union allselect 1501 as id, 1570 as pid from dual union allselect 1571 as id, 1580 as pid from dual union allselect 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 Lowfrom 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
0 0
- oracle合并连续数值
- Oracle 合并连续时间段
- 将连续空格合并
- 【数论 / 数值】【RQNOJ】连续正整数
- 合并连续数字的算法
- 判断数组中的数值是否连续
- 查找某张表中未使用到的连续数值
- 连续数值属性的CART decision tree
- oracle数值函数意义
- oracle 修改 sequence 数值
- Oracle中的数值函数
- Oracle中的数值类型
- oracle中的数值函数
- ORACLE数值型函数
- Oracle 数值类型
- oracle数值函数
- oracle 数据类型---数值型
- Oracle 内置数据类型 -- 数值
- 工作总结第十一天
- Oracle数据库中system和sys的区别
- Paint、Canvas、Matrix使用讲解(一、Paint)
- asp.net MVC前台View页面向后台Controller控制器传递数据的几种方式
- 辣妈萌宝APP很好地承载了所有的幸福记录
- oracle合并连续数值
- 位运算符,“,”逗号运算符,%
- 计算题
- 微课新鲜出炉了,欢迎大家品评!
- 辣妈萌宝APP是一款基于云技术的云相册分享平台
- Leetcode NO.31 Next Permutation
- Android软件开发之获取通讯录联系人信息
- xpages repeat的table里显示
- Hibernate相关资料