Oracle 分析函数 - 如何重用 PARTITION BY 子句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5473038/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Analytic functions - How to reuse a PARTITION BY clause?
提问by Benoit
I have written a complex SQL query with Oracle in which I want to use two analytic function over the same partition.
我用 Oracle 编写了一个复杂的 SQL 查询，我想在同一个分区上使用两个分析函数。
Let's be quite simple but not too much:
让我们很简单但不要太多：
SELECT col1,
MAX(col2) OVER(PARTITION BY col3, col4, col5, col6,
CASE WHEN col7 LIKE 'foo'
THEN SUBSTR(col7,1,5)
ELSE col7
END
ORDER BY col5 ASC, col6 DESC),
MIN(col2) OVER(PARTITION BY col3, col4, col5, col6,
CASE WHEN col7 LIKE 'foo'
THEN SUBSTR(col7,1,5)
ELSE col7
END
ORDER BY col5 ASC, col6 DESC)
FROM my_table;
Is there a more elegant syntax for factoring the PARTITION BYclause?
是否有更优雅的语法来分解PARTITION BY子句？
Thank you.
谢谢你。
回答by a_horse_with_no_name
If you are referring to the standard WINDOW clause like this:
如果您指的是像这样的标准 WINDOW 子句：
SELECT col1,
MAX(col2) OVER(w),
MIN(col2) OVER(w)
FROM my_table
WINDOW w AS (PARTITION BY col3, col4, col5, col6,
CASE WHEN col7 LIKE 'foo'
THEN SUBSTR(col7,1,5)
ELSE col7
END
ORDER BY col5 ASC, col6 DESC);
then I believe the answer is no, Oracle does not support this (checked with 11gR2).
那么我相信答案是否定的，Oracle 不支持这个（用 11gR2 检查）。
回答by Rob van Wijk
You can use subquery factoring, also known as the with-clause:
您可以使用子查询分解，也称为 with 子句：
(untested)
（未经测试）
with t as
( select col1
, col2
, col3
, col4
, col5
, col6
, case col7
when 'foo' then
substr(col7,1,5)
else
col7
end col7
from my_table
)
select col1
, max(col2) over (partition by col3,col4,col5,col6,col7 order by col5,col6 desc)
, min(col2) over (partition by col3,col4,col5,col6,col7 order by col5,col6 desc)
from t
Regards,
Rob.
问候，
罗布。
回答by Lukasz Szozda
Partition definition could be reused with WINDOWclause. Starting from version 20c Oracle supports it:
分区定义可以与WINDOW子句重用。从 20c 版开始 Oracle 支持它：
The query_block clause of a SELECT statement now supports the window_clause, which implements the window clause of the SQL standard table expression as defined in the SQL:2011 standard.
Note that OVER window_nameis not equivalent to OVER (window_name …). OVER (window_name …)implies copying and modifying the window specification, and will be rejected if the referenced window specification includes a windowing_clause.
You cannot use existing_window_name with windowing_clause
SELECT 语句的 query_block 子句现在支持 window_clause，它实现了 SQL:2011 标准中定义的 SQL 标准表表达式的 window 子句。
请注意，OVER window_name不等同于OVER (window_name ...)。OVER (window_name ...)暗示复制和修改窗口规范，如果引用的窗口规范包含 windowing_clause，则将被拒绝。
您不能将 existing_window_name 与 windowing_clause 一起使用
Query could be rewritten as:
查询可以改写为：
SELECT col1,
MAX(col2) OVER w AS max_col2,
MIN(col2) OVER w AS min_col2
FROM my_table
WINDOW w AS (PARTITION BY col3, col4, col5, col6,
CASE WHEN col7 LIKE 'foo'
THEN SUBSTR(col7,1,5)
ELSE col7
END
ORDER BY col5 ASC, col6 DESC);
Note that part of window definition could be extended, for instance queries could share PARTITION BYbut have different sorting:
请注意，部分窗口定义可以扩展，例如查询可以共享PARTITION BY但具有不同的排序：
SELECT col_x,
FIRST_VALUE(col_y) OVER (w ORDER BY col3),
FIRST_VALUE(col_z) OVER (w ORDER BY col4)
FROM tab
WINDOW w AS (PARTITION BY col1, col2);
We cannot perform sth like sharing the same PARTITION BYand ORDER BYbut with different window size:
我们不能像共享相同的执行某事PARTITION BY，并ORDER BY用不同的窗口大小，但是：
SELECT col_x,
AVG(col_y) OVER (w ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS moving_avg_3,
AVG(col_y) OVER (w ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS moving_avg_5
FROM tab
WINDOW w AS (PARTITION BY col1, col2 ORDER BY col3)
