Oracle Analytics-SQL查询的分区和顺序 [英] Oracle Analytics - partitions and ordering of sql queries

问题描述

这是在回答其他用户的

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

给出下表:

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

以及以下查询:

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);

我得到这些结果:

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

预期:

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

当然，问题是，为什么我在col1等中的每一行优先级都不能得到0.2? LAST_VALUE应该首先执行ORDER BY，然后从分区中选择最后一个值.对于上面的查询，该分区是整个记录集，因此我将在上面看到我的预期结果.

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.

有人可以解释吗?

推荐答案

当您在Partitioning子句中包含ORDER by时，可以包括一个显式的windowing子句.

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

如果您希望这些LAST_VALUES遍及所有行，则应在Order By之后添加该字符:

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

未绑定的跟前和未绑定的跟随之间的行

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

那应该可以解决您的查询.

That should fix your query.

文档中的更多详细信息:

More details from the docs:

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

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

• 如果指定了ORDER BY子句，则窗口从分区的第一行(UNBOUNDED PRECEDING)开始，到当前行(CURRENT ROW)结束.
• 如果未指定ORDER BY子句，则窗口从分区的第一行开始(UNBOUNDED PRECEDING)，然后从分区的最后一行开始(UNBOUNDED FOLLOWING).

这篇关于Oracle Analytics-SQL查询的分区和顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！