NTILE 和 PERCENT_RANK() 的区别

 

NTILE 和 PERCENT_RANK 都是用于排名和分配的窗口函数，但它们的工作方式和结果有所不同。以下是它们的主要区别：

1. NTILE

功能：

• NTILE(n) 将数据分成 n 个桶或区间，按排序顺序将每个数据项分配到这些桶中。
• 每个桶大约包含数据的 1/n。

结果：

• 结果是桶的编号，从 1 到 n。
• 如果数据量不能完全均匀分布，某些桶可能会比其他桶多一个数据项。

用法：

• 适用于需要将数据分组到固定数量的区间或桶中的场景。

示例： 假设你要将学生的成绩分成 10 个桶，每个桶包含约 10% 的学生：

2. PERCENT_RANK

功能：

• PERCENT_RANK() 计算每个数据项的百分比排名。
• 排名范围从 0 到 1，其中 0 代表最底部，1 代表最顶部。

结果：

• 结果是一个浮动的百分比值，表示该数据项在其排序中的相对位置。

用法：

• 适用于需要知道数据项相对位置的场景，比如计算百分位数排名。

 

CREATE TABLE student_scores (
    student_id STRING,
    subject STRING,
    score DOUBLE
);


INSERT INTO student_scores (student_id, subject, score) VALUES
('S001', 'Math', 95.0),
('S002', 'Math', 85.0),
('S003', 'Math', 75.0),
('S004', 'Math', 65.0),
('S005', 'Math', 55.0),
('S001', 'Science', 88.0),
('S002', 'Science', 78.0),
('S003', 'Science', 70.0),
('S004', 'Science', 60.0),
('S005', 'Science', 50.0);

 

结果对比：

  
 >  
 > SELECT
 >     student_id,
 >     subject,
 >     score,
 >     ntile(10) OVER (PARTITION BY subject ORDER BY score DESC) AS rank_percent
 > FROM student_scores;
student_id    subject                score        rank_percent
S001            Math            95.0             1
S002            Math            85.0             2
S003            Math            75.0             3
S004            Math            65.0             4
S005            Math            55.0             5
S001            Science            88.0             1
S002            Science            78.0             2
S003            Science            70.0             3
S004            Science            60.0             4
S005            Science            50.0             5

 

 

         > SELECT
         >     student_id,
         >     subject,
         >     score,
         >     PERCENT_RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS rank_percent
         > FROM student_scores;
student_id             subject         score    rank_percent
S001                   Math             95.0        0.0
S002                   Math             85.0        0.25
S003                   Math             75.0        0.5
S004                   Math             65.0        0.75
S005                   Math             55.0        1.0
S001                   Science         88.0        0.0
S002                   Science         78.0        0.25
S003                   Science         70.0        0.5
S004                   Science         60.0        0.75
S005                   Science         50.0        1.0