Hive的一些重要函数用法

1、查看hive是否支持某个函数：describe function nvl;

2、ROW_NUMBER() OVER(PARTITION BY 分组字段 ORDER BY 排序字段)：分组排序后，ROW_NUMBER()为返回的记录打上从1开始的编号

select asset_row_id,
       concat(substr(acc_nbr, 1, 3), '*****') as acc_nbr,
       cell_id,
       tel_first_date,
       dw_date,
       latn_id,
       t_flag,
       etl_dt,
       ROW_NUMBER() OVER(PARTITION BY asset_row_id, acc_nbr ORDER BY tel_first_date, dw_date) sn
  from pdm.landing_net_cdr_first_cell a
 where acc_nbr in ('13335750254', '17395753930') limit 10;

3、sum(求和值) over(partition by 分组字段 order by 排序字段)：可用于统计截止各个时间段的累计工单量等……

--各时段累计工单量
select a.c_dept2,
       a.c_dept2_id,
       concat('2020-12-01 ', a.create_time) as create_time,
       sum(a.num) over(partition by a.c_dept2 order by a.create_time) as fina_num
  from (select c_dept2,
               c_dept2_id,
               substr(CREATE_DATE, 12, 2) as create_time,
               count(*) as num
          from pdm.Q_BASIC
         where substr(CREATE_DATE, 1, 10) = '2020-12-01'
         group by c_dept2,
                  c_dept2_id,
                  substr(CREATE_DATE, 12, 2)) a

4、 where not exists();

create table if not exists pdm_pair.evt_isale_i_sale_order_inc_202004 as
select t.*
  from pdm_pair.evt_isale_i_sale_order_inc_01_202004 t
 where not exists (select 1
          from pdm_pair.isale_yuandan_shuandan_order_202004 b
         where t.sale_ord_num = b.c_sale_ord_num);

5、CONCAT相关：

CONCAT(str1,str2,str3,…)：将多个字符串连接成一个字符串，若任何一个参数为NULL，则返回值为NULL。

CONCAT_WS(separator,str1,str2,…)：指定参数之间的分隔符。

GROUP_CONCAT([DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] [,col ...]] [SEPARATOR str_val])：返回一个字符串，该字符串由分组中的值连接组合而成。SEPARATOR是一个字符串值，被用于插入到结果值中，缺省值为逗号(",")，可以通过指定SEPARATOR ""完全移除该分隔字符。例：GROUP_CONCAT(distinct id order by id separator '_')。
有一些hive由于版本问题没有GROUP_CONCAT()函数，可用CONCAT_WS(SEPARATOR ,collect_set(column))代替，但是排序性丧失。collect_set()将分组中的某些列去重转为一个数组返回，与之相似的有collect_list()，但collect_list()不去重。

select province_name,
       concat_ws('/', collect_set(latn_name)) as latn_name,
       area_id
  from sjzx_app.area_code
 where area_id in ('24', '558', '935', '937', '999')
 group by province_name, area_id limit 10;

 6、like：

(1)like '%%'

select count(*) from sjzx_app.dq_channel_view_union_all;
+-------+--+
|  _c0  |
+-------+--+
| 5942  |
+-------+--+

select count(*)
  from sjzx_app.dq_channel_view_union_all a
 where channel_type_lvl2 like '%%';
+-------+--+
|  _c0  |
+-------+--+
| 5893  |
+-------+--+

区别：前者统计所有记录，后者统计channel_type_lvl2不为null的记录。

 (2) like concat('%','模糊查询的值','%') : 有时候hive会将一些特殊字符作转义处理，所以可通过like concat('%','模糊查询的值','%')来实现模糊查询。