Syntax and usage of common SQL analysis functions - IoT Platform - Alibaba Cloud - IoT Platform

SQL analysis provides commonly used functions such as CAST, DECODE, and LEAST. This topic covers the syntax, parameters, and examples for each function.

Function	Description
BETWEEN AND expression	Filters data that falls within or outside a specified range.
CASE WHEN expression	Returns different values based on the result of an expression.
CAST	Converts an expression result to a specified data type.
COALESCE	Returns the first non-null value in the parameter list.
DECODE	Implements the `IF-THEN-ELSE` logic.
GREATEST	Returns the maximum value of the input parameters.
IF	Checks whether a specified condition is true.
LEAST	Returns the minimum value of the input parameters.
NULLIF	Checks whether two input parameters have the same value.
NVL	Returns a specified default value for parameters whose values are null.

BETWEEN AND expression

Syntax
```
<a> [NOT] between <b> and <c>
```
Description

Returns the values of field a that fall in or fall out of the range between b and c.
Description
- a: required. The field whose values you want to obtain.
- b and c: required. The two parameters specify a value range. The data types of the two parameters must be the same as the data type of the a parameter.
Return value

The values that fall in or fall out of the specified range are returned.

If the a, b, or c parameter is empty, null is returned.
Examples
Query the data whose sal is from 1000 to 1500. Sample statement:
```
select * from emp where sal between 1000 and 1500;
```

CASE WHEN expression

Syntax

The SQL analysis feature supports case when expressions in the following formats:

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 the value of result based on the calculation result of value or _condition.
Parameters
- value: required. The value that is used for comparison.
- _condition: required. The condition that is used for comparison.
- result: required. The return value.
Return value
- If the data types of all result values are only BIGINT and DOUBLE, the values are returned after their data types are converted into the DOUBLE type.
- If result values of the STRING type exist, the values are converted into the STRING type before they are returned. If a data type conversion is not supported, an error is returned. For example, data of the BOOLEAN type cannot be converted into the STRING type.
- Conversions between other data types are not allowed.

Examples

Return the value of result based on the data in the sale_detail table that contains the shop_name (STRING), customer_id (STRING), and total_price (DOUBLE) columns. Data in the table:

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
| null       | c5          | NULL        | 2014       | shanghai   |
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
+------------+-------------+-------------+------------+------------+

Sample statement:

select 
case  
when region='china' then 'default_region'
when region like 'shang%' then 'sh_region'
end as region 
from sale_detail;

Sample result:

+------------+
| region     |
+------------+
| default_region |
| default_region |
| default_region |
| sh_region  |
| sh_region  |
| sh_region  |
+------------+

CAST

Syntax
```
cast(<expr> as <type>)
```
Description

Converts the data type of the source specified by expr to the type specified by type.
Parameters
- expr: required. The data source whose data type you want to convert.
- type: required. The target data type. Usage:
  - cast(double as bigint): converts a value of the DOUBLE type into the BIGINT type.
  - cast(string as bigint): converts a value of the STRING type into the BIGINT type. If the string consists of numerals that are expressed in the INTEGER form, the string is converted into the BIGINT type. If the string consists of numerals that are expressed in the FLOAT or EXPONENTIAL form, the string is converted into the DOUBLE type and then into the BIGINT type.
  - The default date format, yyyy-mm-dd hh:mi:ss, is used for cast(string as datetime) and cast(datetime as string).
Return value
- A value of the specified data type is returned.
Examples
- Example 1: common usage. Sample statement:
```
-- The return value is 1. 
select cast('1' as bigint);
```
- Example 2: Convert a value of the STRING type into the BOOLEAN type. If the value of the STRING type is an empty string, false is returned. Otherwise, true is returned. Sample statement:
  - The value of the STRING type is an empty string.
```
select cast("" as boolean);
-- The return value is false.
+------+
| _c0  |
+------+
| false |
+------+
```
  - The value of the STRING type is a non-empty string.
```
select cast("false" as boolean);
-- The return value is true.
+------+
| _c0  |
+------+
| true |
+------+
```

COALESCE

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

Returns the first non-null value in <expr1>, <expr2>, ....
Description

expr: required. The values to check.
Return value

The data type of the return value is the same as the data type of the input parameter.

Examples

Common usage. Sample statement:

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

DECODE

Syntax

decode(<expression>, <search>, <result>[, <search>, <result>]...[, <default>])

Description

Implements the IF-THEN-ELSE logic.
Description
- expression: required. The expression to compare.
- search: required. The search item to compare with expression.
- result: required. The value returned when search matches expression.
- default: optional. If no search item matches the expression, the value of default is returned. If not specified, null is returned.
Note
- Except for null values, all result values must be of the same data type. Otherwise, an error is returned.
- The values of search and expression must be of the same data type. Otherwise, an error is returned.
Return value
- If a search item matches the expression, the value of result is returned.
- If no search item matches the expression, the value of default is returned.
- If no value is specified for default, null is returned.
- If duplicate search items match the expression, the first matching value is returned.
- In most cases, SQL analysis evaluates NULL=NULL as null. However, in this function, two NULL values are considered equal.

Examples

