Hive 排名函数ROW_NUMBER、RANK()、DENSE_RANK等功能介绍、对比和举例_hive rank

目录

1. ROW_NUMBER()

2. RANK()

3. DENSE_RANK()

4. NTILE()

5. CUME_DIST()

6. PERCENT_RANK()

---

1. ROW_NUMBER()

  功能：ROW_NUMBER() 函数为每个分组内的行提供唯一的序列号，从1开始。如果在 OVER() 子句中使用 ORDER BY 语句，它将根据指定的列值对行进行排序。

  对比：

   每个行都会获得一个唯一的排名数字。
   即使两行的排序列值相同，它们也会获得连续的排名，不会有相同的排名值。

  举例： 

SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) as rank
FROM students;

  说明：假设 students 表包含学生的名字和分数。上述查询将根据分数降序排列学生，并为每个学生分配一个唯一的序列号作为排名。如果两个学生分数相同，他们将获得连续的排名编号。

2. RANK()

    功能：RANK() 函数在分组内为行提供排名，从1开始。如果两行或多行在 ORDER BY 子句中指定的排序列上的值相等，则它们将获得相同的排名，并且排名数字会跳过。

    对比：

    如果有相同值的行，它们将共享相同的排名。
    在相同值的行之后的排名会跳过那些共享排名的行数。例如，如果有两行共享排名1，那么下一个排名将是3。

    举例：

SELECT name, score, RANK() OVER (ORDER BY score DESC) as rank
FROM students;

   说明：使用与 ROW_NUMBER() 相同的数据，RANK() 函数将为分数相同的学生分配相同的排名。如果两个学生的分数相同，他们将共享排名，例如都是排名1，下一个学生的排名将是3（假设只有两个学生分数相同）。

3. DENSE_RANK()

   功能：DENSE_RANK() 函数与 RANK() 函数类似，为分组内的行提供排名，但是它不会跳过排名数字。

   对比：

    与 RANK() 类似，相同值的行会获得相同的排名。
    不同于 RANK()，DENSE_RANK() 在相同值的行之后不会跳过排名数字。例如，如果有两行共享排名1，那么下一个排名将是2。

   举例：

SELECT name, score, DENSE_RANK() OVER (ORDER BY score DESC) as rank
FROM students;

   说明：使用与 RANK() 相同的数据，DENSE_RANK() 函数也会为分数相同的学生分配相同的排名。不同之处在于，它不会跳过排名，所以在上述例子中，下一个学生的排名将是2，而不是3。

4. NTILE()

   功能：NTILE() 函数将分组内的行分配到指定数量的桶中，并为每个行提供桶的编号。这对于数据分区和分布式处理很有用。

   对比：

    NTILE() 用于更均匀地分配行到固定数量的桶中，而不是基于其值的排名。
    它更适合于当你需要将数据均匀地分成几部分时使用。

   举例：

SELECT name, score, NTILE(4) OVER (ORDER BY score DESC) as bucket
FROM students;

  说明：假设我们想要将学生根据分数高低分成4个桶。NTILE(4) 将学生分成四个组，每个组有相近数量的学生，并为每个学生分配一个桶编号。

5. CUME_DIST()

    功能：CUME_DIST() 函数计算每个记录的累计分布百分比。这个函数返回一个介于 0 和 1 之间的值，表示小于或等于当前记录值的所有记录所占的比例。计算方式是 number of rows preceding or peer with the current row / total rows。

    对比：

    这是一个统计函数，用于计算行在整体分布中的位置。
    它不提供排名，而是提供了每行相对于整个数据集的位置度量。

    举例：

SELECT name, score, CUME_DIST() OVER (ORDER BY score DESC) as cume_dist
FROM students;

  说明：这将计算每个学生的累积分布值。在上述查询中，每个学生的 cume_dist 值表示有多少比例的学生分数低于或等于该学生。在这个例子中，每个学生的成绩会根据分数从高到低计算出一个累计分布百分比。例如，如果有 10 名学生，当前学生是按成绩排序后的第 3 名，那么前三名（包括当前学生）的学生数占总学生数的比例即为当前学生的累计分布百分比。如果前三名的成绩相同，那么这三名学生的累计分布百分比相同，都是 0.3。

6. PERCENT_RANK()

    功能：PERCENT_RANK() 函数计算每个记录在结果集中的相对排名百分比。这个函数会返回一个介于 0 和 1 之间的值，其中 0 表示排名最高（第一位），而 1 表示排名最低（最后一位）。计算方式是 (rank - 1) / (total rows - 1)。

   对比：

     PERCENT_RANK() 提供了一个介于0和1之间的数值，这个数值表示当前行的相对排名。
    它用于确定行在其分组内的相对位置，而不是一个绝对的排名数字。

    举例：

SELECT name, score, PERCENT_RANK() OVER (ORDER BY score DESC) as percent_rank
FROM students;

   说明：在这个例子中，每个学生的成绩会根据分数从高到低计算出一个百分比排名。第一名的学生百分比排名是 0，最后一名的百分比排名是 1，其他学生的百分比排名介于两者之间。