一row_number() over()
使用row_number函数，对表中的数据按照省份分组，按照人数倒序排序并进行标记：

select
 province_name
 ,city_name
 ,pc_cnt
 ,row_number() over(partition by province_name order by pc_cnt desc) as rn
 from
 wedw_tmp.t_rn
 ;

hive 多个row_number如何优化 hive中row_number用法_默认值

然后，利用上面的结果，查询出rn<=2的即为最终需求

select
 tmp.province_name
 ,tmp.city_name
 ,tmp.pc_cnt
 from
 (
 select
 province_name
 ,city_name
 ,pc_cnt
 ,row_number() over(partition by province_name order by pc_cnt desc) as rn
 from
 wedw_tmp.t_rn
 ) tmp
 where tmp.rn <= 2
 ;

hive 多个row_number如何优化 hive中row_number用法_默认值_02

二 sum() over()
对于每个人的一个月的销售额和累计到当前月的销售总额

select
 user_name
 ,month_id
 ,sale_amt
 ,sum(sale_amt) over(partition by user_name order by month_id rows between unbounded preceding and current row) as all_sale_amt
 from wedw_tmp.t_sum_over;

hive 多个row_number如何优化 hive中row_number用法_数据窗_03

注：这些窗口的划分都是在分区内部！超过分区大小就无效了
可以看到如果不指定ROWS BETWEEN,默认统计窗口为从起点到当前行;
关键是理解 ROWS BETWEEN 含义,也叫做window子句：
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：无边界，UNBOUNDED PRECEDING 表示从最前面的起点开始， UNBOUNDED FOLLOWING：表示到最后面的终点
其他测试：

select
 user_name
 ,month_id
 ,sale_amt
 ,==sum(sale_amt) over(partition by user_name order by month_id) as all_sale_amt1 ==–默认为从起点行到当前行
 ,sum(sale_amt) over(partition by user_name order by month_id rows between unbounded preceding and current row) as all_sale_amt2 --从起点行到当前行
 ,sum(sale_amt) over(partition by user_name order by month_id rows between 3 preceding and current row) as all_sale_amt3 --当前行及往前3行之和
 ,sum(sale_amt) over(partition by user_name order by month_id rows between 3 preceding and 1 following) as all_sale_amt4 --当前行及往前3行往后1行之和
 ,sum(sale_amt) over(partition by user_name order by month_id rows between current row and unbounded following) as all_sale_amt5 --当前行及往后所有行之和
 from wedw_tmp.t_sum_over;

hive 多个row_number如何优化 hive中row_number用法_数据窗_04

三lag/lead() over()
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
第一个参数为列名，第二个参数为往上第n行（默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
第一个参数为列名，第二个参数为往下第n行（默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
以lag() over()为例：
数据准备:

create table t_hosp(
 user_name string
 ,age int
 ,in_hosp date
 ,out_hosp date)
 row format delimited fields terminated by ‘,’;
 xiaohong,25,2020-05-12,2020-06-03
 xiaoming,30,2020-06-06,2020-06-15
 xiaohong,25,2020-06-14,2020-06-19
 xiaoming,30,2020-06-20,2020-07-02
 user_name:用户名
 age:年龄
 in_hosp:住院日期
 out_hosp：出院日期

需求：求同一个患者每次住院与上一次出院的时间间隔
第一步：

select
 user_name
 ,age
 ,in_hosp
 ,out_hosp
 ,LAG(out_hosp,1,in_hosp) OVER(PARTITION BY user_name ORDER BY out_hosp asc) AS pre_out_date
 from
 t_hosp
 ;
其中，LAG(out_hosp,1,in_hosp) OVER(PARTITION BY user_name ORDER BY out_hosp asc)
表示根据user_name分组按照out_hosp升序取每条数据的上一条数据的out_hosp，
如果上一条数据为空，则使用默认值in_hosp来代替

hive 多个row_number如何优化 hive中row_number用法_数据_05

第二步：每条数据的in_hosp与pre_out_date的差值即本次住院日期与上次出院日期的间隔:

select
user_name
,age
,in_hosp
,out_hosp
,==datediff(in_hosp,LAG(out_hosp,1,in_hosp) OVER(PARTITION BY user_name ORDER BY out_hosp asc)) as days ==
from
t_hosp
;

hive 多个row_number如何优化 hive中row_number用法_数据_06

补充：
一.聚合分析函数
SUM ： 该函数计算组中表达式的累积和
COUNT ： 对一组内发生的事情进行累积计数
MIN ： 在一个组中的数据窗口中查找表达式的最小值
MAX ： 在一个组中的数据窗口中查找表达式的最大值
AVG ： 用于计算一个组和数据窗口内表达式的平均值。
二.排名分析函数
ROW_NUMBER ： – 正常排序[1,2,3,4] – 必须有order_by
RANK ： – 跳跃排序[1,2,2,4] – 必须有order_by
DENSE_RANK ： – 密集排序[1,2,2,3] – 必须有order_by
FIRST ： 从DENSE_RANK返回的集合中取出排在最前面的一个值的行
LAST ： 从DENSE_RANK返回的集合中取出排在最后面的一个值的行
FIRST_VALUE ： 返回组中数据窗口的第一个值
LAST_VALUE ： 返回组中数据窗口的最后一个值。