Windowing function with conditions?
-
Hi,
I have the table below:
CREATE TABLE [dbo].[ATABLE]([EVENT_ID] [int] NOT NULL,
[EVENT_RANK] [int] NULL,
[MIN_AGE] [float] NULL,
[MAX_AGE] [float] NULL
) ON [PRIMARY]
GO
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, 15, 22)
GO
INSERT [dbo].[ATABLE] ([EVENT_ID], [EVENT_RANK], [MIN_AGE], [MAX_AGE]) VALUES (1205, 4, 23, 29)
GO
What I would like to do is to return a running maximum and a running minimum
but with the added complexity that (sorting by rank ID) if the lower rank min age is higher than the current min age and the lower rank max age is lower than the current max age
that the running minimum and maximum ignores these values
in this case the running min and max at the end would be
Minimum 23
Maximum 29
I am struggling to get a windowing function to help with this and would very much welcome your help.
See below my mess:
SELECTEVENT_ID, EVENT_RANK,
MIN_AGE,
MAX_AGE,
MAX(MIN_AGE) OVER (PARTITION BY EVENT_ID ORDER BY EVENT_RANK) AS C_MIN1,
MIN(MAX_AGE) OVER (PARTITION BY EVENT_ID ORDER BY EVENT_RANK) AS C_MAX1,
CASE
WHEN MAX_AGE < MAX(MIN_AGE) OVER (PARTITION BY EVENT_ID ORDER BY EVENT_RANK)
OR MAX_AGE > MIN(MAX_AGE) OVER (PARTITION BY EVENT_ID ORDER BY EVENT_RANK)
THEN NULL
ELSE MAX(MIN_AGE) OVER (PARTITION BY EVENT_ID ORDER BY EVENT_RANK) END AS C_MIN,
CASE
WHEN MIN_AGE > MIN(MAX_AGE) OVER (PARTITION BY EVENT_ID ORDER BY EVENT_RANK)
OR MIN_AGE < MAX(MIN_AGE) OVER (PARTITION BY EVENT_ID ORDER BY EVENT_RANK)
THEN NULL
ELSE MIN(MAX_AGE) OVER (PARTITION BY EVENT_ID ORDER BY EVENT_RANK) END AS C_MAX,
CASE
WHEN MAX_AGE < MAX(MIN_AGE) OVER (PARTITION BY EVENT_ID ORDER BY EVENT_RANK) THEN 0
WHEN MIN_AGE > MIN(MAX_AGE) OVER (PARTITION BY EVENT_ID ORDER BY EVENT_RANK) THEN 0
ELSE 1 END AS VALID
FROM ATABLE
any help would be very gratefully appreciated.
Thanks
Oliver
-
Can you amend the sample data please?
😎
Running it returns this error
Msg 207, Level 16, State 1, Line 32Invalid column name 'CALIBRATION_TYPE_ID'.
-
amended, sorry about that.
I am going to try a while loop as another option.
Thanks,
Oliver
-
olibbhq (12/22/2016)
Hi,What I would like to do is to return a running maximum and a running minimum
but with the added complexity that (sorting by rank ID) if the lower rank min age is higher than the current min age and the lower rank max age is lower than the current max age
that the running minimum and maximum ignores these values
in this case the running min and max at the end would be
Minimum 23
Maximum 29
- There is no Rank ID, but you do have Event_ID & Event_Rank. I assume Event_Rank is your sort column.
- Will there be multiple Event_IDs that need to be partitioned? I assume so, but it would be nice if your sample data showed that as well.
- Rank has different meaning depending on context. 1st place is higher than 2nd place, but 1 is lower than 2. Can you clarify what you mean by lower rank? I assume you mean numerically at this point.
- You didn't describe the valid column. What is that for?
Can you add more sample data and provide the expected result? It would also be helpful to provide the business (plain English) description of the results. Something like "minimum age is the oldest young person at the most prestigious event"... That can help us to hone in on the solution.
Based on my assumptions, these both get your expected results:
--INNER JOIN
SELECT A1.EVENT_ID,
Min_Age = min(CASE WHEN A1.MIN_AGE > A2.MIN_AGE THEN A1.MIN_AGE ELSE NULL END), --the minimum of the larger minimum age
Max_Age = max(CASE WHEN A1.MAX_AGE < A2.MAX_AGE THEN A1.MAX_AGE ELSE NULL END ) --the maximum of the smaller maximum age
FROM dbo.ATABLE AS A1 --join the table to itself to compare event ranks
INNER JOIN dbo.ATABLE AS A2
ON A1.EVENT_RANK > A2.EVENT_RANK --compare each rank to all of the ranks beneath it
AND A1.EVENT_ID = A2.EVENT_ID
GROUP BY A1.EVENT_ID;
--CROSS APPLY
SELECT o.EVENT_ID,
Min_Age = MIN(oi.Max_Min),
Max_Age = MAX(oi.Min_Max)
FROM dbo.ATABLE AS o
CROSS APPLY
( SELECT Min_Max = MIN(i.MAX_AGE),
Max_Min = MAX(i.MIN_AGE)
FROM dbo.ATABLE AS i
WHERE i.EVENT_ID = o.EVENT_ID
AND i.EVENT_RANK > o.EVENT_RANK
) AS oi
GROUP BY o.EVENT_ID;
I'm not fond of either solution. I have concerns about how well these will scale with a large number of rows, especially the inner join version. I'm hoping someone else here has something better.
Wes
(A solid design is always preferable to a creative workaround) -
Can we get some expected results? This gives me a right result at the end, but i'm pretty sure EVENT_RANK 3 is wrong with a C_MIN of 23, and a C_MAX of 22.#
SELECT *,CASE WHEN A.MIN_AGE > ISNULL(LAG(A.MIN_AGE) OVER (PARTITION BY EVENT_ID ORDER BY EVENT_RANK), A.MIN_AGE)
THEN A.MIN_AGE
ELSE ISNULL(LAG(A.MIN_AGE) OVER (PARTITION BY EVENT_ID ORDER BY EVENT_RANK), A.MIN_AGE) END AS C_MIN,
CASE WHEN A.MAX_AGE > ISNULL(LAG(A.MAX_AGE) OVER (PARTITION BY EVENT_ID ORDER BY EVENT_RANK), A.MAX_AGE)
THEN A.MAX_AGE
ELSE ISNULL(LAG(A.MAX_AGE) OVER (PARTITION BY EVENT_ID ORDER BY EVENT_RANK), A.MAX_AGE) END AS C_MAX
FROM ATABLE A
ORDER BY A.EVENT_ID;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Thank you for the replies.
The required result is:
I have written a while loop which seems to work but my word it is clunky.
DECLARE@RANK_ID INT,
@MAX_RANK_ID INT,
@RUNNING_MIN_AGE FLOAT = NULL,
@RUNNING_MAX_AGE FLOAT = NULL
DECLARE @VALID_TABLE TABLE (EVENT_ID INT, EVENT_RANK INT, MIN_AGE FLOAT, MAX_AGE FLOAT, RUNNING_MIN FLOAT, RUNNING_MAX FLOAT, VALID INT)
SET @RANK_ID = 1
SELECT @MAX_RANK_ID = MAX(EVENT_RANK) FROM ATABLE
WHILE @RANK_ID < @MAX_RANK_ID + 1
BEGIN
DECLARE
@MIN_AGE FLOAT,
@MAX_AGE FLOAT,
@VALID INT = 0
SELECT @MIN_AGE = MIN_AGE, @MAX_AGE = MAX_AGE FROM ATABLE WHERE EVENT_RANK = @RANK_ID
SELECT @RUNNING_MIN_AGE = RUNNING_MIN, @RUNNING_MAX_AGE = RUNNING_MAX FROM @VALID_TABLE WHERE EVENT_RANK = @RANK_ID - 1
IF @RUNNING_MAX_AGE IS NULL BEGIN
SET @RUNNING_MAX_AGE = @MAX_AGE
SET @VALID = 1 END
IF @RUNNING_MIN_AGE IS NULL BEGIN
SET @RUNNING_MIN_AGE = @MIN_AGE
SET @VALID = 1 END
IF @MAX_AGE < @RUNNING_MAX_AGE AND @MAX_AGE > @RUNNING_MIN_AGE BEGIN
SET @RUNNING_MAX_AGE = @MAX_AGE
SET @VALID = 1 END
IF @MIN_AGE > @RUNNING_MIN_AGE AND @MIN_AGE < @RUNNING_MAX_AGE BEGIN
SET @RUNNING_MIN_AGE = @MIN_AGE
SET @VALID = 1 END
INSERT INTO @VALID_TABLE (EVENT_ID, EVENT_RANK, MIN_AGE, MAX_AGE, RUNNING_MIN, RUNNING_MAX, VALID)
SELECT EVENT_ID, EVENT_RANK, MIN_AGE, MAX_AGE, @RUNNING_MIN_AGE, @RUNNING_MAX_AGE, @VALID
FROM ATABLE
WHERE EVENT_RANK = @RANK_ID
SET @RANK_ID = @RANK_ID + 1
END
SELECT * FROM @VALID_TABLE
-
I can't see the image.
Please also provide more sample data.
Wes
(A solid design is always preferable to a creative workaround) -
For those that can't see the image above.
SELECT 1205, 1 AS EVETN_RANK, NULL as MIN_AGE, 55 AS MAX_AGE, NULL AS RUNNING_MIN, 55 AS RUNNING_MAX, 1 AS VALIDUNION
SELECT 1205, 2, 23, NULL, 23, 55, 1
UNION
SELECT 1205, 3, 15, 22, 23, 55, 0
UNION
SELECT 1205, 4, 23, 29, 23, 29, 1;
EVENT_ID EVENT_RANK MIN_AGE MAX_AGE RUNNING_MIN RUNNING_MAX VALID----------- ----------- ----------- ----------- ----------- ----------- -----------
1205 1 NULL 55 NULL 55 1
1205 2 23 NULL 23 55 1
1205 3 15 22 23 55 0
1205 4 23 29 23 29 1
To the OP, this is how you should supply Expected results.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Thank you for posting the table Thom and for your window function. I understand what you have done and I too get the correct results in a much more elegant method than I obtain.
The last piece of logic is that
IF MAX_AGE < RUNNING_MAX_AGE AND MAX_AGE > RUNNING_MIN_AGE
Then
RUNNING_MAX_AGE = MAX_AGE
IF MIN_AGE > RUNNING_MIN_AGE AND MIN_AGE < RUNNING_MAX_AGE
Then
RUNNING_MIN_AGE = MIN_AGE
I don't think the window function covers this example?
Many Thanks,
Oliver
-
olibbhq (12/22/2016)
Thank you for posting the table Thom and for your window function. I understand what you have done and I too get the correct results in a much more elegant method than I obtain.The last piece of logic is that
IF MAX_AGE < RUNNING_MAX_AGE AND MAX_AGE > RUNNING_MIN_AGE
Then
RUNNING_MAX_AGE = MAX_AGE
IF MIN_AGE > RUNNING_MIN_AGE AND MIN_AGE < RUNNING_MAX_AGE
Then
RUNNING_MIN_AGE = MIN_AGE
I don't think the window function covers this example?
Many Thanks,
Oliver
it doesn't, working on another solution now.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
This gives me the right results, however, this is quite a small dataset, you might want to check on others.
Also, what is the logic for VALID? I have omitted this for the moment.
SELECT A.EVENT_ID,A.EVENT_RANK,
A.MIN_AGE,
A.MAX_AGE,
CASE WHEN A.MIN_AGE < MAX(R.MIN_AGE)
THEN MAX(R.MIN_AGE)
ELSE A.MIN_AGE END AS C_MIN,
CASE WHEN ISNULL(A.MAX_AGE, MAX(R.MAX_AGE)) <= MAX(R.MAX_AGE) AND ISNULL(A.MAX_AGE, MAX(R.MAX_AGE)) <= ISNULL(LAG(A.MAX_AGE) OVER (PARTITION BY A.EVENT_ID ORDER BY A.EVENT_RANK), MAX(R.MAX_AGE))
THEN MAX(R.MAX_AGE)
ELSE A.MAX_AGE END AS C_MAX
FROM ATABLE A
JOIN ATABLE r ON A.EVENT_RANK >= r.EVENT_RANK
GROUP BY A.EVENT_ID,
A.EVENT_RANK,
A.MIN_AGE,
A.MAX_AGE
ORDER BY A.EVENT_ID,
A.EVENT_RANK;
Others, feel free to complain about query plans on that SQL, I'm sure it is horrid <3 😎
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Thank you Thom, I am very grateful.
I will try this with a larger dataset and see how it gets on.
I am curious you moved away from windowing to joining is this because windowing cant handle the task?
Many Thanks and to the others that have helped me.
Oliver
-
olibbhq (12/22/2016)
Thank you Thom, I am very grateful.I will try this with a larger dataset and see how it gets on.
I am curious you moved away from windowing to joining is this because windowing cant handle the task?
Many Thanks and to the others that have helped me.
Oliver
I have actually used both in the MAX (ugly, but works).
Partly, yes. The main reason being that you want a running max, but also wanted to check the prior row.
To be honest, I probably should have still done this with a window function, and used ROWS PRECENDING and CURRENT ROW (I always foget they exist). I'm going to experiment and see if it can be fully windowed (unless someone beats me to it).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
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 😉
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Thanks Thom, I will run this through and check it out.
Will also need to check out the proceeding functions.
Many Thanks,
Oliver
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply