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

 relatively simple mode, median, mean Rate Topic Display Mode Topic Options
Author
 Message
 Posted Sunday, September 30, 2007 7:28 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, January 5, 2011 10:15 AM Points: 175, Visits: 74
 Comments posted to this topic are about the item relatively simple mode, median, mean
Post #404673
 Posted Monday, January 26, 2009 3:48 PM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, December 3, 2014 11:26 PM Points: 1, Visits: 26
 Good post, I needed some mode and median calculations in a short time frame, and your SQL was invaluable. Thanks.
Post #643763
 Posted Friday, April 3, 2009 12:03 PM
 Old Hand Group: General Forum Members Last Login: Monday, September 22, 2014 11:18 AM Points: 305, Visits: 567
 There's only one problem with the calculation of the median: if the row count of the sample data is even, then the median is not unique (see http://en.wikipedia.org/wiki/Median). Here's a version of the calc that accounts for even row counts:`;WITH TopHalf AS ( SELECT TOP 50 PERCENT DataValue FROM DataSet ORDER BY DataValue ASC),BottomHalf AS ( SELECT TOP 50 PERCENT DataValue FROM DataSet ORDER BY DataValue DESC),BottomOfTopHalf AS ( SELECT TOP 1 DataValue FROM TopHalf ORDER BY DataValue DESC),TopOfBottomHalf AS ( SELECT TOP 1 DataValue FROM BottomHalf ORDER BY DataValue ASC)SELECT Median = (BottomOfTopHalf.DataValue + TopOfBottomHalf.DataValue) / 2.0FROM BottomOfTopHalf, TopOfBottomHalf`
Post #690122
 Posted Wednesday, September 9, 2009 10:04 AM
 Grasshopper Group: General Forum Members Last Login: Wednesday, March 7, 2012 2:08 AM Points: 10, Visits: 34
 I need something similar, but I need the daily median of a data set, effectively doing a group by on the Date.Only way I've found to do this is with a cursor was wondering if any clever chaps out there have a better idea?
Post #785132
 Posted Thursday, September 10, 2009 2:26 PM
 Old Hand Group: General Forum Members Last Login: Monday, September 22, 2014 11:18 AM Points: 305, Visits: 567
 leea (9/9/2009)I need something similar, but I need the daily median of a data set, effectively doing a group by on the Date.Only way I've found to do this is with a cursor was wondering if any clever chaps out there have a better idea?I'm guessing that you're running this against data that is historically static, correct? If so, how about creating a table to hold the results of your daily median calculation? That way you don't have to rerun it for historic data every time a user needs it.
Post #786004
 Posted Thursday, June 10, 2010 1:12 PM
 SSC Rookie Group: General Forum Members Last Login: Friday, June 17, 2016 12:49 PM Points: 42, Visits: 508
 Very good catch Jesse!Jesse McLain (4/3/2009)There's only one problem with the calculation of the median: if the row count of the sample data is even, then the median is not unique (see http://en.wikipedia.org/wiki/Median). Here's a version of the calc that accounts for even row counts:`;WITH TopHalf AS ( SELECT TOP 50 PERCENT DataValue FROM DataSet ORDER BY DataValue ASC),BottomHalf AS ( SELECT TOP 50 PERCENT DataValue FROM DataSet ORDER BY DataValue DESC),BottomOfTopHalf AS ( SELECT TOP 1 DataValue FROM TopHalf ORDER BY DataValue DESC),TopOfBottomHalf AS ( SELECT TOP 1 DataValue FROM BottomHalf ORDER BY DataValue ASC)SELECT Median = (BottomOfTopHalf.DataValue + TopOfBottomHalf.DataValue) / 2.0FROM BottomOfTopHalf, TopOfBottomHalf`
Post #935660

 Permissions