hive开窗函数中range和rows的区别

• rows是物理窗口，是哪一行就是哪一行，与当前行的值（order by key的key的值）无关，只与排序后的行号相关，就是我们常规理解的那样。
• range是逻辑窗口，与当前行的值有关（order by key的key的值）,在key上操作range范围。

select id
,sum(id) over(order by id) default_sum
,sum(id) over(order by id range between unbounded preceding and current row) range_sum
,sum(id) over(order by id rows between unbounded preceding and current row) rows_sum
,sum(id) over(order by id range between 1 preceding and 2 following) range_sum1
,sum(id) over(order by id rows between 1 preceding and 2 following) rows_sum1
from tmp

运行结果如下

分析：

• 1.当order by后面的rows/range缺失时，默认是range between unbounded preceding and current row
• 2.关于range_sum1的range分析
  • order by id，所以要看id的值，并对key(id进行range操作)，即[id-1, id+2]。注意是闭区间
  • 当id=1时，因为是对id的值在[0, 3]的行都包括在内(取id为1,1,3)，所以sum(id) = 1 + 1 + 3 = 5
  • 当id=3时，因为是对id的值在[2, 5]的行都包括在内(取id为3)，所以sum(id) = 3
  • 当id=6时，[5,8]，sum(id) = 6 + 6 + 6 + 7 + 8 = 33
  • 当id=7时，[6,9]，sum(id) = 6 + 6 + 6 + 7 + 8 + 9 = 42
  • 当id=8时，[7,10]，sum(id) = 7 + 8 + 9 = 24
  • 当id=9时，[8,11]，sum(id) = 8 + 9 = 17
• 3.关于range_sum的range分析同上
  • 当id=1时，id在[-inf, 1]，故sum(id) = 1 + 1 = 2
  • 当id=3时，id在[-inf, 3]，故sum(id) = 1 + 1 + 3 = 5
  • 当id=6时，id在[-inf, 6]，故sum(id) = 1 + 1 + 3 + 6 + 6 + 6 = 23
  • 当id=7时，id在[-inf, 7]，故sum(id) = 1 + 1 + 3 + 6 + 6 + 6 + 7 = 30
  • 当id=8时，id在[-inf, 8]，故sum(id) = 1 + 1 + 3 + 6 + 6 + 6 + 7 + 8 = 38
  • 当id=9时，id在[-inf, 9]，故sum(id) = 1 + 1 + 3 + 6 + 6 + 6 + 7 + 8 + 9 = 47

ps: row_number不受order by key 的重复key的影响