全部产品
Search
文档中心

云原生数据湖分析 DLA - 文档停止维护:条件运算函数

更新时间:Apr 20, 2021

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

简单CASE表达式

CASE expression
WHEN value THEN result
[ WHEN ... ]
[ ELSE result ]
END
  • 命令说明:从左到右依次查找value,如果找到和expression相等的value则返回对应的result结果;如果未找到相等的value,则返回ELSE语句中result结果。

  • 示例:以下示例将person表中age10的记录的改为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直到第一个为TRUEcondition,返回对应的result结果。如果没有为Truecondition,返回ELSE子句的result结果。

  • 示例:以下示例将person表中age10的记录改为age+10namebondage改为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)
  • 命令说明:如果conditiontrue,返回true_value;否则返回null

  • 示例:

      select if((2+3)>4,5);
      +-------+
      | _col0 |
      +-------+
      |     5 |
IF(condition,true_value,false_value)
  • 命令说明:如果conditiontrue,返回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)
  • 命令说明:如果expr1expr2值相等,返回NULL;否则返回expr1的值。

  • 示例:

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

COALESCE

COALESCE(value[, …])
  • 命令说明:从左到右依次查找value,返回第一个非nullvalue

  • 示例:

      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                                |