This is the multi-page printable view of this section. Click here to print.
Other Functions
- 1: ASSUME_NOT_NULL
- 2: EXISTS
- 3: GROUPING
- 4: HUMANIZE_NUMBER
- 5: HUMANIZE_SIZE
- 6: IGNORE
- 7: REMOVE_NULLABLE
- 8: TO_NULLABLE
- 9: TYPEOF
1 - ASSUME_NOT_NULL
Results in an equivalent non-Nullable value for a Nullable type. In case the original value is NULL the result is undetermined.
Analyze Syntax
func.assume_not_null(<x>)
Analyze Examples
With a table like:
┌────────────────────┐
│ x │ y │
├────────────────────┤
│ 1 │ NULL │
│ 2 │ 3 │
└────────────────────┘
func.assume_not_null(y)
┌─────────────────────────┐
│ func.assume_not_null(y) │
├─────────────────────────┤
│ 0 │
│ 3 │
└─────────────────────────┘
SQL Syntax
ASSUME_NOT_NULL(<x>)
Aliases
Return Type
Returns the original datatype from the non-Nullable type; Returns the embedded non-Nullable datatype for Nullable type.
SQL Examples
CREATE TABLE default.t_null ( x int, y int null);
INSERT INTO default.t_null values (1, null), (2, 3);
SELECT ASSUME_NOT_NULL(y), REMOVE_NULLABLE(y) FROM t_null;
┌─────────────────────────────────────────┐
│ assume_not_null(y) │ remove_nullable(y) │
├────────────────────┼────────────────────┤
│ 0 │ 0 │
│ 3 │ 3 │
└─────────────────────────────────────────┘
2 - EXISTS
The exists condition is used in combination with a subquery and is considered "to be met" if the subquery returns at least one row.
SQL Syntax
WHERE EXISTS ( <subquery> );
SQL Examples
SELECT number FROM numbers(5) AS A WHERE exists (SELECT * FROM numbers(3) WHERE number=1);
+--------+
| number |
+--------+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
+--------+
3 - GROUPING
Returns a bit mask indicating which GROUP BY expressions are not included in the current grouping set. Bits are assigned with the rightmost argument corresponding to the least-significant bit; each bit is 0 if the corresponding expression is included in the grouping criteria of the grouping set generating the current result row, and 1 if it is not included.
SQL Syntax
GROUPING ( expr [, expr, ...] )
Note:
GROUPING can only be used with GROUPING SETS, ROLLUP, or CUBE, and its arguments must be in the grouping sets list.Arguments
Grouping sets items.
Return Type
UInt32.
SQL Examples
select a, b, grouping(a), grouping(b), grouping(a,b), grouping(b,a) from t group by grouping sets ((a,b),(a),(b), ()) ;
+------+------+-------------+-------------+----------------+----------------+
| a | b | grouping(a) | grouping(b) | grouping(a, b) | grouping(b, a) |
+------+------+-------------+-------------+----------------+----------------+
| NULL | A | 1 | 0 | 2 | 1 |
| a | NULL | 0 | 1 | 1 | 2 |
| b | A | 0 | 0 | 0 | 0 |
| NULL | NULL | 1 | 1 | 3 | 3 |
| a | A | 0 | 0 | 0 | 0 |
| b | B | 0 | 0 | 0 | 0 |
| b | NULL | 0 | 1 | 1 | 2 |
| a | B | 0 | 0 | 0 | 0 |
| NULL | B | 1 | 0 | 2 | 1 |
+------+------+-------------+-------------+----------------+----------------+
4 - HUMANIZE_NUMBER
Returns a readable number.
Analyze Syntax
func.humanize_number(x);
Analyze Examples
func.humanize_number(1000 * 1000)
+-------------------------------------+
| func.humanize_number((1000 * 1000)) |
+-------------------------------------+
| 1 million |
+-------------------------------------+
SQL Syntax
HUMANIZE_NUMBER(x);
Arguments
| Arguments | Description |
|---|---|
| x | The numerical size. |
Return Type
String.
SQL Examples
SELECT HUMANIZE_NUMBER(1000 * 1000)
+-------------------------+
| HUMANIZE_NUMBER((1000 * 1000)) |
+-------------------------+
| 1 million |
+-------------------------+
5 - HUMANIZE_SIZE
Returns the readable size with a suffix(KiB, MiB, etc).
Analyze Syntax
func.humanize_size(x);
Analyze Examples
func.humanize_size(1024 * 1024)
+----------------------------------------+
| func.func.humanize_size((1024 * 1024)) |
+----------------------------------------+
| 1 MiB |
+----------------------------------------+
SQL Syntax
HUMANIZE_SIZE(x);
Arguments
| Arguments | Description |
|---|---|
| x | The numerical size. |
Return Type
String.
SQL Examples
SELECT HUMANIZE_SIZE(1024 * 1024)
+-------------------------+
| HUMANIZE_SIZE((1024 * 1024)) |
+-------------------------+
| 1 MiB |
+-------------------------+
6 - IGNORE
By using insert ignore statement, the rows with invalid data that cause the error are ignored and the rows with valid data are inserted into the table.
SQL Syntax
INSERT ignore INTO TABLE(column_list)
VALUES( value_list),
( value_list),
...
7 - REMOVE_NULLABLE
Alias for ASSUME_NOT_NULL.
8 - TO_NULLABLE
Converts a value to its nullable equivalent.
When you apply this function to a value, it checks if the value is already able to hold NULL values or not. If the value is already able to hold NULL values, the function will return the value without making any changes.
However, if the value is not able to hold NULL values, the TO_NULLABLE function will modify the value to make it able to hold NULL values. It does this by wrapping the value in a structure that can hold NULL values, which means the value can now hold NULL values in the future.
Analyze Syntax
func.to_nullable(x);
Analyze Examples
func.typeof(3), func.to_nullable(3), func.typeof(func.to_nullable(3))
func.typeof(3) | func.to_nullable(3) | func.typeof(func.to_nullable(3)) |
-----------------+---------------------+----------------------------------+
TINYINT UNSIGNED | 3 | TINYINT UNSIGNED NULL |
SQL Syntax
TO_NULLABLE(x);
Arguments
| Arguments | Description |
|---|---|
| x | The original value. |
Return Type
Returns a value of the same data type as the input value, but wrapped in a nullable container if the input value is not already nullable.
SQL Examples
SELECT typeof(3), TO_NULLABLE(3), typeof(TO_NULLABLE(3));
typeof(3) |to_nullable(3)|typeof(to_nullable(3))|
----------------+--------------+----------------------+
TINYINT UNSIGNED| 3|TINYINT UNSIGNED NULL |
9 - TYPEOF
TYPEOF function is used to return the name of a data type.
Analyze Syntax
func.typeof( <expr> )
Analyze Examples
func.typeof(1)
+------------------+
| func.typeof(1) |
+------------------+
| INT |
+------------------+
SQL Syntax
TYPEOF( <expr> )
Arguments
| Arguments | Description |
|---|---|
<expr> | Any expression. This may be a column name, the result of another function, or a math operation. |
Return Type
String
SQL Examples
SELECT typeof(1::INT);
+------------------+
| typeof(1::Int32) |
+------------------+
| INT |
+------------------+