Hive SQL常用的函数记录

最新推荐文章于 2025-03-02 17:05:59 发布

原创最新推荐文章于 2025-03-02 17:05:59 发布 · 1k 阅读

4 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#hive #sql #hadoop

hive 专栏收录该内容

7 篇文章

订阅专栏

本文详细介绍了Hive中的日期函数、lag与lead函数以及窗口函数。lag与lead函数可获取同一字段前后N行数据，窗口函数可用于统计最近几天的数据，还介绍了rows between和range between的用法及区别，有助于进行数据过滤和统计分析。

1.日期函数

获取当前日期是第几周：from_unixtime(unix_timestamp('20210909','yyyyMMdd'), 'ww');
下面这种方式获取周，会少了一周：
weekofyear(from_unixtime(unix_timestamp(cast('20210809' as string),'yyyyMMdd'),'yyyy-MM-dd')) ;
日期范围当前月的第一天到最后一天：
and a.day >=date_format(DATE_SUB(FROM_UNIXTIME(UNIX_TIMESTAMP()),DAYOFMONTH(FROM_UNIXTIME(UNIX_TIMESTAMP()))),'yyyyMM01') 
and a.day <=date_format(DATE_SUB(FROM_UNIXTIME(UNIX_TIMESTAMP()),DAYOFMONTH(FROM_UNIXTIME(UNIX_TIMESTAMP()))),'yyyyMMdd')

查询当前系统时间(包括毫秒数): current_timestamp;  
查询当月第几天: dayofmonth(current_date);
月末: last_day(current_date)
当月第1天: date_sub(current_date,dayofmonth(current_date)-1)
下个月第1天: add_months(date_sub(current_date,dayofmonth(current_date)-1),1)
获取当前周的周一和周日
date_add(next_day(from_unixtime(unix_timestamp('20210923','yyyyMMdd'),'yyyy-MM-dd'),'MO'),-7) as monday,
date_add(next_day(from_unixtime(unix_timestamp('20210923','yyyyMMdd'),'yyyy-MM-dd'),'MO'),0) as sunday

获取当前日期函数

from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss') 和系统时间没有差异
now()函数和系统时间有8小时之差 from_unixtime(unix_timestamp(now()), 'yyyy-MM-dd HH:mm:ss') 
测试case：
from_unixtime(unix_timestamp() , 'yyyy-MM-dd HH:mm:ss') ==> 2021-12-29 10:42:12
from_unixtime(unix_timestamp(now()),'yyyy-MM-dd HH:mm:ss') ==> 2021-12-29 02:42:12
from_unixtime(unix_timestamp(now())+28800 ,'yyyy-MM-dd HH:mm:ss')  ===> 2021-12-29 10:42:12

2.lag与lead函数

lag与lead函数是跟偏移量相关的两个分析函数，通过这两个函数可以在一次查询中取出同一字段的前N行的数据(lag)和后N行的数据(lead)作为独立的列,从而更方便地进行进行数据过滤。这种操作可以代替表的自联接，并且LAG和LEAD有更高的效率。

over()表示 lag()与lead()操作的数据都在over()的范围内，他里面可以使用partition by 语句（用于分组） order by 语句（用于排序）。partition by a order by b表示以a字段进行分组，再以b字段进行排序，对数据进行查询。

例如：lead(field, num, defaultvalue) field需要查找的字段，num往后查找的num行的数据，defaultvalue没有符合条件的默认值。

如：1、获取当前记录的id，以及下一条记录的id


select t.id id ,
       lead(t.id, 1, null) over (order by t.id)  next_record_id, t.cphm
from tb_test t       
  order by t.id asc

如：2、获取当前记录的id，以及上一条记录的id

select t.id id ,
       lag(t.id, 1, null) over (order by t.id)  next_record_id, t.cphm
from tb_test t       
  order by t.id asc

如：3、获取号牌号码相同的，当前记录的id与，下一条记录的id（使用partition by）

select t.id id, 
       lead(t.id, 1, null) over(partition by cphm order by t.id) next_same_cphm_id, t.cphm
from tb_test t
     order by t.id asc  

如：4、查询 cphm的总数，当create_date与下一条记录的create_date时间间隔不超过10分钟则忽略

select cphm, count(1) total from
(
select t.id, 
  t.create_date t1,
  lead(t.create_date,1, null) over( partition by  cphm order by create_date asc ) t2,  
  ( lead(t.create_date,1, null) over(  partition by  cphm order by create_date asc )  - t.create_date ) * 86400 as itvtime,
  t.cphm
from tb_test t 
  order by t.cphm, t.create_date asc
) tt
where tt.itvtime >= 600 or  tt.itvtime  is null
group by tt.cphm

