sql执行计划 unkown error

我执行sql explain WITH dim_pre AS
(SELECT uid,
mmr_end_game
FROM
(SELECT uid,
mmr_end_game,
row_number() over(partition BY uid
ORDER BY record_time DESC) ra
FROM dwd_battle_end_view
WHERE env=’[“outnet-test”]’
AND date >= ‘2021-12-01’
AND date <= ‘2021-12-02’ ) tmp
WHERE ra =1),
dim_tags AS
(SELECT split(concat_ws(’-’,percentile_approx(mmr_end_game,0.99), percentile_approx(mmr_end_game,0.95), percentile_approx(mmr_end_game,0.8), percentile_approx(mmr_end_game,0.5), min(mmr_end_game)),’-’) AS levels
FROM dim_pre),
res_u AS
(SELECT uid,
mmr_end_game,
CASE
WHEN mmr_end_game>=levels[1] THEN 1
WHEN mmr_end_game>=levels[2] THEN 2
WHEN mmr_end_game>=levels[3] THEN 3
WHEN mmr_end_game>=levels[4] THEN 4
WHEN mmr_end_game>=levels[5] THEN 5
END AS lev
FROM
(SELECT uid,
mmr_end_game,
levels
FROM dim_pre
JOIN dim_tags) tmp3),
res_uid as (select cast(uid as int) as uid from res_u where lev=3),
hero_s AS
(SELECT uid,
game_type,
hero_plan,
hero_choose,
player_rank_game
FROM dwd_battle_end_view
WHERE 1=1
AND date >= ‘2021-12-01’
AND date <= ‘2021-12-02’
AND room_type >= 1
AND game_type >= 1
AND env=’[“outnet-test”]’
AND uid IN
(SELECT uid
FROM res_uid where 1=1
)),
hero_do AS
(SELECT game_type,
split(hero_plan,’;’) AS arr,
hero_choose,
player_rank_game
FROM hero_s),
hero_choose_cnts AS
(SELECT hero_choose,
count(1) AS hero_choose_cnts,
sum(CASE
WHEN game_type=1
AND player_rank_game<=4 THEN 1
WHEN game_type=2
AND player_rank_game<=2 THEN 1
ELSE 0
END) AS win_nums
FROM hero_do
GROUP BY hero_choose),
hero_do2 AS
(SELECT split(arr[1],’:’)[2] AS hero_plan,
hero_choose,
player_rank_game
FROM hero_do
UNION ALL SELECT split(arr[2],’:’)[2] AS hero_plan,
hero_choose,
player_rank_game
FROM hero_do
UNION ALL SELECT split(arr[3],’:’)[2] AS hero_plan,
hero_choose,
player_rank_game
FROM hero_do),
hero_plan_s AS
(SELECT hero_plan,
count(1) AS hero_plan_cnts
FROM hero_do2
GROUP BY hero_plan),
hero_choose_ratio AS
(SELECT hero_plan,
hero_plan_cnts,
win_nums,
hero_choose_cnts
FROM hero_plan_s hp
JOIN hero_choose_cnts hc ON hp.hero_plan=hc.hero_choose),
hero_rank AS
(SELECT hero_choose,
round(avg(player_rank_game)) AS avg_rank,
sum(CASE
WHEN player_rank_game=1 THEN 1
ELSE 0
END) AS rank1,
sum(CASE
WHEN player_rank_game=2 THEN 1
ELSE 0
END) AS rank2,
sum(CASE
WHEN player_rank_game=3 THEN 1
ELSE 0
END) AS rank3,
sum(CASE
WHEN player_rank_game=4 THEN 1
ELSE 0
END) AS rank4,
sum(CASE
WHEN player_rank_game=5 THEN 1
ELSE 0
END) AS rank5,
sum(CASE
WHEN player_rank_game=6 THEN 1
ELSE 0
END) AS rank6,
sum(CASE
WHEN player_rank_game=7 THEN 1
ELSE 0
END) AS rank7,
sum(CASE
WHEN player_rank_game=8 THEN 1
ELSE 0
END) AS rank8
FROM hero_do
GROUP BY hero_choose),
res AS
(SELECT *,
round((hero_choose_cnts/hero_plan_cnts),2) AS ratio
FROM hero_choose_ratio hc
JOIN hero_rank hr ON hc.hero_plan=hr.hero_choose)
SELECT *
FROM res
LIMIT 4;

最后的两张表单独都能拿出来 ,然后join 就有问题了。 问题来自于这个uid 的子查询 in 的操作,或者换成inner join的写法 也会报错,查看执行计划 也报 未知错误

可以发一下FE的warn日志么,UNKNOW ERROR一般是有抛异常的