|
|
|
Forum Newbie
Group: Forum Members
Last Login: 5/10/2008 8:36:20 AM
Posts: 8,
Visits: 2
|
|
|
Does anyone know how to calculate the median using SQL?
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 2 days ago @ 3:41:05 AM
Posts: 522,
Visits: 264
|
|
DROP table median_test;
CREATE MULTISET TABLE median_test
(
id INTEGER
,med_group INTEGER
,med_value INTEGER
) PRIMARY INDEX (id);
INSERT INTO median_test VALUES (1, 1, 1);
INSERT INTO median_test VALUES (2, 1, 2);
INSERT INTO median_test VALUES (3, 1, 3);
INSERT INTO median_test VALUES (4, 1, 5);
INSERT INTO median_test VALUES (5, 2, 1);
INSERT INTO median_test VALUES (6, 2, 6);
INSERT INTO median_test VALUES (7, 2, 7);
INSERT INTO median_test VALUES (8, 3, 1);
INSERT INTO median_test VALUES (9, 4, 4);
INSERT INTO median_test VALUES (10, 4, 5);
INSERT INTO median_test VALUES (11, 4, 6);
INSERT INTO median_test VALUES (12, 4, 6);
INSERT INTO median_test VALUES (13, 4, 7);
INSERT INTO median_test VALUES (14, 4, 7);
INSERT INTO median_test VALUES (15, 4, 7);
INSERT INTO median_test VALUES (16, 4, 8);
/*** "financial median": Group COUNT + ROW_NUMBER ***/
SELECT
dt1.med_group
,AVG(med_value)
FROM
(SELECT
med_group
,med_value
,ROW_NUMBER() OVER (PARTITION BY med_group
ORDER BY med_value) AS row_num
,COUNT(*) OVER (PARTITION BY med_group
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS row_count
FROM median_test
) AS dt1
WHERE
row_num = (row_count + 1) / 2
OR
row_num = (row_count / 2) + 1
GROUP BY med_group
ORDER BY med_group
;
/*** "statistical median": Group COUNT + ROW_NUMBER ***/
SELECT
dt1.med_group
,med_value
FROM
(SELECT
med_group
,med_value
,ROW_NUMBER() OVER (PARTITION BY med_group
ORDER BY med_value) AS row_num
,COUNT(*) OVER (PARTITION BY med_group
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS row_count
FROM median_test
) AS dt1
WHERE
row_num = (row_count + 1) / 2 --left (lesser) value
-- row_num = (row_count / 2) + 1 --right (greater) value
ORDER BY med_group
;
Dieter
|
|
|
|