Analytic Function MAX Issues

Question

WITH t AS (
    SELECT 9 a
        ,sysdate d
        ,1 c
        ,1 e
    FROM dual       
    UNION       
    SELECT 1 a
        ,sysdate - 5 d
        ,2 c
        ,1 e
    FROM dual       
    UNION       
    SELECT 2 a
        ,sysdate - 2 d
        ,6 c
        ,1 e
    FROM dual       
    UNION       
    SELECT 5 a
        ,sysdate - 4 d
        ,2 c
        ,1 e
    FROM dual       
    UNION       
    SELECT 3 a
        ,sysdate - 1 d
        ,1 c
        ,1 e
    FROM dual       )
SELECT MAX(CASE 
            WHEN c = 2
                THEN d
            ELSE NULL
            END) OVER (
        PARTITION BY e ORDER BY d DESC          ) DT1
    ,MAX(CASE 
            WHEN c = 2
                THEN d
            ELSE NULL
            END) OVER (PARTITION BY e) DT2
    ,t.*
FROM t

Question: In the above query I am not able to understand Why am getting Nulls in DT1 wherein not in DT2. What is the impact of Order By Clause in the analytical function MAX here?. Please help me understanding this

result:

DT             || DT1            || A || D              || C    || E 
NULL           || 10/4/2016 0:03 || 9 || 10/8/2016 0:03 || 1    || 1 
NULL           || 10/4/2016 0:03 || 3 || 10/7/2016 0:03 || 1    || 1 
NULL           || 10/4/2016 0:03 || 2 || 10/6/2016 0:03 || 6    || 1
10/4/2016 0:03 || 10/4/2016 0:03 || 5 || 10/4/2016 0:03 || 2    || 1 
10/4/2016 0:03 || 10/4/2016 0:03 || 1 || 10/3/2016 0:03 || 2    || 1

DT || DT1 || A || D || C || E NULL || 10/4/2016 0:03 || 9 || 10/8/2016 0:03 || 1 || 1 NULL || 10/4/2016 0:03 || 3 || 10/7/2016 0:03 || 1 || 1 NULL || 10/4/2016 0:03 || 2 || 10/6/2016 0:03 || 6 || 1 10/4/2016 0:03 || 10/4/2016 0:03 || 5 || 10/4/2016 0:03 || 2 || 1 10/4/2016 0:03 || 10/4/2016 0:03 || 1 || 10/3/2016 0:03 || 2 || 1 — KrGk, Oct 08 '16 at 09:20

dnoeth · Answer 1 · 2016-10-08T09:47:47.120

0

When you specify ORDER BY within an Analytic Function it defaults to a window definition of RANGE UNBOUNDED PRECEDING (based on Standard SQL).

This is your query with the implicit default windows:

SELECT Max(CASE 
            WHEN c = 2
                THEN d
            ELSE NULL
            END) Over (
        PARTITION BY e ORDER BY d DESC Range Unbounded Preceding
        ) DT1
    ,Max(CASE 
            WHEN c = 2
                THEN d
            ELSE NULL
            END) Over (PARTITION BY e range between unbounded preceding and unbounded following) DT2
    ,t.*
FROM t

Btw, additionally RANGE is way less efficient than ROWS, that's why there's a common recommendation to always add the window definition instead of relying on defaults.

edited Oct 08 '16 at 09:47

answered Oct 08 '16 at 09:42

dnoeth

59,503
4
39
56

@krgk - To explain dnoeth's answer further: in DT2, the `max()` is over the entire partition (that is how it works with no `order by` clause after `partition by`). However, in DT1, the rows with `c=2` come last (due to `order by d desc` and the rows with `c=2` having the oldest dates). For this reason, the `max(case...)`, applied on a "running" basis (from first to current row in the current partition) takes a `max()` over nulls only, until it gets to the last two rows. – Oct 08 '16 at 12:43
Regarding the point about `range` and `rows`: in a case like the present one, where there may be ties, `range` and `rows` have different logical meaning, so the choice should be based on the problem's requirement, not efficiency. – Oct 08 '16 at 12:46
@mathguy: You're right, of course there's a difference between RANGE and ROWS (unless the ORDER BY is unique), I assumed this is known. At least you should know it when you explicitly write it :) – dnoeth Oct 08 '16 at 13:22

Analytic Function MAX Issues

1 Answers1