hive归档 - Page 4 of 4

apache hive 日期函数的语法介绍和具体使用场景

无论是我们写普通SQL还是，大数据的hsql(apache hive sql)，很多的场景下都会涉及到时间、日期相关的字段处理，而这些数据库或者大数据组件都给我们提供其内置的时间、日期处理函数，以供我们更便捷的处理相关字段。

1、获取当前UNIX时间戳函数: unix_timestamp

语法: unix_timestamp()
返回值: bigint
说明: 获得当前时区的UNIX时间戳

hive> select unix_timestamp() from hive_table;
1323309615

语法:unix_timestamp(string date)
返回值: bigint
说明: 转换格式为“yyyy-MM-dd HH:mm:ss“的日期到UNIX时间戳。如果转化失败，则返回0。

hive>   select unix_timestamp('2011-12-07 13:01:03') from hive_table;
1323234063

语法: unix_timestamp(string date, string pattern)
返回值: bigint
说明: 转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。

hive>   select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss') from hive_table;
1323234063

2、日期函数UNIX时间戳转日期函数: from_unixtime

语法:from_unixtime(bigint unixtime[, string format])
返回值: string
说明: 转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式

hive> select from_unixtime(1323308943,'yyyyMMdd') from hive_table;
20111208

3、日期时间转日期函数: to_date

语法: to_date(string timestamp)
返回值: string
说明: 返回日期时间字段中的日期部分。

hive>   select to_date('2011-12-08 10:03:01') from hive_table;
2011-12-08

4、日期转年函数: year

语法: year(string date)
返回值: int
说明: 返回日期中的年。

hive>   select year('2021-06-10 10:03:01') from hive_table;
2021
hive>   select year('2020-06-10') from hive_table;
2020

5、日期转月函数: month

语法: month (string date)
返回值: int
说明: 返回日期中的月份

hive>   select month('2021-06-10 10:03:01') from hive_table;
6
hive>   select month('2020-10-10') from hive_table;
10

6、日期转天函数: day

语法: day (string date)
返回值: int
说明: 返回日期中的天。

hive>   select day('2021-06-10 10:03:01') from hive_table;
10
hive>   select day('2020-10-24') from hive_table;
24

7、日期转小时函数: hour

语法: hour (string date)
返回值: int
说明: 返回日期中的小时。

hive>   select hour('2021-06-10 10:03:01') from hive_table;
10

8、日期转分钟函数: minute

语法: minute (string date)
返回值: int
说明: 返回日期中的分钟。

hive>   select minute('2021-06-10 10:03:01') from hive_table;
3

9、日期转秒函数: second

语法: second (string date)
返回值: int
说明: 返回日期中的秒。

hive>   select second('2021-06-10 10:03:01') from hive_table;
1

10、日期转周函数: weekofyear

语法: weekofyear (string date)
返回值: int
说明: 返回日期在当前的周数。

hive>   select weekofyear('2021-12-08 10:03:01') from hive_table;
49

11、日期减少函数: date_sub

语法: date_sub (string startdate, int days)
返回值: string
说明: 返回开始日期startdate减少days天后的日期。

hive>   select date_sub('2021-12-08',10) from hive_table;
2021-11-28

12、日期增加函数: date_add

语法: date_add(string startdate, int days)
返回值: string
说明: 返回开始日期startdate增加days天后的日期。

hive>   select date_add('2021-12-08',10) from hive_table;
2021-12-18

13、日期比较函数: datediff

语法: datediff(string enddate, string startdate)
返回值: int
说明: 返回结束日期减去开始日期的天数。

hive>   select datediff('2021-12-08','2021-12-19') from hive_table;
11

hive 中使用with…as语法建立临时中间表

当我们书写一些结构相对复杂的SQL语句时，可能某个子查询在多个层级多个地方存在重复使用的情况，这个时候我们可以使用 with as 语句将其独立出来，极大提高SQL可读性，简化SQL。前mysql、 oracle、sql server、hive等均支持 with as 用法

1、介绍

with as 也叫做子查询部分，首先定义一个sql片段，该sql片段会被整个sql语句所用到，为了让sql语句的可读性更高些，作为提供数据的部分，也常常用在union等集合操作中。
with as就类似于一个视图或临时表，可以用来存储一部分的sql语句作为别名，不同的是with as 属于一次性的，而且必须要和其他sql一起使用才可以！
其最大的好处就是适当的提高代码可读性，而且如果with子句在后面要多次使用到，这可以大大的简化SQL；更重要的是：一次分析，多次使用，这也是为什么会提供性能的地方，达到了“少读”的目标。