Return the value of result based on the data in the sale_detail table that contains the shop_name (STRING), customer_id (STRING), and total_price (DOUBLE) columns. Data in the table:

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
| null       | c5          | NULL        | 2014       | shanghai   |
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
+------------+-------------+-------------+------------+------------+

Sample statement:

-- If the value of customer_id is c1, Taobao is returned. If the value is c2, Alipay is returned. If the value is c3, Aliyun is returned. If the value is null, N/A is returned. In other cases, Others is returned. 
select
decode(customer_id,
'c1', 'Taobao',
'c2', 'Alipay',
'c3', 'Aliyun',
Null, 'N/A',
'Others') as result
from sale_detail;
-- The preceding statement is equivalent to the following statement: 
if customer_id = c1 then
result := 'Taobao';
elsif customer_id = c2 then
result := 'Alipay';
elsif customer_id = c3 then
result := 'Aliyun';
...
else
result := 'Others';
end if;

Sample result:

+------------+
| result     |
+------------+
| Others     |
| Others     |
| Others     |
| Taobao     |
| Alipay     |
| Aliyun     |
+------------+

GREATEST

Syntax
```
greatest(<var1>, <var2>[,...])
```
Description

Returns the maximum value of the input parameters.
Description

var1 and var2: required. Values of the BIGINT, DOUBLE, DECIMAL, DATETIME, or STRING type.
Return value
- The maximum value of the input parameters is returned. If implicit conversions are not performed, the return value is of the same data type as the input parameters.
- The value null is interpreted as the minimum value.
- If the input parameters are of different data types, the input parameters of the DOUBLE, BIGINT, DECIMAL, and STRING types are converted into the DOUBLE type for comparison, and the input parameters of the STRING and DATETIME types are converted into the DATETIME type for comparison. Implicit conversions of other data types are not allowed.

IF

Syntax

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

Description

Checks whether testCondition is true. If testCondition is true, the value of valueTrue is returned. Otherwise, the value of valueFalseOrNull is returned.
Description
- testCondition: required. The expression to evaluate. The value is of the BOOLEAN type.
- valueTrue: required. The value returned when testCondition is true.
- valueFalseOrNull: the value returned when testCondition is false. You can set this parameter to null.
Return value

The data type of the return value is the same as the data type of valueTrue or valueFalseOrNull.

Examples

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

LEAST

Syntax
```
least(<var1>, <var2>[,...])
```
Description

Returns the minimum value of the input parameters.
Description

var: required. The values of the input parameters. Values of the BIGINT, DOUBLE, DECIMAL, DATETIME, or STRING type.
Return value
- The minimum value of the input parameters is returned. If implicit conversions are not performed, the return value is of the same data type as the input parameters.
- If a data type conversion is performed between the DOUBLE, BIGINT, and STRING types, a value of the DOUBLE type is returned. If a data type conversion is performed between the STRING and DATETIME types, a value of the DATETIME type is returned. If a data type conversion is performed between the DECIMAL, DOUBLE, BIGINT, and STRING types, a value of the DECIMAL type is returned. Implicit conversions of other data types are not allowed.
- The value null is interpreted as the minimum value.
- If the values of all input parameters are null, null is returned.

Examples

-- The return value is 2. 
select least(5, 2, 7);

NULLIF

Syntax
```
T nullif(T <expr1>, T <expr2>)
```
Description

Compares the values of expr1 and expr2. If the values are the same, null is returned. If the values are different, the value of expr1 is returned.
Description

expr1 and expr2: required. Expressions of any data type. T specifies the type of input data. The type can be a data type supported by the SQL analysis feature.
Return value

The value of expr1 or null is returned.

Examples

-- The return value is 2. 
select nullif(2, 3);
-- The return value is null. 
select nullif(2, 2);
-- The return value is 3. 
select nullif(3, null);

NVL

Syntax
```
nvl(T <value>, T <default_value>)
```
Description

Returns default_value if value is null. Otherwise, value is returned. The value and default_value parameters must be of the same data type.
Description
- value: required. The input parameter. T specifies the data type, which can be any type supported by SQL analysis.
- default_value: required. The replacement value for null. The data type must be the same as value.

Examples

A table named t_data contains three columns: c1 string, c2 bigint, and c3 datetime. The c1 column is of the STRING type. The c2 column is of the BIGINT type. The c3 column is of the DATETIME type. Data in the table:

+----+------------+------------+
| c1 | c2 | c3 |
+----+------------+------------+
| NULL | 20 | 2017-11-13 05:00:00 |
| ddd | 25 | NULL |
| bbb | NULL | 2017-11-12 08:00:00 |
| aaa | 23 | 2017-11-11 00:00:00 |
+----+------------+------------+

After the NVL function is called, the null value in c1 is returned as 00000, the null value in c2 is returned as 0, and the null value in c3 is returned as hyphen (-). Sample statement:

select nvl(c1,'00000'),nvl(c2,0),nvl(c3,'-') from nvl_test;
-- Sample result: 
+-----+------------+-----+
| _c0 | _c1 | _c2 |
+-----+------------+-----+
| 00000 | 20 | 2017-11-13 05:00:00 |
| ddd | 25 | - |
| bbb | 0 | 2017-11-12 08:00:00 |
| aaa | 23 | 2017-11-11 00:00:00 |
+-----+------------+-----+