hive的重要操作

1、trans_array实现

       select 
            b1.item_id,
            b2.product_info
        from(
            select 
                product_id as product_list
            from table_1
            where product_id is not null and length(product_id)>1
        )b1
        LATERAL VIEW EXPLODE(split(b1.product_list, '###')) b2 AS product_info      

    # map类型展开
    select explode(mapcol) as keyname, valuename from tablename;

2、group by 字符串连接操作

select 
        user_id
        ,concat_ws(',', collect_set(leaf_name)) as leaf_names  # 字符串格式
        ,concat_ws(',', collect_list(leaf_name)) as leaf_names # 字符串格式
        
        ,collect_set(leaf_name) as leaf_name_array  # 数组格式
        ,collect_list(leaf_name) as leaf_name_array2  # 数组格式

        ,count(*) as cnt 
from table_1
group by user_id 

主意:collect_set 返回不重复的集合；collect_list返回重复的集合

另外concat_ws也可以不在group by中使用，比如：

select concat_ws(',', 'a', 'b', 'c') from dual
# 输出：a,b,c

concat是用在非goup by 场景，如：

select  concat('a', '\n', 'b', '\t', 'c',  ',',  'd')

#测试
select 
    --replace(info, '\t', '')
    regexp_replace(info, '\t', '')
    ,regexp_replace(info, '(\t|\n|###|  )', '')
from(
    select concat('a', '\n', 'b', '\t', 'c', '\t', '测试###a    c,华尔街') as info
)a

参考： https://blog.csdn.net/changzoe/article/details/81181820

3.1、分位数值计算 PERCENTILE_APPROX
percentile_approx 是一种在 Spark SQL 中使用的近似百分位数计算函数。该函数可以用于计算给定数据集中的某个值所对应的百分位数。
参考：https://juejin.cn/s/percentile_approx 使用

select 
  cate,  
  count(distinct prod_id) as prod_cnt, 
  percentile_approx(price, 0.01) as limit_price_10_percent,  -- 价格的1%分位值
  percentile_approx(price, 0.99) as limit_price_99_percent -- 价格的99%分位值
from table  # 主键 <cate, prod_id>
group by cate

3.2 累计分位数 sum() over(), 比如计算top pv 95%的商品池

(sum(pv) over(order by pv desc))/(sum(pv) over()) as pv_rate

4、map类型数据构造
str_to_map(字符串参数, 分隔符1, 分隔符2)
使用两个分隔符将文本拆分为键值对。

分隔符1将文本分成K-V对，分隔符2分割每个K-V对。对于分隔符1默认分隔符是 ','，对于分隔符2默认分隔符是 ':' . [默认分隔符可以自适应？]

 str_to_map(concat(
                if(t2.id is not null,  'version_1:1.0',  'version_1:0.0'), ',',
                if(t3.id is not null,  'version_2:1.0',, 'version_2:0.0'), ',',
                if(t4.id is not null,  'version_3:1.0',, 'version_3:0.0') )
            ) as is_cheat_extend


select 
    str_to_map( 'version_1:1.0###version_3:6.0', '###', ':')   
as is_cheat_extend;

5、Hive json + json array解析

# 1. json 字符串解析，示例如下
{
  "cate_path": "毛绒布艺类玩具",
  "sku_image_list": [
    "xx1",
    "xx2"
  ],
  "id": "xxx"
}
可以用get_json_object() 直接解析，比如：
 get_json_object(json_str, '$.cate_path') as cate_path,

# 2. json array 字符串解析，示例如下

[{"id":"1234","traceId":"2}]
[{"id":"0000","traceId":1}]

用get_json_object解析：
get_json_object(json_array_str,'$[0].id')  # 解析数组第一个元素中的id字段，即"1234"
get_json_object(json_array_str,'$[1].id')  # 解析数组第二个元素中的id字段，即"0000"

# 3. map类型字段解析，示例如下
{"attr_1_0":"1","attr_1_1":"1","attr_2_0":"1","attr_2_1":"1"}

直接用map['key']即可，如map["attr_1_0"]="1"

6、hive array

1. 计算array元素个数，使用size()函数
2. 计算两个数据的交并差集
  array_except(array1, array2)  # 差集
  array_intersect(array1, array2)  # 交集
  array_union(array1, array2)  # 并集