Oracle Analytics - sql查询的分区和排序

This came up when answering another user's question (TheSoftwareJedi)...

在回答另一个用户的问题(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 these 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 is of course, why don't I get 0.2 for each row priority in col1, etc? LAST_VALUE is supposed to perform the ORDER BY first and then choose the last value from the partition. In the case of the query above, the partition is the entire recordset so I would to see my expected results above.

问题当然是,为什么我不能在col1等中获得每行优先级0.2? LAST_VALUE应首先执行ORDER BY,然后从分区中选择最后一个值。在上面的查询的情况下,分区是整个记录集,所以我将看到上面的预期结果。

Can anyone explain?

谁有人解释一下?

1 个解决方案

#1

when you include an ORDER by in the Partitioning clause, you can include a windowing clause to be explicit.

当您在Partitioning子句中包含ORDER by时,可以包含一个显式的窗口子句。

If you want these LAST_VALUES to be over all rows, you should include this after your Order By:

如果您希望这些LAST_VALUES遍布所有行,则应在Order By之后包含此行:

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

*预先与无约束之间的行

That should fix your query.

这应该可以解决您的疑问。

More details from the docs:

来自文档的更多细节:

If you do not specify a ROW or a RANGE clause, the window size is determined as follows:

如果未指定ROW或RANGE子句,则窗口大小确定如下:

If an ORDER BY clause is specified, the window starts with the first row in the partition (UNBOUNDED PRECEDING) and ends with the current row (CURRENT ROW).

如果指定了ORDER BY子句,则窗口以分区中的第一行(UNBOUNDED PRECEDING)开始,并以当前行(CURRENT ROW)结束。

If an ORDER BY clause is not specified, the window starts with the first row in the partition (UNBOUNDED PRECEDING) and ends with last row in the partition (UNBOUNDED FOLLOWING).

如果未指定ORDER BY子句,则窗口以分区中的第一行(UNBOUNDED PRECEDING)开始,并以分区中的最后一行结束(UNBOUNDED FOLLOWING)。

#1