Adding Mode to this SQL
I have this SQL for MS SQL Server 2008:
WITH CTE AS (
SELECT e_id,
scale,
开发者_运维百科 ROW_NUMBER() OVER(PARTITION BY e_id ORDER BY scale ASC) AS rn,
COUNT(scale) OVER(PARTITION BY e_id) AS cn
FROM waypoint.dbo.ScoreMaster
WHERE scale IS NOT NULL
)
SELECT e_id,
cast(AVG (cast(scale as decimal(5,2))) as decimal(5,3)) as [AVG],
cast (STDEV(cast(scale as decimal(5,1))) as decimal(5,3)) as [STDDEV],
AVG(CASE WHEN 2 * rn - cn BETWEEN 0 AND 2 THEN
scale END) AS FinancialMedian,
MAX(CASE WHEN 2 * rn - cn BETWEEN 0 AND 2 THEN
scale END) AS StatisticalMedian
from CTE
GROUP BY e_id
and I would like to add the Mode. I've been trying different ideas, but nothing works yet...
WITH s (e_id, scale) AS
(
SELECT 1, 0.0
UNION ALL
SELECT 1, 0.1
UNION ALL
SELECT 1, 0.1
UNION ALL
SELECT 1, 0.2
UNION ALL
SELECT 1, 0.2
UNION ALL
SELECT 1, 0.3
),
cte AS
(
SELECT e_id,
scale,
ROW_NUMBER() OVER (PARTITION BY e_id ORDER BY scale ASC) AS rn,
COUNT(scale) OVER (PARTITION BY e_id) AS cn,
COUNT(*) OVER (PARTITION BY e_id, scale) AS sn
FROM s
WHERE scale IS NOT NULL
)
SELECT e_id,
AVG(scale),
(
SELECT AVG(scale)
FROM cte ci
WHERE ci.e_id = co.e_id
AND ci.sn = MAX(co.sn)
) AS mode
FROM cte co
GROUP BY
e_id
This will select the average of all modal scales. Replace AVG
with TOP 1 ... ORDER BY
to get a single scale.
Note how we can use MAX(co.sn)
in the WHERE
clause :)
Take a look at the following:
http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/calculating-mean-median-and-mode-with-sq
Try this :
; WITH Mean AS (
SELECT AVG(scale)/1.0 AS MeanVal
FROM waypoint.dbo.ScoreMaster
WHERE scale IS NOT NULL
), Median AS (
SELECT ( ( SELECT TOP 1 scale
FROM ( SELECT TOP 50 PERCENT scale FROM waypoint.dbo.ScoreMaster WHERE scale IS NOT NULL ORDER BY scale ) AS A
ORDER BY scale DESC )
+ ( SELECT TOP 1 scale
FROM ( SELECT TOP 50 PERCENT scale FROM waypoint.dbo.ScoreMaster WHERE scale IS NOT NULL ORDER BY scale DESC ) AS A
ORDER BY scale ASC ) ) / 2.0 AS MedianVal
), Mode AS (
SELECT scale AS ModeVal
FROM waypoint.dbo.ScoreMaster
WHERE scale IS NOT NULL
GROUP BY scale
HAVING COUNT(scale) = ( SELECT TOP 1 COUNT(scale)
FROM waypoint.dbo.ScoreMaster
WHERE scale IS NOT NULL
GROUP BY scale
ORDER BY COUNT(scale) DESC )
)
SELECT MeanVal
, MedianVal
, ModeVal
FROM Mean
, Median
, Mode
I know it's ugly, but the trick is to cross join all the possible values given by Mode with the value of Mean and Median.
Or this :
; WITH Mean AS (
SELECT AVG(scale)/1.0 AS MeanVal
FROM waypoint.dbo.ScoreMaster
WHERE scale IS NOT NULL
), Median AS (
SELECT ( ( SELECT TOP 1 scale
FROM ( SELECT TOP 50 PERCENT scale FROM waypoint.dbo.ScoreMaster WHERE scale IS NOT NULL ORDER BY scale ) AS A
ORDER BY scale DESC )
+ ( SELECT TOP 1 scale
FROM ( SELECT TOP 50 PERCENT scale FROM waypoint.dbo.ScoreMaster WHERE scale IS NOT NULL ORDER BY scale DESC ) AS A
ORDER BY scale ASC ) ) / 2.0 AS MedianVal
), Mode AS (
SELECT AVG(scale) AS ModeVal
FROM waypoint.dbo.ScoreMaster
WHERE scale IS NOT NULL
HAVING COUNT(scale) = ( SELECT TOP 1 COUNT(scale)
FROM waypoint.dbo.ScoreMaster
WHERE scale IS NOT NULL
GROUP BY scale
ORDER BY COUNT(scale) DESC )
)
SELECT MeanVal
, MedianVal
, ModeVal
FROM Mean
, Median
, Mode
if you want Mode to be the AVG between scales with the highest count.
精彩评论