December 1, 2015 at 11:32 am
Ok, I confirmed that the problem is ties throwing off the relationship between the ascending and descending row numbers.
It seems that method is just not safe because of the ordering behavior with respect to ties (it could be fixed by throwing in a unique column as a secondary sort, but the method with COUNT is a bit more intuitive for me anyway); I'll have to force my fingers to forget it and use the COUNT-based one instead for pre-2012 medians 🙂
This should work regardless of ties:
WITH Medians
AS ( SELECT Ascending_RN = ROW_NUMBER() OVER ( PARTITION BY YearMonth ORDER BY LOSInMinutes ASC ) ,
Count_YearMonth = COUNT(*) OVER ( PARTITION BY YearMonth ) ,
YearMonth ,
LOSInMinutes
FROM #TEST
)
SELECT AVG(LOSInMinutes * 1.0) ,
YearMonth
FROM Medians
WHERE Medians.Ascending_RN IN ( ( Count_YearMonth + 1 ) / 2,
( Count_YearMonth + 2 ) / 2 )
GROUP BY Medians.YearMonth
ORDER BY Medians.YearMonth;
Cheers!
December 1, 2015 at 11:38 am
Wonderful. I can't thank you enough.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply