请教Oracle依时间顺序分组 并行转列
id date
步骤3 11/17 00:00
步骤1 11/17 01:00
步骤2 11/17 02:00
步骤3 11/17 03:00
步骤1 11/17 04:00
步骤2 11/17 05:00
步骤3 11/17 06:00
原始数据如以上所示,想要得到这样的结果:
序号 步骤1 步骤2 步骤3
1 11/17 01:00 11/17 02:00 11/17 03:00
2 11/17 04:00 11/17 05:00 11/17 06:00
如何通过sql取得这样的结果?
[最优解释]
with test as (
SELECT 'bz3' as id,'11/17 00:00' AS dateD from dual
union all
SELECT 'bz1' as id,'11/17 01:00' AS dateD from dual
union all
SELECT 'bz2' as id,'11/17 02:00' AS dateD from dual
union all
SELECT 'bz3' as id,'11/17 03:00' AS dateD from dual
union all
SELECT 'bz1' as id,'11/17 04:00' AS dateD from dual
union all
SELECT 'bz2' as id,'11/17 05:00' AS dateD from dual
union all
SELECT 'bz3' as id,'11/17 06:00' AS dateD from dual
union all
SELECT 'bz1' as id,'11/17 07:00' AS dateD from dual
)
select *
from (select max(decode(ID, 'bz1', dateD, '')) as str1,
max(decode(ID, 'bz2', dateD, '')) as str2,
max(decode(ID, 'bz3', dateD, '')) as str3
from (select row_number() over(order by dateD) - regexp_substr(id, '\d+') as rn,
ID,
dateD
from test)
group by rn)
where str1 is not null
and str2 is not null
and str2 is not null
--------------------------------------------
111/17 01:0011/17 02:0011/17 03:00
211/17 04:0011/17 05:0011/17 06:00
偷懒啦。
[其他解释]
弟弟，你要仔细研究一下3楼的sql，应该满足你的需求的
即使有偏差，改动也不大。
row_number() over(order by dateD) --按照时间排序
regexp_substr(id, '\d+') --截取 步骤1 步骤2 步骤3 后面的 1,2,3 这三个数字
--两者相减，得到的结果 既可以达到你想要的分组目的
--仔细研究一下，本来想给你写一个，定睛一瞧，3楼已经给出了答案，呵呵。
另外，看明白之后记得这里也要给分哦。
[其他解释]
步骤3 11/17 00:00
这个归到哪一组呢
[其他解释]
忘记说明,每一组从步骤1开始,步骤1前面的记录忽略.
如果不能忽略,则自己归一组囖.
[其他解释]
不好意思,大哥,我的sample data 太有规律了,实际上,后面的时间是完全没有规律的.
但是效果就是你这个例子的结果 .
我想要把按时间为顺序的步骤1-步骤3为一组,顺着下来,遇到步骤1就归到组的Id+1.
[其他解释]
with test as
(SELECT 'bz3' as id, '11/17 00:00' AS dateD
from dual
union all
SELECT 'bz1' as id, '11/17 01:00' AS dateD
from dual
union all
SELECT 'bz2' as id, '11/17 02:00' AS dateD
from dual
union all
SELECT 'bz3' as id, '11/17 03:00' AS dateD
from dual
union all
SELECT 'bz1' as id, '11/17 04:00' AS dateD
from dual
union all
SELECT 'bz2' as id, '11/17 05:00' AS dateD
from dual
union all
SELECT 'bz3' as id, '11/17 06:00' AS dateD
from dual
union all
SELECT 'bz1' as id, '11/17 07:00' AS dateD from dual)
select rk,
max(decode(ID, 'bz1', dateD, '')) as str1,
max(decode(ID, 'bz2', dateD, '')) as str2,
max(decode(ID, 'bz3', dateD, '')) as str3
from (select id,
dateD,
row_number() over(partition by id order by dateD asc) rk
from test)
group by rk;
------其他解决方案--------------------
对不起,是我没看清楚.
可是可是又有一个新的问题.我补充在楼下.
[其他解释]
嘿,确实我没有看明白,焦点放在辣个时间上去了.
这个做法是通过减去步骤1,2,3 的数字,得到一个共同的数字去做分组的依据.
可是实际上的数据,步骤1,2,3这个字段的值是一些特定的值,但不会有这个规律,而且不一定每个分组出现的值都是一样的,例如:
第一组:
抽真空开始
抽真空结束
泄气开始
镀膜开始
镀膜结束
泄气结束
第二组
抽真空开始
抽真空结束
泄气开始
镀膜开始
镀膜结束
镀膜开始
镀膜结束
异常1
泄气结束
这样的数据可以套用吗?对不起,我sql比较烂...
另外,我不是弟弟,哇哈哈哈~
[其他解释]
三楼的SQL很巧妙，值得一赞
[其他解释]
这个好办，你建一个表
把你写的这些东西插进去，按照指定顺序编个号，这个表作为配置表
再利用3楼的sql 就ok了，问题不大。