Hive有哪些常见函数

发布时间：2021-07-10 15:32:44 作者：chen
来源：亿速云阅读：145

本篇内容主要讲解“Hive有哪些常见函数”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“Hive有哪些常见函数”吧!

1. HQL 判断空的函数

https://www.w3school.com.cn/sql/func_date_format.asp

2. 时间转换函数

year(string date)：返回年份部分的日期或时间戳字符串：year("1970-01-01 00:00:00") = 1970, year("1970-01-01") = 1970

https://blog.csdn.net/wzzfeitian/article/details/55097563

https://www.yiibai.com/hive/hive_built_in_functions.html

3. group by 函数，需要多次进行分组

数据类型转换：https://blog.csdn.net/qq_31573519/article/details/100139218

4. hive获取上个月第一天和最后一天的时间

//获取上个月第一天
date('Y-m-01',strtotime('-1 month'));
 
//获取上个月最后一天
date('Y-m-t',strtotime('-1 month'));

SELECT
--上个月第一天
concat(SUBSTR(DATE_SUB(FROM_UNIXTIME(UNIX_TIMESTAMP()),DAY(FROM_UNIXTIME(UNIX_TIMESTAMP()))),1,7) ,'-01'),
--上个月最后一天
DATE_SUB(FROM_UNIXTIME(UNIX_TIMESTAMP()),DAY(FROM_UNIXTIME(UNIX_TIMESTAMP())))

select
--上个月第一天
trunc(add_months(CURRENT_TIMESTAMP,-1),'MM'),
--上个月第一天
concat(substr(add_months(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),-1),1,7),'-01'),
--上个月最后一天
date_sub(trunc(CURRENT_TIMESTAMP,'MM'),1);

5. 当月第一天

-- 当月第一天
SELECT TRUNC(sysdate(0), 'MM') ;

6. 前两个月第一天

-- 前两个月第一天
select TRUNC(add_months(CURRENT_TIMESTAMP, -2), 'MM');

7. CAST 转换小数保留位数

cast(column_name as decimal(10,2)) cast函数四舍五入（推荐使用）

select cast(68.666666666666668 as decimal(10,2));
_c0
68.67

select cast(68.666666666666668 as decimal(10,3));
_c0
68.667

8. 求两数除数,保留两位小数

ROUND((SUM(delivered_num) / SUM(plan_num) * 100),2)

cast(column_name as decimal(10,2)) cast函数四舍五入（推荐使用）

CAST((SUM(delivered_num) / SUM(plan_num) * 100) AS DECIMAL(10, 2))

9. Presto 引擎的数值转换

SELECT 
CAST(
    CAST('913' AS DOUBLE) / CAST('1000' AS DOUBLE) * cast('100' as DOUBLE) as DECIMAL(10,2)
);

先转成 double,在进行乘除运算,最后再转成 decimal
==> 91.3

列别名一定要用"双引号"

10. if 语句

if null 判断语句

函数：
1. coalesce( value1,value2,… )
2. if( value1 is null, value2, value1)
IF( Test Condition, True Value, False Value ) 

例子：
hive> select coalesce(col1, col2, cols) as res1, if(col1 is null, if(col2 is null, col3, col2), col1) as res2
    > from(
    >     select 1 as col1, 2 as col2, 3 as col3 
    >     union all
    >     select null as col1, 2 as col2, 3 as col3 
    >     union all
    >     select null as col1, null as col2, 3 as col3 
    >     union all 
    >     select null as col1, null as col2, null as col3
    > ) as test

结果：
    res1   res2
    1      1
    2      2
    3      3
    null   null

11. 一对多表关联去重统计

背景：假设我们是一个电商网站，用用户下单，订单有来源数据，每个订单会有多个订单明细，订单明细记录了商品相关信息、以及商品的出库、发货、收货时间都不同。

需求：想要统计订单维度相关的数据，要求统计出订单不同来源下的出库单量、发货单量、收货单量。

分析：订单和订单明细是一对多的关系，一个订单有多个明细，每个明细的商品都有各自的出库、发货、收货时间，也有可能未及时发货、出库、收货之类的导致为空，想要按照订单维度统计这些量，只需要判断订单中对应的多个明细中，只有出库、发货。收货时间即可，然后再汇总。

实现：

先统计基础的数据，订单与订单明细关联，查询订单id、订单来源、以及根据出库时间、发货时间、收货时间是否存在来判断是否出库、是否发货、是否收货；
根据订单来源、订单id进行分组，然后利用单行函数 max 取出具体id分组下的是否出库、发货、收货值；
最后在统计上一步查的数据，再按照订单来源分组，利用单行函数 sum 来求订单总数、出库、发货、收货总单量。

此时就需要使用 group by 进行分组，同时，分组完毕之后，在 select 列上需要针对未分组的字段进行单行函数，比如 max、min、sum等等。

with base_data as (
	select
		source, -- 下单来源
		max(if_out) if_out, -- 是否出库
		max(if_send) if_send, -- 是否发货
		max(if_received) if_received -- 是否收货
	from (
		select
			o.id, -- 订单id
			o.source, -- 来源
			(
				case when od.out_time is not null then 1
				else 0
				end
			) as if_out, -- 是否出库
			(
				case when od.send_time is not null then 1
				else 0
				end
			) as if_send, -- 是否发货
			(
				case when od.received_time is not null then 1
				else 0
				end
			) as if_received -- 是否收货
		from order o
		left join order_detail od on o.id = od.order_id
		where o.create_time > '2021-04-01'
	) a 
	group by source,id
)
select
	source, -- 下单来源
	sum(1), -- 订单数量
	sum(if_out), -- 出库总单量
	sum(if_send), -- 发货总单量
	sum(if_received) -- 收货总单量
from base_data
group by source

其实就是一个订单包含多个商品数据，每个商品的三种不同的时间都不同，想要算按照整个订单来统计三个时间，只需要取订单对应的多个商品数据，取它们的最小或最大时间来进行分组统计。

注意 distinct 只是针对整行数据去重，如果查询结果有多列，比如 user_id、name、age、order_id 等等，查询的数据中 user_id、name、age 都是一样的，但是 order_id 有多个，那么使用 distinct 只会去除四个字段相同的行数据。

到此，相信大家对“Hive有哪些常见函数”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！