Other functions-IoT Platform (Deprecated)(IoT)-阿里云帮助中心

DataAnalysis supports functions such as CAST, DECODE, LEAST, ARRAY, SIZE, and NVL. This topic provides the syntax, parameters, and examples for each function.

Function	Feature
CAST	Transforms an expression result into a specified data type.
COALESCE	Returns the first non-NULL value from a list.
UUID	Generates a random identifier.
CASE WHEN expression	Returns different values based on an expression.
IF expression	Returns a value based on a Boolean condition.

[...]

CAST

Syntax
```
cast(<expr> as <type>)
```
Description
Converts the result of expr into a specified target data type. Usage examples include:
- cast(double as bigint): Converts a DOUBLE value to BIGINT.
- cast(string as bigint): Converts a string to BIGINT. If the string contains an integer, it is directly converted. If the string contains a floating-point or exponential number, it is first converted to DOUBLE and then to BIGINT.
- cast(string as datetime) or cast(datetime as string): Uses the default format yyyy-mm-dd hh:mi:ss.
Parameter Description
- expr: Required. The expression to be converted.
- type: Required. The target data type for the conversion.
Return Value Description

Returns a value of the specified data type.
Examples
- Example 1: Common usage.
```
-- The return value is 1.
    cast('1' as bigint);
```
- Example 2: Incorrect usage. If the conversion fails or an unsupported conversion is attempted, null is returned.
```
-- NULL is returned.
    cast('abc' as bigint);
```

COALESCE

Syntax
```
coalesce(<expr1>, <expr2>, ...)
```
Description

Returns the first non-NULL value from the list <expr1>, <expr2>, .... If all are NULL, then NULL is returned.
Parameter Description

expr: Required. The expressions to evaluate, excluding NULL values. All expressions must be of the same data type to avoid errors. If all expressions are NULL, an error is returned.
Return Value Description

The data type of the return value matches that of the non-NULL parameter.

Examples

Example 1: Common Usage

-- The return value is 1.
    coalesce(null, null, 1, null, 3, 5, 7);

Example 2: Incorrect Usage

-- An error is returned because 'abc' is not a valid data type.
    coalesce(null, null, 1, null, 'abc', 5, 7);

Example 3: Incorrect Usage

-- An error is returned because there must be at least one non-NULL value.
    coalesce(null, null, null, null);

UUID

Syntax
```
string uuid()
```
Description

This function returns a random UUID in the format of 29347a88-1e57-41ae-bb68-a9edbdd9****.

Note
This function returns a globally unique identifier (UUID) that is random and unique in most scenarios.

CASE WHEN expression

Syntax

Two case when formats are available:

case <value>
when <value1> then <result1>
when <value2> then <result2>
...
else <resultn>
end

case
when (<condition1>) then <result1>
when (<condition2>) then <result2>
when (<condition3>) then <result3>
...
else <resultn>
end

Description

Returns different result values based on the evaluation of value or condition.
Parameter description
- value: Required. The value to be compared.
- condition: Required. The condition to be evaluated.
- result: Required. The value returned upon condition match.
Note
- If the result types are only BIGINT and DOUBLE, they are implicitly converted to DOUBLE before being returned.
- If the result types include STRING, they are implicitly converted to STRING before being returned. Unsupported data type conversions will result in an error. For instance, BOOLEAN cannot be converted to STRING.
- Implicit conversions between other data types are not permitted.

Examples

Select the appropriate region based on the shop_name value.

case  
when shop_name is null then 'default_region'
when shop_name like 'hang%' then 'zj_region'
end as region 
from sale_detail;

IF expression

Syntax

if(<testCondition>, <valueTrue>, <valueFalseOrNull>)

Description

Evaluates the testCondition. If it is true, it returns the valueTrue; otherwise, it returns the valueFalseOrNull.
Parameter Description
- testCondition: Required. The BOOLEAN expression to test.
- valueTrue: Required. The value returned if testCondition is true.
- valueFalseOrNull: The value returned if testCondition is false. Can be NULL.
Return Value Description

The return value's data type matches that of valueTrue or valueFalseOrNull.

Examples

-- The return value is 200.
    if(1=2,100,200);