Oracle 分析函数 - 如何重用 PARTITION BY 子句？

Question

我用 Oracle 编写了一个复杂的 SQL 查询，其中我想在同一分区上使用两个分析函数。

让我们变得非常简单，但不要太过分：

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;

是否有更优雅的语法来分解 PARTITION BY 子句？

谢谢。

Answer 1

如果您指的是这样的标准 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);

那么我相信答案是否，Oracle 不支持此（使用 11gR2 检查）。

Answer 2

您可以使用子查询分解，也称为 with 子句：（

未经测试）

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

问候，
抢。

Answer 3

分区定义可以与 WINDOW 子句一起重用。从 Oracle 20c 版本开始支持它：

增强的分析函数

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一起使用

---

查询可以重写为：

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

---

请注意，窗口定义的一部分可以扩展，例如查询可以共享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);

我们不能执行共享相同的 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)