Oracle Analytics - sql query sections and orders

Question

This happened when answering another user question (TheSoftwareJedi) ...

Given the following table:

ROW_PRIORITY   COL1     COL2    COL3
0              0.1      100     <NULL>
12             <NULL>   <NULL>  3
24             0.2      <NULL>  <NULL>

and the following query:

select  'B' METRIC, ROW_PRIORITY,
        last_value(col1 ignore nulls) over (ORDER BY ROW_PRIORITY) col1,
        last_value(col2 ignore nulls) over (ORDER BY ROW_PRIORITY) col2,
        last_value(col3 ignore nulls) over (ORDER BY ROW_PRIORITY) col3
from    (SELECT * FROM ZTEST);

I get the following results:

METRIC  ROW_PRIORITY    COL1    COL2    COL3
B            0          0.1     100     <NULL>
B           12          0.1     100     3
B           24          0.2     100     3

EXPECTED:

METRIC  ROW_PRIORITY    COL1    COL2    COL3
B            0          0.2     100     3
B           12          0.2     100     3
B           24          0.2     100     3

The question, of course, is why I am not getting 0.2 for each line priority in col1, etc.? LAST_VALUE must first execute ORDER BY, and then select the last value from the section. In the case of the above query, the section is the entire set of records, so I should see my expected results above.

Can anyone explain?

Answer 1

when you include ORDER in a Partitioning clause, you can explicitly include the windowing clause.

, LAST_VALUES , . :

.

:

ROW RANGE, :

• ORDER BY, ( ) (CURRENT ROW).
• ORDER BY , ( ) ​​ ( ).