下面还有很多用法，就不一一列举了，简单介绍一下，和上面用法类似：

count() over(partition by ... order by ...)：求分组后的总数。
　　max() over(partition by ... order by ...)：求分组后的最大值。
　　min() over(partition by ... order by ...)：求分组后的最小值。
　　avg() over(partition by ... order by ...)：求分组后的平均值。
　　lag() over(partition by ... order by ...)：取出前n行数据。　　

　　lead() over(partition by ... order by ...)：取出后n行数据。

　　ratio_to_report() over(partition by ... order by ...)：Ratio_to_report() 括号中就是分子，over() 括号中就是分母。

　　percent_rank() over(partition by ... order by ...)：

3.窗口函数统计最近几天

rows between …… and ……

unbounded preceding 前面所有行
unbounded following 后面所有行
current row 当前行
n following 后面n行
n preceding 前面n行
rows between 例子
当前行与后面所有行的累加（分区内）

// 从当前行到最后的数据
sum(sales_volume) over(partition by id rows between current row and unbounded following) sum_sales

前面所有行与当前行的累加（分区内）

sum(sales_volume) over(partition by id rows between unbounded preceding and current row) sum_sales

当前行与后两行的累加（分区内）

sum(sales_volume) over(partition by id rows between current row and 2 following) sum_sales

前一行与当前行的累加（分区内）

sum(sales_volume) over(partition by id rows between 1 preceding and current row) sum_sales

前一行的值+当前行的值+后一行的值

sum(id) over(partition by category rows between 1 preceding and 1 following) rank from t

取当前行的前两条及后两条来参与计算，一般用于移动平均值

rows between 2 preceding and 2 following

按DEPTNO分区，ENAME顺序排列，统计从开始到结束的所有数据,这里相当于没有写ORDER BY

SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

按DEPTNO分区，ENAME顺序排列，统计从当前行到下一行数据

SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)

起始行到末尾行（分组范围内）

rows between unbounded preceding and unbounded following

select *,
count(*) over(partition by groupname
   order by id
                       rows between current row and 1 following) 当前行_后1行,

count(*) over(partition by groupname
   order by id
                       rows between 1 preceding and current row ) 前1行_当前行,

count(*) over(partition by groupname
   order by id
                       rows between current row and unbounded following) 当前行_最后1行,

count(*) over(partition by groupname
   order by id
                       rows between unbounded preceding and current row) 第1行_当前行,

   count(*) over(partition by groupname
   order by id rows between 1 preceding and 1 following) 前1行_后1行,

   count(*) over(partition by groupname
   order by id
                       rows between unbounded preceding and 1 following) 第1行_后1行,

   count(*) over(partition by groupname
   order by id
                       rows between 1 preceding and unbounded following) 前1行_最后1行
from test

2 range between 按照列值限制窗口大小（在非条件表达式中等同于rows）
rows表示行，就是前n行，后n行

而range表示的是具体的值，比这个值小n的行，比这个值大n的行

range between是以当前值为锚点进行计算

比如

range between 4 preceding AND 7 following
表示：如果当前值为10的话就取前后的值在6到17之间的数据。

sum(close) range between 100 preceding and 200 following
则通过字段差值来进行选择。如当前行的 close 字段值是 200，那么这个窗口大小的定义就会选择分区中 close 字段值落在 100 至 400 区间的记录（行）。

窗口范围为该分区的第一行和该分区的最后一行，在非条件表达式中等同于ROWS

SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
窗口范围为该分区的第一行和本行，在非条件表达式中等同于ROWS

SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
窗口范围为该分区内小于本记录hire_date-365天的所有的薪资累计

SUM(salary) OVER(PARTITION BY dept_id ORDER BY hire_date RANGE BETWEEN UNBOUNDED PRECEDING AND 365/*value_expr*/ PRECEDING)
UNBOUNDED PRECEDING 可以理解为第一行

参考：PostgreSQL窗口函数中 ROWS 和 RANGE 模式的区别

ROWS：是按物理行来进行窗口级别里再次进行范围选择的。
RANGE：是按逻辑行来进行窗口级别里再次进行范围选择的。RANGE时，相同行会被合并成同一条数据再进行计算，相同行窗口计算时的结果也是相同的。
是否是相同行，是根据ORDER BY排序时的结果决定的。
有ORDER BY时：同行是说在ORDER BY排序时不唯一的行。【即具有相同数值的行】
不同行是说ORDER BY排序时具有不同的数值的行。
没有ORDER BY：那么就是当前分区的所有行都包含在框架中，因为所有行都会成为当前行的相同行。

hive常用函数大全超详细整理_m0_46657040的博客-CSDN博客_hive常用函数整理