hiveSQL中有大量的函数,这里挑选了几个在日常工作中经常用到的:
case when
CASE WHEN ... ELSE ...END
使
用场景:
- 1.通过已知的数据,按照另外的分组方式进行统计
select
case when city in ('北京','上海','广州','深圳') then '一线城市'
when city in ('杭州','苏州') then '二线城市'
else '其他' end as city_level,
sum(salary)
from table
2.行转列
例:已知小说埋点表,用户触发一次埋点生成一条记录,需要记录不同个埋点的pv/uv
select
t1.dt,
case when mn ='首页曝光' then count(uuid) else NULL end AS index_show_pv,
case when mn ='详情页' then count(uuid) else NULL end AS details_pv,
case when mn ='阅读页' then count(uuid) else NULL end AS read_show_pv,
case when mn ='小说标签点击' then count(uuid) else NULL end AS novel_tab_pv,
case when mn ='首页免费小说' then count(uuid) else NULL end AS index_free_novel_pv,
case when mn ='首页曝光' then count(distinct uuid) else NULL end AS index_show_uv,
case when mn ='详情页' then count(distinct uuid) else NULL end AS details_uv,
case when mn ='阅读页' then count(distinct uuid) else NULL end AS read_show_uv,
case when mn ='小说标签点击' then count(distinct uuid) else NULL end AS novel_tab_uv,
case when mn ='首页免费小说' then count(distinct uuid) else NULL end AS index_free_novel_uv
from novel_table
concat()
1.concat()
concat(STRING|BINARY a, STRING|BINARY b...)
使用场景:常用于拼接字段或字符串
注意:可以拼接多个字段或字符串,但是其中有任何一个字段为NULL的话返回NULL
2.concat_ws()
concat_ws(STRING sep, STRING a, STRING b...)
使用场景:通过指定的链接符号,连接多个字段或字符串
例:
select concat_ws(':','姓名','小明')
-- >>> 姓名:小明
substr()
substr(column,0,6)
使用场景:截取字段,截取前N个字符串
例:提取7月整月数据
select * from table where substr(dt,0,6) = '202007'
datediff()
datediff(enddate,statdate)
使用场景:计算两个时间的时间差
例:计算用户下单时间与登记时间的天数差
SELECT datediff('2020-07-31','2020-07-20')
-- >>>11
注意:该函数只支持'yyyy-MM-dd'或'yyyy-MM-dd HH:mm:ss'格式的日期,需要对日期字段进行处理
from_unixtime()
from_unixtime(BIGINT unixtime [, STRING format])
使用场景:
时间戳转日期,自定义日期格式
例:数据表中存的日期为时间戳,需要转化为yyyy-MM-dd格式
select from_unixtime(1596620215,'yyyy-MM-dd') publish_date
-- >>> 2020-08-05
row_number() over()
row_number() over(partition by col1 order by col2)
使用场景:
1.分组排序
例:将不同部门的员工薪资按照高到低进行排序
select
deptid,
empid,
salary,
row_number() over(partition by deptid order by salary desc) as rank
from table
2.分组去重
例:根据多个字段进行数据去重
select t1.col1,
t1.col2,
t1.rank
from (
select
col1,
col2,
row_number() over(partition col1,col2 order by col3) as rank
from table) t1
where t1.rank=1