CODEWITHSUNDEEP
sqloracle-databasewindow-functionsdata-partitioning
Danatela
Danatela

Reputation: 349

Define window and use it in several analytic columns

I have an oracle query in which I need use several analytic functions within the same window. How can I define a window once and then use it for each of these calls?

I.e. I wanna to write it like in PostGRESql:

SELECT
    sum(salary) OVER w,
    avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

Forgot to mention, the goal is to avoid code duplication, not to speed up the query.

Upvotes: 1

Views: 94

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175686

Starting from version 20c Oracle supports window clause syntax.

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

So your query will work perfectly fine without changes:

SELECT
    sum(salary) OVER w,
    avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

Upvotes: 0

MT0
MT0

Reputation: 167972

Oracle does not support that syntax. Just specify the window in each analytic statement:

SELECT
    sum(salary) OVER (PARTITION BY depname ORDER BY salary DESC),
    avg(salary) OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;

If you look at the explain plan for the query then there should only be a single WINDOW (SORT) operation as Oracle knows to re-use the window for both statements.

Upvotes: 2

Related Questions