over 函数 和 leteral view

hive中udf或者聚合函数，计算函数这里就不说了，直接介绍下比较罕见的窗口函数和分析函数。




分析函数:
1.ntile  均分成2份


select 
      nick, 
      payment ,
      NTILE(2) OVER(ORDER BY payment desc) AS rn 
from test_nick_payment;




2.Rank，Dense_Rank, Row_Number


select 
   class1,
   score,
   rank() over(partition by class1 order by score desc) rk1,
   dense_rank() over(partition by class1 order by score desc) rk2,
   row_number() over(partition by class1 order by score desc) rk3
from zyy_test1;


row_number就是每行单纯的数字递增
rank是重复的有一样的rank值，后面的值递增时加上重复数
rank是重复的有一样的dense_rank值，后面的值递增时加上重复数仅为1






窗口函数:
Lag, Lead
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值


LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值, 与LAG相反
具体含义见图




first_value:  取分组内排序后，截止到当前行，第一个值
last_value:  取分组内排序后，截止到当前行，最后一个值


select
   dp_id,
   mt,
   payment,
   FIRST_VALUE(payment) over(partition by dp_id order by mt) payment_g_first,
   LAST_VALUE(payment) over(partition by dp_id order by mt) payment_g_last,
  FIRST_VALUE(payment) over(partition by dp_id order by mt desc) payment_g_last_global
from test2
ORDER BY dp_id,mt;










问题来了，over()是个啥?


OVER(PARTITION BY)函数介绍


Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。
 
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：


over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数
over（partition by deptno）按照部门分区
over（partition by deptno order by salary）


开窗的窗口范围：
over（order by salary range between 5 preceding and 5 following）：窗口范围为当前行数据幅度减5加5后的范围内的。
关键字range between




select name,class,s, sum(s)over(order by s rows between 2 preceding and 2 following) mm from t2  表示在上下两行之间的范围内
adf        3        45        174  （45+55+74=174）
asdf       3        55        252   （45+55+74+78=252）
cfe        2        74        332    （74+55+45+78+80=332）
3dd        3        78        379    （78+74+55+80+92=379）
fda        1        80        419
gds        2        92        440
ffd        1        95        461




所以over就是个开窗函数
它常与上面的基于窗口的分析函数和窗口函数结合起来使用。


over语句还可以独立出来，用window重写:
select shop_id, stat_date, ordamt, sum(ordamt) over win as t  
from rt_data   
where dt = '2015-01-11' and shop_id = 10026  
window win as (distribute by shop_id sort by shop_id, ordamt desc rows between unbounded preceding and current row);  


在深入研究Over字句之前，一定要注意：在SQL处理中，窗口函数都是最后一步执行，而且仅位于Order by字句之前.


select name,count(*) over ()
from t_window
where substring(orderdate,1,7) = '2015-04'






接下来最后介绍下explode和lateral view的概念:
explode是一种udtf，其它还有很多，及将一行变成多行的ud table function。




string pageid Array<int> adid_list
"front_page" [1, 2, 3]
"contact_page" [3, 4, 5]




SELECT pageid, adid FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;


string pageid int adid
"front_page" 1
"front_page" 2
"front_page" 3
"contact_page" 3
"contact_page" 4
"contact_page"  








Array<int> col1 Array<string> col2
[1, 2] [a", "b", "c"]
[3, 4] [d", "e", "f"]






SELECT myCol1, myCol2 FROM baseTable LATERAL VIEW explode(col1) myTable1 AS myCol1  
LATERAL VIEW explode(col2) myTable2 AS myCol2;  






int myCol1 string myCol2
1 "a"
1 "b"
1 "c"
2 "a"
2 "b"
2 "c"
3 "d"
3 "e"
3 "f"
4 "d"
4 "e"
4 "f"




即explode 或者udtf的数据要想被使用，必须结合lateral view将 user defined table封装起来，就当是个udtf好了。可以认为leteral view就是udtf的附属品。








顺便介绍下udf udaf  udtf
udf:1->1的转换
udaf 多->1的聚合
udtf  1->多的扩展