with sub_query_name1 as (
	select 
		xx
	from
		xxx
), -- 注意这里用逗号分割
sub_query_name2 as ( --注意这里没有as，上面一个as跟多个sub_query
	select
		xx
	from
		xx
)
select --正式查询语句，与上面的with as直接没有符号分割
	xx
from
	sub_query_name1,sub_query_name2

2、注意事项

整体作为一条sql查询，即with as语句后不能加分号，不然会报错。
with子句必须在引用的select语句之前定义,同级with关键字只能使用一次,多个只能用逗号分割；最后一个with 子句与下面的查询之间不能有逗号，只通过右括号分割,with 子句的查询必须用括号括起来.
如果定义了with子句，但其后没有跟select查询，则会报错！
前面的with子句定义的查询在后面的with子句中可以使用。但是一个with子句内部不能嵌套with子句！

3、代码示例

# 直接查询
with tmp_a as (
select f1,f2,f3 from test1
)
select f1,f2,f3 from tmp_a;

# 多表关联
with tmp_a as (
select f1,f2,f3 from test1
),
tmp_b as(
select f1,f4,f5 from test2
)
select a.f1,a.f2,a.f3,b.f4,b.f5 from
tmp_a a
left join
tmp_b b
on a.f1 = b.f1

Hive之列转行,行转列

hive sql 处理数据的时候经常会有行转列，列转行的需求。废话不多说，上代码。

hive> select * from test limit 10;
OK
test.user_id    test.order_id
104399    1715131
104399    2105395
104399    1758844
104399    981085
104399    2444143
104399    1458638
104399    968412
104400    1609001
104400    2986088
104400    1795054

列传行–> 把相同user_id的order_id按照逗号转为一行

select user_id,
concat_ws(',',collect_list(order_id)) as order_value 
from test
group by user_id
limit 10;

//结果(简写)
user_id    order_value
104399    1715131,2105395,1758844,981085,2444143

说明：

使用函数：concat_ws(‘,’,collect_set(column)) ；collect_list 不去重，collect_set 去重。 column的数据类型要求是string

行转列 –> 把一行的数据按某个分隔符分割后形成多行

测试数据如下：

hive> select * from test;
OK
test.user_id    test.order_value
104408    2909888,2662805,2922438,674972,2877863,190237
104407    2982655,814964,1484250,2323912,2689723,2034331,1692373,677498,156562,2862492,338128
104406    1463273,2351480,1958037,2606570,3226561,3239512,990271,1436056,2262338,2858678
104405    153023,2076625,1734614,2796812,1633995,2298856,2833641,3286778,2402946,2944051,181577,464232
104404    1815641,108556,3110738,2536910,1977293,424564
104403    253936,2917434,2345879,235401,2268252,2149562,2910478,375109,932923,1989353
104402    3373196,1908678,291757,1603657,1807247,573497,1050134,3402420
104401    814760,213922,2008045,3305934,2130994,1602245,419609,2502539,3040058,2828163,3063469
104400    1609001,2986088,1795054,429550,1812893
104399    1715131,2105395,1758844,981085,2444143,1458638,968412
Time taken: 0.065 seconds, Fetched: 10 row(s)

将order_value的每条记录切割为单元素

select user_id,order_value,order_id
from test
lateral view explode(split(order_value,',')) num as order_id
limit 10;

//结果
user_id    order_value    order_id
104408    2909888,2662805,2922438,674972,2877863,190237    2909888
104408    2909888,2662805,2922438,674972,2877863,190237    2662805
104408    2909888,2662805,2922438,674972,2877863,190237    2922438
104408    2909888,2662805,2922438,674972,2877863,190237    674972
104408    2909888,2662805,2922438,674972,2877863,190237    2877863
104408    2909888,2662805,2922438,674972,2877863,190237    190237
104407    2982655,814964,1484250,2323912,2689723,2034331,1692373,677498,156562,2862492,338128    2982655
104407    2982655,814964,1484250,2323912,2689723,2034331,1692373,677498,156562,2862492,338128    814964
104407    2982655,814964,1484250,2323912,2689723,2034331,1692373,677498,156562,2862492,338128    1484250
104407    2982655,814964,1484250,2323912,2689723,2034331,1692373,677498,156562,2862492,338128    2323912
Time taken: 0.096 seconds, Fetched: 10 row(s)