Oracle Analytic functions - How to reuse a PARTITION BY clause?

I have written a complex SQL query with Oracle in which I want to use two analytic function over the same partition.

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 c开发者_StackOverflow社区ol7
                                   END
                                   ORDER BY col5 ASC, col6 DESC)
  FROM my_table;

Is there a more elegant syntax for factoring the PARTITION BY clause?

Thank you.

If you are referring to the standard WINDOW clause like this:

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

You can use subquery factoring, also known as the with-clause:

(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.

Partition definition could be reused with WINDOW clause. Starting from version 20c Oracle supports it:

Enhanced Analytic Functions

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.

SELECT

• Note that OVER window_name is 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

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 BY but have different sorting:

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 BY and ORDER BY but with different window size:

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)