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中非常重要的一種條件語句，可以幫助我們實現更複雜的數據分析和轉換。