DLA中的条件运算函数_云原生数据湖分析（文档停止维护）-阿里云帮助中心

本文主要介绍DLA中的条件运算函数。

简单CASE表达式

CASE expression
WHEN value THEN result
[ WHEN ... ]
[ ELSE result ]
END

命令说明：从左到右依次查找value，如果找到和expression相等的value则返回对应的result结果；如果未找到相等的value，则返回ELSE语句中result结果。

示例：以下示例将person表中age为10的记录的改为50，其他age均加10。

  select * from hello_mysql_vpc_rds.person
   +------+------+----+
   | id  | name | age |
   +-----+------+-----+
   |    1| james|   10|
   |    2| bond |   20|
   |    3| jack |   30|
   |    4| lucy |   40|
   +------+------+----+
  select *, case age when 10 then 50 else(age+10) end as new_age
    from hello_mysql_vpc_rds.person
   +-----+------+-----+-----+
   | id  | name | age |new_age
   +-----+------+-----+-----+
   |    1| james|   10| 50  |
   |    2| bond |   20|30   |
   |    3| jack |   30|40   |
   |    4| lucy |   40|50   |

高级CASE表达式

CASE
    WHEN condition THEN result
    [ WHEN ... ]
    [ ELSE result ]
END

命令说明：从左到右依次计算condition直到第一个为TRUE的condition，返回对应的result结果。如果没有为True的condition，返回ELSE子句的result结果。

示例：以下示例将person表中age为10的记录改为age+10、name为bond的age改为age+20，其他记录的age均改为age+30。

  select * from hello_mysql_vpc_rds.person
   +------+------+----+
   | id  | name | age |
   +-----+------+-----+
   |    1| james|   10|
   |    2| bond |   20|
   |    3| jack |   30|
   |    4| lucy |   40|
   +------+------+----+
  select *, case when age=10 then (age+10) when name='bond' then (age+20) else (age+30) end from hello_mysql_vpc_rds.person
   +-----+------+-----+-----+
   | id  | name | age |new_age
   +-----+------+-----+-----+
   |    1| james|   10| 20  |
   |    2| bond |   20| 40  |
   |    3| jack |   30| 60  |
   |    4| lucy |   40| 70  |

IF

IF(condition,true_value)

命令说明：如果condition为true，返回true_value；否则返回null。

示例：

  select if((2+3)>4,5);
  +-------+
  | _col0 |
  +-------+
  |     5 |

IF(condition,true_value,false_value)

命令说明：如果condition为true，返回true_value；否则返回false_value。

示例：

  select if((2+3)<5,5，6);
  +-------+
  | _col0 |
  +-------+
  |     6 |

IFNULL

IFNULL(expr1,expr2)

命令说明：如果expr1结果不为null，返回expr1的值；否则返回expr2的值。

示例：

  select ifnull(NULL,2);
  +-------+
  | _col0 |
  +-------+
  |     2 |
  select ifnull(1,0);
  +-------+
  | _col0 |
  +-------+
  |     1 |

NULLIF

NULLIF(expr1,expr2)

命令说明：如果expr1与expr2值相等，返回NULL；否则返回expr1的值。

示例：

  select nullif(2,1);
  +-------+
  | _col0 |
  +-------+
  |     2 |
  select nullif(2,2);
  +-------+
  | _col0 |
  +-------+
  | NULL  |

COALESCE

COALESCE(value[, …])

命令说明：从左到右依次查找value，返回第一个非null的value。

示例：

  select coalesce(null,2,3);
  +--------------------+
  | coalesce(null,2,3) |
  +--------------------+
  |                  2 |

TRY

TRY(expression)

命令说明：计算并返回表达式expression的值，如果计算表达式时遇到错误则返回null。
- 查询数据时，如果您不希望查询过程抛出异常，可以使用TRY函数屏蔽异常。
- TRY函数遇到异常时默认返回null，您可以使用COALESCE函数指定TRY函数遇到异常时的返回值。
- TRY函数可以处理以下错误：
  - 除0。
  - 无效的转换或者无效的函数参数。
  - 数值大小超出规定范围。

示例：

  select try(3/0);
  +----------+
  | try(3/0) |
  +----------+
  | NULL     |

  select try(3/2);
  +----------+
  | try(3/2) |
  +----------+
  |      1.5 |

源数据中包含非法数据：

  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

查询中不使用TRY函数时，查询失败。

  select cast(origin_zip as BIGINT) from shipping;
  ---------------------------------------------------
  Query failed: Can not cast 'P332a' to BIGINT

查询中使用TRY函数时，查询不会报错。

  select try(cast(origin_zip as BIGINT)) from shipping;
  ------------
  origin_zip
  ------------
        94131
   NULL
        94025
        08544

COALESCE嵌套TRY函数，查询报错时将返回指定值。

  select coalesce(try(total_cost/packages), 0) as per_package from shipping;
  -------------
   per_package
  -------------
            4
           14
            0
           19

GREATEST

GREATEST(value1,value2,...)

命令说明：返回参数中的最大值。

示例：

  select greatest(2,0);
  +---------------+
  | greatest(2,0) |
  +---------------+
  |             2 |

  select greatest('B','A','C');
  +-----------------------+
  | greatest('B','A','C') |
  +-----------------------+
  | C                     |

LEAST

LEAST(value1,value2,...)

命令说明：返回参数中的最小值。

示例：

  select least('B','A','C');
  +--------------------+
  | least('B','A','C') |
  +--------------------+
  | A                  |

  select least(34.0,3.0,5.0,767.0);
  +---------------------------+
  | least(34.0,3.0,5.0,767.0) |
  +---------------------------+
  |                       3.0 |

NVL2

NVL2(expr1,expr2,expr3)

命令说明：如果expr1的结果为非null值，将返回expr2的值；如果expr1的结果为null值，将返回expr3的值。

示例：

  select nvl2(1, 2, 3);
  +---------------+
  | nvl2(1, 2, 3) |
  +---------------+
  |             2 |

  select nvl2(null, 2, 3);
  +------------------+
  | nvl2(null, 2, 3) |
  +------------------+
  |                3 |

DECODE

DECODE(expr,search1,result1,search2,result2,...searchn,resultn,default)

命令说明：从左到右依次查找，如果expr的值等于search1则返回result1，如果expr的值等于search2则返回result2，以此类推，如果没有与expr相等的result，则返回default。

示例：

  select decode(1, 1, '1A', 2, '2A', '3A');
  +-----------------------------------+
  | decode(1, 1, '1A', 2, '2A', '3A') |
  +-----------------------------------+
  | 1A                                |

  select decode(1, 2, '1A', 1, '2A', '3A');
  +-----------------------------------+
  | decode(1, 2, '1A', 1, '2A', '3A') |
  +-----------------------------------+
  | 2A                                |

  select decode(1, 2, '1A', 2, '2A', '3A');
  +-----------------------------------+
  | decode(1, 2, '1A', 2, '2A', '3A') |
  +-----------------------------------+
  | 3A                                |