配置单元查询CASE WHEN状态

Question

我想使用带有CASE和WHEN的hive编写查询。

SELECT CASE WHEN home_score > away_score THEN away_team 
WHEN home_score < away_score THEN home_team  
END AS year_group, 
COUNT(1) AS count  
FROM matches 
GROUP BY year_group 
ORDER BY count desc 
limit 5;

但这在蜂箱里不起作用。请帮我解决这个问题。

Answer 1

将CASE添加到GROUP BY：

SELECT CASE WHEN home_score > away_score THEN away_team 
WHEN home_score < away_score THEN home_team  
END AS year_group, 
COUNT(1) AS cnt
FROM matches 
GROUP BY CASE WHEN home_score > away_score THEN away_team 
WHEN home_score < away_score THEN home_team  
END
ORDER BY cnt desc 
limit 5;