Tính thứ hạng phần trăm trong MS SQL

Tôi nghĩ rằng đây sẽ là giải pháp đơn giản nhất:

SELECT TOP N PERCENT FROM TheTable ORDER BY TheScore DESC

Trong đó N = (100 - phần trăm mong muốn). Vì vậy, nếu bạn muốn tất cả các hàng trong phân vị thứ 90, bạn sẽ chọn 10% hàng đầu.

Tôi không chắc ý của bạn là "tốt nhất là trong một bản ghi". Bạn có nghĩa là tính toán phần trăm mà một điểm nhất định cho một bản ghi sẽ rơi vào? ví dụ. bạn có muốn đưa ra tuyên bố như "điểm của bạn là 83, đưa bạn vào phần trăm thứ 91". ?

EDIT: OK, tôi đã nghĩ thêm về câu hỏi của bạn và đưa ra cách giải thích này. Bạn đang hỏi làm thế nào để tính điểm cắt cho một tỷ lệ phần trăm cụ thể? ví dụ. đại loại như thế này: để ở phần trăm thứ 90, bạn phải có số điểm lớn hơn 78.

Nếu vậy, truy vấn này hoạt động. Tôi không thích các truy vấn phụ, vì vậy tùy thuộc vào mục đích của nó, có lẽ tôi sẽ cố gắng tìm một giải pháp thanh lịch hơn. Tuy nhiên, nó trả về một bản ghi với một điểm số duy nhất.

-- 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

Kiểm tra lệnh NTILE - nó sẽ cung cấp cho bạn phần trăm khá dễ dàng!

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)

Còn cái này thì sao:

SELECT
  Group,
  75_percentile =  MAX(case when NTILE(4) OVER(ORDER BY score ASC) = 3 then score  else 0 end),
  90_percentile =  MAX(case when NTILE(10) OVER(ORDER BY score  ASC) = 9 then score  else 0 end)     
FROM TheScore
GROUP BY Group

Phân vị thứ 50 giống như trung vị. Khi tính toán phần trăm khác, giả sử là thứ 80, sắp xếp dữ liệu cho 80 phần trăm dữ liệu theo thứ tự tăng dần và 20 phần trăm khác theo thứ tự giảm dần và lấy avg của hai giá trị trung bình.

Lưu ý: Truy vấn trung bình đã có từ lâu, nhưng không thể nhớ chính xác tôi đã lấy nó từ đâu, tôi chỉ sửa đổi nó để tính các phân vị khác.

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

Tôi đã làm việc về vấn đề này nhiều hơn một chút và đây là những gì tôi nghĩ ra cho đến nay:

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

Sau đó, trong một thủ tục lưu trữ khác, tôi làm điều này:

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

Nó vẫn không làm được những gì tôi đang tìm kiếm. Điều này sẽ có được số liệu thống kê cho tất cả các bài kiểm tra; trong khi tôi muốn có thể chọn từ bảng TestScores có nhiều bài kiểm tra khác nhau và lấy lại các số liệu thống kê giống nhau cho mỗi bài kiểm tra khác nhau (như tôi có trong bảng ví dụ của mình trong câu hỏi của mình).

Phần trăm được tính theo

(Rank -1) /(total_rows -1) khi bạn sắp xếp các giá trị theo thứ tự tăng dần.

Truy vấn dưới đây sẽ cung cấp cho bạn giá trị phần trăm trong khoảng từ 0 đến 1. Người có điểm thấp nhất sẽ có 0 phần trăm.

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

tôi có thể sử dụng máy chủ sql 2005 

row_number () over (thứ tự theo điểm số)/(chọn tính (*) từ điểm số)

hoặc một cái gì đó dọc theo các đường dây. 

tôi sẽ làm một cái gì đó như:

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

thê nay đung không?