Teradata 学习笔记4

 CASE 表达式：

基于值(Valued)的CASE语句
CASE语句允许对返回的数据记录进行条件处理。基于值的CASE表达式的基
本格式为：
CASE value-expr WHEN expr1 THEN result1
WHEN expr2 THEN result2
:
ELSE resultn END

它的特点是：
! 对处理的每一条数据记录都将返回一个单独的结果
! 每一行的值决定于每一个WHEN子句
! 对于某行的处理首先找到第一个匹配的值并返回结果，如果没有匹配的
值，则返回ELSE子句后面的值。
 
例：计算部门401的工资数占整个公司工资总数的比例。
SELECT SUM(
CASE department_number
WHEN 401 THEN salary_amount
ELSE 0
END) / SUM(salary_amount)
FROM employee;

结果如下：
(Sum(<CASE expression>)/Sum(salary_amount))
2.22834717118098E-001
 
如需计算部门401和501的工资总额，可以使用下面的SQL语句：
SELECT CAST (SUM(
CASE department_number
WHEN 401 THEN salary_amount
WHEN 501 THEN salary_amount

我们看到，在基于值的CASE语句中，必须在CASE后面指定一个表达式，对
此表达式列出几种可能的值来进行匹配。如果没有表达式来计算值，可以使用下面
基于搜索的CASE结构。

我们看到，在基于值的CASE语句中，必须在CASE后面指定一个表达式，对
此表达式列出几种可能的值来进行匹配。如果没有表达式来计算值，可以使用下面
基于搜索的CASE结构。

在基于搜索的CASE语句中，没有必要指定一个表达式来进行计算，将计算的
值作为匹配的基础。取而代之的是，可以指定多个、任意的搜索条件，对每一个搜
索条件返回相应的值。对于不匹配的条件处理，可以明确地指定一个ELSE子句，
或利用缺省的ELSE NULL子句。
 
基于搜索的CASE语句格式为：
CASE WHEN condition1 THEN value-expr1
WHEN condition2 THEN value-expr2

ELSE value-expr END

例：
SELECT last_name,
CASE
WHEN salary_amount < 30000
THEN 'Under $30K'
WHEN salary_amount < 40000
THEN 'Under $40K'
WHEN salary_amount < 50000
THEN 'Under $50K'
ELSE
'Over $50K'
END
FROM employee
ORDER BY salary_amount;

返回结果如下：
last_name <CASE expression>
---------- ----------------------------
Phillips Under $30K
Crane Under $30K
Hoover Under $30K
Rabbit Under $30K

再研究一个例子：计算部门401和501占工资总数的比例，允许部门501的员工
的工资都增加10％，则语句为：
SELECT SUM(
CASE
WHEN department_number = 401 THEN salary_amount
WHEN department_number = 501 THEN salary_amount * 1.1
ELSE 0
END) / SUM(CASE WHEN department_number = 501
THEN salary_amount * 1.1
ELSE salary_amount
END) (FORMAT 'Z.99')
AS sal_ratio
FROM employee;
 
返回结果如下：
sal_ratio
.42

NULLIF表达式
NULLIF实际上用来作为CASE语句在某种情况下的缩写，其格式为：
NULLIF ( <expression1> , <expression2> )

! 如果表达式1等于表达式2，则返回NULL
! 如果表达式1不等于表达式2，则返回表达式1的值。

NULLIF等价于：
CASE
WHEN <expression1> = <expression2> THEN NULL
ELSE <expression1>
END

例：
SELECT call_number
,labor_hours (TITLE 'ACTUAL HOURS')
,NULLIF (labor_hours, 0)
(TITLE 'NULLIF ZERO HOURS')
FROM call_employee
ORDER BY labor_hours;

除法中使用NULLIF
在除法表达式中，如果被除数为零，则系统返回divide by zero错误。如果被除
数为NULL，则返回结果也为NULL，但没有错误产生。因此，在除法表达式中，
如果被除数有可能为零值，经常使用NULLIF来避免除零错。
 
例：找出工作描述包含"analyst"项目的每小时收费与每小时成本的比率。

如果没有NULLIF时:
SELECT description
,hourly_billing_rate / hourly_cost_rate
(TITLE 'Billing to Cost Ratio')
FROM job
WHERE description like "%analyst%';
 
产生错误：
Error Message: Division by zero in an expression involving job. hourly_cost_rate.

使用NULLIF时:
SELECT description
,hourly_billing_rate /NULLIF(hourly_cost_rate, 0)
(TITLE 'Billing to Cost Ratio')
FROM job
WHERE description like '%analyst%';
 
结果为：
description Billing to Cost Ratio
Software Analyst 1.29
System Support Analyst ?
System Analyst 1.14

COALESCE(接合)表达式
COALESCE实际上也是CASE语句在某种特殊情况下的宿写。COALESCE将返
回第一个非NULL表达式的值。其格式为：
COALESCE ( <expression1> , <expression2> [, <expressionX> ] )
 
等价于：
CASE
WHEN <expression1> IS NOT NULL THEN <expression1>
WHEN <expression2> IS NOT NULL THEN <expression2 >
...
WHEN <expressionX> IS NOT NULL THEN <expressionX>
ELSE NULL
END

例：从phone_table表中，列出姓名和电话号码，如果办公室电话存在则列出办
公室电话，否则列出家里电话。
SELECT name
,COALESCE (office_phone, home_phone)
FROM phone_table;
 
例：转换可能的NULL值为零：
SELECT course_name
,COALESCE (num_students,0)

(TITLE '# Students')
FROM class_schedule;
 
结果如下：
course_name # Students
Teradata SQL 17
Physical DB Design 0

例：转换可能的NULL值，用NULL VALUE来表示。
SELECT course_name
,COALESCE (num_students, 'NULL VALUE')
(TITLE '# Students')
FROM class_schedule;
 
返回结果如下：
course_name # Students
Teradata SQL 17
Physical DB Design NULL VALUE

例：COALESCE和NULLIF在聚合计算中的使用

使用下面的SQL语句：
SELECT
AVG (labor_hours) (TITLE 'DEFAULT//AVG')
,AVG (NULLIF ( labor_hours, 0)) (TITLE 'NIZ//AVG')
,AVG (COALESCE ( labor_hours, 0)) (TITLE 'ZIN//AVG')
(FORMAT 'zz,zzz.z')
,COUNT ( labor-hours) (TITLE 'DEFAULT//COUNT') (FORMAT 'zz9')
,COUNT (NULLIF ( labor_hours, 0)) (TITLE 'NIZ//COUNT')
(FORMAT 'zz9')
,COUNT (COALESCE ( labor_hours, 0)) (TITLE 'ZIN//COUNT')
(FORMAT 'zz9')
FROM call_employee;