HiveSql-常用语句

拼接排序

select category_id,

      regexp_replace(

        concat_ws(',',  -- 对排序后array拼接

          sort_array(  -- 对数组排序

            collect_list(  -- 竖转横

              concat_ws(':',lpad(cast(rn as string),4,'0'),cast(topic_id as string)) -- 字符串排序需转为同位数

            )

          )

        ),

      '\\d+\:','') -- 替换掉用于排序的拼接 0001:

from topic_recommend_score

where rn >= 1 and rn <= 1000

group by category_id

;

json字符串解析

 get_json_object(page_attr,'$.goods_id')

时间函数

(1) 获取当前时间: from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss')

条件中位数

percentile_approx(if(click_ps_cnt>0 and label=1,click_ps_cnt,null),0.5) 

排名

(1) row_number() over() -- 123 

(2) rank() over()              -- 113

(3) dense_rank() over()   -- 112

(4) ntile(3) over()  -- 1112233(7r), 按数量平均切成3片(每片数量差值不超过1, 默认增加第一切片), 常用归一化 rn/3

字符串split

(1) lateral view explode (split(tagids, ',')) s as tagid  -- hive

(2) cross join unnest (split(tagids, ',')) as s (tagid)  -- presto

判断a字段是否包含b字段的内容

(1) a like concat('%', b, '%')

(2) array_contains(split(a, ' '), b) -- 此方法只适合判断a数组切分后是否有b元素, 如 a='xy', b='x', 此时返回false.