如何根据行优先级将此Oracle结果集压缩为值，忽略空值？

I'll simplify the problem as much as possible:

我会尽可能简化问题:

I have an oracle table:

我有一个oracle表:

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?

而且似乎都没有忽略空值。任何提示?

3 个解决方案

#1

You need to put rownum = 1 OUTSIDE the analytical query

您需要将rownum = 1 OUTSIDE放在分析查询中

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

#2

-1

The COALESCE function may be of help to you here. Perhaps like ...

COALESCE功能可能对您有所帮助。也许就像......

select first_value(coalesce(col1,0) ignore nulls) over () col1,
       first_value(coalesce(col2,0) ignore nulls) over () col2,
       first_value(coalesce(col3,0) ignore nulls) over () col3
from THE_TABLE

#3

-1

An alternative:

SELECT
  MAX(col1) KEEP (DENSE_RANK LAST ORDER BY row_priority),
  MAX(col2) KEEP (DENSE_RANK LAST ORDER BY row_priority),
  MAX(col3) KEEP (DENSE_RANK LAST ORDER BY row_priority)
FROM the_table

The performance of this may be different from the analytic version; whether it is better or worse depends on your data and environment.

其性能可能与分析版本不同;是好还是坏取决于您的数据和环境。

#1