hive特殊字符的替换

hive特殊字符的替换

1.去掉换行符，tab等特殊字符

--去掉换行符，tab等特殊字符
select regexp_replace(businessscope,'\\n|\\r|\\t|,','');

--查询是否包含某个字段
select locate("(上海)信息技术", '1(上海)信息技术2');

--查询是否包含某个字段
select INSTR('aaba','b'); 

-- 把json转换成map
FROM_JSON(flaw_detail,'map<string, string>') 
--获取map所有的key
map_keys(FROM_JSON(flaw_detail,'map<string, string>'))

2.判断有无中文和全是数字

--有无中文
select if(length(regexp_extract(province,'([\\\u4E00-\\\u9FA5]+)',1))=0,province,null) as province

--是否全是数字
SELECT '123abc456' rlike '^\\d+$';

3.去掉任何字符

select regexp_replace('$%^&*!@(djaioo奥利给，^&*!。.,','[[^0-9a-zA-Z\\\u4e00-\\\u9fa5]]','');

##去掉任何数值
select regexp_replace('09da奥里给22','[0-9]','');

4.从字符串中提取数字

select regexp_replace('1299奥利给99giao123nidi666!','(\\D+)','')

5.hive拆分json数组

-- 1.先替换切割字符串
-- 2.替换[]
-- 3.按照替换得物特殊字符串切割，并对数据炸开（炸开后的数据可用where做过滤）
SELECT operation_log,GET_JSON_OBJECT(aa,'$.time') 
from (
select  
    operation_log
from jiuwu_mrt.ods_trade_jiuwu_jiuwu_sellers_order_attr_ds WHERE dt='20211116'
and seller_order_no='95021111478659953579' 
)
 LATERAL VIEW explode(split(REPLACE(REPLACE(REPLACE(operation_log,'},{','}###{'),'[',''),']',''),'###')) tmp AS aa
 WHERE GET_JSON_OBJECT(aa,'$.type')='retrieve'
 ;

6.hive计算维度

-- FIRST_VALUE: 取分组内排序后，截止到当前行，第一个值
--LAST_VALUE: 取分组内排序后，截止到当前行，最后一个值
select 
  cookieid, 
  createtime, 
  url, 
  row_number() over (partition by cookieid order by createtime) as rn, 
  last_value(url) over (partition by cookieid order by createtime) as last1 
from cookie.cookie4;



1. with cube
-- with cube 对所有的字段分别进行聚合（从0个字段到n个字段）可以实现多个任意维度的查询
-- cube(a,b,c)则首先会对(a,b,c)进行group by ，然后依次是(a,b),(a,c),(a),(b,c),(b),(c),最后在对全表进行group by，他会统计所选列中值的所有组合的聚合，用cube函数就可以完成所有维度的聚合工作


SELECT city_name,city_level , COUNT(*) from 
jiuwu_mrt.dim_city_level GROUP BY city_name,city_level WITH cube  ;


2. with rollup 
-- with rollup 
-- rullup函数是cube的子集,以最左侧维度为主,按照顺序依次进行聚合。
-- 例如聚合的维度为 col1,col2,col3 使用rollup聚合的字段分别为 col1,(col1,col2),(col1,col3),(col1,col2,col3)

SELECT city_name,city_level , COUNT(*) from 
jiuwu_mrt.dim_city_level GROUP BY city_name,city_level WITH ROLLUP  ;



3. grouping sets 
-- grouping sets  
-- 当不需要cube将所有维度都列出来的时候,当只需要部分维度的时候，可以使用grouping sets来进行决定聚合那些维度
-- 例如 grouping sets(col1,(col2,col3)) 只聚合col1维度,(col2,col3)维度 并不会自动聚合(col1,col2,col3)维度

SELECT col1,col2 ,col3, COUNT(*) from 
jiuwu_mrt.dim_city_level GROUP BY col1,col2 ,col3 group setings(col1,(col2,col3))