mysql 按比例计算排名_计算MS SQL中的百分比排名

第50百分位与中位数相同。在计算其他百分位数时，比如说第80位，按照升序排序80%数据的数据，其他百分数按降序排序，并取两个中间值的平均值。

注意：中位查询已经存在了很长一段时间，但不记得我从哪里得到它，我只修改它来计算其他百分点。

DECLARE @Temp TABLE(Id INT IDENTITY(1,1), DATA DECIMAL(10,5))

INSERT INTO @Temp VALUES(0)

INSERT INTO @Temp VALUES(2)

INSERT INTO @Temp VALUES(8)

INSERT INTO @Temp VALUES(4)

INSERT INTO @Temp VALUES(3)

INSERT INTO @Temp VALUES(6)

INSERT INTO @Temp VALUES(6)

INSERT INTO @Temp VALUES(6)

INSERT INTO @Temp VALUES(7)

INSERT INTO @Temp VALUES(0)

INSERT INTO @Temp VALUES(1)

INSERT INTO @Temp VALUES(NULL)

--50th percentile or median

SELECT ((

SELECT TOP 1 DATA

FROM (

SELECT TOP 50 PERCENT DATA

FROM @Temp

WHERE DATA IS NOT NULL

ORDER BY DATA

) AS A

ORDER BY DATA DESC) +

(

SELECT TOP 1 DATA

FROM (

SELECT TOP 50 PERCENT DATA

FROM @Temp

WHERE DATA IS NOT NULL

ORDER BY DATA DESC

) AS A

ORDER BY DATA ASC))/2.0

--90th percentile

SELECT ((

SELECT TOP 1 DATA

FROM (

SELECT TOP 90 PERCENT DATA

FROM @Temp

WHERE DATA IS NOT NULL

ORDER BY DATA

) AS A

ORDER BY DATA DESC) +

(

SELECT TOP 1 DATA

FROM (

SELECT TOP 10 PERCENT DATA

FROM @Temp

WHERE DATA IS NOT NULL

ORDER BY DATA DESC

) AS A

ORDER BY DATA ASC))/2.0

--75th percentile

SELECT ((

SELECT TOP 1 DATA

FROM (

SELECT TOP 75 PERCENT DATA

FROM @Temp

WHERE DATA IS NOT NULL

ORDER BY DATA

) AS A

ORDER BY DATA DESC) +

(

SELECT TOP 1 DATA

FROM (

SELECT TOP 25 PERCENT DATA

FROM @Temp

WHERE DATA IS NOT NULL

ORDER BY DATA DESC

) AS A

ORDER BY DATA ASC))/2.0