Windowing function with conditions?

Question

Post reply

Windowing function with conditions?

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

Have a look at Understanding the OVER clause[/url], which explains the ROWS BETWEEN clauses.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

drew.allen SSC Guru Points: 76988 More actions · Answer 2

Thom A (12/22/2016)
Fully Windowed, and VALID included.
WITH CTE (EVENT_ID, EVENT_RANK, MIN_AGE, MAX_AGE, C_MIN, C_MAX) AS
(
SELECT A.EVENT_ID,
A.EVENT_RANK,
A.MIN_AGE,
A.MAX_AGE,
CASE WHEN ISNULL(A.MIN_AGE,0) < MAX(A.MIN_AGE) OVER (PARTITION BY A.EVENT_ID ORDER BY A.EVENT_RANK
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW)
THEN MAX(A.MIN_AGE) OVER (PARTITION BY A.EVENT_ID ORDER BY A.EVENT_RANK
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW)
ELSE A.MIN_AGE END AS C_MIN,
CASE WHEN ISNULL(A.MAX_AGE,0) < MAX(A.MAX_AGE) OVER (PARTITION BY A.EVENT_ID ORDER BY A.EVENT_RANK
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW)
AND ISNULL(A.MAX_AGE,0) <= ISNULL(LAG(A.MAX_AGE) OVER (PARTITION BY A.EVENT_ID ORDER BY A.EVENT_RANK), A.MAX_AGE)
THEN MAX(A.MAX_AGE) OVER (PARTITION BY A.EVENT_ID ORDER BY A.EVENT_RANK
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW)
ELSE A.MAX_AGE END AS C_MAX
FROM ATABLE A)
SELECT *,
CASE WHEN C.MAX_AGE < C.C_MIN THEN 0
WHEN C.MIN_AGE > C.C_MAX THEN 0
ELSE 1 END AS VALID
FROM CTE C
ORDER BY C.EVENT_ID,
C.EVENT_RANK;
If this really does work, the "MARK AS SOLUTION" button is always appreciated 😉

I think this can be greatly simplified, but the logic is not really clear and the sample is too small to cover all of the cases. Specifically it would help to have more than one record after the invalid record and it would help to have more than one invalid record. Even as it stands, the current query can be simplified. Specifically

-- This formula is unnecessarily complex

CASE WHEN ISNULL(A.MIN_AGE,0) < MAX(A.MIN_AGE) OVER (PARTITION BY A.EVENT_ID ORDER BY A.EVENT_RANK

ROWS BETWEEN UNBOUNDED PRECEDING

AND CURRENT ROW)

THEN MAX(A.MIN_AGE) OVER (PARTITION BY A.EVENT_ID ORDER BY A.EVENT_RANK

ROWS BETWEEN UNBOUNDED PRECEDING

AND CURRENT ROW)

ELSE A.MIN_AGE END AS C_MIN,

-- It is equivalent to

MAX(A.MIN_AGE) OVER (PARTITION BY A.EVENT_ID ORDER BY A.EVENT_RANK

ROWS BETWEEN UNBOUNDED PRECEDING

AND CURRENT ROW)

AS C_MIN,

1) A.MIN_AGE is the current row, so it is included in ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

2) By the definition of MAX() and the fact that A.MIN_AGE is included in the set, we know that A.MIN_AGE <= MAX(A.MIN_AGE) OVER(...).

3) If A.MIN_AGE is NULL or A.MIN_AGE < MAX(A.MIN_AGE) OVER(...), then we are outputting MAX(A.MIN_AGE) OVER(...)

4) If the first case fails, then we know that A.MIN_AGE >= MAX(A.MIN_AGE) OVER(...), but we've already shown that A.MIN_AGE <= MAX(A.MIN_AGE) OVER(...), so we know that A.MIN_AGE = MAX(A.MIN_AGE) OVER(...), so we can substitute equal expressions and output MAX(A.MIN_AGE) OVER(...).

So, in all cases, we are outputting MAX(A.MIN_AGE) OVER(...).

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

olibbhq SSCarpal Tunnel Points: 4034 More actions · Answer 3

Sorry Thom,

I have tested with a different dataset and it isnt returning correctly.

INSERT [dbo].[ATABLE] ([EVENT_ID], [EVENT_RANK], [MIN_AGE], [MAX_AGE]) VALUES (1205, 1, NULL, 55.00000000089)

GO

INSERT [dbo].[ATABLE] ([EVENT_ID], [EVENT_RANK], [MIN_AGE], [MAX_AGE]) VALUES (1205, 2, 23, NULL)

GO

INSERT [dbo].[ATABLE] ([EVENT_ID], [EVENT_RANK], [MIN_AGE], [MAX_AGE]) VALUES (1205, 3, 25, 36)

GO

This returns c min 25 which is correct and c_max as 55 which is wrong, it should be 36.

Sorry to be such a pain, it is such an elegant solution I am sure it is just a tweak.

Thanks

Oliver