DuckDB教程：条件表达式

DuckDB 条件表达式CASE可以基于不同条件产生不同的结果实现类似于编程语言中的 IF-THEN-ELSE 逻辑处理功能。例如根据员工的 KPI 计算相应的涨薪幅度根据学生考试成绩评出优秀、良好、及格等。DuckDB 支持两种形式的条件表达式简单 CASE 表达式和搜索 CASE 表达式。另外它还提供了两个简单的函数IF() 与 SWITCH()。简单条件表达式简单 CASE 表达式的语法如下CASEexpressionWHENvalue1THENresult1WHENvalue2THENresult2...[ELSEdefault]END以上语句执行时首先计算 expression 的值然后和第 1 个 WHEN 子句中的数据value1进行比较如果相等则返回对应 THEN 子句中的结果result1如果不相等则继续和第 2 个 WHEN 子句中的数据value2进行比较如果相等则返回对应 THEN 子句中的结果result2依此类推如果没有找到相等的数据返回 ELSE 子句中的默认结果default如果没有指定 ELSE 子句返回空值。简单条件达式的计算过程如下图所示。例如以下查询使用简单 CASE 表达式将员工的部门编号转换为相应的名称SELECTemp_name,CASEdept_idWHEN1THEN行政管理部WHEN2THEN人力资源部WHEN3THEN财务部WHEN4THEN研发部WHEN5THEN销售部WHEN6THEN保卫部ELSE其他部门ENDAS部门名称FROMemployee;┌──────────┬────────────┐ │ emp_name │ 部门名称 │ │varchar│varchar│ ├──────────┼────────────┤ │ 刘备 │ 行政管理部 │ │ 关羽 │ 行政管理部 │ │ 张飞 │ 行政管理部 │...│ 邓芝 │ 销售部 │ │ 简雍 │ 销售部 │ │ 孙乾 │ 销售部 │ └──────────┴────────────┘25rows2columns简单 CASE 表达式在进行条件判断时使用的是等值比较只能处理简单的比较逻辑。如果想要实现复杂的逻辑处理例如根据学生考试成绩范围评出优秀、良好等需要使用更加强大的搜索 CASE 表达式。搜索条件表达式搜索 CASE 表达式的语法如下CASEWHENcondition1THENresult1WHENcondition2THENresult2...[ELSEdefault]END以上语句执行时首先判断第 1 个 WHEN 子句中的条件condition1是否成立如果成立则返回对应 THEN 子句中的结果result1如果不成立则继续判断第 2 个 WHEN 子句中的条件condition2是否成立如果成立则返回对应 THEN 子句中的结果result2依此类推如果没有任何条件成立返回 ELSE 子句中的默认结果default如果没有指定 ELSE 子句返回空值。搜索CASE达式的计算过程如下图所示。前文中的简单 CASE 表达式示例可以使用等价的搜索 CASE 表达式实现如下SELECTemp_nameAS员工姓名,emp_idAS部门编号,CASEWHENdept_id1THEN行政管理部WHENdept_id2THEN人力资源部WHENdept_id3THEN财务部WHENdept_id4THEN研发部WHENdept_id5THEN销售部WHENdept_id6THEN保卫部ELSE其他部门ENDAS部门名称FROMemployee;搜索 CASE 表达式中的判断条件可以像 WHERE 子句中的过滤条件一样复杂。例如以下查询基于员工的月薪将他们的收入分为“高”、“中”、“低”三个级别SELECTemp_nameAS员工姓名,salaryAS月薪,CASEWHENsalary10000THEN低收入WHENsalary20000THEN中收入ELSE高收入ENDAS收入级别FROMemployee;┌──────────┬──────────────┬──────────┐ │ 员工姓名 │ 月薪 │ 收入级别 │ │varchar│decimal(8,2)│varchar│ ├──────────┼──────────────┼──────────┤ │ 刘备 │30000.00│ 高收入 │ │ 关羽 │26000.00│ 高收入 │ │ 张飞 │24000.00│ 高收入 │...│ 邓芝 │4000.00│ 低收入 │ │ 简雍 │4800.00│ 低收入 │ │ 孙乾 │4700.00│ 低收入 │ └──────────┴──────────────┴──────────┘25rows3columns月薪小于 10000 时返回“低收入”否则如果月薪小于 20000大于等于 10000返回“中收入”否则月薪大于等于 20000返回“高收入”。CASE 表达式也可以在其他子句中使用包括 WHERE、ORDER BY 等子句。例如以下语句使用 CASE 表达式实现了空值的自定义排序SELECTemp_nameAS员工姓名,CASEWHENbonusISNULLTHEN0ELSEbonusENDAS奖金FROMemployeeWHEREdept_id2ORDERBYCASEWHENbonusISNULLTHEN0ELSEbonusEND;┌──────────┬───────────────┐ │ 员工姓名 │ 奖金 │ │varchar│decimal(12,2)│ ├──────────┼───────────────┤ │ 黄忠 │0.00│ │ 魏延 │0.00│ │ 诸葛亮 │8000.00│ └──────────┴───────────────┘其中ORDER BY 子句中的 CASE 表达式将 bonus 为空的数据转换为 0从而实现了空值排在最前的效果。IF 函数DuckDB 还提供了一个 IF(cond, a, b) 函数可以实现单个条件的 CASE 表达式的功能。如果 cond 成立不等于 0 或者不为空值返回 a 的值否则返回 b 的值。例如SELECTIF(12,12,12)ASresult;┌─────────┐ │ result │ │varchar│ ├─────────┤ │12│ └─────────┘SWITCH 函数SWITCH(expr, MAP, [defualt]) 函数实际上是一种简单 CASE 表达式的语法糖。例如前文中的简单 CASE 表达式示例可以使用SWITCH() 函数实现如下SELECTemp_name,SWITCH(dept_id,MAP {1:行政管理部,2:人力资源部,3:财务部,4:研发部,5:销售部,6:保卫部},其他部门)AS部门名称FROMemployee;案例分析假如公司即将成立 20 周年打算给全体员工发放一个周年庆礼品。发放礼品的规则如下截至 2020 年入职年限不满 10 年的员工男性员工的礼品为手表一块女性员工的礼品为化妆品一套截至 2020 年入职年限满 10 年不满 15 年的员工男性员工的礼品为手机一部女性员工的礼品为项链一条截至 2020 入职年限满 15 年的员工不论男女礼品统一为电脑一台。现在人事部门需要知道为每位员工发放什么礼品如何通过查询语句得到这些信息搜索 CASE 表达式非常合适这类逻辑条件的处理我们可以使用以下语句SELECTemp_nameAS员工姓名,hire_dateAS入职日期,CASEWHENEXTRACT(YEARFROMhire_date)2011ANDsex男THEN手表WHENEXTRACT(YEARFROMhire_date)2011ANDsex女THEN化妆品WHENEXTRACT(YEARFROMhire_date)2006ANDsex男THEN手机WHENEXTRACT(YEARFROMhire_date)2006ANDsex女THEN项链ELSE电脑ENDAS礼品FROMemployee;┌──────────┬────────────┬─────────┐ │ 员工姓名 │ 入职日期 │ 礼品 │ │varchar│date│varchar│ ├──────────┼────────────┼─────────┤ │ 刘备 │2000-01-01│ 电脑 │ │ 关羽 │2000-01-01│ 电脑 │ │ 张飞 │2000-01-01│ 电脑 │...│ 邓芝 │2018-11-11│ 手表 │ │ 简雍 │2019-05-11│ 手表 │ │ 孙乾 │2018-10-09│ 手表 │ └──────────┴────────────┴─────────┘25rows3columns除了搜索 CASE 表达式之外我们还使用了 EXTRACT 函数提取员工的入职年份。