Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Effective T-SQL for Median Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, July 30, 2013 4:20 PM
 SSC-Addicted Group: General Forum Members Last Login: Wednesday, November 25, 2015 6:33 AM Points: 459, Visits: 430
 Is this an effective way to find median?`CREATE TABLE #T (i TINYINT);GOINSERT INTO #TSELECT ABS(CHECKSUM(NEWID())) % 250GO 1000;WITH t AS (SELECT i, ROW_NUMBER() OVER (ORDER BY i) AS n, count(*) OVER (PARTITION BY (SELECT NULL)) AS c FROM #T) SELECTAVG(i) AS MedianFROM tWHERE( c % 2 = 1 AND n = ((c-1)/2)+1 )OR (c % 2 = 0 AND (n = c / 2 OR n = c / 2 + 1))`
Post #1479218
 Posted Tuesday, July 30, 2013 4:52 PM
 SSCrazy Eights Group: General Forum Members Last Login: Today @ 1:48 PM Points: 8,233, Visits: 17,809
 Last week I came up with this option. I haven't made performace tests. Maybe you could help me with that.`DECLARE @test TABLE( myint int)INSERT INTO @test VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9);WITH CTE AS( SELECT myint, NTILE( 2) OVER( ORDER BY myint) tile FROM @test)SELECT CASE WHEN SUM( CASE WHEN tile = 1 THEN 1 END) = SUM( CASE WHEN tile = 2 THEN 1 END) THEN (MAX( CASE WHEN tile = 1 THEN myint END) + MIN( CASE WHEN tile = 2 THEN myint END))/2.0 ELSE MAX( CASE WHEN tile = 1 THEN myint END) ENDFROM CTE` Luis C.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2
Post #1479227
 Posted Tuesday, July 30, 2013 6:06 PM
 SSCertifiable Group: General Forum Members Last Login: Today @ 1:21 PM Points: 5,674, Visits: 8,201
 Fortunately for you SQL 2012 has much better Windowing Function support. Look into the PERCENTILE_CONT function. I also HIGHLY recommend you purchase Itzik Ben-Gan's SQL Server 2012 High-Performance TSQL Using Window Functions book. Best,Kevin G. BolesSQL Server ConsultantSQL MVP 2007-2012TheSQLGuru at GMail
Post #1479235
 Posted Tuesday, July 30, 2013 6:48 PM
 SSC-Addicted Group: General Forum Members Last Login: Wednesday, November 25, 2015 6:33 AM Points: 459, Visits: 430
 TheSQLGuru (7/30/2013)Fortunately for you SQL 2012 has much better Windowing Function support. Look into the PERCENTILE_CONT function. I also HIGHLY recommend you purchase Itzik Ben-Gan's SQL Server 2012 High-Performance TSQL Using Window Functions book.This is awesome thanks!
Post #1479246
 Posted Wednesday, July 31, 2013 3:04 PM
 SSCrazy Group: General Forum Members Last Login: Today @ 12:52 PM Points: 2,164, Visits: 7,283
 dkschill (7/30/2013)Is this an effective way to find median?`CREATE TABLE #T (i TINYINT);GOINSERT INTO #TSELECT ABS(CHECKSUM(NEWID())) % 250GO 1000;WITH t AS (SELECT i, ROW_NUMBER() OVER (ORDER BY i) AS n, count(*) OVER (PARTITION BY (SELECT NULL)) AS c FROM #T) SELECTAVG(i) AS MedianFROM tWHERE( c % 2 = 1 AND n = ((c-1)/2)+1 )OR (c % 2 = 0 AND (n = c / 2 OR n = c / 2 + 1))`First and foremost, for your sample data, don't use GO 1000. You will find a tally table works much better for this kind of thing. `WITH t(i) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM sys.all_objects)INSERT INTO #T SELECT ABS(CHECKSUM(NEWID()))%200 FROM t WHERE i<=1000;` My non-2012 solution is: `;WITH t(i,n,c) AS( SELECT i, ROW_NUMBER() OVER (ORDER BY i), COUNT(*) OVER (PARTITION BY (SELECT NULL)) FROM #T) SELECTAVG(i) AS MedianFROM tWHERE (c%2=1 AND n=((c-1)/2)+1) OR (c%2=0 AND (n=c/2 OR n=c/2+1));`I tested this against Louis' solution (Brilliant use of NTILE BTW) and, though it appears to produce a better query plan (more parallelism, lower est. query cost) it runs about the same speed as what Louis put together (~7 seconds/1 million rows on my crappy test box [4-cpu, 4gb ram test]).Edit: code formatting messed up. -- Alan BursteinBest practices for getting help on SQLServerCentralNeed to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)Need a pattern-based splitter? Try PatternSplitCMNeed to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K. "I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
