Я думал бы, что это будет простым решением:
SELECT TOP N PERCENT FROM TheTable ORDER BY TheScore DESC
, Где N = (100 - желаемая процентиль). Таким образом, если бы Вы хотели все строки в 90-й процентили, Вы выбрали бы лучшие 10%.
я не уверен, под чем Вы подразумеваете "предпочтительно в единственной записи". Вы имеете в виду, вычисляют, в какую процентиль данный счет к единственной записи упал бы? например, Вы хотите быть в состоянии сделать операторы как "свой счет, 83, который помещает Вас в 91-ю процентиль".?
РЕДАКТИРОВАНИЕ: хорошо, я думал еще немного о Вашем вопросе и придумал эту интерпретацию. Вы спрашиваете, как вычислить счет сокращения к конкретной процентили? например, что-то вроде этого: чтобы быть в 90-й процентили, у Вас должен быть счет, больше, чем 78.
Если так, этот запрос работы. Мне не нравятся подзапросы, хотя, таким образом, в зависимости от того, для чего это было, я, вероятно, попытаюсь найти более изящное решение. Это действительно, однако, возвращает единственную запись с единственным счетом.
-- Find the minimum score for all scores in the 90th percentile
SELECT Min(subq.TheScore) FROM
(SELECT TOP 10 PERCENT TheScore FROM TheTable
ORDER BY TheScore DESC) AS subq
Проверьте команду NTILE - она даст вам процентили очень легко!
SELECT SalesOrderID,
OrderQty,
RowNum = Row_Number() OVER(Order By OrderQty),
Rnk = RANK() OVER(ORDER BY OrderQty),
DenseRnk = DENSE_RANK() OVER(ORDER BY OrderQty),
NTile4 = NTILE(4) OVER(ORDER BY OrderQty)
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43689, 63181)
Я работал над этим немного больше, и вот что я придумал до сих пор:
CREATE PROCEDURE [dbo].[TestGetPercentile]
@percentile as float,
@resultval as float output
AS
BEGIN
WITH scores(score, prev_rank, curr_rank, next_rank) AS (
SELECT dblScore,
(ROW_NUMBER() OVER ( ORDER BY dblScore ) - 1.0) / ((SELECT COUNT(*) FROM TestScores) + 1) [prev_rank],
(ROW_NUMBER() OVER ( ORDER BY dblScore ) + 0.0) / ((SELECT COUNT(*) FROM TestScores) + 1) [curr_rank],
(ROW_NUMBER() OVER ( ORDER BY dblScore ) + 1.0) / ((SELECT COUNT(*) FROM TestScores) + 1) [next_rank]
FROM TestScores
)
SELECT @resultval = (
SELECT TOP 1
CASE WHEN t1.score = t2.score
THEN t1.score
ELSE
t1.score + (t2.score - t1.score) * ((@percentile - t1.curr_rank) / (t2.curr_rank - t1.curr_rank))
END
FROM scores t1, scores t2
WHERE (t1.curr_rank = @percentile OR (t1.curr_rank < @percentile AND t1.next_rank > @percentile))
AND (t2.curr_rank = @percentile OR (t2.curr_rank > @percentile AND t2.prev_rank < @percentile))
)
END
Затем в другой хранимой процедуре я делаю это:
DECLARE @pct25 float;
DECLARE @pct50 float;
DECLARE @pct75 float;
exec SurveyGetPercentile .25, @pct25 output
exec SurveyGetPercentile .50, @pct50 output
exec SurveyGetPercentile .75, @pct75 output
Select
min(dblScore) as minScore,
max(dblScore) as maxScore,
avg(dblScore) as avgScore,
@pct25 as percentile25,
@pct50 as percentile50,
@pct75 as percentile75
From TestScores
Это все еще не делает то, что я ищу. Это позволит получить статистику по всем тестам; тогда как я хотел бы иметь возможность выбирать из таблицы TestScores, в которой есть несколько разных тестов, и получать одинаковую статистику для каждого отдельного теста (как у меня в моей таблице примеров в моем вопросе).
я бы сделал что-то вроде:
select @n = count(*) from tbl1
select @median = @n / 2
select @p75 = @n * 3 / 4
select @p90 = @n * 9 / 10
select top 1 score from (select top @median score from tbl1 order by score asc) order by score desc
это правильно?
я, вероятно, использовал бы SQL-сервер 2005
row_number () по (порядок счетом) / (избранное количество (*) от очков)
или что-то вдоль тех строк.
Процентное значение рассчитывается по
(Rank -1) /(total_rows -1)
при сортировке значений в порядке возрастания.
Приведенный ниже запрос даст вам значение процентиля от 0 до 1. У человека с наименьшими оценками будет 0 процентиль.
SELECT Name, marks, (rank_1-1)/((select count(*) as total_1 from table)-1)as percentile_rank
from
(
SELECT Name,
Marks,
RANK() OVER (ORDER BY Marks) AS rank_1
from table
) as A