Advertisement
MikeHNJ

Alternate quartile calc algorithm

Jul 13th, 2012
221
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 1.38 KB | None | 0 0
  1. With SourceData AS
  2. (
  3.     SELECT GroupID, CONVERT( decimal(20, 10), Measure) Measure
  4.     FROM dbo.TEST_MEDIAN
  5.     UNPIVOT(Measure FOR GroupID IN (Computer, Laptop, Mouse)) AS normalized
  6. )  
  7. SELECT GroupID,
  8.     (Max(Q1LoVal) + Max(Q1HiVal)) / 2 Quartile1,
  9.     (Max(Q2LoVal) + Max(Q2HiVal)) / 2 Median,
  10.     (Max(Q3LoVal) + Max(Q3HiVal)) / 2 Quartile3,
  11.     Min(Measure) Minimum, Max(Measure) Maximum
  12. FROM (
  13.     SELECT detail.GroupID,
  14.         CASE WHEN RowNum = Q1Lo THEN Measure ELSE NULL END Q1LoVal,
  15.         CASE WHEN RowNum = Q1Hi THEN Measure ELSE NULL END Q1HiVal,
  16.         CASE WHEN RowNum = Q2Lo THEN Measure ELSE NULL END Q2LoVal,
  17.         CASE WHEN RowNum = Q2Hi THEN Measure ELSE NULL END Q2HiVal,
  18.         CASE WHEN RowNum = Q3Lo THEN Measure ELSE NULL END Q3LoVal,
  19.         CASE WHEN RowNum = Q3Hi THEN Measure ELSE NULL END Q3HiVal,
  20.         Measure
  21.     FROM
  22.         (SELECT *,  ROW_NUMBER() OVER (PARTITION BY GroupID ORDER BY Measure) RowNum
  23.         FROM SourceData) AS detail
  24.     INNER JOIN (
  25.         SELECT GroupID,
  26.             CEILING(FLOOR(COUNT(*) / 2.0) / 2) Q1Lo,
  27.             CEILING(CEILING(COUNT(*) / 2.0) / 2) Q1Hi,
  28.             (COUNT(*) + 1) / 2 AS Q2Lo,
  29.             (COUNT(*) + 2) / 2 AS Q2Hi,
  30.             (COUNT(*) + 1) - CEILING(CEILING(COUNT(*) / 2.0) / 2) Q3Lo,
  31.             (COUNT(*) + 1) - CEILING(FLOOR(COUNT(*) / 2.0) / 2) Q3Hi,
  32.             COUNT(*) Ct
  33.         FROM SourceData
  34.         GROUP BY GroupID
  35.         HAVING COUNT(*) > 1
  36.     ) AS summary ON detail.GroupID  = summary.GroupID
  37. ) AS combined
  38. GROUP BY GroupID;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement