一、基本语法
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中非常重要的一种条件语句,可以帮助我们实现更复杂的数据分析和转换。