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

Question

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 col7
                                   END
                                   ORDER BY col5 ASC, col6 DESC)
  FROM my_table;

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

Thank you.

Safekeeping

Considering that the functions you are using are MAX and MIN and also col5 and col6 are in the partition and the order by, the order by clause for each column appears to be redundant. – Shinbone

You are right but this was intended as a simple example, it could be LAST_VALUE or any other analytic. – Safekeeping

possible duplicate of Applying Multiple Window Functions On Same Partition. This duplicate was not easy to find. – Safekeeping

Answer 1

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

Humility

I just noticed that trying to use it. So bad. – Safekeeping

Interesting! I wasn't aware that this was part of the SQL:2008 standard. I double-checked, it is defined in 7.4 <table expression>. Nice. So far, I have only observed this clause to be implemented in Postgres... – Mceachern

👍 Now window clause is supported: https://mcmap.net/q/1148144/-oracle-analytic-functions-how-to-reuse-a-partition-by-clause – Syphilology

Answer 2

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.

Grubby

Yes, but still it is a bit long. However +1 for suggesting WITH which is a great keyword in Oracle. – Safekeeping

You can shorten the with clause to "select t.*, [case expression] as new_col7 from mytable t" and use new_col7 in the query. – Grubby

Still, it won't really address the root problem: in your code you still repeate the (partition by ...) part! – Safekeeping

True. Although one might argue whether that is to be categorized as a problem. – Grubby

In my opinion anything that makes code less readable or less understandable is a problem. – Safekeeping

@Benoit, IMO, this is more readable than the query posed by the question, with the added benefit that it works in Oracle. I suppose "more readable" and "more understandable" is in the eye of the beholder ;-) +1 all around for an interesting question and good answers. – Those

Answer 3

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)

Syphilology