GaussDB SQL基本语法示例：CASE表达式深度解析

GaussDB SQL基本语法示例：CASE表达式深度解析

一、CASE表达式核心价值

1.1 条件逻辑的SQL化实现
​​数据分类​​：将数值/文本字段映射为业务标签（如订单状态转换）
​​动态计算​​：根据条件分支执行不同计算逻辑（如阶梯价格计算）
​​数据清洗​​：统一异常值编码（如将NULL转换为"N/A"）
1.2 典型应用场景
场景 实现方式 性能特征
报表字段映射 将数字代码转为业务名称 毫秒级响应
数据脱敏 敏感字段动态掩码 低开销
业务规则引擎 嵌入复杂业务逻辑 支持百万级处理

二、CASE语法全解析

1. 基础语法结构

-- 简单CASE（等值匹配）
CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  [ELSE default_result]
END

-- 搜索CASE（条件判断）
CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  [ELSE default_result]
END

2. GaussDB特有功能

-- 结合聚合函数的CASE
SELECT 
  product_category,
  COUNT(*) AS total,
  SUM(CASE WHEN price > 1000 THEN 1 ELSE 0 END) AS premium_count
FROM products
GROUP BY product_category;

-- 嵌套CASE实现多级分类
SELECT 
  user_id,
  CASE 
    WHEN age < 18 THEN '未成年人'
    WHEN age BETWEEN 18 AND 60 THEN 
      CASE WHEN vip_level > 2 THEN '高级会员' ELSE '普通会员' END
    ELSE '老年人'
  END AS user_group
FROM users;

三、实战应用示例

1. 数据分类转换

-- 订单状态机转换
SELECT 
  order_id,
  order_status,
  CASE order_status
    WHEN 'CREATED' THEN '待支付'
    WHEN 'PAID' THEN '已付款'
    WHEN 'SHIPPED' THEN '配送中'
    ELSE '已完成'
  END AS status_cn
FROM orders;

-- 文本脱敏处理
SELECT 
  user_id,
  CASE 
    WHEN phone LIKE '138%' THEN '138****' || SUBSTRING(phone FROM 8)
    ELSE phone
  END AS masked_phone
FROM customers;

2. 动态计算逻辑

-- 阶梯价格计算
SELECT 
  product_id,
  quantity,
  CASE 
    WHEN quantity >= 100 THEN unit_price * 0.8
    WHEN quantity >= 50 THEN unit_price * 0.9
    ELSE unit_price
  END AS final_price
FROM order_items;

-- 绩效评级计算
SELECT 
  employee_id,
  sales_amount,
  CASE 
    WHEN sales_amount > 500000 THEN 'S级'
    WHEN sales_amount > 300000 THEN 'A级'
    WHEN sales_amount > 100000 THEN 'B级'
    ELSE 'C级'
  END AS performance_grade
FROM sales_staff;

3. 数据清洗与转换

-- 异常值标准化处理
SELECT 
  sensor_id,
  reading,
  CASE 
    WHEN reading < 0 THEN NULL  -- 无效值转NULL
    WHEN reading > 100 THEN 100  -- 超限值截断
    ELSE reading
  END AS valid_reading
FROM sensor_data;

-- 数据类型转换增强
SELECT 
  log_entry,
  CASE 
    WHEN log_type = 'ERROR' THEN '严重错误'
    WHEN log_type = 'WARN' THEN '警告'
    ELSE '信息'
  END AS log_level
FROM system_logs;

四、高级应用技巧

1. 结合窗口函数

-- 分区排名计算
SELECT 
  department_id,
  employee_id,
  salary,
  CASE 
    WHEN RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) <= 3 
    THEN 'TOP3' 
    ELSE '常规' 
  END AS salary_rank
FROM employees;

-- 累积计算增强
SELECT 
  order_date,
  product_id,
  CASE 
    WHEN SUM(quantity) OVER (PARTITION BY product_id ORDER BY order_date) 
         > 1000 THEN '热销' 
    ELSE '常规' 
  END AS sales_status
FROM order_details;

2. 在UPDATE/DELETE中使用

-- 批量数据修正
UPDATE product_catalog
SET price_category = 
  CASE 
    WHEN price < 50 THEN '低价'
    WHEN price BETWEEN 50 AND 200 THEN '中价'
    ELSE '高价'
  END;

-- 条件删除数据
DELETE FROM audit_log
WHERE 
  CASE 
    WHEN log_type = 'ACCESS' AND log_time < NOW() - INTERVAL '1 year' THEN TRUE
    WHEN log_type = 'ERROR' AND log_time < NOW() - INTERVAL '6 months' THEN TRUE
    ELSE FALSE
  END;

五、性能优化指南

1. 执行计划对比

-- 创建测试表
CREATE TABLE test_case (
  id SERIAL PRIMARY KEY,
  value INT,
  category VARCHAR
);

-- 插入测试数据
INSERT INTO test_case (value, category)
SELECT floor(random()*100), 
       CASE WHEN random() < 0.3 THEN 'A' 
            WHEN random() < 0.6 THEN 'B' 
            ELSE 'C' END
FROM generate_series(1, 1000000);

-- 分析简单CASE性能
EXPLAIN ANALYZE
SELECT * FROM test_case
WHERE category = CASE WHEN value > 50 THEN 'B' ELSE 'A' END;

-- 分析搜索CASE性能
EXPLAIN ANALYZE
SELECT * FROM test_case
WHERE CASE WHEN value > 50 THEN value::TEXT ELSE 'N/A' END = '50';

2. 优化策略
   优化方向 具体措施 效果提升
   索引优化 对CASE条件字段建立组合索引 查询速度提升3-5倍
   条件顺序调整 将高选择性条件前置 减少扫描行数
   避免函数嵌套 预计算CASE表达式结果 CPU消耗降低40%

六、避坑指南

1. 执行顺序陷阱

-- 错误示例：ELSE优先级问题
SELECT 
  CASE 
    WHEN id = 1 THEN 'A'
    WHEN id = 1 OR id = 2 THEN 'B' 
    ELSE 'C' 
  END
FROM test_case;

-- 正确做法：明确条件范围
SELECT 
  CASE 
    WHEN id = 1 THEN 'A'
    WHEN id = 2 THEN 'B' 
    ELSE 'C' 
  END
FROM test_case;

2. 数据类型转换

-- 错误示例：隐式类型转换失败
SELECT 
  CASE 
    WHEN text_column = 123 THEN '数字' 
    ELSE '非数字' 
  END
FROM mixed_data;

-- 正确做法：显式类型检查
SELECT 
  CASE 
    WHEN text_column ~ '^\d+$' THEN '数字' 
    ELSE '非数字' 
  END
FROM mixed_data;