hadoop - 如何计算 Hive 表中小于特定行的行数？

Question

在 Hive 中考虑下表

+------+------+
| id   | res  |
+------+------+
|    1 |   55 |
|    2 |   10 |
|    3 |   89 |
|    4 |  100 |
|    5 |   80 |
|    6 |   55 |
|    7 |   70 |
|    8 |   35 |
|    9 |   46 |
|   10 |   51 |
+------+------+

现在我必须计算特定行中小于 res 值的行数。

对于上表，输出应该是

+------+------+
| id   |count |
+------+------+
|    1 |    4 |
|    2 |    0 |
|    3 |    8 |
|    4 |    9 |  
|    5 |    7 |
|    6 |    4 | 
|    7 |    6 |
|    8 |    1 |
|    9 |    2 |
|   10 |    3 |
+------+------+

Answer 1

您可以尝试 RANK OVER 功能。

示例 Hiveql

select
  id,
  res,
  rank() over (ORDER BY res) as rank
from
  my_table
order by
  res

阅读更多 here和 here .