Post #1479711
 Posted Wednesday, July 31, 2013 3:12 PM
 SSC-Addicted Group: General Forum Members Last Login: Wednesday, November 25, 2015 6:33 AM Points: 459, Visits: 430
Post #1479712
 Posted Wednesday, July 31, 2013 3:27 PM
 SSCrazy Group: General Forum Members Last Login: Today @ 12:52 PM Points: 2,164, Visits: 7,283
 TheSQLGuru (7/30/2013)Fortunately for you SQL 2012 has much better Windowing Function support. Look into the PERCENTILE_CONT function. I also HIGHLY recommend you purchase Itzik Ben-Gan's SQL Server 2012 High-Performance TSQL Using Window Functions book.Just an interesting FYI...I have never used PERCENTILE_CONT but I do have Server 2012 High-Performance TSQL Using Window Functions. With the same sample data from earlier (#T), this is a modified version of Ben-Gan's 2012-based solution using PERCENTILE_COUNT: `WITH t(r,m) AS( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY i) OVER(PARTITION BY NULL) FROM #T)SELECT m AS medianFROM tWHERE r=1;`Though it is much simpler, Ben-Gan's 2012-PERCENTILE_CONT solution is about twice as slow as any other solution posted thus far. Again, just an FYI. -- Alan BursteinBest practices for getting help on SQLServerCentralNeed to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)Need a pattern-based splitter? Try PatternSplitCMNeed to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K. "I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
Post #1479718
 Posted Wednesday, July 31, 2013 3:38 PM
 SSC-Addicted Group: General Forum Members Last Login: Wednesday, November 25, 2015 6:33 AM Points: 459, Visits: 430
 Thanks for the example!
Post #1479724
 Posted Thursday, August 1, 2013 1:16 AM
 Old Hand Group: General Forum Members Last Login: Thursday, December 3, 2015 11:29 PM Points: 319, Visits: 953
 The original query can be formulated a little bit more elegantly as this:`;WITH t(i,n,c) AS( SELECT i, ROW_NUMBER() OVER (ORDER BY i), COUNT(*) OVER (PARTITION BY (SELECT NULL)) FROM #T) SELECTAVG(i) AS MedianFROM tWHERE 2*n-c BETWEEN 0 AND 2;`This version should be slightly faster because of the simpler expression in the WHERE clause/SG
Post #1479811
 Posted Thursday, August 1, 2013 1:34 AM
 SSCoach Group: General Forum Members Last Login: Today @ 1:35 AM Points: 15,510, Visits: 13,170
 TheSQLGuru (7/30/2013)Fortunately for you SQL 2012 has much better Windowing Function support. Look into the PERCENTILE_CONT function. I also HIGHLY recommend you purchase Itzik Ben-Gan's SQL Server 2012 High-Performance TSQL Using Window Functions book.+1 great book! How to post forum questions.Need an answer? No, you need a question.What’s the deal with Excel & SSIS?Member of LinkedIn. My blog at SQLKover. MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1479818

 Permissions