Relational Data - Functions - Conditional Functions: Difference between revisions
Chris.Hansen (talk | contribs) (Import ClickHouse Docs: Wed Aug 28 2024 14:52:19 GMT-0400 (Eastern Daylight Time)) |
Chris.Hansen (talk | contribs) (Import ClickHouse Docs: Wed Aug 28 2024 15:05:45 GMT-0400 (Eastern Daylight Time)) |
||
Line 1: | Line 1: | ||
──────────────────────────────────────────────────────────────────────────┘ | == if == | ||
Performs conditional branching. | |||
If the condition <code>cond</code> evaluates to a non-zero value, the function returns the result of the expression <code>then</code>. If <code>cond</code> evaluates to zero or <code>NULL</code>, then the result of the <code>else</code> expression is returned. | |||
Setting [https://clickhouse.com/docs/en/operations/settings/settings#short-circuit-function-evaluation short_circuit_function_evaluation] controls whether short-circuit evaluation is used. If enabled, the <code>then</code> expression is evaluated only on rows where <code>cond</code> is <code>true</code> and the <code>else</code> expression where <code>cond</code> is <code>false</code>. For example, with short-circuit evaluation, no division-by-zero exception is thrown when executing the query <code>SELECT if(number = 0, 0, intDiv(42, number)) FROM numbers(10)</code>. | |||
<code>then</code> and <code>else</code> must be of a similar type. | |||
'''Syntax''' | |||
<syntaxhighlight lang="sql">if(cond, then, else)</syntaxhighlight> | |||
Alias: <code>cond ? then : else</code> (ternary operator) | |||
'''Arguments''' | |||
* <code>cond</code> – The evaluated condition. UInt8, Nullable(UInt8) or NULL. | |||
* <code>then</code> – The expression returned if <code>condition</code> is true. | |||
* <code>else</code> – The expression returned if <code>condition</code> is <code>false</code> or NULL. | |||
'''Returned values''' | |||
The result of either the <code>then</code> and <code>else</code> expressions, depending on condition <code>cond</code>. | |||
'''Example''' | |||
<syntaxhighlight lang="sql">SELECT if(1, plus(2, 2), plus(2, 6));</syntaxhighlight> | |||
Result: | |||
<syntaxhighlight lang="text">┌─plus(2, 2)─┐ | |||
│ 4 │ | |||
└────────────┘</syntaxhighlight> | |||
<span id="multiif"></span> | |||
== multiIf == | |||
Allows to write the [[Relational_Data_-_Operators#operator_case|CASE]] operator more compactly in the query. | |||
'''Syntax''' | |||
<syntaxhighlight lang="sql">multiIf(cond_1, then_1, cond_2, then_2, ..., else)</syntaxhighlight> | |||
Setting [https://clickhouse.com/docs/en/operations/settings/settings#short-circuit-function-evaluation short_circuit_function_evaluation] controls whether short-circuit evaluation is used. If enabled, the <code>then_i</code> expression is evaluated only on rows where <code>((NOT cond_1) AND (NOT cond_2) AND ... AND (NOT cond_{i-1}) AND cond_i)</code> is <code>true</code>, <code>cond_i</code> will be evaluated only on rows where <code>((NOT cond_1) AND (NOT cond_2) AND ... AND (NOT cond_{i-1}))</code> is <code>true</code>. For example, with short-circuit evaluation, no division-by-zero exception is thrown when executing the query <code>SELECT multiIf(number = 2, intDiv(1, number), number = 5) FROM numbers(10)</code>. | |||
'''Arguments''' | |||
The function accepts <code>2N+1</code> parameters: - <code>cond_N</code> — The N-th evaluated condition which controls if <code>then_N</code> is returned. - <code>then_N</code> — The result of the function when <code>cond_N</code> is true. - <code>else</code> — The result of the function if none of conditions is true. | |||
'''Returned values''' | |||
The result of either any of the <code>then_N</code> or <code>else</code> expressions, depending on the conditions <code>cond_N</code>. | |||
'''Example''' | |||
Assuming this table: | |||
<syntaxhighlight lang="text">┌─left─┬─right─┐ | |||
│ ᴺᵁᴸᴸ │ 4 │ | |||
│ 1 │ 3 │ | |||
│ 2 │ 2 │ | |||
│ 3 │ 1 │ | |||
│ 4 │ ᴺᵁᴸᴸ │ | |||
└──────┴───────┘</syntaxhighlight> | |||
<syntaxhighlight lang="sql">SELECT | |||
left, | |||
right, | |||
multiIf(left < right, 'left is smaller', left > right, 'left is greater', left = right, 'Both equal', 'Null value') AS result | |||
FROM LEFT_RIGHT | |||
┌─left─┬─right─┬─result──────────┐ | |||
│ ᴺᵁᴸᴸ │ 4 │ Null value │ | |||
│ 1 │ 3 │ left is smaller │ | |||
│ 2 │ 2 │ Both equal │ | |||
│ 3 │ 1 │ left is greater │ | |||
│ 4 │ ᴺᵁᴸᴸ │ Null value │ | |||
└──────┴───────┴─────────────────┘</syntaxhighlight> | |||
<span id="using-conditional-results-directly"></span> | |||
== Using Conditional Results Directly == | |||
Conditionals always result to <code>0</code>, <code>1</code> or <code>NULL</code>. So you can use conditional results directly like this: | |||
<syntaxhighlight lang="sql">SELECT left < right AS is_small | |||
FROM LEFT_RIGHT | |||
┌─is_small─┐ | |||
│ ᴺᵁᴸᴸ │ | |||
│ 1 │ | |||
│ 0 │ | |||
│ 0 │ | |||
│ ᴺᵁᴸᴸ │ | |||
└──────────┘</syntaxhighlight> | |||
<span id="null-values-in-conditionals"></span> | |||
== NULL Values in Conditionals == | |||
When <code>NULL</code> values are involved in conditionals, the result will also be <code>NULL</code>. | |||
<syntaxhighlight lang="sql">SELECT | |||
NULL < 1, | |||
2 < NULL, | |||
NULL < NULL, | |||
NULL = NULL | |||
┌─less(NULL, 1)─┬─less(2, NULL)─┬─less(NULL, NULL)─┬─equals(NULL, NULL)─┐ | |||
│ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ | |||
└───────────────┴───────────────┴──────────────────┴────────────────────┘</syntaxhighlight> | |||
So you should construct your queries carefully if the types are <code>Nullable</code>. | |||
The following example demonstrates this by failing to add equals condition to <code>multiIf</code>. | |||
<syntaxhighlight lang="sql">SELECT | |||
left, | |||
right, | |||
multiIf(left < right, 'left is smaller', left > right, 'right is smaller', 'Both equal') AS faulty_result | |||
FROM LEFT_RIGHT | |||
┌─left─┬─right─┬─faulty_result────┐ | |||
│ ᴺᵁᴸᴸ │ 4 │ Both equal │ | |||
│ 1 │ 3 │ left is smaller │ | |||
│ 2 │ 2 │ Both equal │ | |||
│ 3 │ 1 │ right is smaller │ | |||
│ 4 │ ᴺᵁᴸᴸ │ Both equal │ | |||
└──────┴───────┴──────────────────┘</syntaxhighlight> | |||
== greatest == | |||
Returns the greatest across a list of values. All of the list members must be of comparable types. | |||
Examples: | |||
<syntaxhighlight lang="sql">SELECT greatest(1, 2, toUInt8(3), 3.) result, toTypeName(result) type;</syntaxhighlight> | |||
<pre class="response">┌─result─┬─type────┐ | |||
│ 3 │ Float64 │ | |||
└────────┴─────────┘</pre> | |||
<div class="note"> | |||
The type returned is a Float64 as the UInt8 must be promoted to 64 bit for the comparison. | |||
</div> | |||
<syntaxhighlight lang="sql">SELECT greatest(['hello'], ['there'], ['world'])</syntaxhighlight> | |||
<pre class="response">┌─greatest(['hello'], ['there'], ['world'])─┐ | |||
│ ['world'] │ | |||
└───────────────────────────────────────────┘</pre> | |||
<syntaxhighlight lang="sql">SELECT greatest(toDateTime32(now() + toIntervalDay(1)), toDateTime64(now(), 3))</syntaxhighlight> | |||
<pre class="response">┌─greatest(toDateTime32(plus(now(), toIntervalDay(1))), toDateTime64(now(), 3))─┐ | |||
│ 2023-05-12 01:16:59.000 │ | |||
└──---──────────────────────────────────────────────────────────────────────────┘</pre> | |||
<div class="note"> | |||
The type returned is a DateTime64 as the DataTime32 must be promoted to 64 bit for the comparison. | The type returned is a DateTime64 as the DataTime32 must be promoted to 64 bit for the comparison. | ||
Returns the least across a list of values. | </div> | ||
== least == | |||
Returns the least across a list of values. All of the list members must be of comparable types. | |||
Examples: | Examples: | ||
<syntaxhighlight lang="sql">SELECT least(1, 2, toUInt8(3), 3.) result, toTypeName(result) type;</syntaxhighlight> | |||
SELECT least(1, 2, toUInt8(3), 3.) result, toTypeName(result) type;</ | |||
<pre class="response">┌─result─┬─type────┐ | <pre class="response">┌─result─┬─type────┐ | ||
│ 1 │ Float64 │ | │ 1 │ Float64 │ |
Latest revision as of 19:05, 28 August 2024
if
Performs conditional branching.
If the condition cond
evaluates to a non-zero value, the function returns the result of the expression then
. If cond
evaluates to zero or NULL
, then the result of the else
expression is returned.
Setting short_circuit_function_evaluation controls whether short-circuit evaluation is used. If enabled, the then
expression is evaluated only on rows where cond
is true
and the else
expression where cond
is false
. For example, with short-circuit evaluation, no division-by-zero exception is thrown when executing the query SELECT if(number = 0, 0, intDiv(42, number)) FROM numbers(10)
.
then
and else
must be of a similar type.
Syntax
if(cond, then, else)
Alias: cond ? then : else
(ternary operator)
Arguments
cond
– The evaluated condition. UInt8, Nullable(UInt8) or NULL.then
– The expression returned ifcondition
is true.else
– The expression returned ifcondition
isfalse
or NULL.
Returned values
The result of either the then
and else
expressions, depending on condition cond
.
Example
SELECT if(1, plus(2, 2), plus(2, 6));
Result:
┌─plus(2, 2)─┐
│ 4 │
└────────────┘
multiIf
Allows to write the CASE operator more compactly in the query.
Syntax
multiIf(cond_1, then_1, cond_2, then_2, ..., else)
Setting short_circuit_function_evaluation controls whether short-circuit evaluation is used. If enabled, the then_i
expression is evaluated only on rows where ((NOT cond_1) AND (NOT cond_2) AND ... AND (NOT cond_{i-1}) AND cond_i)
is true
, cond_i
will be evaluated only on rows where ((NOT cond_1) AND (NOT cond_2) AND ... AND (NOT cond_{i-1}))
is true
. For example, with short-circuit evaluation, no division-by-zero exception is thrown when executing the query SELECT multiIf(number = 2, intDiv(1, number), number = 5) FROM numbers(10)
.
Arguments
The function accepts 2N+1
parameters: - cond_N
— The N-th evaluated condition which controls if then_N
is returned. - then_N
— The result of the function when cond_N
is true. - else
— The result of the function if none of conditions is true.
Returned values
The result of either any of the then_N
or else
expressions, depending on the conditions cond_N
.
Example
Assuming this table:
┌─left─┬─right─┐
│ ᴺᵁᴸᴸ │ 4 │
│ 1 │ 3 │
│ 2 │ 2 │
│ 3 │ 1 │
│ 4 │ ᴺᵁᴸᴸ │
└──────┴───────┘
SELECT
left,
right,
multiIf(left < right, 'left is smaller', left > right, 'left is greater', left = right, 'Both equal', 'Null value') AS result
FROM LEFT_RIGHT
┌─left─┬─right─┬─result──────────┐
│ ᴺᵁᴸᴸ │ 4 │ Null value │
│ 1 │ 3 │ left is smaller │
│ 2 │ 2 │ Both equal │
│ 3 │ 1 │ left is greater │
│ 4 │ ᴺᵁᴸᴸ │ Null value │
└──────┴───────┴─────────────────┘
Using Conditional Results Directly
Conditionals always result to 0
, 1
or NULL
. So you can use conditional results directly like this:
SELECT left < right AS is_small
FROM LEFT_RIGHT
┌─is_small─┐
│ ᴺᵁᴸᴸ │
│ 1 │
│ 0 │
│ 0 │
│ ᴺᵁᴸᴸ │
└──────────┘
NULL Values in Conditionals
When NULL
values are involved in conditionals, the result will also be NULL
.
SELECT
NULL < 1,
2 < NULL,
NULL < NULL,
NULL = NULL
┌─less(NULL, 1)─┬─less(2, NULL)─┬─less(NULL, NULL)─┬─equals(NULL, NULL)─┐
│ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │
└───────────────┴───────────────┴──────────────────┴────────────────────┘
So you should construct your queries carefully if the types are Nullable
.
The following example demonstrates this by failing to add equals condition to multiIf
.
SELECT
left,
right,
multiIf(left < right, 'left is smaller', left > right, 'right is smaller', 'Both equal') AS faulty_result
FROM LEFT_RIGHT
┌─left─┬─right─┬─faulty_result────┐
│ ᴺᵁᴸᴸ │ 4 │ Both equal │
│ 1 │ 3 │ left is smaller │
│ 2 │ 2 │ Both equal │
│ 3 │ 1 │ right is smaller │
│ 4 │ ᴺᵁᴸᴸ │ Both equal │
└──────┴───────┴──────────────────┘
greatest
Returns the greatest across a list of values. All of the list members must be of comparable types.
Examples:
SELECT greatest(1, 2, toUInt8(3), 3.) result, toTypeName(result) type;
┌─result─┬─type────┐ │ 3 │ Float64 │ └────────┴─────────┘
The type returned is a Float64 as the UInt8 must be promoted to 64 bit for the comparison.
SELECT greatest(['hello'], ['there'], ['world'])
┌─greatest(['hello'], ['there'], ['world'])─┐ │ ['world'] │ └───────────────────────────────────────────┘
SELECT greatest(toDateTime32(now() + toIntervalDay(1)), toDateTime64(now(), 3))
┌─greatest(toDateTime32(plus(now(), toIntervalDay(1))), toDateTime64(now(), 3))─┐ │ 2023-05-12 01:16:59.000 │ └──---──────────────────────────────────────────────────────────────────────────┘
The type returned is a DateTime64 as the DataTime32 must be promoted to 64 bit for the comparison.
least
Returns the least across a list of values. All of the list members must be of comparable types.
Examples:
SELECT least(1, 2, toUInt8(3), 3.) result, toTypeName(result) type;
┌─result─┬─type────┐ │ 1 │ Float64 │ └────────┴─────────┘
The type returned is a Float64 as the UInt8 must be promoted to 64 bit for the comparison.
SELECT least(['hello'], ['there'], ['world'])
┌─least(['hello'], ['there'], ['world'])─┐ │ ['hello'] │ └────────────────────────────────────────┘
SELECT least(toDateTime32(now() + toIntervalDay(1)), toDateTime64(now(), 3))
┌─least(toDateTime32(plus(now(), toIntervalDay(1))), toDateTime64(now(), 3))─┐ │ 2023-05-12 01:16:59.000 │ └────────────────────────────────────────────────────────────────────────────┘
The type returned is a DateTime64 as the DataTime32 must be promoted to 64 bit for the comparison.
clamp
Constrain the return value between A and B.
Syntax
clamp(value, min, max)
Arguments
value
– Input value.min
– Limit the lower bound.max
– Limit the upper bound.
Returned values
If the value is less than the minimum value, return the minimum value; if it is greater than the maximum value, return the maximum value; otherwise, return the current value.
Examples:
SELECT clamp(1, 2, 3) result, toTypeName(result) type;
┌─result─┬─type────┐ │ 2 │ Float64 │ └────────┴─────────┘