Oracle Analytic Functions - Resetting a Windowing Clause

Oracle Analytic functions - resetting a windowing clause

This is a little complicated. Instead of using rank() or the like, use lag() to see when something changes. Then do a cumulative sum of the flag.

select dept, date1,
       CASE WHEN StartFlag = 0 THEN 1
            ELSE 1+StartFlag+NVL(lag(StartFlag) over (order by date1),0)
       END as rnk
from (select t1.*,
             (case when dept = lag(dept) over (order by date1)
                   then 1
                   else 0
              end) as StartFlag
      from t1
     ) t1
order by date1;

Here is the SQLFiddle.

EDIT:

This is Gordon editing my own answer. Oops. The original query was 90% of the way there. It identified the groups where the numbers should increase, but did not assign the numbers within the groups. I would do this with another level of row_number() as in:

select dept, date1,
       row_number() over (partition by dept, grp order by date1) as rnk
from (select dept, date1, startflag,
             sum(StartFlag) over (partition by dept order by date1) as grp
      from (select t1.*,
                   (case when dept = lag(dept) over (order by date1)
                         then 0
                         else 1
                    end) as StartFlag
            from t1
           ) t1
     ) t1
order by date1;

So, the overall idea is the following. First use lag() to determine where a group begins (that is, where there is a department change from one date to the next). Then, assign a "group id" to these, by doing a cumulative sum. These are the records that are to be enumerated. The final step is to enumerate them using row_number().

Analytic function windowing clause

The error is in val preceding and val following. It should be 1 preceding and 1 following.

The number you specify there is relative to the current record, the record corresponding to val (in the given window order), so if you specify val there you are going back (or ahead) too far. You should need to get the min/max up to one record before (or after) the current record.

So:

WITH T AS
       (SELECT     CAST(LEVEL AS NUMBER) val
        FROM       DUAL
        CONNECT BY LEVEL < 4)
SELECT   val
        ,MIN(val) OVER(ORDER BY val RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) A
        ,MAX(val) OVER(ORDER BY val RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) B
        ,MIN(val) OVER(ORDER BY val RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) C
        ,MAX(val) OVER(ORDER BY val RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) D
FROM     T
WHERE    val IS NOT NULL
ORDER BY 1
/

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

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

Analytic functions and means of window clause

Maybe this is what you are looking for.

Sample data:

WITH
    emp (ID, EMP_NAME, HIRE_DATE, SALARY, DEPT) AS 
        (
            Select 601, 'HILLER',   To_Date('23-JAN-82', 'dd-MON-yy'),  4800,  60 From Dual Union All
            Select 602, 'MILLER',   To_Date('23-FEB-82', 'dd-MON-yy'),  9000,  60 From Dual Union All
            Select 603, 'SMITH',    To_Date('23-MAR-82', 'dd-MON-yy'),  4800,  60 From Dual Union All
            Select 604, 'FORD',     To_Date('23-APR-82', 'dd-MON-yy'),  4200,  60 From Dual Union All
            Select 605, 'KING',   To_Date('23-MAY-82', 'dd-MON-yy'),    6000,  60 From Dual Union All
            Select 201, 'SCOT',   To_Date('23-MAR-82', 'dd-MON-yy'), 13000,  20 From Dual Union All
            Select 202, 'JONES',    To_Date('23-AUG-82', 'dd-MON-yy'),  6000,  20 From Dual 
        ),

Create CTE named grid with several analytic functions and windowing clauses. They are not all needed but the resulting dataset below shows the logic with all components included.

    grid AS
        (
          Select 
              g.*, Max(GAP) OVER(PARTITION BY DEPT) "DEPT_MAX_GAP"
          From
              (
                  Select  
                      ROWNUM "RN",
                      Sum(1) OVER(Partition By DEPT Order By DEPT, HIRE_DATE, ID ROWS BETWEEN Unbounded Preceding And Current Row) "RN_DEPT",
                      ID, EMP_NAME, HIRE_DATE, DEPT, SALARY,
                      --
                      Nvl(Sum(SALARY) OVER(Partition By DEPT Order By DEPT, HIRE_DATE, ID ROWS BETWEEN 1 Following And Unbounded Following), 0) "SUM_SAL_LATER",
                      Nvl(Sum(1) OVER(Partition By DEPT Order By DEPT, HIRE_DATE, ID ROWS BETWEEN 1 Following And Unbounded Following), 0) "COUNT_EMP_LATER",
                      --
                      Nvl(Sum(SALARY) OVER(Partition By DEPT Order By DEPT, HIRE_DATE, ID ROWS BETWEEN 1 Following And Unbounded Following) /
                      Sum(1) OVER(Partition By DEPT Order By DEPT, HIRE_DATE, ID ROWS BETWEEN 1 Following And Unbounded Following), 0) "AVG_LATER",
                      --
                      SALARY - 
                      Nvl((
                      Sum(SALARY) OVER(Partition By DEPT Order By DEPT, HIRE_DATE, ID ROWS BETWEEN 1 Following And Unbounded Following) /
                      Sum(1) OVER(Partition By DEPT Order By DEPT, HIRE_DATE, ID ROWS BETWEEN 1 Following And Unbounded Following)
                      ), 0)  "GAP"
                  from 
                      emp
                  Order By
                      DEPT, HIRE_DATE, ID
              ) g
          Order By
              RN
        )

CTE grid resultiing dataset:

SELECT
    department_id,
    SUM(salary) total_sal
FROM
    (
        SELECT
            employee_id,
            first_name
            || ' '
            || last_name,
            department_id,
            salary,
            ROW_NUMBER()
            OVER(PARTITION BY department_id
                 ORDER BY
                     salary DESC  
                 --ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING     
            ) result
        FROM
            employees
    ) e
WHERE
    result <= 3
GROUP BY department_id 

SELECT 
    department_id,
    SUM(salary) OVER(PARTITION BY department_id) total_sal
FROM (
        SELECT e.*, 
            ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY salary DESC) result
        FROM employees e
) e
WHERE result <= 3
ORDER BY result FETCH FIRST ROW WITH TIES 

SELECT department_id, total_sal
FROM (
    SELECT e.*,
        SUM(salary) OVER(PARTITION BY department_id) total_sal
    FROM (
        SELECT e.*, 
            ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY salary DESC) result
        FROM employees e
    ) e
    WHERE result <= 3
) e
where result = 1

select P.*,
       first_value(product_name) over (
         partition by product_category
         order by price desc
         range between unbounded preceding and unbounded following
       ) AS MAX_PRICE,
       Last_value(product_name) over (
         partition by product_category
         order by price desc
         range between unbounded preceding and unbounded following
       ) MIN_PRICE
from   product p;

select P.*,
       first_value(product_name) over w AS MAX_PRICE,
       Last_value(product_name)  over w AS MIN_PRICE
from   product p
       window w as (
         partition by product_category
         order by price desc
         range between unbounded preceding and unbounded following
       )

WITH cte AS (
    SELECT branch, item, "date", order_num,
           RANK() OVER (PARTITION BY branch, item ORDER BY "date" DESC) rnk
    FROM yourTable
)

SELECT branch, item, "date", order_num
FROM cte
WHERE rnk = 1;