更新时间:2024-07-02 GMT+08:00
分享

条件表达式

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 
使用Try返回NULL:
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)

相关文档