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子句？

谢谢。

- Benoit

---

考虑到您正在使用的函数是 MAX 和 MIN，并且 col5 和 col6 都在 partition 和 order by 中，每个列的 order by 子句似乎是多余的。 - Mike Meyers

你说得对，但这只是一个简单的例子，它可以是LAST_VALUE或任何其他分析函数。 - Benoit

可能是在同一分区上应用多个窗口函数的重复问题。这个重复问题不容易找到。 - Benoit

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 上进行了验证）。

- user330315

---

我刚刚注意到尝试使用它。太糟糕了。 - Benoit

有趣！我不知道这是 SQL：2008 标准的一部分。我双重检查了，它在 7.4 <table expression> 中有定义。很好。到目前为止，我只观察到 Postgres 实现了这个子句... - Lukas Eder

现在已经支持window子句：https://dev59.com/PlXTa4cB1Zd3GeqP36-I#60247454 - Lukasz Szozda

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

祝好，
罗布。

- Rob van Wijk

---

是的，但还是有点长。不过因为建议使用 Oracle 中很好的关键字 WITH，所以加一分。 - Benoit

你可以将 with 子句缩短为 "select t.*, [case expression] as new_col7 from mytable t"，并在查询中使用 new_col7。 - Rob van Wijk

然而，这并没有真正解决根本问题：在您的代码中，您仍然重复了(partition by ...)部分！ - Benoit

正确。虽然有人可能会争论这是否应该归类为问题。 - Rob van Wijk

在我看来，任何使代码变得不易读或不易理解的因素都是一个问题。 - Benoit

@Benoit，依我之见，这比问题中提出的查询更易读，并带有它在Oracle中工作的额外好处。我想“更易读”和“更易懂”是因人而异的；-) 对于一个有趣的问题和好答案，我点赞。 - DCookie

Answer 3

通过使用 WINDOW 子句，可以重用分区定义。从 20c 版本开始，Oracle 支持它：

增强的分析函数

现在 SELECT 语句的 query_block 子句支持 window_clause，该子句实现了 SQL 标准表达式中定义的窗口子句，如 SQL：2011 标准所定义。

SELECT

• 请注意，OVER window_name 与 OVER（window_name ...） 不等价。 OVER（window_name ...） 意味着复制和修改窗口规范，并且如果引用的窗口规范包括 windowing_clause，则会被拒绝。

• 您不能在 windowing_clause 中使用 existing_window_name

---

查询可以重写为：

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)

- Lukasz Szozda