如何根据行优先级将此Oracle结果集压缩为值,忽略null? [英] How do I compress this Oracle resultset into values according to row priority, ignoring nulls?
本文介绍了如何根据行优先级将此Oracle结果集压缩为值,忽略null?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我会尽可能简化问题:
我有一个oracle表:
row_priority,col1,col2,col3
0,.1,100,{null}
12,{null},{null},3
,.2,{null},{null}
所需结果:
col1,col2,col3
.2,100,3
因此,根据行的优先级,它将覆盖以前的行值(如果给出)。
使用表上的分析函数解决一个解决方案,但它只是不行为...
我尝试:
select last_value(col1 ignore nulls)over()col1,
last_value(col2 ignore nulls)over()col2,
last_value ()col3
from(select * from THE_TABLE order by row_priority)
其中rownum = 1
$ b b
或反之:
select first_value(col1 ignore nulls)over()col1,
first_value col2忽略nulls)over()col2,
first_value(col3 ignore nulls)over()col3
from(select * from THE_TABLE order by row_priority desc)
其中rownum = 1
似乎没有忽略null。任何提示?
解决方案
您需要将rownum = 1 OUTSIDE分析查询
SELECT *
FROM(select last_value(col1 ignore nulls)over()col1,
last_value(col2 ignore nulls)over()col2,
last_value(col3 ignore null)over()col3
from(select * from THE_TABLE ORDER BY ROW_PRIORITY)
)
WHERE ROWNUM = 1
COL1 COL2 COL3
------ ------- ----
0.2 100 3
I'll simplify the problem as much as possible:
I have an oracle table:
row_priority, col1, col2, col3 0, .1, 100, {null} 12, {null}, {null}, 3 24, .2, {null}, {null}
Desired result:
col1, col2, col3 .2, 100, 3
So according to the priority of the row, it overrides previous row values, if given.
I'm attempting to work out a solution using analytical functions over the table, but it just isn't behaving...
I try:
select last_value(col1 ignore nulls) over () col1, last_value(col2 ignore nulls) over () col2, last_value(col3 ignore nulls) over () col3 from (select * from THE_TABLE order by row_priority) where rownum = 1
or the inverse:
select first_value(col1 ignore nulls) over () col1, first_value(col2 ignore nulls) over () col2, first_value(col3 ignore nulls) over () col3 from (select * from THE_TABLE order by row_priority desc) where rownum = 1
And neither seem to ignore nulls. Any hints?
解决方案You need to put rownum = 1 OUTSIDE the analytical query
SELECT * FROM ( select last_value(col1 ignore nulls) over () col1, last_value(col2 ignore nulls) over () col2, last_value(col3 ignore nulls) over () col3 from (select * from THE_TABLE ORDER BY ROW_PRIORITY) ) WHERE ROWNUM = 1
which results in (using your values above):
COL1 COL2 COL3 ------ ------- ---- 0.2 100 3
这篇关于如何根据行优先级将此Oracle结果集压缩为值,忽略null?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文