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.

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. — Mike Meyers, Mar 29 '11 at 13:25
You are right but this was intended as a simple example, it could be LAST_VALUE or any other analytic. — Benoit, Mar 29 '11 at 13:29
possible duplicate of [Applying Multiple Window Functions On Same Partition](http://stackoverflow.com/questions/1896102/applying-multiple-window-functions-on-same-partition). This duplicate was not easy to find. — Benoit, Mar 30 '11 at 07:15

score 13 · Accepted Answer · 2011-03-29T13:31:32.773

13

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

edited Mar 29 '11 at 13:31

answered Mar 29 '11 at 13:23

I just noticed that trying to use it. So bad. – Benoit Mar 29 '11 at 13:33
Interesting! I wasn't aware that this was part of the SQL:2008 standard. I double-checked, it is defined in `7.4 `. Nice. So far, I have only observed this clause to be implemented in Postgres...
– Lukas Eder Sep 08 '12 at 07:37
Now window clause is supported: https://stackoverflow.com/a/60247454/5070879 – Lukasz Szozda Feb 16 '20 at 10:50

score 4 · Answer 2 · answered Mar 29 '11 at 13:20

4

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.

answered Mar 29 '11 at 13:20

Rob van Wijk

17,555
5
39
55

Yes, but still it is a bit long. However +1 for suggesting `WITH` which is a great keyword in Oracle. – Benoit Mar 29 '11 at 13:27
You can shorten the with clause to "select t.*, [case expression] as new_col7 from mytable t" and use new_col7 in the query. – Rob van Wijk Mar 29 '11 at 13:43
Still, it won't really address the root problem: in your code you still repeate the `(partition by ...)` part! – Benoit Mar 29 '11 at 13:46
1

True. Although one might argue whether that is to be categorized as a problem. – Rob van Wijk Mar 29 '11 at 14:18
In my opinion anything that makes code less readable or less understandable is a problem. – Benoit Mar 29 '11 at 14:27
@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. – DCookie Mar 29 '11 at 16:07

Lukasz Szozda · Answer 3 · 2020-02-16T10:52:58.890

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)

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

3 Answers3