如何在SQLite中按百分位数为成绩列分配等级（转SQL实现）

Since SQLite doesn’t include a native quantile() function like R does, we can use the PERCENT_RANK() window function to calculate relative percentile ranks and map them to your grade rules. Let’s break this down into actionable steps:

Clarify Your Percentile Thresholds #

First, translate your grade rules into clear percentile cutoffs (we’ll sort scores in descending order so higher scores get better grades):

• A: Top 0.9% → Percentile rank ≤ 0.009
• B: Next 15% → Percentile rank > 0.009 and ≤ 0.159 (0.009 + 0.15)
• C: Next 25% → Percentile rank > 0.159 and ≤ 0.409 (0.159 + 0.25)
• D: Next 30% → Percentile rank > 0.409 and ≤ 0.709 (0.409 + 0.30)
• E: Next 13% → Percentile rank > 0.709 and ≤ 0.839 (0.709 + 0.13)
• F: Remaining → Percentile rank > 0.839

Practical SQL Implementation #

Assuming your table is named student_scores with columns like student_id (unique identifier) and score (numeric grade), here are two ways to implement this:

Option 1: Calculate Grades in a SELECT Query

This returns each student’s score and assigned grade without modifying your table:

WITH ranked_scores AS (
    SELECT
        student_id,
        score,
        -- Compute relative percentile rank (0 = highest score, 1 = lowest)
        PERCENT_RANK() OVER (ORDER BY score DESC) AS percentile_rank
    FROM student_scores
)
SELECT
    student_id,
    score,
    CASE
        WHEN percentile_rank <= 0.009 THEN 'A'
        WHEN percentile_rank <= 0.159 THEN 'B'
        WHEN percentile_rank <= 0.409 THEN 'C'
        WHEN percentile_rank <= 0.709 THEN 'D'
        WHEN percentile_rank <= 0.839 THEN 'E'
        ELSE 'F'
    END AS grade
FROM ranked_scores;

Option 2: Add a Permanent grade Column to Your Table

If you want to store the grades directly in the table:

-- First, add the grade column to your table
ALTER TABLE student_scores ADD COLUMN grade TEXT;

-- Update the column with calculated grades
WITH ranked_scores AS (
    SELECT
        student_id,
        PERCENT_RANK() OVER (ORDER BY score DESC) AS percentile_rank
    FROM student_scores
)
UPDATE student_scores
SET grade = (
    CASE
        WHEN percentile_rank <= 0.009 THEN 'A'
        WHEN percentile_rank <= 0.159 THEN 'B'
        WHEN percentile_rank <= 0.409 THEN 'C'
        WHEN percentile_rank <= 0.709 THEN 'D'
        WHEN percentile_rank <= 0.839 THEN 'E'
        ELSE 'F'
    END
)
FROM ranked_scores
WHERE student_scores.student_id = ranked_scores.student_id;

Key Notes #

• PERCENT_RANK() Explained: This function returns a value between 0 and 1, calculated as (current_row_rank - 1) / (total_rows - 1). It ensures consistent relative positioning even when multiple students have the same score (they’ll all get the same grade, which is standard for percentile-based grading).
• No Unique ID?: If your table doesn’t have a student_id or similar unique column, use SQLite’s built-in ROWID column to link the CTE results to the main table in the UPDATE query.

内容的提问来源于stack exchange，提问作者user9374533