--- title: Other Functions reference meta: description: Other available functions in Tinybird. headingMaxLevels: 2 --- # Other functions The following functions perform various operations in addition to the ones already described. ## basename Extracts the tail of a string following its last slash or backslash. This function if often used to extract the filename from a path. ```sql basename(expr) ``` ### Arguments - `expr`: A value of type String. Backslashes must be escaped. ### Returned value A string that contains: - The tail of the input string after its last slash or backslash. If the input string ends with a slash or backslash (e.g. `/` or `c:\`), the function returns an empty string. - The original string if there are no slashes or backslashes. ### Example Query: ```sql SELECT 'some/long/path/to/file' AS a, basename(a) ``` Result: ```text ┌─a──────────────────────┬─basename('some\\long\\path\\to\\file')─┐ │ some\long\path\to\file │ file │ └────────────────────────┴────────────────────────────────────────┘ ``` Query: ```sql SELECT 'some\\long\\path\\to\\file' AS a, basename(a) ``` Result: ```text ┌─a──────────────────────┬─basename('some\\long\\path\\to\\file')─┐ │ some\long\path\to\file │ file │ └────────────────────────┴────────────────────────────────────────┘ ``` Query: ```sql SELECT 'some-file-name' AS a, basename(a) ``` Result: ```text ┌─a──────────────┬─basename('some-file-name')─┐ │ some-file-name │ some-file-name │ └────────────────┴────────────────────────────┘ ``` ## visibleWidth Calculates the approximate width when outputting values to the console in text format (tab-separated). This function is used by the system to implement Pretty formats. `NULL` is represented as a string corresponding to `NULL` in `Pretty` formats. ### Syntax ```sql visibleWidth(x) ``` ### Example Query: ```sql SELECT visibleWidth(NULL) ``` Result: ```text ┌─visibleWidth(NULL)─┐ │ 4 │ └────────────────────┘ ``` ## toTypeName Returns the type name of the passed argument. If `NULL` is passed, the function returns type `Nullable(Nothing)`, which corresponds to Tinybird's internal `NULL` representation. ### Syntax ```sql toTypeName(value) ``` ### Arguments - `value`: A value of arbitrary type. ### Returned value - The data type name of the input value. String. ### Example Query: ```sql SELECT toTypeName(123) ``` Result: ```result ┌─toTypeName(123)─┐ │ UInt8 │ └─────────────────┘ ``` ## blockSize In Tinybird, queries are processed in blocks (chunks). This function returns the size (row count) of the block the function is called on. ### Syntax ```sql blockSize() ``` ### Example Query: ```sql SELECT blockSize() FROM ( SELECT * FROM numbers(5)) ``` Result: ```result ┌─blockSize()─┐ │ 5 │ │ 5 │ │ 5 │ │ 5 │ │ 5 │ └─────────────┘ ``` ## byteSize Returns an estimation of uncompressed byte size of its arguments in memory. ### Syntax ```sql byteSize(argument [, ...]) ``` ### Arguments - `argument`: Value. ### Returned value - Estimation of byte size of the arguments in memory. UInt64. ### Examples For String arguments, the function returns the string length + 9 (terminating zero + length). Query: ```sql SELECT byteSize('string') ``` Result: ```text ┌─byteSize('string')─┐ │ 15 │ └────────────────────┘ ``` Query: ```sql SELECT key, byteSize(u8) AS `byteSize(UInt8)`, byteSize(u16) AS `byteSize(UInt16)`, byteSize(u32) AS `byteSize(UInt32)`, byteSize(u64) AS `byteSize(UInt64)`, byteSize(i8) AS `byteSize(Int8)`, byteSize(i16) AS `byteSize(Int16)`, byteSize(i32) AS `byteSize(Int32)`, byteSize(i64) AS `byteSize(Int64)`, byteSize(f32) AS `byteSize(Float32)`, byteSize(f64) AS `byteSize(Float64)` FROM (select c1::Int32 as key, c2::UInt8 as u8, c3::UInt16 as u16, c4::UInt32 as u32, c5::UInt64 as u64, c6::Int8 as i8, c7::Int16 as i16, c8::Int32 as i32, c9::Int64 as i64, c10::Float32 as f32, c11::Float64 as f64 from values((1, 8, 16, 32, 64, -8, -16, -32, -64, 32.32, 64.64))) ``` Result: ```text key: 1 byteSize(UInt8): 1 byteSize(UInt16): 2 byteSize(UInt32): 4 byteSize(UInt64): 8 byteSize(Int8): 1 byteSize(Int16): 2 byteSize(Int32): 4 byteSize(Int64): 8 byteSize(Float32): 4 byteSize(Float64): 8 ``` If the function has multiple arguments, the function accumulates their byte sizes. Query: ```sql SELECT byteSize(NULL, 1, 0.3, '') ``` Result: ```text ┌─byteSize(NULL, 1, 0.3, '')─┐ │ 19 │ └────────────────────────────┘ ``` ## materialize Turns a constant into a full column containing a single value. Full columns and constants are represented differently in memory. Functions usually execute different code for normal and constant arguments, although the result should typically be the same. This function can be used to debug this behavior. ### Syntax ```sql materialize(x) ``` ### Parameters - `x`: A constant. Constant. ### Returned value - A column containing a single value `x`. ### Example In the example below the `countMatches` function expects a constant second argument. This behaviour can be debugged by using the `materialize` function to turn a constant into a full column, verifying that the function throws an error for a non-constant argument. Query: ```sql SELECT countMatches('foobarfoo', 'foo') SELECT countMatches('foobarfoo', materialize('foo')) ``` Result: ```result 2 Code: 44. DB::Exception: Received from localhost:9000. DB::Exception: Illegal type of argument #2 'pattern' of function countMatches, expected constant String, got String ``` ## ignore Accepts arbitrary arguments and unconditionally returns `0`. The argument is still evaluated internally, making it useful for eg. benchmarking. ### Syntax ```sql ignore([arg1[, arg2[, ...]]) ``` ### Arguments - Accepts arbitrarily many arguments of arbitrary type, including `NULL`. ### Returned value - Returns `0`. ### Example Query: ```sql SELECT ignore(0, 'Tinybird', NULL) ``` Result: ```result ┌─ignore(0, 'Tinybird', NULL)─┐ │ 0 │ └───────────────────────────────┘ ``` ## isConstant Returns whether the argument is a constant expression. A constant expression is an expression whose result is known during query analysis, i.e. before execution. For example, expressions over literals are constant expressions. This function is mostly intended for development, debugging and demonstration. ### Syntax ```sql isConstant(x) ``` ### Arguments - `x`: Expression to check. ### Returned values - `1` if `x` is constant. UInt8. - `0` if `x` is non-constant. UInt8. ### Examples Query: ```sql SELECT isConstant(x + 1) FROM (SELECT 43 AS x) ``` Result: ```text ┌─isConstant(plus(x, 1))─┐ │ 1 │ └────────────────────────┘ ``` Query: ```sql WITH 3.14 AS pi SELECT isConstant(cos(pi)) ``` Result: ```text ┌─isConstant(cos(pi))─┐ │ 1 │ └─────────────────────┘ ``` Query: ```sql SELECT isConstant(number) FROM numbers(1) ``` Result: ```text ┌─isConstant(number)─┐ │ 0 │ └────────────────────┘ ``` ## bar Builds a bar chart. `bar(x, min, max, width)` draws a band with width proportional to `(x - min)` and equal to `width` characters when `x = max`. ### Arguments - `x`: Size to display. - `min, max`: Integer constants. The value must fit in `Int64`. - `width`: Constant, positive integer, can be fractional. The band is drawn with accuracy to one eighth of a symbol. Example: ```sql SELECT toHour(EventTime) AS h, count() AS c, bar(c, 0, 600000, 20) AS bar FROM test.hits GROUP BY h ORDER BY h ASC ``` ```text ┌──h─┬──────c─┬─bar────────────────┐ │ 0 │ 292907 │ █████████▋ │ │ 1 │ 180563 │ ██████ │ │ 2 │ 114861 │ ███▋ │ │ 3 │ 85069 │ ██▋ │ │ 4 │ 68543 │ ██▎ │ │ 5 │ 78116 │ ██▌ │ │ 6 │ 113474 │ ███▋ │ │ 7 │ 170678 │ █████▋ │ │ 8 │ 278380 │ █████████▎ │ │ 9 │ 391053 │ █████████████ │ │ 10 │ 457681 │ ███████████████▎ │ │ 11 │ 493667 │ ████████████████▍ │ │ 12 │ 509641 │ ████████████████▊ │ │ 13 │ 522947 │ █████████████████▍ │ │ 14 │ 539954 │ █████████████████▊ │ │ 15 │ 528460 │ █████████████████▌ │ │ 16 │ 539201 │ █████████████████▊ │ │ 17 │ 523539 │ █████████████████▍ │ │ 18 │ 506467 │ ████████████████▊ │ │ 19 │ 520915 │ █████████████████▎ │ │ 20 │ 521665 │ █████████████████▍ │ │ 21 │ 542078 │ ██████████████████ │ │ 22 │ 493642 │ ████████████████▍ │ │ 23 │ 400397 │ █████████████▎ │ └────┴────────┴────────────────────┘ ``` ## transform Transforms a value according to the explicitly defined mapping of some elements to other ones. There are two variations of this function: ### transform(x, array_from, array_to, default) `x` – What to transform. `array_from` – Constant array of values to convert. `array_to` – Constant array of values to convert the values in ‘from’ to. `default` – Which value to use if ‘x’ isn't equal to any of the values in ‘from’. `array_from` and `array_to` must have equally many elements. Signature: For `x` equal to one of the elements in `array_from`, the function returns the corresponding element in `array_to`, i.e. the one at the same array index. Otherwise, it returns `default`. If multiple matching elements exist `array_from`, it returns the element corresponding to the first of them. `transform(T, Array(T), Array(U), U) -> U` `T` and `U` can be numeric, string, or Date or DateTime types. The same letter (T or U) means that types must be mutually compatible and not necessarily equal. For example, the first argument could have type `Int64`, while the second argument could have type `Array(UInt16)`. Example: ```sql SELECT transform(SearchEngineID, [2, 3], ['Yandex', 'Google'], 'Other') AS title, count() AS c FROM test.hits WHERE SearchEngineID != 0 GROUP BY title ORDER BY c DESC ``` ```text ┌─title─────┬──────c─┐ │ Yandex │ 498635 │ │ Google │ 229872 │ │ Other │ 104472 │ └───────────┴────────┘ ``` ### transform(x, array_from, array_to) Similar to the other variation but has no ‘default’ argument. In case no match can be found, `x` is returned. Example: ```sql SELECT transform(domain(Referer), ['yandex.ru', 'google.ru', 'vkontakte.ru'], ['www.yandex', 'example.com', 'vk.com']) AS s, count() AS c FROM test.hits GROUP BY domain(Referer) ORDER BY count() DESC LIMIT 10 ``` ```text ┌─s──────────────┬───────c─┐ │ │ 2906259 │ │ www.yandex │ 867767 │ │ ███████.ru │ 313599 │ │ mail.yandex.ru │ 107147 │ │ ██████.ru │ 100355 │ │ █████████.ru │ 65040 │ │ news.yandex.ru │ 64515 │ │ ██████.net │ 59141 │ │ example.com │ 57316 │ └────────────────┴─────────┘ ``` ## formatReadableDecimalSize Given a size (number of bytes), this function returns a readable, rounded size with suffix (KB, MB, etc.) as string. The opposite operations of this function are parseReadableSize, parseReadableSizeOrZero, and parseReadableSizeOrNull. ### Syntax ```sql formatReadableDecimalSize(x) ``` ### Example Query: ```sql SELECT arrayJoin([1, 1024, 1024*1024, 192851925]) AS filesize_bytes, formatReadableDecimalSize(filesize_bytes) AS filesize ``` Result: ```text ┌─filesize_bytes─┬─filesize───┐ │ 1 │ 1.00 B │ │ 1024 │ 1.02 KB │ │ 1048576 │ 1.05 MB │ │ 192851925 │ 192.85 MB │ └────────────────┴────────────┘ ``` ## formatReadableSize Given a size (number of bytes), this function returns a readable, rounded size with suffix (KiB, MiB, etc.) as string. The opposite operations of this function are parseReadableSize, parseReadableSizeOrZero, and parseReadableSizeOrNull. ### Syntax ```sql formatReadableSize(x) ``` Alias: `FORMAT_BYTES`. ### Example Query: ```sql SELECT arrayJoin([1, 1024, 1024*1024, 192851925]) AS filesize_bytes, formatReadableSize(filesize_bytes) AS filesize ``` Result: ```text ┌─filesize_bytes─┬─filesize───┐ │ 1 │ 1.00 B │ │ 1024 │ 1.00 KiB │ │ 1048576 │ 1.00 MiB │ │ 192851925 │ 183.92 MiB │ └────────────────┴────────────┘ ``` ## formatReadableQuantity Given a number, this function returns a rounded number with suffix (thousand, million, billion, etc.) as string. ### Syntax ```sql formatReadableQuantity(x) ``` ### Example Query: ```sql SELECT arrayJoin([1024, 1234 * 1000, (4567 * 1000) * 1000, 98765432101234]) AS number, formatReadableQuantity(number) AS number_for_humans ``` Result: ```text ┌─────────number─┬─number_for_humans─┐ │ 1024 │ 1.02 thousand │ │ 1234000 │ 1.23 million │ │ 4567000000 │ 4.57 billion │ │ 98765432101234 │ 98.77 trillion │ └────────────────┴───────────────────┘ ``` ## formatReadableTimeDelta Given a time interval (delta) in seconds, this function returns a time delta with year/month/day/hour/minute/second/millisecond/microsecond/nanosecond as string. ### Syntax ```sql formatReadableTimeDelta(column[, maximum_unit, minimum_unit]) ``` ### Arguments - `column`: A column with a numeric time delta. - `maximum_unit`: Optional. Maximum unit to show. - Acceptable values: `nanoseconds`, `microseconds`, `milliseconds`, `seconds`, `minutes`, `hours`, `days`, `months`, `years`. - Default value: `years`. - `minimum_unit`: Optional. Minimum unit to show. All smaller units are truncated. - Acceptable values: `nanoseconds`, `microseconds`, `milliseconds`, `seconds`, `minutes`, `hours`, `days`, `months`, `years`. - If explicitly specified value is bigger than `maximum_unit`, an exception will be thrown. - Default value: `seconds` if `maximum_unit` is `seconds` or bigger, `nanoseconds` otherwise. ### Example ```sql SELECT arrayJoin([100, 12345, 432546534]) AS elapsed, formatReadableTimeDelta(elapsed) AS time_delta ``` ```text ┌────elapsed─┬─time_delta ─────────────────────────────────────────────────────┐ │ 100 │ 1 minute and 40 seconds │ │ 12345 │ 3 hours, 25 minutes and 45 seconds │ │ 432546534 │ 13 years, 8 months, 17 days, 7 hours, 48 minutes and 54 seconds │ └────────────┴─────────────────────────────────────────────────────────────────┘ ``` ```sql SELECT arrayJoin([100, 12345, 432546534]) AS elapsed, formatReadableTimeDelta(elapsed, 'minutes') AS time_delta ``` ```text ┌────elapsed─┬─time_delta ─────────────────────────────────────────────────────┐ │ 100 │ 1 minute and 40 seconds │ │ 12345 │ 205 minutes and 45 seconds │ │ 432546534 │ 7209108 minutes and 54 seconds │ └────────────┴─────────────────────────────────────────────────────────────────┘ ``` ```sql SELECT arrayJoin([100, 12345, 432546534.00000006]) AS elapsed, formatReadableTimeDelta(elapsed, 'minutes', 'nanoseconds') AS time_delta ``` ```text ┌────────────elapsed─┬─time_delta─────────────────────────────────────┐ │ 100 │ 1 minute and 40 seconds │ │ 12345 │ 205 minutes and 45 seconds │ │ 432546534.00000006 │ 7209108 minutes, 54 seconds and 60 nanoseconds │ └────────────────────┴────────────────────────────────────────────────┘ ``` ## parseReadableSize Given a string containing a byte size and `B`, `KiB`, `KB`, `MiB`, `MB`, etc. as a unit (i.e. ISO/IEC 80000-13 or decimal byte unit), this function returns the corresponding number of bytes. If the function is unable to parse the input value, it throws an exception. The inverse operations of this function are formatReadableSize and formatReadableDecimalSize. ### Syntax ```sql formatReadableSize(x) ``` ### Arguments - `x` : Readable size with ISO/IEC 80000-13 or decimal byte unit (String). ### Returned value - Number of bytes, rounded up to the nearest integer (UInt64). ### Example ```sql SELECT arrayJoin(['1 B', '1 KiB', '3 MB', '5.314 KiB']) AS readable_sizes, parseReadableSize(readable_sizes) AS sizes ``` ```text ┌─readable_sizes─┬───sizes─┐ │ 1 B │ 1 │ │ 1 KiB │ 1024 │ │ 3 MB │ 3000000 │ │ 5.314 KiB │ 5442 │ └────────────────┴─────────┘ ``` ## parseReadableSizeOrNull Given a string containing a byte size and `B`, `KiB`, `KB`, `MiB`, `MB`, etc. as a unit (i.e. ISO/IEC 80000-13 or decimal byte unit), this function returns the corresponding number of bytes. If the function is unable to parse the input value, it returns `NULL`. The inverse operations of this function are formatReadableSize and formatReadableDecimalSize. ### Syntax ```sql parseReadableSizeOrNull(x) ``` ### Arguments - `x` : Readable size with ISO/IEC 80000-13 or decimal byte unit (String). ### Returned value - Number of bytes, rounded up to the nearest integer, or NULL if unable to parse the input (Nullable(UInt64)). ### Example ```sql SELECT arrayJoin(['1 B', '1 KiB', '3 MB', '5.314 KiB', 'invalid']) AS readable_sizes, parseReadableSizeOrNull(readable_sizes) AS sizes ``` ```text ┌─readable_sizes─┬───sizes─┐ │ 1 B │ 1 │ │ 1 KiB │ 1024 │ │ 3 MB │ 3000000 │ │ 5.314 KiB │ 5442 │ │ invalid │ ᴺᵁᴸᴸ │ └────────────────┴─────────┘ ``` ## parseReadableSizeOrZero Given a string containing a byte size and `B`, `KiB`, `KB`, `MiB`, `MB`, etc. as a unit (i.e. ISO/IEC 80000-13 or decimal byte unit), this function returns the corresponding number of bytes. If the function is unable to parse the input value, it returns `0`. The inverse operations of this function are formatReadableSize and formatReadableDecimalSize. ### Syntax ```sql parseReadableSizeOrZero(x) ``` ### Arguments - `x` : Readable size with ISO/IEC 80000-13 or decimal byte unit (String). ### Returned value - Number of bytes, rounded up to the nearest integer, or 0 if unable to parse the input (UInt64). ### Example ```sql SELECT arrayJoin(['1 B', '1 KiB', '3 MB', '5.314 KiB', 'invalid']) AS readable_sizes, parseReadableSizeOrZero(readable_sizes) AS sizes ``` ```text ┌─readable_sizes─┬───sizes─┐ │ 1 B │ 1 │ │ 1 KiB │ 1024 │ │ 3 MB │ 3000000 │ │ 5.314 KiB │ 5442 │ │ invalid │ 0 │ └────────────────┴─────────┘ ``` ## parseTimeDelta Parse a sequence of numbers followed by something resembling a time unit. ### Syntax ```sql parseTimeDelta(timestr) ``` ### Arguments - `timestr`: A sequence of numbers followed by something resembling a time unit. ### Returned value - A floating-point number with the number of seconds. ### Example ```sql SELECT parseTimeDelta('11s+22min') ``` ```text ┌─parseTimeDelta('11s+22min')─┐ │ 1331 │ └─────────────────────────────┘ ``` ```sql SELECT parseTimeDelta('1yr2mo') ``` ```text ┌─parseTimeDelta('1yr2mo')─┐ │ 36806400 │ └──────────────────────────┘ ``` ## least Returns the smaller value of a and b. ### Syntax ```sql least(a, b) ``` ## greatest Returns the larger value of a and b. ### Syntax ```sql greatest(a, b) ``` ## blockNumber Returns a monotonically increasing sequence number of the block containing the row. The returned block number is updated on a best-effort basis, i.e. it may not be fully accurate. ### Syntax ```sql blockNumber() ``` ### Returned value - Sequence number of the data block where the row is located. UInt64. ### Example Query: ```sql SELECT blockNumber() FROM ( SELECT * FROM system.numbers LIMIT 10 ) SETTINGS max_block_size = 2 ``` Result: ```result ┌─blockNumber()─┐ │ 7 │ │ 7 │ └───────────────┘ ┌─blockNumber()─┐ │ 8 │ │ 8 │ └───────────────┘ ┌─blockNumber()─┐ │ 9 │ │ 9 │ └───────────────┘ ┌─blockNumber()─┐ │ 10 │ │ 10 │ └───────────────┘ ┌─blockNumber()─┐ │ 11 │ │ 11 │ └───────────────┘ ``` ## rowNumberInBlock Returns for each block processed by `rowNumberInBlock` the number of the current row. The returned number starts for each block at 0. ### Syntax ```sql rowNumberInBlock() ``` ### Returned value - Ordinal number of the row in the data block starting from 0. UInt64. ### Example Query: ```sql SELECT rowNumberInBlock() FROM ( SELECT * FROM system.numbers_mt LIMIT 10 ) SETTINGS max_block_size = 2 ``` Result: ```result ┌─rowNumberInBlock()─┐ │ 0 │ │ 1 │ └────────────────────┘ ┌─rowNumberInBlock()─┐ │ 0 │ │ 1 │ └────────────────────┘ ┌─rowNumberInBlock()─┐ │ 0 │ │ 1 │ └────────────────────┘ ┌─rowNumberInBlock()─┐ │ 0 │ │ 1 │ └────────────────────┘ ┌─rowNumberInBlock()─┐ │ 0 │ │ 1 │ └────────────────────┘ ``` ## rowNumberInAllBlocks Returns a unique row number for each row processed by `rowNumberInAllBlocks`. The returned numbers start at 0. ### Syntax ```sql rowNumberInAllBlocks() ``` ### Returned value - Ordinal number of the row in the data block starting from 0. UInt64. ### Example Query: ```sql SELECT rowNumberInAllBlocks() FROM ( SELECT * FROM system.numbers_mt LIMIT 10 ) SETTINGS max_block_size = 2 ``` Result: ```result ┌─rowNumberInAllBlocks()─┐ │ 0 │ │ 1 │ └────────────────────────┘ ┌─rowNumberInAllBlocks()─┐ │ 4 │ │ 5 │ └────────────────────────┘ ┌─rowNumberInAllBlocks()─┐ │ 2 │ │ 3 │ └────────────────────────┘ ┌─rowNumberInAllBlocks()─┐ │ 6 │ │ 7 │ └────────────────────────┘ ┌─rowNumberInAllBlocks()─┐ │ 8 │ │ 9 │ └────────────────────────┘ ``` ## neighbor The window function that provides access to a row at a specified offset before or after the current row of a given column. ### Syntax ```sql neighbor(column, offset[, default_value]) ``` The result of the function depends on the affected data blocks and the order of data in the block. {% callout type="info" %} Only returns neighbor inside the currently processed data block. Because of this error-prone behavior the function is DEPRECATED, please use proper window functions instead. {% /callout %} The order of rows during calculation of `neighbor()` can differ from the order of rows returned to the user. To prevent that you can create a subquery with ORDER BY and call the function from outside the subquery. ### Arguments - `column`: A column name or scalar expression. - `offset`: The number of rows to look before or ahead of the current row in `column`. Int64. - `default_value`: Optional. The returned value if offset is beyond the block boundaries. Type of data blocks affected. ### Returned values - Value of `column` with `offset` distance from current row, if `offset` isn't outside the block boundaries. - The default value of `column` or `default_value` (if given), if `offset` is outside the block boundaries. {% callout type="info" %} The return type will be that of the data blocks affected or the default value type. {% /callout %} ### Example Query: ```sql SELECT number, neighbor(number, 2) FROM system.numbers LIMIT 10 ``` Result: ```text ┌─number─┬─neighbor(number, 2)─┐ │ 0 │ 2 │ │ 1 │ 3 │ │ 2 │ 4 │ │ 3 │ 5 │ │ 4 │ 6 │ │ 5 │ 7 │ │ 6 │ 8 │ │ 7 │ 9 │ │ 8 │ 0 │ │ 9 │ 0 │ └────────┴─────────────────────┘ ``` Query: ```sql SELECT number, neighbor(number, 2, 999) FROM system.numbers LIMIT 10 ``` Result: ```text ┌─number─┬─neighbor(number, 2, 999)─┐ │ 0 │ 2 │ │ 1 │ 3 │ │ 2 │ 4 │ │ 3 │ 5 │ │ 4 │ 6 │ │ 5 │ 7 │ │ 6 │ 8 │ │ 7 │ 9 │ │ 8 │ 999 │ │ 9 │ 999 │ └────────┴──────────────────────────┘ ``` This function can be used to compute year-over-year metric value: Query: ```sql WITH toDate('2018-01-01') AS start_date SELECT toStartOfMonth(start_date + (number * 32)) AS month, toInt32(month) % 100 AS money, neighbor(money, -12) AS prev_year, round(prev_year / money, 2) AS year_over_year FROM numbers(16) ``` Result: ```text ┌──────month─┬─money─┬─prev_year─┬─year_over_year─┐ │ 2018-01-01 │ 32 │ 0 │ 0 │ │ 2018-02-01 │ 63 │ 0 │ 0 │ │ 2018-03-01 │ 91 │ 0 │ 0 │ │ 2018-04-01 │ 22 │ 0 │ 0 │ │ 2018-05-01 │ 52 │ 0 │ 0 │ │ 2018-06-01 │ 83 │ 0 │ 0 │ │ 2018-07-01 │ 13 │ 0 │ 0 │ │ 2018-08-01 │ 44 │ 0 │ 0 │ │ 2018-09-01 │ 75 │ 0 │ 0 │ │ 2018-10-01 │ 5 │ 0 │ 0 │ │ 2018-11-01 │ 36 │ 0 │ 0 │ │ 2018-12-01 │ 66 │ 0 │ 0 │ │ 2019-01-01 │ 97 │ 32 │ 0.33 │ │ 2019-02-01 │ 28 │ 63 │ 2.25 │ │ 2019-03-01 │ 56 │ 91 │ 1.62 │ │ 2019-04-01 │ 87 │ 22 │ 0.25 │ └────────────┴───────┴───────────┴────────────────┘ ``` ## runningDifference Calculates the difference between two consecutive row values in the data block. Returns 0 for the first row, and for subsequent rows the difference to the previous row. {% callout type="info" %} Only returns differences inside the currently processed data block. Because of this error-prone behavior the function is DEPRECATED, please use proper window functions instead. {% /callout %} The result of the function depends on the affected data blocks and the order of data in the block. The order of rows during calculation of `runningDifference()` can differ from the order of rows returned to the user. To prevent that you can create a subquery with ORDER BY and call the function from outside the subquery. ### Syntax ```sql runningDifference(x) ``` ### Example Query: ```sql SELECT EventID, EventTime, runningDifference(EventTime) AS delta FROM ( SELECT EventID, EventTime FROM events WHERE EventDate = '2016-11-24' ORDER BY EventTime ASC LIMIT 5 ) ``` Result: ```text ┌─EventID─┬───────────EventTime─┬─delta─┐ │ 1106 │ 2016-11-24 00:00:04 │ 0 │ │ 1107 │ 2016-11-24 00:00:05 │ 1 │ │ 1108 │ 2016-11-24 00:00:05 │ 0 │ │ 1109 │ 2016-11-24 00:00:09 │ 4 │ │ 1110 │ 2016-11-24 00:00:10 │ 1 │ └─────────┴─────────────────────┴───────┘ ``` Please note that the block size affects the result. The internal state of `runningDifference` state is reset for each new block. Query: ```sql SELECT number, runningDifference(number + 1) AS diff FROM numbers(100000) WHERE diff != 1 ``` Result: ```text ┌─number─┬─diff─┐ │ 0 │ 0 │ └────────┴──────┘ ┌─number─┬─diff─┐ │ 65536 │ 0 │ └────────┴──────┘ ``` Query: ```sql set max_block_size=100000 -- default value is 65536! SELECT number, runningDifference(number + 1) AS diff FROM numbers(100000) WHERE diff != 1 ``` Result: ```text ┌─number─┬─diff─┐ │ 0 │ 0 │ └────────┴──────┘ ``` ## runningDifferenceStartingWithFirstValue {% callout type="info" %} This function is DEPRECATED (see the note for `runningDifference`). {% /callout %} Same as runningDifference, but returns the value of the first row as the value on the first row. ## runningConcurrency Calculates the number of concurrent events. Each event has a start time and an end time. The start time is included in the event, while the end time is excluded. Columns with a start time and an end time must be of the same data type. The function calculates the total number of active (concurrent) events for each event start time. {% /callout %}tip Events must be ordered by the start time in ascending order. If this requirement is violated the function raises an exception. Every data block is processed separately. If events from different data blocks overlap then they can't be processed correctly. {% /callout %} ### Syntax ```sql runningConcurrency(start, end) ``` ### Arguments - `start`: A column with the start time of events. Date, DateTime, or DateTime64. - `end`: A column with the end time of events. Date, DateTime, or DateTime64. ### Returned values - The number of concurrent events at each event start time. UInt32 ### Example Consider the table: ```text ┌──────start─┬────────end─┐ │ 2021-03-03 │ 2021-03-11 │ │ 2021-03-06 │ 2021-03-12 │ │ 2021-03-07 │ 2021-03-08 │ │ 2021-03-11 │ 2021-03-12 │ └────────────┴────────────┘ ``` Query: ```sql SELECT start, runningConcurrency(start, end) FROM example_table ``` Result: ```text ┌──────start─┬─runningConcurrency(start, end)─┐ │ 2021-03-03 │ 1 │ │ 2021-03-06 │ 2 │ │ 2021-03-07 │ 3 │ │ 2021-03-11 │ 2 │ └────────────┴────────────────────────────────┘ ``` ## MACNumToString Interprets a UInt64 number as a MAC address in big endian format. Returns the corresponding MAC address in format AA:BB:CC:DD:EE:FF (colon-separated numbers in hexadecimal form) as string. ### Syntax ```sql MACNumToString(num) ``` ## MACStringToNum The inverse function of MACNumToString. If the MAC address has an invalid format, it returns 0. ### Syntax ```sql MACStringToNum(s) ``` ## MACStringToOUI Given a MAC address in format AA:BB:CC:DD:EE:FF (colon-separated numbers in hexadecimal form), returns the first three octets as a UInt64 number. If the MAC address has an invalid format, it returns 0. ### Syntax ```sql MACStringToOUI(s) ``` ## getSizeOfEnumType Returns the number of fields in Enum. An exception is thrown if the type isn't `Enum`. ### Syntax ```sql getSizeOfEnumType(value) ``` ### Arguments - `value`: Value of type `Enum`. ### Returned values - The number of fields with `Enum` input values. ### Example ```sql SELECT getSizeOfEnumType( CAST('a' AS Enum8('a' = 1, 'b' = 2) ) ) AS x ``` ```text ┌─x─┐ │ 2 │ └───┘ ``` ## blockSerializedSize Returns the size on disk without considering compression. ```sql blockSerializedSize(value[, value[, ...]]) ``` ### Arguments - `value`: Any value. ### Returned values - The number of bytes that will be written to disk for block of values without compression. ### Example Query: ```sql SELECT blockSerializedSize(maxState(1)) as x ``` Result: ```text ┌─x─┐ │ 2 │ └───┘ ``` ## toColumnTypeName Returns the internal name of the data type that represents the value. ### Syntax ```sql toColumnTypeName(value) ``` ### Arguments - `value`: Any type of value. ### Returned values - The internal data type name used to represent `value`. ### Example Difference between `toTypeName` and `toColumnTypeName`: ```sql SELECT toTypeName(CAST('2018-01-01 01:02:03' AS DateTime)) ``` Result: ```text ┌─toTypeName(CAST('2018-01-01 01:02:03', 'DateTime'))─┐ │ DateTime │ └─────────────────────────────────────────────────────┘ ``` Query: ```sql SELECT toColumnTypeName(CAST('2018-01-01 01:02:03' AS DateTime)) ``` Result: ```text ┌─toColumnTypeName(CAST('2018-01-01 01:02:03', 'DateTime'))─┐ │ Const(UInt32) │ └───────────────────────────────────────────────────────────┘ ``` The example shows that the `DateTime` data type is internally stored as `Const(UInt32)`. ## dumpColumnStructure Outputs a detailed description of data structures in RAM ```sql dumpColumnStructure(value) ``` ### Arguments - `value`: Any type of value. ### Returned values - A description of the column structure used for representing `value`. ### Example ```sql SELECT dumpColumnStructure(CAST('2018-01-01 01:02:03', 'DateTime')) ``` ```text ┌─dumpColumnStructure(CAST('2018-01-01 01:02:03', 'DateTime'))─┐ │ DateTime, Const(size = 1, UInt32(size = 1)) │ └──────────────────────────────────────────────────────────────┘ ``` ## defaultValueOfArgumentType Returns the default value for the given data type. Does not include default values for custom columns set by the user. ### Syntax ```sql defaultValueOfArgumentType(expression) ``` ### Arguments - `expression`: Arbitrary type of value or an expression that results in a value of an arbitrary type. ### Returned values - `0` for numbers. - Empty string for strings. - `ᴺᵁᴸᴸ` for Nullable. ### Example Query: ```sql SELECT defaultValueOfArgumentType( CAST(1 AS Int8) ) ``` Result: ```text ┌─defaultValueOfArgumentType(CAST(1, 'Int8'))─┐ │ 0 │ └─────────────────────────────────────────────┘ ``` Query: ```sql SELECT defaultValueOfArgumentType( CAST(1 AS Nullable(Int8) ) ) ``` Result: ```text ┌─defaultValueOfArgumentType(CAST(1, 'Nullable(Int8)'))─┐ │ ᴺᵁᴸᴸ │ └───────────────────────────────────────────────────────┘ ``` ## defaultValueOfTypeName Returns the default value for the given type name. Does not include default values for custom columns set by the user. ```sql defaultValueOfTypeName(type) ``` ### Arguments - `type`: A string representing a type name. ### Returned values - `0` for numbers. - Empty string for strings. - `ᴺᵁᴸᴸ` for Nullable. ### Example Query: ```sql SELECT defaultValueOfTypeName('Int8') ``` Result: ```text ┌─defaultValueOfTypeName('Int8')─┐ │ 0 │ └────────────────────────────────┘ ``` Query: ```sql SELECT defaultValueOfTypeName('Nullable(Int8)') ``` Result: ```text ┌─defaultValueOfTypeName('Nullable(Int8)')─┐ │ ᴺᵁᴸᴸ │ └──────────────────────────────────────────┘ ``` ## replicate Creates an array with a single value. {% callout type="info" %} This function is used for the internal implementation of arrayJoin. {% /callout %} ### Syntax ```sql replicate(x, arr) ``` ### Arguments - `x`: The value to fill the result array with. - `arr`: An array. Array. ### Returned value An array of the lame length as `arr` filled with value `x`. Array. ### Example Query: ```sql SELECT replicate(1, ['a', 'b', 'c']) ``` Result: ```text ┌─replicate(1, ['a', 'b', 'c'])─┐ │ [1,1,1] │ └───────────────────────────────┘ ``` ## initializeAggregation Calculates the result of an aggregate function based on a single value. This function can be used to initialize aggregate functions with combinator -State. You can create states of aggregate functions and insert them to columns of type AggregateFunction or use initialized aggregates as default values. ### Syntax ```sql initializeAggregation (aggregate_function, arg1, arg2, ..., argN) ``` ### Arguments - `aggregate_function`: Name of the aggregation function to initialize. String. - `arg`: Arguments of aggregate function. ### Returned values - Result of aggregation for every row passed to the function. The return type is the same as the return type of function, that `initializeAggregation` takes as first argument. ### Example Query: ```sql SELECT uniqMerge(state) FROM (SELECT initializeAggregation('uniqState', number % 3) AS state FROM numbers(10000)) ``` Result: ```text ┌─uniqMerge(state)─┐ │ 3 │ └──────────────────┘ ``` Query: ```sql SELECT finalizeAggregation(state), toTypeName(state) FROM (SELECT initializeAggregation('sumState', number % 3) AS state FROM numbers(5)) ``` Result: ```text ┌─finalizeAggregation(state)─┬─toTypeName(state)─────────────┐ │ 0 │ AggregateFunction(sum, UInt8) │ │ 1 │ AggregateFunction(sum, UInt8) │ │ 2 │ AggregateFunction(sum, UInt8) │ │ 0 │ AggregateFunction(sum, UInt8) │ │ 1 │ AggregateFunction(sum, UInt8) │ └────────────────────────────┴───────────────────────────────┘ ``` Example with `AggregatingMergeTree` table engine and `AggregateFunction` column: ```tb {%title="metrics.datasource"%} SCHEMA > key UInt64, value AggregateFunction(sum, UInt64) DEFAULT initializeAggregation('sumState', toUInt64(0)) ENGINE "AggregatingMergeTree" ENGINE_SORTING_KEY "key" ``` ## finalizeAggregation Given a state of aggregate function, this function returns the result of aggregation (or finalized state when using a -State combinator). ### Syntax ```sql finalizeAggregation(state) ``` ### Arguments - `state`: State of aggregation. AggregateFunction. ### Returned values - Value/values that was aggregated. {% callout type="info" %} The return type is equal to that of any types which were aggregated. {% /callout %} ### Examples Query: ```sql SELECT finalizeAggregation(( SELECT countState(number) FROM numbers(10))) ``` Result: ```text ┌─finalizeAggregation(_subquery16)─┐ │ 10 │ └──────────────────────────────────┘ ``` Query: ```sql SELECT finalizeAggregation(( SELECT sumState(number) FROM numbers(10))) ``` Result: ```text ┌─finalizeAggregation(_subquery20)─┐ │ 45 │ └──────────────────────────────────┘ ``` Note that `NULL` values are ignored. Query: ```sql SELECT finalizeAggregation(arrayReduce('anyState', [NULL, 2, 3])) ``` Result: ```text ┌─finalizeAggregation(arrayReduce('anyState', [NULL, 2, 3]))─┐ │ 2 │ └────────────────────────────────────────────────────────────┘ ``` Combined example: Query: ```sql WITH initializeAggregation('sumState', number) AS one_row_sum_state SELECT number, finalizeAggregation(one_row_sum_state) AS one_row_sum, runningAccumulate(one_row_sum_state) AS cumulative_sum FROM numbers(10) ``` Result: ```text ┌─number─┬─one_row_sum─┬─cumulative_sum─┐ │ 0 │ 0 │ 0 │ │ 1 │ 1 │ 1 │ │ 2 │ 2 │ 3 │ │ 3 │ 3 │ 6 │ │ 4 │ 4 │ 10 │ │ 5 │ 5 │ 15 │ │ 6 │ 6 │ 21 │ │ 7 │ 7 │ 28 │ │ 8 │ 8 │ 36 │ │ 9 │ 9 │ 45 │ └────────┴─────────────┴────────────────┘ ``` ## runningAccumulate Accumulates the states of an aggregate function for each row of a data block. {% callout type="info" %} The state is reset for each new block of data. Because of this error-prone behavior the function is DEPRECATED, please use proper window functions instead. {% /callout %} ### Syntax ```sql runningAccumulate(agg_state[, grouping]) ``` ### Arguments - `agg_state`: State of the aggregate function. AggregateFunction. - `grouping`: Grouping key. Optional. The state of the function is reset if the `grouping` value is changed. It can be any of the supported data types for which the equality operator is defined. ### Returned value - Each resulting row contains a result of the aggregate function, accumulated for all the input rows from 0 to the current position. `runningAccumulate` resets states for each new data block or when the `grouping` value changes. Type depends on the aggregate function used. ### Examples Consider how you can use `runningAccumulate` to find the cumulative sum of numbers without and with grouping. Query: ```sql SELECT k, runningAccumulate(sum_k) AS res FROM (SELECT number as k, sumState(k) AS sum_k FROM numbers(10) GROUP BY k ORDER BY k) ``` Result: ```text ┌─k─┬─res─┐ │ 0 │ 0 │ │ 1 │ 1 │ │ 2 │ 3 │ │ 3 │ 6 │ │ 4 │ 10 │ │ 5 │ 15 │ │ 6 │ 21 │ │ 7 │ 28 │ │ 8 │ 36 │ │ 9 │ 45 │ └───┴─────┘ ``` The subquery generates `sumState` for every number from `0` to `9`. `sumState` returns the state of the sum function that contains the sum of a single number. The whole query does the following: 1. For the first row, `runningAccumulate` takes `sumState(0)` and returns `0`. 2. For the second row, the function merges `sumState(0)` and `sumState(1)` resulting in `sumState(0 + 1)`, and returns `1` as a result. 3. For the third row, the function merges `sumState(0 + 1)` and `sumState(2)` resulting in `sumState(0 + 1 + 2)`, and returns `3` as a result. 4. The actions are repeated until the block ends. The following example shows the `groupping` parameter usage: Query: ```sql SELECT grouping, item, runningAccumulate(state, grouping) AS res FROM ( SELECT toInt8(number / 4) AS grouping, number AS item, sumState(number) AS state FROM numbers(15) GROUP BY item ORDER BY item ASC ) ``` Result: ```text ┌─grouping─┬─item─┬─res─┐ │ 0 │ 0 │ 0 │ │ 0 │ 1 │ 1 │ │ 0 │ 2 │ 3 │ │ 0 │ 3 │ 6 │ │ 1 │ 4 │ 4 │ │ 1 │ 5 │ 9 │ │ 1 │ 6 │ 15 │ │ 1 │ 7 │ 22 │ │ 2 │ 8 │ 8 │ │ 2 │ 9 │ 17 │ │ 2 │ 10 │ 27 │ │ 2 │ 11 │ 38 │ │ 3 │ 12 │ 12 │ │ 3 │ 13 │ 25 │ │ 3 │ 14 │ 39 │ └──────────┴──────┴─────┘ ``` As you can see, `runningAccumulate` merges states for each group of rows separately. ## throwIf Throw an exception if argument `x` is true. ### Syntax ```sql throwIf(x[, message[, error_code]]) ``` ### Arguments - `x` - the condition to check. - `message` - a constant string providing a custom error message. Optional. - `error_code` - A constant integer providing a custom error code. Optional. To use the `error_code` argument, configuration parameter `allow_custom_error_code_in_throwif` must be enabled. ### Example ```sql SELECT throwIf(number = 3, 'Too many') FROM numbers(10) ``` Result: ```text ↙ Progress: 0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) Received exception from server (version 19.14.1): Code: 395. DB::Exception: Received from localhost:9000. DB::Exception: Too many. ``` ## identity Returns its argument. Intended for debugging and testing. Allows to cancel using index, and get the query performance of a full scan. When the query is analyzed for possible use of an index, the analyzer ignores everything in `identity` functions. Also disables constant folding. ### Syntax ```sql identity(x) ``` ### Example Query: ```sql SELECT identity(42) ``` Result: ```text ┌─identity(42)─┐ │ 42 │ └──────────────┘ ``` ## isDecimalOverflow Checks whether the Decimal value is outside its precision or outside the specified precision. ### Syntax ```sql isDecimalOverflow(d, [p]) ``` ### Arguments - `d`: value. Decimal. - `p`: precision. Optional. If omitted, the initial precision of the first argument is used. This parameter can be helpful to migrate data from/to another database or file. UInt8. ### Returned values - `1`: Decimal value has more digits then allowed by its precision, - `0`: Decimal value satisfies the specified precision. ### Example Query: ```sql SELECT isDecimalOverflow(toDecimal32(1000000000, 0), 9), isDecimalOverflow(toDecimal32(1000000000, 0)), isDecimalOverflow(toDecimal32(-1000000000, 0), 9), isDecimalOverflow(toDecimal32(-1000000000, 0)) ``` Result: ```text 1 1 1 1 ``` ## countDigits Returns number of decimal digits need to represent a value. ### Syntax ```sql countDigits(x) ``` ### Arguments - `x`: Int or Decimal value. ### Returned value - Number of digits. UInt8. {% callout type="info" %} For `Decimal` values takes into account their scales: calculates result over underlying integer type which is `(value * scale)`. For example: `countDigits(42) = 2`, `countDigits(42.000) = 5`, `countDigits(0.04200) = 4`. I.e. you may check decimal overflow for `Decimal64` with `countDecimal(x) > 18`. It's a slow variant of isDecimalOverflow. {% /callout %} ### Example Query: ```sql SELECT countDigits(toDecimal32(1, 9)), countDigits(toDecimal32(-1, 9)), countDigits(toDecimal64(1, 18)), countDigits(toDecimal64(-1, 18)), countDigits(toDecimal128(1, 38)), countDigits(toDecimal128(-1, 38)) ``` Result: ```text 10 10 19 19 39 39 ``` ## errorCodeToName - The textual name of an error code. LowCardinality(String). ### Syntax ```sql errorCodeToName(1) ``` Result: ```text UNSUPPORTED_METHOD ``` ## enabledProfiles Returns settings profiles, assigned to the current user both explicitly and implicitly. Explicitly assigned profiles are the same as returned by the currentProfiles function. Implicitly assigned profiles include parent profiles of other assigned profiles, profiles assigned via granted roles, profiles assigned via their own settings, and the main default profile (see the `default_profile` section in the main server configuration file). ### Syntax ```sql enabledProfiles() ``` ### Returned value - List of the enabled settings profiles. Array(String). ## minSampleSizeConversion Calculates minimum required sample size for an A/B test comparing conversions (proportions) in two samples. ### Syntax ```sql minSampleSizeConversion(baseline, mde, power, alpha) ``` Uses the formula described in this article. Assumes equal sizes of treatment and control groups. Returns the sample size required for one group (i.e. the sample size required for the whole experiment is twice the returned value). ### Arguments - `baseline`: Baseline conversion. Float. - `mde`: Minimum detectable effect (MDE) as percentage points (e.g. for a baseline conversion 0.25 the MDE 0.03 means an expected change to 0.25 ± 0.03). Float. - `power`: Required statistical power of a test (1 - probability of Type II error). Float. - `alpha`: Required significance level of a test (probability of Type I error). Float. ### Returned value A named Tuple with 3 elements: - `"minimum_sample_size"`: Required sample size. Float64. - `"detect_range_lower"`: Lower bound of the range of values not detectable with the returned required sample size (i.e. all values less than or equal to `"detect_range_lower"` are detectable with the provided `alpha` and `power`). Calculated as `baseline - mde`. Float64. - `"detect_range_upper"`: Upper bound of the range of values not detectable with the returned required sample size (i.e. all values greater than or equal to `"detect_range_upper"` are detectable with the provided `alpha` and `power`). Calculated as `baseline + mde`. Float64. ### Example The following query calculates the required sample size for an A/B test with baseline conversion of 25%, MDE of 3%, significance level of 5%, and the desired statistical power of 80%: ```sql SELECT minSampleSizeConversion(0.25, 0.03, 0.80, 0.05) AS sample_size ``` Result: ```text ┌─sample_size───────────────────┐ │ (3396.077603219163,0.22,0.28) │ └───────────────────────────────┘ ``` ## minSampleSizeContinuous Calculates minimum required sample size for an A/B test comparing means of a continuous metric in two samples. ### Syntax ```sql minSampleSizeContinous(baseline, sigma, mde, power, alpha) ``` Alias: `minSampleSizeContinous` Uses the formula described in this article. Assumes equal sizes of treatment and control groups. Returns the required sample size for one group (i.e. the sample size required for the whole experiment is twice the returned value). Also assumes equal variance of the test metric in treatment and control groups. ### Arguments - `baseline`: Baseline value of a metric. Integer or Float. - `sigma`: Baseline standard deviation of a metric. Integer or Float. - `mde`: Minimum detectable effect (MDE) as percentage of the baseline value (e.g. for a baseline value 112.25 the MDE 0.03 means an expected change to 112.25 ± 112.25\*0.03). Integer or Float. - `power`: Required statistical power of a test (1 - probability of Type II error). Integer or Float. - `alpha`: Required significance level of a test (probability of Type I error). Integer or Float. ### Returned value A named Tuple with 3 elements: - `"minimum_sample_size"`: Required sample size. Float64. - `"detect_range_lower"`: Lower bound of the range of values not detectable with the returned required sample size (i.e. all values less than or equal to `"detect_range_lower"` are detectable with the provided `alpha` and `power`). Calculated as `baseline * (1 - mde)`. Float64. - `"detect_range_upper"`: Upper bound of the range of values not detectable with the returned required sample size (i.e. all values greater than or equal to `"detect_range_upper"` are detectable with the provided `alpha` and `power`). Calculated as `baseline * (1 + mde)`. Float64. ### Example The following query calculates the required sample size for an A/B test on a metric with baseline value of 112.25, standard deviation of 21.1, MDE of 3%, significance level of 5%, and the desired statistical power of 80%: ```sql SELECT minSampleSizeContinous(112.25, 21.1, 0.03, 0.80, 0.05) AS sample_size ``` Result: ```text ┌─sample_size───────────────────────────┐ │ (616.2931945826209,108.8825,115.6175) │ └───────────────────────────────────────┘ ``` ## connectionId Retrieves the connection ID of the client that submitted the current query and returns it as a UInt64 integer. ### Syntax ```sql connectionId() ``` Alias: `connection_id`. ### Parameters None. ### Returned value The current connection ID. UInt64. ### Implementation details This function is most useful in debugging scenarios or for internal purposes within the MySQL handler. It was created for compatibility with MySQL's `CONNECTION_ID` function It isn't typically used in production queries. ### Example Query: ```sql SELECT connectionId() ``` ```result 0 ``` ## lowCardinalityIndices Returns the position of a value in the dictionary of a LowCardinality column. Positions start at 1. Since LowCardinality have per-part dictionaries, this function may return different positions for the same value in different parts. ### Syntax ```sql lowCardinalityIndices(col) ``` ### Arguments - `col`: a low cardinality column. LowCardinality. ### Returned value - The position of the value in the dictionary of the current part. UInt64. ### Example ```tb {% title="lc_s.datasource" %} SCHEMA > s LowCardinality(String) ENGINE "MergeTree" ``` ```bash tb push datasources/lc_s.datasource ## create two parts: echo 'ab\ncd\nab\nab\ndf' > lc.csv tb datasource append lc_s lc.csv echo 'ef\ncd\nab\ncd\nef' > lc.csv tb datasource append lc_s lc.csv tb sql "SELECT s, lowCardinalityIndices(s) FROM lc_s" --------------------------------- | s | lowCardinalityIndices(s) | --------------------------------- | ab | 1 | | cd | 2 | | ab | 1 | | ab | 1 | | df | 3 | | ef | 1 | | cd | 2 | | ab | 3 | | cd | 2 | | ef | 1 | --------------------------------- ``` ## lowCardinalityKeys Returns the dictionary values of a LowCardinality column. If the block is smaller or larger than the dictionary size, the result will be truncated or extended with default values. Since LowCardinality have per-part dictionaries, this function may return different dictionary values in different parts. ### Syntax ```sql lowCardinalityIndices(col) ``` ### Arguments - `col`: a low cardinality column. LowCardinality. ### Returned value - The dictionary keys. UInt64. ### Example ```tb {% title="lc_s.datasource" %} SCHEMA > s LowCardinality(String) ENGINE "MergeTree" ``` ```bash tb push datasources/lc_s.datasource ## create two parts: echo 'ab\ncd\nab\nab\ndf' > lc.csv tb datasource append lc_s lc.csv echo 'ef\ncd\nab\ncd\nef' > lc.csv tb datasource append lc_s lc.csv tb sql "select s, lowCardinalityKeys(s) from lc_s" ------------------------------ | s | lowCardinalityKeys(s) | ------------------------------ | ab | | | cd | ab | | ab | cd | | ab | df | | df | | | ef | | | cd | ef | | ab | cd | | cd | ab | | ef | | ------------------------------ ``` ## getSubcolumn Takes a table expression or identifier and constant string with the name of the sub-column, and returns the requested sub-column extracted from the expression. ### Syntax ```sql getSubcolumn(col_name, subcol_name) ``` ### Arguments - `col_name`: Table expression or identifier. Expression, Identifier. - `subcol_name`: The name of the sub-column. String. ### Returned value - Returns the extracted sub-column. ### Example Query: ```sql SELECT getSubcolumn(arr, 'subcolumn1'), getSubcolumn(arr, 'subcolumn2') FROM ( select c1::Array(Tuple(subcolumn1 UInt32, subcolumn2 String)) as arr from values(([(1, 'Hello'), (2, 'World')]), ([(3, 'This'), (4, 'is'), (5, 'subcolumn')])) ) ``` Result: ```result ┌─getSubcolumn(arr, 'subcolumn1')─┬─getSubcolumn(arr, 'subcolumn2')─┐ │ [1,2] │ ['Hello','World'] │ │ [3,4,5] │ ['This','is','subcolumn'] │ └─────────────────────────────────┴─────────────────────────────────┘ ``` ## getTypeSerializationStreams Enumerates stream paths of a data type. {% callout type="info" %} This function is intended for use by developers. {% /callout %} ### Syntax ```sql getTypeSerializationStreams(col) ``` ### Arguments - `col`: Column or string representation of a data-type from which the data type will be detected. ### Returned value - Returns an array with all the serialization sub-stream paths.Array(String). ### Examples Query: ```sql SELECT getTypeSerializationStreams(tuple('a', 1, 'b', 2)) ``` Result: ```result ┌─getTypeSerializationStreams(('a', 1, 'b', 2))─────────────────────────────────────────────────────────────────────────┐ 1. │ ['{TupleElement(1), Regular}','{TupleElement(2), Regular}','{TupleElement(3), Regular}','{TupleElement(4), Regular}'] │ └───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ ``` Query: ```sql SELECT getTypeSerializationStreams('Map(String, Int64)') ``` Result: ```result ┌─getTypeSerializationStreams('Map(String, Int64)')────────────────────────────────────────────────────────────────┐ 1. │ ['{ArraySizes}','{ArrayElements, TupleElement(keys), Regular}','{ArrayElements, TupleElement(values), Regular}'] │ └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ ```