Hive SQl 学习笔记

Hive SQL 常用窗口函数

排序函数

排序函数：

• row_number()
• rank()
• dense_rank()

需要和over()搭配使用

over() 内常用参数有

• order by 排序
• partition by 分组

• rank(): 跳跃式排序 —— 比如分数为99，99，90，89，那么通过这个函数得到的排名为1，1，3，4
• dense_rank(): 并列连续型排序 —— 比如分数为99，99，90，89，那么通过这个函数得到的排名为1，1，2，3
• row_number(): 连续型排序 —— 比如分数为99，99，90，89，那么通过这个函数得到的排名为1，2，3，

Lead() Lag()

分组中位于当前行后n行（lead）/ 前n行(lag)的记录值

可以用于相邻间隔问题 https://zhuanlan.zhihu.com/p/141699676

头尾函数

用途：得到分区中的第一个/最后一个指定参数的值。

SELECT s.sname, c.cname, sc.score,
       FIRST_VALUE(sc.score) OVER (PARTITION BY s.sname
                                   ORDER BY sc.score DESC) as firstVal,
       LAST_VALUE(sc.score) OVER (PARTITION BY s.sname
                                  ORDER BY sc.score DESC) as lastVal
FROM student s INNER JOIN score sc ON s.sid = sc.sid
	       INNER JOIN course c ON sc.cid = c.cid

 

分布函数

percent_rank() / cume_dist()

percent_rank() ：

• 和之前的RANK()函数相关，每行按照如下公式进行计算

                      (rank - 1) / (rows - 1)

• 其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数。

cume_dist()

• 用途：分组内大于等于当前rank值的行数/分组内总行数，这个函数比percen_rank使用场景更多。
• 应用场景：班级中某同学成绩占前%多少

 

日期相关

语法:  
TIMESTAMPDIFF(interval, datetime_expr1, datetime_expr2)  
说明:  
返回日期或日期时间表达式datetime_expr1 和datetime_expr2the 之间的整数差。其结果的单位由interval 参数给出。interval 的法定值同TIMESTAMPADD()函数说明中所列出的相同。  

DATEDIFF()

DATEADD()

说明：

DATEDIFF函数计算两个日期之间的小时、天、周、月、年等时间间隔总数。DATEADD函数计算一个日期通过给时间间隔加减来获得一个新的日期

Now()

返回当前时间

mysql> SELECT TIMESTAMPDIFF(MONTH,'2009-12-01','2009-09-01');  
-> -3  
mysql> SELECT TIMESTAMPDIFF(YEAR,'2009-05-01','2008-01-01');  

-> -1

时间戳函数

unix_timestamp函数
1、UNIX_TIMESTAMP() ：若无参数调用，则返回一个 Unix timestamp ('1970-01-01 00:00:00' GMT 之后的秒数) 作为无符号整数，得到当前时间戳 
2、UNIX_TIMESTAMP(date) ：若用date 来调用 UNIX_TIMESTAMP()，它会将参数值以'1970-01-01 00:00:00' GMT后的秒数的形式返回。date 可以是一个 DATE 字符串、一个 DATETIME字符串、一个 TIMESTAMP或一个当地时间的YYMMDD 或YYYMMDD格式的数字。

mysql> SELECT UNIX_TIMESTAMP() ; （执行时的时间:2009-08-06 10:10:40）
->1249524739
mysql> SELECT UNIX_TIMESTAMP('2009-08-06') ;
->1249488000 

---

1. 场景1：分组求TOP 

2.行/列转换