Analytic function windowing clause

Question

Column VAL is a number list from 1 to 3, the other columns are supposed to show:

A) MIN of all lower values than VAL
B) MAX of all lower values than VAL
C) MIN of all greater values than VAL
D) MAX of all greater values than VAL

I would expect this result:

V   A   B   C   D
-------------------
1 |   |   | 2 | 3
2 | 1 | 1 | 3 | 3
3 | 1 | 2 |   |

But the result I get is:

V   A   B   C   D
-------------------
1 |   |   | 2 | 3
2 |   |   |   |  
3 |   |   |   |

(*) All blank cells are NULL results

The query I wrote:

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 val PRECEDING) A --MIN_PRECEDING
        ,MAX(val) OVER(ORDER BY val RANGE BETWEEN UNBOUNDED PRECEDING AND val PRECEDING) B --MAX_PRECEDING
        ,MIN(val) OVER(ORDER BY val RANGE BETWEEN val FOLLOWING AND UNBOUNDED FOLLOWING) C --MIN_FOLLOWING
        ,MAX(val) OVER(ORDER BY val RANGE BETWEEN val FOLLOWING AND UNBOUNDED FOLLOWING) D --MAX_FOLLOWING
FROM     T
WHERE    val IS NOT NULL
ORDER BY 1
/

Does anybody see what's wrong with this query?

Thanks in advance!

I've been wanting to learn more about this. thanks for the question! — xQbert, Aug 18 '17 at 22:55

score 2 · Accepted Answer · answered Aug 18 '17 at 22:45

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
/

Analytic function windowing clause

1 Answers1