GaussDB SQL基本语法示例:CASE表达式深度解析
GaussDB SQL基本语法示例:CASE表达式深度解析
一、CASE表达式核心价值
1.1 条件逻辑的SQL化实现
数据分类:将数值/文本字段映射为业务标签(如订单状态转换)
动态计算:根据条件分支执行不同计算逻辑(如阶梯价格计算)
数据清洗:统一异常值编码(如将NULL转换为"N/A")
1.2 典型应用场景
场景 实现方式 性能特征
报表字段映射 将数字代码转为业务名称 毫秒级响应
数据脱敏 敏感字段动态掩码 低开销
业务规则引擎 嵌入复杂业务逻辑 支持百万级处理
二、CASE语法全解析
- 基础语法结构
-- 简单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
- 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;
三、实战应用示例
- 数据分类转换
-- 订单状态机转换
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;
- 动态计算逻辑
-- 阶梯价格计算
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;
- 数据清洗与转换
-- 异常值标准化处理
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;
四、高级应用技巧
- 结合窗口函数
-- 分区排名计算
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;
- 在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;
五、性能优化指南
- 执行计划对比
-- 创建测试表
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';
- 优化策略
优化方向 具体措施 效果提升
索引优化 对CASE条件字段建立组合索引 查询速度提升3-5倍
条件顺序调整 将高选择性条件前置 减少扫描行数
避免函数嵌套 预计算CASE表达式结果 CPU消耗降低40%
六、避坑指南
- 执行顺序陷阱
-- 错误示例: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;
- 数据类型转换
-- 错误示例:隐式类型转换失败
SELECT
CASE
WHEN text_column = 123 THEN '数字'
ELSE '非数字'
END
FROM mixed_data;
-- 正确做法:显式类型检查
SELECT
CASE
WHEN text_column ~ '^\d+$' THEN '数字'
ELSE '非数字'
END
FROM mixed_data;