Over Clause in Oracle

OVER clause in Oracle

The OVER clause specifies the partitioning, ordering and window "over which" the analytic function operates.

Example #1: calculate a moving average

AVG(amt) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

date   amt   avg_amt
=====  ====  =======
1-Jan  10.0  10.5
2-Jan  11.0  17.0
3-Jan  30.0  17.0
4-Jan  10.0  18.0
5-Jan  14.0  12.0

It operates over a moving window (3 rows wide) over the rows, ordered by date.

Example #2: calculate a running balance

SUM(amt) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

date   amt   sum_amt
=====  ====  =======
1-Jan  10.0  10.0
2-Jan  11.0  21.0
3-Jan  30.0  51.0
4-Jan  10.0  61.0
5-Jan  14.0  75.0

It operates over a window that includes the current row and all prior rows.

Note: for an aggregate with an OVER clause specifying a sort ORDER, the default window is UNBOUNDED PRECEDING to CURRENT ROW, so the above expression may be simplified to, with the same result:

SUM(amt) OVER (ORDER BY date)

Example #3: calculate the maximum within each group

MAX(amt) OVER (PARTITION BY dept)

dept  amt   max_amt
====  ====  =======
ACCT   5.0   7.0
ACCT   7.0   7.0
ACCT   6.0   7.0
MRKT  10.0  11.0
MRKT  11.0  11.0
SLES   2.0   2.0

It operates over a window that includes all rows for a particular dept.

SQL Fiddle: http://sqlfiddle.com/#!4/9eecb7d/122

Oracle Partition By Keyword

The PARTITION BY clause sets the range of records that will be used for each "GROUP" within the OVER clause.

In your example SQL, DEPT_COUNT will return the number of employees within that department for every employee record. (It is as if you're de-nomalising the emp table; you still return every record in the emp table.)

emp_no  dept_no  DEPT_COUNT
1       10       3
2       10       3
3       10       3 <- three because there are three "dept_no = 10" records
4       20       2
5       20       2 <- two because there are two "dept_no = 20" records

If there was another column (e.g., state) then you could count how many departments in that State.

It is like getting the results of a GROUP BY (SUM, AVG, etc.) without the aggregating the result set (i.e. removing matching records).

It is useful when you use the LAST OVER or MIN OVER functions to get, for example, the lowest and highest salary in the department and then use that in a calculation against this records salary without a sub select, which is much faster.

Read the linked AskTom article for further details.

What are difference between KEEP and OVER in Oracle analytic SQL

Keep and OVER clause can be used in same query or individually. While OVER clause is used to work over analytical functions KEEP is used with DENSE_RANK FIRST to KEEP the value of FIRST row in DENSE_RANK.

Please refer to below post for detailed explanation.

POST

Using empty OVER() clause in subquery

Here is another option for you to try:

SELECT t1.id,
       t3.code,
       t3.processed_date,
       (t1.total / t2.rate) 
FROM table1 t1
         JOIN table2 t2 ON t2.code= t1.code
         JOIN table3 t3 ON t3.id = t1.id
         JOIN table4 t4 ON t4.code = t1.code
    AND t4.type IN ('value1', 'value2', 'value3')
    AND t3.processed_date >= '01 JUL 2019'
    AND t3.processed_date < '22 JUL 2019'
    AND EXISTS (SELECT 1
                                FROM tableCore tc
                                WHERE tc.effective_date <= t3.processed_date
                                  AND t1.code = tc.code
                                HAVING t2.effective_date = max(tc.effective_date))

For proper performance analysis, it would be great to have the Sizes of your tables, and an explain Plan of the different queries (and a Check, if the Optimizer Statistics are accurate).

And FYI: Your last query with row_number() will not deliver the same result, as the windowing function will be evaluated before the join ct.effective_date <= t3.processed_date, and therefore you will miss some rows.

MAX() OVER PARTITION BY in Oracle SQL

Use window function ROW_NUMBER() OVER (PARTITION BY receipt_item ORDER BY receipt_date DESC) to assign a sequence number to each row. The row with the most recent receipt_date for a receipt_item will be numbered as 1.

WITH
-- various other subqueries above...

    AllData AS
    (
        SELECT VEND_NUM, VEND_NAME, RECEIPT_NUM, RECEIPT_ITEM, RECEIPT_DATE,
        ROW_NUMBER() OVER (PARTITION BY RECEIPT_ITEM ORDER BY RECEIPT_DATE DESC ) AS RN
        FROM tblVend
            INNER JOIN tblReceipt ON VEND_NUM = RECEIPT_VEND_NUM
        WHERE
            VEND_NUM IN ( '100','200')  AND RECEIPT_DATE >= '01-Jan-2017'
    )
   SELECT VEND_NUM, VEND_NAME, RECEIPT_NUM, RECEIPT_ITEM, RECEIPT_DATE
   FROM AllData WHERE RN = 1

Oracle SQL: Further sort PARTITION BY groups based on first row in each partition

Luckily, you only need to add an analytic max() to the order by clause. You don't need to do anything else.

Suppose "current query" is your existing query, not ordered yet in any way (no order by clause). Add the following at the very end:

... existing query ...
order  by max(timetocomplete) over (partition by itemkey) desc,
          itemkey,
          timetocomplete desc
;

Note that you do not need to add the analytic function to the select clause. The SQL standard says you do; Oracle syntax says you don't. Oracle is taking care of the small additional steps for us, behind the scenes.

This computes the max time to complete for each key. It orders by that max first. In the case of ties (two or more different keys with the same max time to complete), it further orders by key first, and then within each key, by time to complete (descending).

max, over partition by, oracle, take max rownum record

This seems very curious. You cannot refer to an alias in the where clause for the select that defines it. So, put the definition in a subquery:

select t.*,
from (select id, NAME_TYPE, NAME, EFFDT, 
             rownum as rn, max(rownum) over (partition by id) as max_rownum
      from name_table 
     ) nt
where rn <= max_rownum and
      rownum <= 2000;

---

Related Topics