条件表达式

CASE

标准的SQL CASE表达式有两种模式。

• “简单模式”从左向右查找表达式的每个value，直到找出相等的expression：

  CASE expression

  WHEN value THEN result

  [ WHEN ... ]

  [ ELSE result ]

  END

  返回匹配value的result。如果没有匹配到任何值，则返回ELSE子句的result；如果没有ELSE子句，则返回空。示例：

  select a,
  case a
   when 1 then 'one'
   when 2 then 'two'
   else 'many' end from
   (values (1),(2),(3),(4)) as t(a);
   a | _col1 
  ---|-------
   1 | one   
   2 | two   
   3 | many  
   4 | many  
  (4 rows)

• “查找模式”从左向右判断每个condition的布尔值，直到判断为真，返回匹配result：

  CASE

  WHEN condition THEN result

  [ WHEN ... ]

  [ ELSE result ] END

  如果判断条件都不成立，则返回ELSE子句的result；如果没有ELSE子句，则返回空。示例：

  select a,b,
  case
  when a=1 then 'one'
  when b=2 then 'tow'
  else 'many' end from (values (1,2),(3,4),(1,3),(4,2)) as t(a,b);
   a | b | _col2 
  ---|---|-------
   1 | 2 | one   
   3 | 4 | many  
   1 | 3 | one   
   4 | 2 | tow   
  (4 rows)

IF

IF函数是语言结构，它与下面的CASE表达式功能相同：

CASE

WHEN condition THEN true_value

[ ELSE false_value ] END

• if(condition, true_value)

  如果condition为真，返回true_value；否则返回NULL，true_value不进行计算。

  select if(a=1,8) from (values (1),(1),(2)) as t(a); -- 8 8 NULL
  select if(a=1,'value') from (values (1),(1),(2)) as t(a); -- value value NULL

• if(condition, true_value, false_value)

  如果condition为真，返回true_value；否则计算并返回false_value 。

  select if(a=1,'on','off') from (values (1),(1),(2)) as t(a); 
  _col0 
  -------
   on    
   on    
   off   
  (3 rows)

COALESCE

coalesce(value[, ...])

返回参数列表中的第一个非空value。与CASE表达式相似，仅在必要时计算参数。

可类比MySQL的nvl功能，经常用于转空值为0或者' '（空字符）。

 select coalesce(a,0) from (values (2),(3),(null)) as t(a); -- 2 3 0

NULLIF

• nullif(value1, value2)

  如果value1与value2相等，返回NULL；否则返回value1 。

  select nullif(a,b) from (values (1,1),(1,2)) as t(a,b); --
   _col0 
  -------
    NULL 
       1 
  (2 rows)

• ZEROIFNULL(value)

  如果value为null，返回0，否则返回原值。目前支持数值类型还有varchar类型。

  select zeroifnull(a),zeroifnull(b),zeroifnull(c) from (values (null,13.11,bigint '157'),(88,null,bigint '188'),(55,14.11,null)) as t(a,b,c);
   _col0 | _col1 | _col2 
  -------|-------|-------
       0 | 13.11 |   157 
      88 |  0.00 |   188 
      55 | 14.11 |     0 
  (3 rows)

• NVL(value1,value2)

  如果value1为NULL，返回value2，否则，返回value1。

  select nvl(NULL,3);  -- 3
  select nvl(2,3);    --2

• ISNULL(value)

  如果value1为NULL，返回true，否则返回false。

  Create table nulltest(col1 int,col2 int);
  insert into nulltest values(null,3);
  select isnull(col1),isnull(col2) from nulltest;
   _col0 | _col1 
  -------|-------
   true  | false 
  (1 row)

• ISNOTNULL(value)
  如果value1为NULL，返回false，否则返回true。

  select isnotnull(col1),isnotnull(col2) from nulltest;
   _col0 | _col1 
  -------|-------
   false | true  
  (1 row)

TRY

评估一个表达式，如果出错，则返回Null。类似于编程语言中的try catch。try函数一般结合COALESCE使用，COALESCE可以将异常的空值转为0或者空，以下情况会被try捕获：

• 分母为0
• 错误的cast操作或者函数入参
• 数字超过了定义长度

不推荐使用，应该明确以上异常，做数据预处理

示例：

假设有以下表，字段origin_zip中包含了一些无效数据：

-- 创建表
create  table shipping (origin_state varchar,origin_zip varchar,packages int ,total_cost int);

-- 插入数据
insert into shipping 
values 
('California','94131',25,100),
('California','P332a',5,72),
('California','94025',0,155),
('New Jersey','08544',225,490);

-- 查询数据
SELECT * FROM shipping;
 origin_state | origin_zip | packages | total_cost
 --------------+------------+----------+------------ 
 California   |      94131 |       25 |        100 
 California   |      P332a |        5 |         72
 California   |      94025 |        0 |        155 
 New Jersey   |      08544 |      225 |        490 
(4 rows) 

不使用Try查询失败：

SELECT CAST(origin_zip AS BIGINT) FROM shipping;
Query failed: Cannot cast 'P332a' to BIGINT 

SELECT TRY(CAST(origin_zip AS BIGINT)) FROM shipping;
 origin_zip
 ------------  
     94131 
     NULL
     94025
     08544
 (4 rows) 

不使用try查询失败：

SELECT total_cost/packages AS per_package FROM shipping;
Query failed: Division by zero 

使用TRY和COALESCE返回默认值：

SELECT COALESCE(TRY(total_cost/packages),0) AS per_package FROM shipping;  
 per_package
 -------------
    4  
    14     
    0      
    19
 (4 rows)