sql - Hive 查询 CASE WHEN 状态

Question

我想使用 Hive 和 CASE 和 WHEN 编写查询。

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;

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

标签： sqlhivecasehiveql

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;