Hive sql常用函数

--如：select case when age<20 then "20岁以下"

when age>=20 and age<30 then "20~30岁"

when age>=30 and age<40 then "30~40岁"

else "40岁以上" end as age_type,

count (distinct user_id) user_num

from user_info

group by ...;

if(条件表达式，结果1，结果2) ：当条件为真-->结果1，否则结果2

--如：select if (level>5,"高","低") [as level_type] from...

get_json_object(string json_string,string path)

string json_string:列名

string path：用$.key取值

--如：字段： extra1(string): {"systemtype":"ios","education":"master","marriage_status":"1","phone brand":"iphone X"}

--字段： extra2(map<string,string>): {"systemtype":"ios","education":"master","marriage_status":"1","phone brand":"iphone X"}

例如：

SELECT get_json_object(extra1, ‘$.phonebrand‘) as phone_brand,

count(distinct user_id) user_num

FROM user_info

GROUP BY get_json_object(extra1, ‘$.phonebrand‘);

例如：select extra2[‘phonebrand‘] as phone_brand,

count(distinct user_id) user_num

FROM user_info

GROUP BY extra2[‘phonebrand‘];