HQL常用命令

查看分区

show partitions  表名
show partitions  test.test_table
show ROWCOUNT extended test::test1

复杂查询

WITH test_t1 AS (
	SELECT  statis_day, logid, test_id
	FROM test::test_t1
	WHERE statis_day=20210430 AND logid>0
	GROUP BY statis_day, logid, test_id
)
, test_t2 AS (
	SELECT statis_day, logid, item_id, uid
	FROM test::test_t2
	WHERE statis_day=20210430 AND logid>0
	GROUP BY statis_day, logid, item_id, uid
)
, test_t3 AS (
	SELECT test_t1.statis_day, test_t1.logid, test_t2.item_id, 
	    test_t2.uid, test_t1.test_id
	FROM test_t1 JOIN test_t2 
	ON test_t1.statis_day = test_t2.statis_day 
	and test_t1.logid = test_t2.logid
), click_t AS (
	SELECT statis_day, logid, item_id, uid
	FROM test::test_click_t3
	WHERE statis_day=20210430 AND logid>0
	GROUP BY statis_day, logid, item_id, uid
)
select 
    test_t3.statis_day, 
    test_t3.test_id,
    count(distinct test_t3.uid) as expost_uv,
    count(distinct click_t.uid) as click_uv, 
    round(count(distinct click_t.uid)/count(distinct expose_t.uid), 6) AS click_rate
from test_t3 left join click_t on 
        test_t3.statis_day = click_t.statis_day 
    	and test_t3.logid = click_t.logid 
    	and test_t3.item_id = click_t.item_id
group by test_t3.statis_day, test_t3.test_id

列转行

# 通过 GROUP BY  + CONCAT_WS + COLLECT_LIST
CREATE TABLE IF NOT EXISTS user_order (
  uid BIGINT,
  order_id BIGINT
);

SELECT 
	uid,
	CONCAT_WS(',', COLLECT_LIST(order_str)) AS order_list
FROM
(
	SELECT uid , CAST(order_id AS STRING) AS order_str
	FROM user_order
) tmp
GROUP BY uid
;

行转列

movie	category
战狼2	战争,动作,灾难

select movie, category_name
from movie_info 
lateral view explode(split(category,',')) table_tmp as category_name;

取尾号

SELECT uid, substring(uid,-2,1)
FROM test.test1
WHERE statis_day=20201122
LIMIT 10

解析jsion串

get_json_object(statparams,'$.freeStatus') as freeStatus

按指定字符分割字符串

split(qimei, '_')[1]