Hive Case When 用法详解

一、基本语法

CASE WHEN语句是Hive中的条件语句之一，通常用于根据给定的条件从多个分支中选择一个返回结果。它的基本语法如下：

SELECT 
    CASE 
        WHEN condition1 THEN result1 
        WHEN condition2 THEN result2 
        WHEN condition3 THEN result3 
        ... 
        ELSE result
    END
FROM table_name

其中，condition是一个布尔表达式，如果成立，就返回对应的结果；如果所有条件均不成立，则返回ELSE中的结果。我们也可以省略掉ELSE关键字，这样如果所有条件均不成立，则返回NULL。

二、常用场景

CASE WHEN语句通常用于数据转换、分类和聚合等场景中。

1. 数据转换

在数据仓库中，我们经常需要将原始数据转换成适合分析和处理的格式。例如，在某个表中，可能存在分数字段，我们需要将分数字段转换成等级字段，可以使用CASE WHEN语句：

SELECT 
    student_name, 
    score, 
    CASE 
        WHEN score >= 90 THEN 'A' 
        WHEN score >= 80 THEN 'B' 
        WHEN score >= 70 THEN 'C' 
        WHEN score >= 60 THEN 'D' 
        ELSE 'F'
    END AS grade
FROM student_scores;

上述代码将根据分数字段(score)生成等级字段(grade)。

2. 分类汇总

CASE WHEN语句还可以用于分类汇总。例如，我们需要查询每个部门中工资最高的员工：

SELECT 
    department_name, 
    MAX(
        CASE 
            WHEN salary >= 5000 THEN salary
            ELSE NULL
        END
    ) AS max_salary
FROM employee 
GROUP BY department_name;

上述代码将根据部门名称分组，然后在每个组中选择工资最高的员工。

3. 复杂条件过滤

如果需要通过多个条件进行数据过滤，就需要使用复杂条件过滤。例如，我们需要查询部门为“Sales”或“Marketing”，且工资大于4000的员工：

SELECT 
    employee_name, 
    department_name,
    salary 
FROM employee 
WHERE 
    (department_name = 'Sales' OR department_name = 'Marketing') 
    AND salary > 4000;

上述代码将返回符合条件的员工列表。

三、高级用法

1、多级CASE WHEN语句

在某些情况下，我们可能需要通过多级嵌套CASE WHEN语句实现更复杂的条件逻辑。例如，以下代码将对客户进行分组，根据购买总额生成客户等级，并给客户打上颜色标签：

SELECT 
    customer_name,
    SUM(total_amount) as total_purchase_amount,
    CASE 
        WHEN SUM(total_amount) >= 10000 THEN 'VIP'
        WHEN SUM(total_amount) >= 5000 THEN 'Gold'
        WHEN SUM(total_amount) >= 1000 THEN 'Silver'
        ELSE 'Bronze'
    END AS customer_level,
    CASE 
        WHEN customer_level = 'VIP' THEN 'VIP'
        WHEN customer_level = 'Gold' THEN 'Gold'
        WHEN customer_level = 'Silver' THEN 'Silver'
        ELSE 'Bronze'
    END AS label
FROM 
    order 
GROUP BY 
    customer_name;

上述代码将客户按照等级进行分组，并且为客户的等级打上颜色标签。

2、CASE WHEN和窗口函数

在某些情况下，我们需要使用窗口函数计算汇总值，并在CASE WHEN语句中进行条件过滤。例如，以下代码将使用窗口函数计算每个部门的平均工资，并基于平均工资进行部门分级：

SELECT 
    department_name,
    salary,
    AVG(salary) OVER (PARTITION BY department_name) AS avg_salary,
    CASE 
        WHEN salary >= 1.5 * avg_salary THEN '高薪'
        WHEN salary >= 1.2 * avg_salary THEN '中薪'
        ELSE '低薪'
    END AS salary_level
FROM 
    employee;

上述代码将计算每个部门的平均工资，并根据每个员工的工资和平均工资的比例对员工进行分级。

3、CASE WHEN嵌套

对于较复杂的逻辑判断，可以嵌套多个CASE WHEN语句。例如，以下代码将对销售量进行分类并在每个分类中计算产品的销售总额、利润和毛利率：

SELECT 
    category_name, 
    product_name,
    sum(quantity) as total_quantity, 
    sum(quantity * price) as total_sales, 
    sum(quantity * profit) as total_profit,
    CASE 
        WHEN sum(quantity) < 10 THEN '少量销售'
        WHEN sum(quantity) < 100 THEN '中量销售'
        ELSE '大量销售'
    END AS sales_level,
    CASE 
        WHEN total_sales = 0 THEN 0 
        ELSE total_profit / total_sales 
    END AS gross_profit_rate
FROM 
    orders 
GROUP BY 
    category_name, 
    product_name;

上述代码将对产品进行分类，并在每个分类中计算销售总量、销售总额、利润和毛利率。

四、总结

本文详细介绍了Hive中的CASE WHEN语句的基本语法和常用场景，并介绍了其高级用法，包括多级嵌套CASE WHEN语句、CASE WHEN和窗口函数以及CASE WHEN嵌套等。CASE WHEN语句是SQL中非常重要的一种条件语句，可以帮助我们实现更复杂的数据分析和转换。