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