This is an automated email from the ASF dual-hosted git repository.
victoria pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/druid.git
The following commit(s) were added to refs/heads/master by this push:
new 48a758ee083 [docs] reverting changes for sql-functions.md (#17019)
48a758ee083 is described below
commit 48a758ee083f26105f47971802f1f86c19f635f4
Author: Edgar Melendrez <[email protected]>
AuthorDate: Fri Sep 6 16:07:32 2024 -0700
[docs] reverting changes for sql-functions.md (#17019)
---
docs/querying/sql-functions.md | 2206 ++++++----------------------------------
1 file changed, 287 insertions(+), 1919 deletions(-)
diff --git a/docs/querying/sql-functions.md b/docs/querying/sql-functions.md
index 4d8470cf822..5cdcbb25495 100644
--- a/docs/querying/sql-functions.md
+++ b/docs/querying/sql-functions.md
@@ -27,70 +27,26 @@ sidebar_label: "All functions"
Apache Druid supports two query languages: Druid SQL and [native
queries](querying.md).
This document describes the SQL language.
:::
-<!-- The **Learn More** at the end of each function section provides further
documentation. -->
-This page provides a reference of Apache Druid® SQL functions in
alphabetical order. For more details on a function, refer to the following:
-* [Aggregation functions](sql-aggregations.md)
-* [Array functions](sql-array-functions.md)
-* [JSON functions](sql-json-functions.md)
-* [Multi-value string functions](sql-multivalue-string-functions.md)
-* [Scalar functions](sql-scalar.md)
-* [Window functions](sql-window-functions.md)
-
-The examples on this page use the following example datasources:
-* `flight-carriers` using `FlightCarrierOnTime (1 month)`
-* `taxi-trips` using `NYC Taxi cabs (3 files)`
-* `kttm` using `KoalasToTheMax one day`
-## ABS
-
-Calculates the absolute value of a numeric expression.
-
-* **Syntax:** `ABS(<NUMERIC>)`
-* **Function type:** Scalar, numeric
-<details><summary>Example</summary>
+This page provides a reference of all Druid SQL functions in alphabetical
order.
+Click the linked function type for documentation on a particular function.
-The following example applies the ABS function to the `ArrDelay` column from
the `flight-carriers` datasource.
+## ABS
-```sql
-SELECT
- "ArrDelay" AS "arrival_delay",
- ABS("ArrDelay") AS "absolute_arrival_delay"
-FROM "flight-carriers"
-WHERE "ArrDelay" < 0
-LIMIT 1
-```
-Returns the following:
+`ABS(<NUMERIC>)`
-| `arrival_delay` | `absolute_arrival_delay` |
-| -- | -- |
-| `-27` | `27` |
-</details>
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-[Learn more](sql-scalar.md#numeric-functions)
+Calculates the absolute value of a numeric expression.
## ACOS
-Calculates the arc cosine (arccosine) of a numeric expression.
-
-* **Syntax:** `ACOS(expr)`
-* **Function type:** Scalar, numeric
-
-<details><summary>Example</summary>
+`ACOS(<NUMERIC>)`
-The following example calculates the arc cosine of `0`.
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-```sql
-SELECT ACOS(0) AS "arc_cosine"
-```
-Returns the following:
-
-| `arc_cosine` |
-| -- |
-| `1.5707963267948966` |
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
+Calculates the arc cosine of a numeric expression.
## ANY_VALUE
@@ -108,7 +64,6 @@ Returns any value of the specified expression.
Counts distinct values of a regular column or a prebuilt sketch column.
-## APPROX_COUNT_DISTINCT_BUILTIN
`APPROX_COUNT_DISTINCT_BUILTIN(expr)`
**Function type:** [Aggregation](sql-aggregations.md)
@@ -296,72 +251,27 @@ Joins all elements of `arr` by the delimiter specified by
`str`.
## ASIN
-Calculates the arc sine (arcsine) of a numeric expression.
-
-* **Syntax:** `ASIN(expr)`
-* **Function type:** Scalar, numeric
-
-<details><summary>Example</summary>
+`ASIN(<NUMERIC>)`
-The following example calculates the arc sine of `1`.
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-```sql
-SELECT ASIN(1) AS "arc_sine"
-```
-Returns the following:
-
-| `arc_sine` |
-| -- |
-| `1.5707963267948966` |
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
+Calculates the arc sine of a numeric expression.
## ATAN
-Calculates the arc tangent (arctangent) of a numeric expression.
-
-* **Syntax:** `ATAN(expr)`
-* **Function type:** Scalar, numeric
-
-<details><summary>Example</summary>
+`ATAN(<NUMERIC>)`
-The following example calculates the arc tangent of `1`.
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-```sql
-SELECT ATAN(1) AS "arc_tangent"
-```
-Returns the following:
-
-| `arc_tangent` |
-| -- |
-| `0.7853981633974483` |
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
+Calculates the arc tangent of a numeric expression.
## ATAN2
-Calculates the arc tangent (arctangent) of a specified x and y coordinate.
-
-* **Syntax:** `ATAN2(x, y)`
-* **Function type:** Scalar, numeric
-
-<details><summary>Example</summary>
+`ATAN2(<NUMERIC>, <NUMERIC>)`
-The following example calculates the arc tangent of the coordinate `(1, -1)`
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-```sql
-SELECT ATAN2(1,-1) AS "arc_tangent_2"
-```
-Returns the following:
-
-| `arc_tangent_2` |
-| -- |
-| `2.356194490192345` |
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
+Calculates the arc tangent of the two arguments.
## AVG
@@ -397,188 +307,67 @@ Performs a bitwise XOR operation on all input values.
## BITWISE_AND
-Returns the bitwise AND between two expressions: `expr1 & expr2`.
-
-* **Syntax:** `BITWISE_AND(expr1, expr2)`
-* **Function type:** Scalar, numeric
-
-<details><summary>Example</summary>
+`BITWISE_AND(expr1, expr2)`
-The following example performs the bitwise AND operation `12 & 10`.
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-```sql
-SELECT BITWISE_AND(12, 10) AS "bitwise_and"
-```
-Returns the following:
-
-| `bitwise_and` |
-| -- |
-| 8 |
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
+Returns the bitwise AND between the two expressions, that is, `expr1 & expr2`.
## BITWISE_COMPLEMENT
-Returns the bitwise complement (bitwise not) for the expression: `~expr`.
-
-* **Syntax:** `BITWISE_COMPLEMENT(expr)`
-* **Function type:** Scalar, numeric
-
-<details><summary>Example</summary>
+`BITWISE_COMPLEMENT(expr)`
-The following example performs the bitwise complement operation `~12`.
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-```sql
-SELECT BITWISE_COMPLEMENT(12) AS "bitwise_complement"
-```
-Returns the following:
-
-| `bitwise_complement` |
-| -- |
-| -13 |
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
+Returns the bitwise NOT for the expression, that is, `~expr`.
## BITWISE_CONVERT_DOUBLE_TO_LONG_BITS
-Converts the bits of an IEEE 754 floating-point double value to long.
-
-* **Syntax:**`BITWISE_CONVERT_DOUBLE_TO_LONG_BITS(expr)`
-* **Function type:** Scalar, numeric
-
-<details><summary>Example</summary>
+`BITWISE_CONVERT_DOUBLE_TO_LONG_BITS(expr)`
-The following example returns the IEEE 754 floating-point double
representation of `255` as a long.
-
-```sql
-SELECT BITWISE_CONVERT_DOUBLE_TO_LONG_BITS(255) AS "ieee_754_double_to_long"
-```
-Returns the following:
-
-| `ieee_754_double_to_long` |
-| -- |
-| `4643176031446892544` |
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
+Converts the bits of an IEEE 754 floating-point double value to a long.
## BITWISE_CONVERT_LONG_BITS_TO_DOUBLE
-Converts a long to the IEEE 754 floating-point double specified by the bits
stored in the long.
-
-* **Syntax:**`BITWISE_CONVERT_LONG_BITS_TO_DOUBLE(expr)`
-* **Function type:** Scalar, numeric
-
-<details><summary>Example</summary>
-
-The following example returns the long representation of `4643176031446892544`
as an IEEE 754 floating-point double.
-
-```sql
-SELECT BITWISE_CONVERT_LONG_BITS_TO_DOUBLE(4643176031446892544) AS
"long_to_ieee_754_double"
-```
-Returns the following:
+`BITWISE_CONVERT_LONG_BITS_TO_DOUBLE(expr)`
-| `long_to_ieee_754_double` |
-| -- |
-| `255` |
-</details>
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-[Learn more](sql-scalar.md#numeric-functions)
+Converts a long to the IEEE 754 floating-point double specified by the bits
stored in the long.
## BITWISE_OR
-Returns the bitwise OR between the two expressions: `expr1 | expr2`.
-
-* **Syntax:** `BITWISE_OR(expr1, expr2)`
-* **Function type:** Scalar, numeric
-
-<details><summary>Example</summary>
-
-The following example performs the bitwise OR operation `12 | 10`.
+`BITWISE_OR(expr1, expr2)`
-```sql
-SELECT BITWISE_OR(12, 10) AS "bitwise_or"
-```
-Returns the following:
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-| `bitwise_or` |
-| -- |
-| `14` |
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
+Returns the bitwise OR between the two expressions, that is, `expr1 | expr2`.
## BITWISE_SHIFT_LEFT
-Returns the bitwise left shift by x positions of an expr: `expr << x`.
-
-* **Syntax:** `BITWISE_SHIFT_LEFT(expr, x)`
-* **Function type:** Scalar, numeric
-
-<details><summary>Example</summary>
-
-The following example performs the bitwise SHIFT operation `2 << 3`.
+`BITWISE_SHIFT_LEFT(expr1, expr2)`
-```sql
-SELECT BITWISE_SHIFT_LEFT(2, 3) AS "bitwise_shift_left"
-```
-Returns the following:
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-| `bitwise_shift_left` |
-| -- |
-| `16` |
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
+Returns a bitwise left shift of expr1, that is, `expr1 << expr2`.
## BITWISE_SHIFT_RIGHT
-Returns the bitwise right shift by x positions of an expr: `expr >> x`.
-
-* **Syntax:** `BITWISE_SHIFT_RIGHT(expr, x)`
-* **Function type:** Scalar, numeric
-
-<details><summary>Example</summary>
-
-The following example performs the bitwise SHIFT operation `16 >> 3`.
+`BITWISE_SHIFT_RIGHT(expr1, expr2)`
-```sql
-SELECT BITWISE_SHIFT_RIGHT(16, 3) AS "bitwise_shift_right"
-```
-Returns the following:
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-| `bitwise_shift_right` |
-| -- |
-| `2` |
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
+Returns a bitwise right shift of expr1, that is, `expr1 >> expr2`.
## BITWISE_XOR
-Returns the bitwise exclusive OR between the two expressions: `expr1 ^ expr2`.
-
-* **Syntax:** `BITWISE_XOR(expr1, expr2)`
-* **Function type:** Scalar, numeric
-
-<details><summary>Example</summary>
-
-The following example performs the bitwise XOR operation `12 ^ 10`.
+`BITWISE_XOR(expr1, expr2)`
-```sql
-SELECT BITWISE_XOR(12, 10) AS "bitwise_xor"
-```
-Returns the following:
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-| `bitwise_xor` |
-| -- |
-| `6` |
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
+Returns the bitwise exclusive OR between the two expressions, that is, `expr1
^ expr2`.
## BLOOM_FILTER
@@ -598,30 +387,11 @@ Returns true if the expression is contained in a
Base64-serialized Bloom filter.
## BTRIM
-Trims characters from both the leading and trailing ends of an expression.
Defaults `chars` to a space if none is provided.
-
-* **Syntax:** `BTRIM(expr[, chars])`
-* **Function type:** Scalar, string
-
-<details><summary>Example</summary>
-
-The following example trims the `_` characters from both ends of the string
expression.
+`BTRIM(<CHARACTER>, [<CHARACTER>])`
-```sql
-SELECT
- '___abc___' AS "original_string",
- BTRIM('___abc___', '_') AS "trim_both_ends"
-```
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-Returns the following:
-
-| `original_string` | `trim_both_ends` |
-| -- | -- |
-| `___abc___` | `abc` |
-
-</details>
-
-[Learn more](sql-scalar.md#string-functions)
+Trims characters from both the leading and trailing ends of an expression.
## CASE
@@ -641,77 +411,35 @@ Converts a value into the specified data type.
## CEIL (date and time)
-Rounds up a timestamp by a given time unit.
-
-* **Syntax:** `CEIL(timestamp_expr TO unit>)`
-* **Function type:** Scalar, date and time
-
-<details><summary>Example</summary>
-
-The following example rounds up the `__time` column from the `taxi-trips`
datasource to the nearest year.
-
-```sql
-SELECT
- "__time" AS "original_time",
- CEIL("__time" TO YEAR) AS "ceiling"
-FROM "taxi-trips"
-LIMIT 1
-```
+`CEIL(<TIMESTAMP> TO <TIME_UNIT>)`
-Returns the following:
+**Function type:** [Scalar, date and
time](sql-scalar.md#date-and-time-functions)
-| `original_time` | `ceiling` |
-| -- | -- |
-| `2013-08-01T08:14:37.000Z` | `2014-01-01T00:00:00.000Z` |
-
-</details>
-
-[Learn more](sql-scalar.md#date-and-time-functions)
+Rounds up a timestamp by a given time unit.
## CEIL (numeric)
-Calculates the smallest integer value greater than or equal to the numeric
expression.
-* **Syntax:** `CEIL(<NUMERIC>)`
-* **Function type:** Scalar, numeric
-
-<details><summary>Example</summary>
-
-The following example applies the CEIL function to the `fare_amount` column
from the `taxi-trips` datasource.
+`CEIL(<NUMERIC>)`
-```sql
-SELECT
- "fare_amount" AS "fare_amount",
- CEIL("fare_amount") AS "ceiling_fare_amount"
-FROM "taxi-trips"
-LIMIT 1
-```
-Returns the following:
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-| `fare_amount` | `ceiling_fare_amount` |
-| -- | -- |
-| `21.25` | `22` |
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
+Calculates the smallest integer value greater than or equal to the numeric
expression.
## CHAR_LENGTH
-Alias for [`LENGTH`](#length).
+`CHAR_LENGTH(expr)`
-* **Syntax:** `CHAR_LENGTH(expr)`
-* **Function type:** Scalar, string
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-[Learn more](sql-scalar.md#string-functions)
+Alias for [`LENGTH`](#length).
## CHARACTER_LENGTH
-Alias for [`LENGTH`](#length).
-
-* **Syntax:** `CHARACTER_LENGTH(expr)`
-* **Function type:** Scalar, string
+`CHARACTER_LENGTH(expr)`
-[Learn more](sql-scalar.md#string-functions)
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
+Alias for [`LENGTH`](#length).
## COALESCE
@@ -723,110 +451,35 @@ Returns the first non-null value.
## CONCAT
-Concatenates a list of expressions.
-
-* **Syntax:** `CONCAT(expr[, expr,...])`
-* **Function type:** Scalar, string
-
-<details><summary>Example</summary>
-
-The following example concatenates the `OriginCityName` column from
`flight-carriers`, the string ` to `, and the `DestCityName` column from
`flight-carriers`.
-
-```sql
-SELECT
- "OriginCityName" AS "origin_city",
- "DestCityName" AS "destination_city",
- CONCAT("OriginCityName", ' to ', "DestCityName") AS
"concatenate_flight_details"
-FROM "flight-carriers"
-LIMIT 1
-```
-
-Returns the following:
-
-| `origin_city` | `destination_city` | `concatenate_flight_details` |
-| -- | -- | -- |
-| `San Juan, PR` | `Washington, DC` | `San Juan, PR to Washington, DC` |
+`CONCAT(expr, expr...)`
-</details>
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-[Learn more](sql-scalar.md#string-functions)
+Concatenates a list of expressions.
## CONTAINS_STRING
-Returns true if `str` is a substring of `expr`, case-sensitive. Otherwise,
returns false.
-
-* **Syntax:** `CONTAINS_STRING(expr, str)`
-* **Function type:** Scalar, string
-
-<details><summary>Example</summary>
-
-The following example returns true if the `OriginCityName` column from the
`flight-carriers` datasource contains the substring `San`.
-
-```sql
-SELECT
- "OriginCityName" AS "origin_city",
- CONTAINS_STRING("OriginCityName", 'San') AS "contains_string"
-FROM "flight-carriers"
-LIMIT 2
-```
-
-Returns the following:
-
-| `origin_city` | `contains_string` |
-| -- | -- |
-| `San Juan, PR` | `true` |
-| `Boston, MA` | `false` |
-
-</details>
+`CONTAINS_STRING(<CHARACTER>, <CHARACTER>)`
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-[Learn more](sql-scalar.md#string-functions)
+Finds whether a string is in a given expression, case-sensitive.
## COS
-Calculates the trigonometric cosine of an angle expressed in radians.
-
-* **Syntax:** `COS(expr)`
-* **Function type:** Scalar, numeric
-
-<details><summary>Example</summary>
-
-The following example calculates the cosine of angle `PI/3` radians.
+`COS(<NUMERIC>)`
-```sql
-SELECT COS(PI / 3) AS "cosine"
-```
-Returns the following:
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-| `cosine` |
-| -- |
-| `0.5000000000000001` |
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
+Calculates the trigonometric cosine of an angle expressed in radians.
## COT
-Calculates the trigonometric cotangent of an angle expressed in radians.
-
-* **Syntax:** `COT(expr)`
-* **Function type:** Scalar, numeric
+`COT(<NUMERIC>)`
-<details><summary>Example</summary>
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-The following example calculates the cotangent of angle `PI/3` radians.
-
-```sql
-SELECT COT(PI / 3) AS "cotangent"
-```
-Returns the following:
-
-| `cotangent` |
-| -- |
-| `0.577350269189626` |
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
+Calculates the trigonometric cotangent of an angle expressed in radians.
## COUNT
@@ -848,84 +501,27 @@ Returns the cumulative distribution of the current row
within the window calcula
## CURRENT_DATE
-Returns the current date in UTC time, unless you specify a different timezone
in the query context.
-
-* **Syntax:** `CURRENT_DATE`
-* **Function type:** Scalar, date and time
-
-<details><summary>Example</summary>
-
-The following example returns the current date.
-
-```sql
-SELECT CURRENT_DATE AS "current_date"
-```
+`CURRENT_DATE`
-Returns the following:
+**Function type:** [Scalar, date and
time](sql-scalar.md#date-and-time-functions)
-| `current_date` |
-| -- |
-| `2024-08-14T00:00:00.000Z `|
-
-</details>
-
-[Learn more](sql-scalar.md#date-and-time-functions)
+Returns the current date in the connection's time zone.
## CURRENT_TIMESTAMP
-Returns the current timestamp in UTC time, unless you specify a different
timezone in the query context.
-
-
-* **Syntax:** `CURRENT_TIMESTAMP`
-* **Function type:** Scalar, date and time
-
-<details><summary>Example</summary>
-
-The following example returns the current timestamp.
-
-```sql
-SELECT CURRENT_TIMESTAMP AS "current_timestamp"
-```
-
-Returns the following:
+`CURRENT_TIMESTAMP`
-| `current_timestamp` |
-| -- |
-| `2024-08-14T21:30:13.793Z` |
+**Function type:** [Scalar, date and
time](sql-scalar.md#date-and-time-functions)
-</details>
-
-[Learn more](sql-scalar.md#date-and-time-functions)
+Returns the current timestamp in the connection's time zone.
## DATE_TRUNC
-Rounds down a timestamp by a given time unit.
-
-* **Syntax:** `DATE_TRUNC(unit, timestamp_expr)`
-* **Function type:** Scalar, date and time
-
-<details><summary>Example</summary>
-
-The following example truncates a timestamp from the `__time` column from the
`taxi-trips` datasource to the most recent `decade`.
-
-```sql
-SELECT
- "__time" AS "original_timestamp",
- DATE_TRUNC('decade', "__time") AS "truncate_timestamp"
-FROM "taxi-trips"
-LIMIT 1
-```
-
-Returns the following:
-
-| `original_timestamp` | `truncate_time` |
-| -- | -- |
-| `2013-08-01T08:14:37.000Z` | `2010-01-01T00:00:00.000Z` |
+`DATE_TRUNC(<CHARACTER>, <TIMESTAMP>)`
-</details>
-
-[Learn more](sql-scalar.md#date-and-time-functions)
+**Function type:** [Scalar, date and
time](sql-scalar.md#date-and-time-functions)
+Rounds down a timestamp by a given time unit.
## DECODE_BASE64_COMPLEX
@@ -937,53 +533,20 @@ Decodes a Base64-encoded string into a complex data type,
where `dataType` is th
## DECODE_BASE64_UTF8
-Decodes a Base64-encoded string into a UTF-8 encoded string.
-
-* **Syntax:** `DECODE_BASE64_UTF8(expr)`
-* **Function type:** Scalar, string
-
-<details><summary>Example</summary>
-
-The following example converts the base64 encoded string
`SGVsbG8gV29ybGQhCg==` into an UTF-8 encoded string.
-
-```sql
-SELECT
- 'SGVsbG8gV29ybGQhCg==' AS "base64_encoding",
- DECODE_BASE64_UTF8('SGVsbG8gV29ybGQhCg==') AS "convert_to_UTF8_encoding"
-```
-
-Returns the following:
+`DECODE_BASE64_UTF8(expr)`
-| `base64_encoding` | `convert_to_UTF8_encoding` |
-| -- | -- |
-| `SGVsbG8gV29ybGQhCg==` | `Hello World!` |
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-</details>
-[Learn more](sql-scalar.md#string-functions)
+Decodes a Base64-encoded string into a UTF-8 encoded string.
## DEGREES
-Converts an angle from radians to degrees.
-
-* **Syntax:** `DEGREES(expr)`
-* **Function type:** Scalar, numeric
-
-<details><summary>Example</summary>
+`DEGREES(<NUMERIC>)`
-The following example converts an angle of `PI` radians to degrees
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-```sql
-SELECT DEGREES(PI) AS "degrees"
-```
-Returns the following:
-
-| `degrees` |
-| -- |
-| `180` |
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
+Converts an angle from radians to degrees.
## DENSE_RANK
@@ -995,36 +558,11 @@ Returns the rank for a row within a window without gaps.
For example, if two row
## DIV
-Returns the result of integer division of `x` by `y`.
-
-* **Syntax:** `DIV(x, y)`
-* **Function type:** Scalar, numeric
-
-<!--
-<details><summary>Example</summary>
-
- The following calculates integer divisions of `78` by `10`.
-
- ```sql
- SELECT DIV(78, 10) as "division"
- ```
-
- Returns the following:
-
- | `division` |
- | -- |
- | `7` |
-
-</details>
--->
+`DIV(x, y)`
-:::info
-
- The `DIV` function is not implemented in Druid versions 30.0.0 or earlier.
Consider using [`SAFE_DIVIDE`](./sql-functions.md/#safe_divide) instead.
-
-:::
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-[Learn more](sql-scalar.md#numeric-functions)
+Returns the result of integer division of `x` by `y`.
## DS_CDF
@@ -1159,55 +697,19 @@ Returns the value of a numeric or string expression
corresponding to the earlies
## EXP
-Calculates _e_ raised to the power of the numeric expression.
-
-* **Syntax:** `EXP(<NUMERIC>)`
-* **Function type:** Scalar, numeric
-
-<details><summary>Example</summary>
+`EXP(<NUMERIC>)`
-The following example calculates _e_ to the power of 1.
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-```sql
-SELECT EXP(1) AS "exponential"
-```
-Returns the following:
-
-| `exponential` |
-| -- |
-| `2.7182818284590455` |
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
+Calculates _e_ raised to the power of the numeric expression.
## EXTRACT
-Extracts the value of some unit from the timestamp.
-
-* **Syntax:** `EXTRACT(unit FROM timestamp_expr)`
-* **Function type:** Scalar, date and time
-
-<details><summary>Example</summary>
-
-The following example extracts the year from the `__time` column from the
`taxi-trips` datasource.
-
-```sql
-SELECT
- "__time" AS "original_time",
- EXTRACT(YEAR FROM "__time" ) AS "year"
-FROM "taxi-trips"
-LIMIT 1
-```
-
-Returns the following:
+`EXTRACT(<TIME_UNIT> FROM <TIMESTAMP>)`
-| `original_time` | `year` |
-| -- | -- |
-| `2013-08-01T08:14:37.000Z` | `2013` |
+**Function type:** [Scalar, date and
time](sql-scalar.md#date-and-time-functions)
-</details>
-
-[Learn more](sql-scalar.md#date-and-time-functions)
+Extracts the value of some unit of the timestamp, optionally from a certain
time zone, and returns the number.
## FIRST_VALUE
@@ -1219,85 +721,27 @@ Returns the value evaluated for the expression for the
first row within the wind
## FLOOR (date and time)
-Rounds down a timestamp by a given time unit.
-
-* **Syntax:** `FLOOR(timestamp_expr TO unit>)`
-* **Function type:** Scalar, date and time
-
-<details><summary>Example</summary>
-
-The following example rounds down the `__time` column from the `taxi-trips`
datasource to the nearest year.
+`FLOOR(<TIMESTAMP> TO <TIME_UNIT>)`
-```sql
-SELECT
- "__time" AS "original_time",
- FLOOR("__time" TO YEAR) AS "floor"
-FROM "taxi-trips"
-LIMIT 1
-```
+**Function type:** [Scalar, date and
time](sql-scalar.md#date-and-time-functions)
-Returns the following:
-
-| `original_time` | `floor` |
-| -- | -- |
-| `2013-08-01T08:14:37.000Z` | `2013-01-01T00:00:00.000Z` |
-
-</details>
-
-[Learn more](sql-scalar.md#date-and-time-functions)
+Rounds down a timestamp by a given time unit.
## FLOOR (numeric)
-Calculates the largest integer less than or equal to the numeric expression.
+`FLOOR(<NUMERIC>)`
-* **Syntax:** `FLOOR(<NUMERIC>)`
-* **Function type:** Scalar, numeric
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-<details><summary>Example</summary>
-
-The following example applies the FLOOR function to the `fare_amount` column
from the `taxi-trips` datasource.
-
-```sql
-SELECT
- "fare_amount" AS "fare_amount",
- FLOOR("fare_amount") AS "floor_fare_amount"
-FROM "taxi-trips"
-LIMIT 1
-```
-Returns the following:
-
-| `fare_amount` | `floor_fare_amount` |
-| -- | -- |
-| `21.25` | `21` |
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
+Calculates the largest integer value less than or equal to the numeric
expression.
## GREATEST
-Returns the maximum value from the provided expressions. For information on
how Druid interprets the arguments passed into the function, see [Reduction
functions](sql-scalar.md#reduction-functions).
-
-* **Syntax:** `GREATEST([expr1, ...])`
-* **Function type:** Scalar, reduction
-
-<details><summary>Example</summary>
-
-The following example returns the greatest value between the numeric constant
`PI`, the integer number `4`, and the double `-5.0`. Druid interprets these
arguments as DOUBLE data type.
-
-```sql
-SELECT GREATEST(PI, 4, -5.0) AS "greatest"
-```
+`GREATEST([expr1, ...])`
-Returns the following:
-
-| `greatest` |
-| -- |
-| `4` |
-
-</details>
-
-[Learn more](sql-scalar.md#reduction-functions)
+**Function type:** [Scalar, reduction](sql-scalar.md#reduction-functions)
+Returns the maximum value from the provided arguments.
## GROUPING
@@ -1341,222 +785,67 @@ Returns a union of HLL sketches.
## HUMAN_READABLE_BINARY_BYTE_FORMAT
-Converts an integer byte size into human-readable
[IEC](https://en.wikipedia.org/wiki/Binary_prefix) format.
-
-* **Syntax:** `HUMAN_READABLE_BINARY_BYTE_FORMAT(value[, precision])`
-* **Function type:** Scalar, numeric
+`HUMAN_READABLE_BINARY_BYTE_FORMAT(value[, precision])`
-<details><summary>Example</summary>
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
- The following example converts `1000000` into IEC format.
-
- ```sql
- SELECT HUMAN_READABLE_BINARY_BYTE_FORMAT(1000000, 2) AS "iec_format"
- ```
-
- Returns the following:
-
- | `iec_format` |
- | -- |
- | `976.56 KiB` |
-
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
+Converts an integer byte size into human-readable IEC format.
## HUMAN_READABLE_DECIMAL_BYTE_FORMAT
-Converts a byte size into human-readable
[SI](https://en.wikipedia.org/wiki/Binary_prefix) format.
-
-* **Syntax:** `HUMAN_READABLE_DECIMAL_BYTE_FORMAT(value[, precision])`
-* **Function type:** Scalar, numeric
+`HUMAN_READABLE_DECIMAL_BYTE_FORMAT(value[, precision])`
-<details><summary>Example</summary>
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-The following example converts `1000000` into SI format.
-
-```sql
-SELECT HUMAN_READABLE_DECIMAL_BYTE_FORMAT(1000000, 2) AS "si_format"
-```
-
-Returns the following:
-
-|`si_format`|
-|--|
-|`1.00 MB`|
-
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
+Converts a byte size into human-readable SI format.
## HUMAN_READABLE_DECIMAL_FORMAT
-Converts a byte size into human-readable SI format with single-character units.
-
-* **Syntax:** `HUMAN_READABLE_DECIMAL_FORMAT(value[, precision])`
-* **Function type:** Scalar, numeric
-
-<details><summary>Example</summary>
+`HUMAN_READABLE_DECIMAL_FORMAT(value[, precision])`
- The following example converts `1000000` into single character SI format.
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-```sql
-SELECT HUMAN_READABLE_DECIMAL_FORMAT(1000000, 2) AS
"single_character_si_format"
-```
-
-Returns the following:
-
-|`single_character_si_format`|
-|--|
-|`1.00 M`|
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
+Converts a byte size into human-readable SI format with single-character units.
## ICONTAINS_STRING
-Returns true if `str` is a substring of `expr`, case-insensitive. Otherwise,
returns false.
-
-* **Syntax:** `ICONTAINS_STRING(expr, str)`
-* **Function type:** Scalar, string
-
-<details><summary>Example</summary>
-
-The following example returns true if the `OriginCityName` column from the
`flight-carriers` datasource contains the case-insensitive substring `san`.
-
-```sql
-SELECT
- "OriginCityName" AS "origin_city",
- ICONTAINS_STRING("OriginCityName", 'san') AS
"contains_case_insensitive_string"
-FROM "flight-carriers"
-LIMIT 2
-```
+`ICONTAINS_STRING(<expr>, str)`
-Returns the following:
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-| `origin_city` | `contains_case_insensitive_string` |
-| -- | -- |
-| `San Juan, PR` | `true` |
-| `Boston, MA` | `false` |
-
-</details>
-
-[Learn more](sql-scalar.md#string-functions)
+Finds whether a string is in a given expression, case-insensitive.
## IPV4_MATCH
-Returns true if the IPv4 `address` belongs to the `subnet` literal, otherwise
returns false.
-
-* **Syntax:** `IPV4_MATCH(address, subnet)`
-* **Function type:** Scalar, IP address
-
-<details><summary>Example</summary>
-
-The following example returns true if the IPv4 address in the `forward_for`
column from the `kttm` datasource belongs to the subnet `181.13.41.0/24`.
-
-```sql
-SELECT
- "forwarded_for" AS "ipv4_address",
- IPV4_MATCH("forwarded_for", '181.13.41.0/24') AS "belongs_in_subnet"
-FROM "kttm"
-LIMIT 2
-```
-
-Returns the following:
-
-| `ipv4_address` | `belongs_in_subnet`|
-| -- | -- |
-| `181.13.41.82` | `true`|
-| `177.242.100.0` | `false`|
+`IPV4_MATCH(address, subnet)`
-</details>
-
-
-[Learn more](sql-scalar.md#ip-address-functions)
+**Function type:** [Scalar, IP address](sql-scalar.md#ip-address-functions)
+Returns true if the IPv4 `address` belongs to the `subnet` literal, else false.
## IPV4_PARSE
-Parses an IPv4 `address` into its integer notation.
-
-* **Syntax:** `IPV4_PARSE(address)`
-* **Function type:** Scalar, IP address
-
-<details><summary>Example</summary>
-
-The following example returns an integer that represents the IPv4 address
`5.5.5.5`.
-
-```sql
-SELECT
- '5.5.5.5' AS "ipv4_address",
- IPV4_PARSE('5.5.5.5') AS "integer"
-```
-
-Returns the following:
-
-| `ipv4_address` | `integer` |
-| -- | -- |
-| `5.5.5.5` | `84215045` |
+`IPV4_PARSE(address)`
-</details>
+**Function type:** [Scalar, IP address](sql-scalar.md#ip-address-functions)
-[Learn more](sql-scalar.md#ip-address-functions)
+Parses `address` into an IPv4 address stored as an integer.
## IPV4_STRINGIFY
-Converts an IPv4 `address` in integer notation into dot-decimal notation.
+`IPV4_STRINGIFY(address)`
-* **Syntax:** `IPV4_STRINGIFY(address)`
-* **Function type:** Scalar, IP address
+**Function type:** [Scalar, IP address](sql-scalar.md#ip-address-functions)
-<details><summary>Example</summary>
-
-The following example returns the integer `84215045` in IPv4 dot-decimal
notation.
-
-```sql
-SELECT
- '84215045' AS "integer",
- IPV4_STRINGIFY(84215045) AS "dot_decimal_notation"
-```
-
-Returns the following:
-
-| `integer` | `dot_decimal_notation` |
-| -- | -- |
-| `84215045` | `5.5.5.5` |
-
-</details>
-
-[Learn more](sql-scalar.md#ip-address-functions)
+Converts `address` into an IPv4 address in dot-decimal notation.
## IPV6_MATCH
-Returns true if the IPv6 `address` belongs to the `subnet` literal. Otherwise,
returns false.
-
-* **Syntax:** `IPV6_MATCH(address, subnet)`
-* **Function type:** Scalar, IP address
-
-<details><summary>Example</summary>
+`IPV6_MATCH(address, subnet)`
-The following example returns true because `75e9:efa4:29c6:85f6::232c` is in
the subnet of `75e9:efa4:29c6:85f6::/64`.
-
-```sql
-SELECT
- '75e9:efa4:29c6:85f6::232c' AS "ipv6_address",
- IPV6_MATCH('75e9:efa4:29c6:85f6::232c', '75e9:efa4:29c6:85f6::/64') AS
"belongs_in_subnet"
-```
-
-Returns the following:
-
-| `ipv6_address` | `belongs_in_subnet` |
-| -- | -- |
-| `75e9:efa4:29c6:85f6::232c` | `true` |
-
-
-</details>
-
-[Learn more](sql-scalar.md#ip-address-functions)
+**Function type:** [Scalar, IP address](sql-scalar.md#ip-address-functions)
+Returns true if the IPv6 `address` belongs to the `subnet` literal, else false.
## JSON_KEYS
@@ -1648,302 +937,91 @@ If you do not supply an `offset`, returns the value
evaluated at the row followi
## LEAST
-Returns the minimum value from the provided expressions. For information on
how Druid interprets the arguments passed into the function, see [Reduction
functions](sql-scalar.md#reduction-functions).
+`LEAST([expr1, ...])`
-* **Syntax:** `LEAST([expr1, ...])`
-* **Function type:** Scalar, reduction
+**Function type:** [Scalar, reduction](sql-scalar.md#reduction-functions)
-<details><summary>Example</summary>
+Returns the minimum value from the provided arguments.
-The following example returns the minimum value between the strings `apple`,
`orange`, and `pear`. Druid interprets these arguments as STRING data type.
+## LEFT
-```sql
-SELECT LEAST( 'apple', 'orange', 'pear') AS "least"
-```
+`LEFT(expr, [length])`
-Returns the following:
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-| `least` |
-| -- |
-| `apple` |
+Returns the leftmost number of characters from an expression.
-</details>
+## LENGTH
-[Learn more](sql-scalar.md#reduction-functions)
+`LENGTH(expr)`
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-## LEFT
+Returns the length of the expression in UTF-16 encoding.
-Returns the `N` leftmost characters of an expression, where `N` is an integer
value.
+## LN
-* **Syntax:** `LEFT(expr, N)`
-* **Function type:** Scalar, string
+`LN(expr)`
-<details><summary>Example</summary>
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-The following example returns the `3` leftmost characters of the expression
`ABCDEFG`.
+Calculates the natural logarithm of the numeric expression.
-```sql
-SELECT
- 'ABCDEFG' AS "expression",
- LEFT('ABCDEFG', 3) AS "leftmost_characters"
-```
+## LOG10
-Returns the following:
+`LOG10(expr)`
-| `expression` | `leftmost_characters` |
-| -- | -- |
-| `ABCDEFG` | `ABC` |
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-</details>
+Calculates the base-10 of the numeric expression.
-[Learn more](sql-scalar.md#string-functions)
+## LOOKUP
-## LENGTH
+`LOOKUP(<CHARACTER>, <CHARACTER>[, <CHARACTER>])`
-Returns the length of the expression in UTF-16 code units.
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-* **Syntax:** `LENGTH(expr)`
-* **Function type:** Scalar, string
+Looks up the expression in a registered query-time lookup table.
-<details><summary>Example</summary>
+## LOWER
-The following example returns the character length of the `OriginCityName`
column from the `flight-carriers` datasource.
+`LOWER(expr)`
-```sql
-SELECT
- "OriginCityName" AS "origin_city_name",
- LENGTH("OriginCityName") AS "city_name_length"
-FROM "flight-carriers"
-LIMIT 1
-```
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-Returns the following:
+Returns the expression in lowercase.
-| `origin_city_name` | `city_name_length` |
-| -- | -- |
-| `San Juan, PR` | `12` |
+## LPAD
-</details>
+`LPAD(<CHARACTER>, <INTEGER>, [<CHARACTER>])`
-[Learn more](sql-scalar.md#string-functions)
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-## LN
+Returns the leftmost number of characters from an expression, optionally
padded with the given characters.
-Calculates the natural logarithm of the numeric expression.
+## LTRIM
-* **Syntax:** `LN(<NUMERIC>)`
-* **Function type:** Scalar, numeric
+`LTRIM(<CHARACTER>, [<CHARACTER>])`
-<details><summary>Example</summary>
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-The following example applies the LN function to the `max_temperature` column
from the `taxi-trips` datasource.
+Trims characters from the leading end of an expression.
-```sql
-SELECT
- "max_temperature" AS "max_temperature",
- LN("max_temperature") AS "natural_log_max_temp"
-FROM "taxi-trips"
-LIMIT 1
-```
+## MAX
-Returns the following:
+`MAX(expr)`
-| `max_temperature` | `natural_log_max_temp` |
-| -- | -- |
-| `76` | `4.330733340286331` |
+**Function type:** [Aggregation](sql-aggregations.md)
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
-
-## LOG10
-
-Calculates the base-10 logarithm of the numeric expression.
-
-* **Syntax:** `LOG10(<NUMERIC>)`
-* **Function type:** Scalar, numeric
-
-<details><summary>Example</summary>
-
-The following example applies the LOG10 function to the `max_temperature`
column from the `taxi-trips` datasource.
-
-```sql
-SELECT
- "max_temperature" AS "max_temperature",
- LOG10("max_temperature") AS "log10_max_temp"
-FROM "taxi-trips"
-LIMIT 1
-```
-Returns the following:
-
-| `max_temperature` | `log10_max_temp` |
-| -- | -- |
-| `76` | `1.8808135922807914` |
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
-
-## LOOKUP
-
-Searches for `expr` in a registered [query-time lookup table](lookups.md)
named `lookupName` and returns the mapped value. If `expr` is null or not
contained in the lookup, returns `defaultValue` if supplied, otherwise returns
null.
-
-* **Syntax:** `LOOKUP(expr, lookupName[, defaultValue])`
-* **Function type:** Scalar, string
-
-<details><summary>Example</summary>
-
-The following example uses a `map` type lookup table named `code_to_name`,
which contains the following key-value pairs:
-
-```json
-{
- "SJU": "Luis Munoz Marin International Airport",
- "IAD": "Dulles International Airport"
-}
-```
-
-The example uses `code_to_name` to map the `Origin` column from the
`flight-carriers` datasource to the corresponding full airport name. Returns
`key not found` if no matching key exists in the lookup table.
-
-```sql
-SELECT
- "Origin" AS "origin_airport",
- LOOKUP("Origin", 'code_to_name','key not found') AS "full_airport_name"
-FROM "flight-carriers"
-LIMIT 2
-```
-
-Returns the following:
-
-| `origin_airport` | `full_airport_name` |
-| -- | -- |
-| `SJU` | `Luis Munoz Marin International Airport` |
-| `BOS` | `key not found` |
-
-</details>
-
-[Learn more](sql-scalar.md#string-functions)
-
-## LOWER
-
-Returns the expression in lowercase.
-
-* **Syntax:** `LOWER(expr)`
-* **Function type:** Scalar, string
-
-<details><summary>Example</summary>
-
-The following example converts the `OriginCityName` column from the
`flight-carriers` datasource to lowercase.
-
-```sql
-SELECT
- "OriginCityName" AS "origin_city",
- LOWER("OriginCityName") AS "lowercase"
-FROM "flight-carriers"
-LIMIT 1
-```
-
-Returns the following:
-
-| `origin_city` | `lowercase` |
-| -- | -- |
-`San Juan, PR` | `san juan, pr` |
-
-</details>
-
-[Learn more](sql-scalar.md#string-functions)
-
-
-## LPAD
-
-Returns a string of size `length` from `expr`. When the length of `expr` is
less than `length`, left pads `expr` with `chars`, which defaults to the space
character. Truncates `expr` to `length` if `length` is shorter than the length
of `expr`.
-
-* **Syntax:** `LPAD(expr, length[, chars])`
-* **Function type:** Scalar, string
-
-<details><summary>Example</summary>
-
-The following example left pads the value of `OriginStateName` from the
`flight-carriers` datasource to return a total of 11 characters.
-
-```sql
-SELECT
- "OriginStateName" AS "origin_state",
- LPAD("OriginStateName", 11, '+') AS "add_left_padding"
-FROM "flight-carriers"
-LIMIT 3
-```
-
-Returns the following:
-
-| `origin_state` | `add_left_padding` |
-| -- | -- |
-| `Puerto Rico` | `Puerto Rico` |
-| `Massachusetts` | `Massachuset` |
-| `Florida` | `++++Florida` |
-
-</details>
-
-[Learn more](sql-scalar.md#string-functions)
-
-
-## LTRIM
-
-Trims characters from the leading end of an expression. Defaults `chars` to a
space if none is provided.
-
-* **Syntax:** `LTRIM(expr[, chars])`
-* **Function type:** Scalar, string
-
-<details><summary>Example</summary>
-
-The following example trims the `_` characters from the leading end of the
string expression.
-
-```sql
-SELECT
- '___abc___' AS "original_string",
- LTRIM('___abc___', '_') AS "trim_leading_end_of_expression"
-```
-
-Returns the following:
-
-| `original_string` | `trim_leading_end_of_expression` |
-| -- | -- |
-| `___abc___` | `abc___` |
-
-</details>
-
-[Learn more](sql-scalar.md#string-functions)
-
-## MAX
-
-`MAX(expr)`
-
-**Function type:** [Aggregation](sql-aggregations.md)
-
-Returns the maximum value of a set of values.
+Returns the maximum value of a set of values.
## MILLIS_TO_TIMESTAMP
-Converts a number of milliseconds since epoch into a timestamp.
-
-* **Syntax:** `MILLIS_TO_TIMESTAMP(millis_expr)`
-* **Function type:** Scalar, date and time
+`MILLIS_TO_TIMESTAMP(millis_expr)`
-<details><summary>Example</summary>
+**Function type:** [Scalar, date and
time](sql-scalar.md#date-and-time-functions)
-The following example converts 1375344877000 milliseconds from epoch into a
timestamp.
-
-```sql
-SELECT MILLIS_TO_TIMESTAMP(1375344877000) AS "timestamp"
-```
-
-Returns the following:
-
-| `timestamp` |
-| -- |
-| `2013-08-01T08:14:37.000Z` |
-
-</details>
-
-[Learn more](sql-scalar.md#date-and-time-functions)
+Converts a number of milliseconds since epoch into a timestamp.
## MIN
@@ -1955,26 +1033,11 @@ Returns the minimum value of a set of values.
## MOD
-Calculates x modulo y, or the remainder of x divided by y. Where x and y are
numeric expressions.
-
-* **Syntax:** `MOD(x, y)`
-* **Function type:** Scalar, numeric
-
-<details><summary>Example</summary>
-
-The following calculates 78 MOD 10.
+`MOD(x, y)`
-```sql
-SELECT MOD(78, 10) as "modulo"
-```
-Returns the following:
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-| `modulo` |
-| -- |
-| `8` |
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
+Calculates x modulo y, or the remainder of x divided by y.
## MV_APPEND
@@ -2130,31 +1193,11 @@ Parses `expr` into a `COMPLEX<json>` object. This
operator deserializes JSON val
## PARSE_LONG
-Converts a string into a long(BIGINT) with the given radix, or into
DECIMAL(base 10) if a radix is not provided.
-
-* **Syntax:**`PARSE_LONG(string[, radix])`
-* **Function type:** Scalar, string
-
-<details><summary>Example</summary>
-
-The following example converts the string representation of the binary, radix
2, number `1100` into its long (BIGINT) equivalent.
-
-```sql
-SELECT
- '1100' AS "binary_as_string",
- PARSE_LONG('1110', 2) AS "bigint_value"
-```
+`PARSE_LONG(<CHARACTER>, [<INTEGER>])`
-Returns the following:
-
-| `binary_as_string` | `bigint_value` |
-| -- | -- |
-| `1100` | `14` |
-
-</details>
-
-[Learn more](sql-scalar.md#string-functions)
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
+Converts a string into a BIGINT with the given base or into a DECIMAL data
type if the base is not specified.
## PERCENT_RANK
@@ -2166,79 +1209,27 @@ Returns the relative rank of the row calculated as a
percentage according to the
## POSITION
-Returns the one-based index position of a substring within an expression,
optionally starting from a given one-based index. If `substring` is not found,
returns 0.
-
-* **Syntax**: `POSITION(substring IN expr [FROM startingIndex])`
-* **Function type:** Scalar, string
-
-<details><summary>Example</summary>
+`POSITION(<CHARACTER> IN <CHARACTER> [FROM <INTEGER>])`
-The following example returns the one-based index of the substring `PR` in the
`OriginCityName` column from the `flight-carriers` datasource starting from
index 5.
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-```sql
-SELECT
- "OriginCityName" AS "origin_city",
- POSITION('PR' IN "OriginCityName" FROM 5) AS "index"
-FROM "flight-carriers"
-LIMIT 2
-```
-
-Returns the following:
-
-| `origin_city` | `index` |
-| -- | -- |
-| `San Juan, PR` | `11` |
-| `Boston, MA` | `0` |
-
-</details>
-
-[Learn more](sql-scalar.md#string-functions)
+Returns the one-based index position of a substring within an expression,
optionally starting from a given one-based index.
## POWER
-Calculates a numerical expression raised to the specified power.
-
-* **Syntax:** `POWER(base, exponent)`
-* **Function type:** Scalar, numeric
-
-<details><summary>Example</summary>
-
-The following example raises 5 to the power of 2.
+`POWER(expr, power)`
-```sql
-SELECT POWER(5, 2) AS "power"
-```
-Returns the following:
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-| `power` |
-| -- |
-| `25` |
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
+Calculates a numerical expression raised to the specified power.
## RADIANS
-Converts an angle from degrees to radians.
-
-* **Syntax:** `RADIANS(expr)`
-* **Function type:** Scalar, numeric
-
-<details><summary>Example</summary>
-
-The following example converts an angle of `180` degrees to radians
-
-```sql
-SELECT RADIANS(180) AS "radians"
-```
-Returns the following:
+`RADIANS(expr)`
-| `radians` |
-| -- |
-| `3.141592653589793` |
-</details>
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-[Learn more](sql-scalar.md#numeric-functions)
+Converts an angle from degrees to radians.
## RANK
@@ -2250,226 +1241,68 @@ Returns the rank with gaps for a row within a window.
For example, if two rows t
## REGEXP_EXTRACT
-Apply regular expression `pattern` to `expr` and extract the Nth capture
group. If `N` is unspecified or zero, returns the first substring that matches
the pattern. Returns `null` if there is no matching pattern.
-
-* **Syntax:** `REGEXP_EXTRACT(expr, pattern[, N])`
-* **Function type:** Scalar, string
-
-<details><summary>Example</summary>
-
-The following example uses regular expressions to find city names inside the
`OriginCityName` column from the `flight-carriers` datasource by matching what
comes before the comma.
-
-```sql
-SELECT
- "OriginCityName" AS "origin_city",
- REGEXP_EXTRACT("OriginCityName", '([^,]+)', 0) AS "pattern_match"
-FROM "flight-carriers"
-LIMIT 1
-```
+`REGEXP_EXTRACT(<CHARACTER>, <CHARACTER>, [<INTEGER>])`
-Returns the following:
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-| `origin_city` | `pattern_match` |
-| -- | -- |
-| `San Juan, PR` | `San Juan`|
-
-</details>
-
-[Learn more](sql-scalar.md#string-functions)
+Applies a regular expression to the string expression and returns the _n_th
match.
## REGEXP_LIKE
-Returns `true` if the regular expression `pattern` finds a match in `expr`.
Returns `false` otherwise.
-
-* **Syntax:** `REGEXP_LIKE(expr, pattern)`
-* **Function type:** Scalar, string
-
-<details><summary>Example</summary>
-
-The following example returns `true` when the `OriginCityName` column from
`flight-carriers` has a city name containing a space.
+`REGEXP_LIKE(<CHARACTER>, <CHARACTER>)`
-```sql
-SELECT
- "OriginCityName" AS "origin_city",
- REGEXP_LIKE("OriginCityName", '[A-Za-z]+\s[A-Za-z]+') AS "pattern_found"
-FROM "flight-carriers"
-LIMIT 2
-```
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-Returns the following:
-
-| `origin_city` | `pattern_found` |
-| -- | -- |
-| `San Juan, PR` | `true` |
-| `Boston, MA` | `false` |
-
-</details>
-
-[Learn more](sql-scalar.md#string-functions)
+Returns true or false signifying whether the regular expression finds a match
in the string expression.
## REGEXP_REPLACE
-Replaces all occurrences of a regular expression in a string expression with a
replacement string. Refer to capture groups in the replacement string using
`$group` syntax. For example: `$1` or `$2`.
-
-* **Syntax:** `REGEXP_REPLACE(expr, pattern, replacement)`
-* **Function type:** Scalar, string
+`REGEXP_REPLACE(<CHARACTER>, <CHARACTER>, <CHARACTER>)`
-<details><summary>Example</summary>
-
-The following example matches three consecutive words, where each word is its
own capture group, and replaces the matched words with the word in the second
capture group punctuated with exclamation marks.
-
-```sql
-SELECT
- 'foo bar baz' AS "original_string",
- REGEXP_REPLACE('foo bar baz', '([A-Za-z]+) ([A-Za-z]+) ([A-Za-z]+)' , '$2!')
AS "modified_string"
-```
-
-Returns the following:
-
-| `original_string` | `modified_string` |
-| -- | -- |
-| `foo bar baz` | `bar!` |
-
-</details>
-
-[Learn more](sql-scalar.md#string-functions)
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
+Replaces all occurrences of a regular expression in a string expression with a
replacement string. The replacement
+string may refer to capture groups using `$1`, `$2`, etc.
## REPEAT
-Repeats the string expression `N` times, where `N` is an integer.
-
-* **Syntax:** `REPEAT(expr, N)`
-* **Function type:** Scalar, string
-
-<details><summary>Example</summary>
-
-The following example returns the string expression `abc` repeated `3` times.
-
-```sql
-SELECT
- 'abc' AS "original_string",
- REPEAT('abc', 3) AS "with_repetition"
-```
+`REPEAT(<CHARACTER>, [<INTEGER>])`
-Returns the following:
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-| `original_string` | `with_repetition` |
-| -- | -- |
-| `abc` | `abcabcabc` |
-
-</details>
-
-[Learn more](sql-scalar.md#string-functions)
+Repeats the string expression an integer number of times.
## REPLACE
-Replaces instances of a substring with a replacement string in the given
expression.
-
-* **Syntax:** `REPLACE(expr, substring, replacement)`
-* **Function type:** Scalar, string
-
-<details><summary>Example</summary>
-
-The following example replaces instances of the substring `abc` with `XYZ`.
+`REPLACE(expr, pattern, replacement)`
-```sql
-SELECT
- 'abc 123 abc 123' AS "original_string",
- REPLACE('abc 123 abc 123', 'abc', 'XYZ') AS "modified_string"
-```
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-Returns the following:
-
-| `original_string` | `modified_string` |
-| -- | -- |
-| `abc 123 abc 123` | `XYZ 123 XYZ 123` |
-
-</details>
-
-[Learn more](sql-scalar.md#string-functions)
+Replaces a pattern with another string in the given expression.
## REVERSE
-Reverses the given expression.
-
-* **Syntax:** `REVERSE(expr)`
-* **Function type:** Scalar, string
+`REVERSE(expr)`
-<details><summary>Example</summary>
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-The following example reverses the string expression `abc`.
-
-```sql
-SELECT
- 'abc' AS "original_string",
- REVERSE('abc') AS "reversal"
-```
-
-Returns the following:
-
-| `original_string` | `reversal` |
-| -- | -- |
-| `abc` | `cba` |
-
-</details>
-
-[Learn more](sql-scalar.md#string-functions)
+Reverses the given expression.
## RIGHT
-Returns the `N` rightmost characters of an expression, where `N` is an integer
value.
-
-* **Syntax:** `RIGHT(expr, N)`
-* **Function type:** Scalar, string
-
-<details><summary>Example</summary>
-
-The following example returns the `3` rightmost characters of the expression
`ABCDEFG`.
-
-```sql
-SELECT
- 'ABCDEFG' AS "expression",
- RIGHT('ABCDEFG', 3) AS "rightmost_characters"
-```
+`RIGHT(expr, [length])`
-Returns the following:
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-| `expression` | `rightmost_characters` |
-| -- | -- |
-| `ABCDEFG` | `EFG` |
-
-</details>
-
-[Learn more](sql-scalar.md#string-functions)
+Returns the rightmost number of characters from an expression.
## ROUND
-Calculates the rounded value for a numerical expression.
-
-* **Syntax:** `ROUND(expr[, digits])`
-* **Function type:** Scalar, numeric
-
-<details><summary>Example</summary>
+`ROUND(expr[, digits])`
-The following applies the ROUND function to 0 decimal points on the
`pickup_longitude` column from the `taxi-trips` datasource.
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-```sql
-SELECT
- "pickup_longitude" AS "pickup_longitude",
- ROUND("pickup_longitude", 0) as "rounded_pickup_longitude"
-FROM "taxi-trips"
-WHERE "pickup_longitude" IS NOT NULL
-LIMIT 1
-```
-Returns the following:
-
-| `pickup_longitude` | `rounded_pickup_longitude` |
-| -- | -- |
-| `-73.9377670288086` | `-74` |
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
+Calculates the rounded value for a numerical expression.
## ROW_NUMBER
@@ -2481,132 +1314,43 @@ Returns the number of the row within the window
starting from 1.
## RPAD
-Returns a string of size `length` from `expr`. When the length of `expr` is
less than `length`, right pads `expr` with `chars`, which defaults to the space
character. Truncates `expr` to `length` if `length` is shorter than the length
of `expr`.
-
-* **Syntax:** `RPAD(expr, length[, chars])`
-* **Function type:** Scalar, string
+`RPAD(<CHARACTER>, <INTEGER>, [<CHARACTER>])`
-<details><summary>Example</summary>
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-The following example right pads the value of `OriginStateName` from the
`flight-carriers` datasource to return a total of 11 characters.
-
-```sql
-SELECT
- "OriginStateName" AS "origin_state",
- RPAD("OriginStateName", 11, '+') AS "add_right_padding"
-FROM "flight-carriers"
-LIMIT 3
-```
-
-Returns the following:
-
-| `origin_state` | `add_right_padding` |
-| -- | -- |
-| `Puerto Rico` | `Puerto Rico` |
-| `Massachusetts` | `Massachuset` |
-| `Florida` | `Florida++++` |
-
-</details>
-
-[Learn more](sql-scalar.md#string-functions)
+Returns the rightmost number of characters from an expression, optionally
padded with the given characters.
## RTRIM
-Trims characters from the trailing end of an expression. Defaults `chars` to a
space if none is provided.
-
-* **Syntax:** `RTRIM(expr[, chars])`
-* **Function type:** Scalar, string
-
-<details><summary>Example</summary>
-
-The following example trims the `_` characters from the trailing end of the
string expression.
-
-```sql
-SELECT
- '___abc___' AS "original_string",
- RTRIM('___abc___', '_') AS "trim_end"
-```
-
-Returns the following:
+`RTRIM(<CHARACTER>, [<CHARACTER>])`
-| `original_string` | `trim_end` |
-| -- | -- |
-| `___abc___` | `___abc` |
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-</details>
-
-[Learn more](sql-scalar.md#string-functions)
+Trims characters from the trailing end of an expression.
## SAFE_DIVIDE
-Returns `x` divided by `y`, guarded on division by 0.
-
-* **Syntax:** `SAFE_DIVIDE(x, y)`
-* **Function type:** Scalar, numeric
-
-<details><summary>Example</summary>
-
-The following example calculates divisions of integer `78` by integer `10`.
+`SAFE_DIVIDE(x, y)`
-```sql
-SELECT SAFE_DIVIDE(78, 10) AS "safe_division"
-```
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-Returns the following:
-
-|`safe_division`|
-|--|
-| `7` |
-
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
+Returns `x` divided by `y`, guarded on division by 0.
## SIN
-Calculates the trigonometric sine of an angle expressed in radians.
+`SIN(expr)`
-* **Syntax:** `SIN(expr)`
-* **Function type:** Scalar, numeric
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-<details><summary>Example</summary>
-
-The following example calculates the sine of angle `PI/3` radians.
-
-```sql
-SELECT SIN(PI / 3) AS "sine"
-```
-Returns the following:
-
-| `sine` |
-| -- |
-| `0.8660254037844386` |
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
+Calculates the trigonometric sine of an angle expressed in radians.
## SQRT
-Calculates the square root of a numeric expression.
-
-* **Syntax:** `SQRT(<NUMERIC>)`
-* **Function type:** Scalar, numeric
-
-<details><summary>Example</summary>
-
-The following example calculates the square root of 25.
-
-```sql
-SELECT SQRT(25) AS "square_root"
-```
-Returns the following:
+`SQRT(expr)`
-| `square_root` |
-| -- |
-| `5` |
-</details>
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-[Learn more](sql-scalar.md#numeric-functions)
+Calculates the square root of a numeric expression.
## STDDEV
@@ -2651,33 +1395,11 @@ Splits `str1` into an array on the delimiter specified
by `str2`, which is a reg
## STRING_FORMAT
-Returns a string formatted in the manner of Java's
[String.format](https://docs.oracle.com/javase/8/docs/api/java/lang/String.html#format-java.lang.String-java.lang.Object...-).
-
-* **Syntax:** `STRING_FORMAT(pattern[, args...])`
-* **Function type:** Scalar, string
-
-<details><summary>Example</summary>
-
-The following example uses Java String format to pass in
`Flight_Number_Reporting_Airline` and `origin_airport` columns, from the
`flight-carriers` datasource, as arguments into the string.
-
-```sql
-SELECT
- "Flight_Number_Reporting_Airline" AS "flight_number",
- "Origin" AS "origin_airport",
- STRING_FORMAT('Flight No.%d departing from %s',
"Flight_Number_Reporting_Airline", "Origin") AS "departure_announcement"
-FROM "flight-carriers"
-LIMIT 1
-```
+`STRING_FORMAT(pattern[, args...])`
-Returns the following:
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-| `flight_number` | `origin_airport` | `departure_announcement` |
-| -- | -- | -- |
-| `314` | `SJU` | `Flight No.314 departing from SJU` |
-
-</details>
-
-[Learn more](sql-scalar.md#string-functions)
+Returns a string formatted in accordance to Java's String.format method.
## STRING_TO_MV
@@ -2689,78 +1411,35 @@ Splits `str1` into an multi-value string on the
delimiter specified by `str2`, w
## STRLEN
-Alias for [`LENGTH`](#length).
+`STRLEN(expr)`
-* **Syntax:** `STRLEN(expr)`
-* **Function type:** Scalar, string
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-[Learn more](sql-scalar.md#string-functions)
+Alias for [`LENGTH`](#length).
## STRPOS
-Returns the one-based index position of a substring within an expression. If
`substring` is not found, returns 0.
+`STRPOS(<CHARACTER>, <CHARACTER>)`
-* **Syntax:** `STRPOS(expr, substring)`
-* **Function type:** Scalar, string
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-<details><summary>Example</summary>
-
-The following example returns the one-based index position of `World`.
-
-```sql
-SELECT
- 'Hello World!' AS "original_string",
- STRPOS('Hello World!', 'World') AS "index"
-```
-
-Returns the following:
-
-| `original_string` | `index` |
-| -- | -- |
-| `Hello World!` | `7` |
-
-</details>
-
-[Learn more](sql-scalar.md#string-functions)
+Returns the one-based index position of a substring within an expression.
## SUBSTR
-Alias for [`SUBSTRING`](#substring).
-
-* **Syntax:** `SUBSTR(expr, index[, length])`
-* **Function type:** Scalar, string
+`SUBSTR(<CHARACTER>, <INTEGER>, [<INTEGER>])`
-[Learn more](sql-scalar.md#string-functions)
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
+Alias for [`SUBSTRING`](#substring).
## SUBSTRING
-Returns a substring of the expression starting at a given one-based index. If
`length` is omitted, extracts characters to the end of the string, otherwise
returns a substring of `length` characters.
-
-* **Syntax:** `SUBSTRING(expr, index[, length])`
-* **Function type:** Scalar, string
+`SUBSTRING(<CHARACTER>, <INTEGER>, [<INTEGER>])`
-<details><summary>Example</summary>
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-The following example extracts a substring from the string expression
`abcdefghi` of length `3` starting at index `4`
-
-```sql
-SELECT
- 'abcdefghi' AS "original_string",
- SUBSTRING('abcdefghi', 4, 3) AS "substring"
-```
-
-Returns the following:
-
-| `original_string` | `substring` |
-| -- | -- |
-| `abcdefghi` | `def` |
-
-</details>
-
-
-
-[Learn more](sql-scalar.md#string-functions)
+Returns a substring of the expression starting at a given one-based index.
## SUM
@@ -2772,26 +1451,11 @@ Calculates the sum of a set of values.
## TAN
-Calculates the trigonometric tangent of an angle expressed in radians.
-
-* **Syntax:** `TAN(expr)`
-* **Function type:** Scalar, numeric
-
-<details><summary>Example</summary>
+`TAN(expr)`
-The following example calculates the tangent of angle `PI/3` radians.
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-```sql
-SELECT TAN(PI / 3) AS "tangent"
-```
-Returns the following:
-
-| `tangent` |
-| -- |
-| `1.7320508075688767` |
-</details>
-
-[Learn more](sql-scalar.md#numeric-functions)
+Calculates the trigonometric tangent of an angle expressed in radians.
## TDIGEST_GENERATE_SKETCH
@@ -2811,32 +1475,11 @@ Returns the quantile for the specified fraction from a
T-Digest sketch construct
## TEXTCAT
-Concatenates two string expressions.
-
-* **Syntax:** `TEXTCAT(expr, expr)`
-* **Function type:** Scalar, string
-
-<details><summary>Example</summary>
+`TEXTCAT(<CHARACTER>, <CHARACTER>)`
-The following example concatenates the `OriginState` column from the
`flight-carriers` datasource to `, USA`.
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-```sql
-SELECT
- "OriginState" AS "origin_state",
- TEXTCAT("OriginState", ', USA') AS "concatenate_state_with_USA"
-FROM "flight-carriers"
-LIMIT 1
-```
-
-Returns the following:
-
-| `origin_state` | `concatenate_state_with_USA` |
-| -- | -- |
-| `PR` | `PR, USA` |
-
-</details>
-
-[Learn more](sql-scalar.md#string-functions)
+Concatenates two string expressions.
## THETA_SKETCH_ESTIMATE
@@ -2880,369 +1523,117 @@ Returns a union of Theta sketches.
## TIME_CEIL
-Rounds up a timestamp to a given ISO 8601 time period. You can specify
`origin` to provide a reference timestamp from which to start rounding. If
provided, `timezone` should be a time zone name like `America/Los_Angeles` or
an offset like `-08:00`.
+`TIME_CEIL(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])`
-* **Syntax:** `TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`
-* **Function type:** Scalar, date and time
+**Function type:** [Scalar, date and
time](sql-scalar.md#date-and-time-functions)
-<details><summary>Example</summary>
-
-The following example rounds up the `__time` column from the `taxi-trips`
datasource to the nearest 45th minute in reference to the timestamp `2013-08-01
08:0:00`.
-
-```sql
-SELECT
- "__time" AS "original_timestamp",
- TIME_CEIL("__time", 'PT45M', TIMESTAMP '2013-08-01 08:0:00') AS
"time_ceiling"
-FROM "taxi-trips"
-LIMIT 2
-```
-
-Returns the following:
-
-| `original_timestamp` | `time_ceiling` |
-| -- | -- |
-| `2013-08-01T08:14:37.000Z` | `2013-08-01T08:45:00.000Z` |
-| `2013-08-01T09:13:00.000Z` | `2013-08-01T09:30:00.000Z` |
-</details>
-
-[Learn more](sql-scalar.md#date-and-time-functions)
+Rounds up a timestamp by a given time period, optionally from some reference
time or timezone.
## TIME_EXTRACT
-Extracts the value of `unit` from the timestamp and returns it as a number. If
provided, `timezone` should be a time zone name like `America/Los_Angeles` or
an offset like `-08:00`.
-
-* **Syntax:** `TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`
-* **Function type:** Scalar, date and time
-
-<details><summary>Example</summary>
-
-The following example extracts the hour from the `__time` column in the
`taxi-trips` datasource and offsets its timezone by `-04:00` hours.
-
-```sql
-SELECT
- "__time" AS "original_timestamp",
- TIME_EXTRACT("__time", 'hour', '-04:00') AS "extract_hour"
-FROM "taxi-trips"
-LIMIT 2
-```
+`TIME_EXTRACT(<TIMESTAMP>, [<unit>, [<timezone>]])`
-Returns the following:
+**Function type:** [Scalar, date and
time](sql-scalar.md#date-and-time-functions)
-| `original_timestamp` | `extract_hour` |
-| -- | -- |
-| `2013-08-01T08:14:37.000Z` | `4` |
-| `2013-08-01T09:13:00.000Z` | `5` |
-
-</details>
-
-[Learn more](sql-scalar.md#date-and-time-functions)
+Extracts the value of some unit of the timestamp and returns the number.
## TIME_FLOOR
-Rounds down a timestamp to a given ISO 8601 time period. You can specify
`origin` to provide a reference timestamp from which to start rounding. If
provided, `timezone` should be a time zone name like `America/Los_Angeles` or
an offset like `-08:00`.
-
-* **Syntax:** `TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`
-* **Function type:** Scalar, date and time
-
-<details><summary>Example</summary>
-
-The following example rounds down the `__time` column from the `taxi-trips`
datasource to the nearest 45th minute in reference to the timestamp `2013-08-01
08:0:00`.
+`TIME_FLOOR(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])`
-```sql
-SELECT
- "__time" AS "original_timestamp",
- TIME_FLOOR("__time", 'PT45M', TIMESTAMP '2013-08-01 08:0:00') AS "time_floor"
-FROM "taxi-trips"
-LIMIT 2
-```
+**Function type:** [Scalar, date and
time](sql-scalar.md#date-and-time-functions)
-Returns the following:
-
-| `original_timestamp` | `time_floor` |
-| -- | -- |
-| `2013-08-01T08:14:37.000Z` | `2013-08-01T08:00:00.000Z` |
-| `2013-08-01T09:13:00.000Z` | `2013-08-01T08:45:00.000Z` |
-
-</details>
-
-[Learn more](sql-scalar.md#date-and-time-functions)
+Rounds down a timestamp by a given time period, optionally from some reference
time or timezone.
## TIME_FORMAT
-Formats a timestamp as a string in a provided [Joda DateTimeFormat
pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html).
If no pattern is provided, `pattern` defaults to ISO 8601. If provided,
`timezone` should be a time zone name like `America/Los_Angeles` or an offset
like `-08:00`.
-
-* **Syntax:** `TIME_FORMAT(timestamp_expr[, pattern[, timezone]])`
-* **Function type:** Scalar, date and time
-
-<details><summary>Example</summary>
+`TIME_FORMAT(<TIMESTAMP>, [<pattern>, [<timezone>]])`
-The following example formats the `__time` column from the `flight-carriers`
datasource into a string format and offsets the result's timezone by `-05:00`
hours.
+**Function type:** [Scalar, date and
time](sql-scalar.md#date-and-time-functions)
-```sql
-SELECT
- "__time" AS "original_time",
-TIME_FORMAT( "__time", 'dd-MM-YYYY hh:mm aa zzz', '-05:00') AS "string"
-FROM "taxi-trips"
-LIMIT 1
-```
-
-Returns the following:
-
-| `original_time` | `string` |
-| -- | -- |
-| `2013-08-01T08:14:37.000Z` | `01-08-2013 03:14 AM -05:00` |
-
-</details>
-
-[Learn more](sql-scalar.md#date-and-time-functions)
+Formats a timestamp as a string.
## TIME_IN_INTERVAL
-Returns true if a timestamp is contained within a particular interval.
Intervals must be formatted as a string literal containing any ISO 8601
interval. The start instant of an interval is inclusive, and the end instant is
exclusive.
-
-* **Syntax:** `TIME_IN_INTERVAL(timestamp_expr, interval)`
-* **Function type:** Scalar, date and time
-
-<details><summary>Example</summary>
-
-The following example returns true when a timestamp in the `__time` column of
the `taxi-trips` datasource is within a hour interval starting from
`2013-08-01T08:00:00`.
-
-```sql
-SELECT
- "__time" AS "original_time",
- TIME_IN_INTERVAL("__time", '2013-08-01T08:00:00/PT1H') AS "in_interval"
-FROM "taxi-trips"
-LIMIT 2
-```
-
-Returns the following:
-
-| `original_time` | `in_interval` |
-| -- | -- |
-| `2013-08-01T08:14:37.000Z` | `true` |
-| `2013-08-01T09:13:00.000Z` | `false` |
+`TIME_IN_INTERVAL(<TIMESTAMP>, <CHARACTER>)`
-</details>
+**Function type:** [Scalar, date and
time](sql-scalar.md#date-and-time-functions)
-[Learn more](sql-scalar.md#date-and-time-functions)
+Returns whether a timestamp is contained within a particular interval,
formatted as a string.
## TIME_PARSE
-Parses a string into a timestamp using a given [Joda DateTimeFormat
pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html).
If no pattern is provided, `pattern` defaults to ISO 8601. Returns NULL if
string cannot be parsed. If provided, `timezone` should be a time zone name
like `America/Los_Angeles` or an offset like `-08:00`.
+`TIME_PARSE(<string_expr>, [<pattern>, [<timezone>]])`
-* **Syntax:** `TIME_PARSE(string_expr[, pattern[, timezone]])`
-* **Function type:** Scalar, date and time
+**Function type:** [Scalar, date and
time](sql-scalar.md#date-and-time-functions)
-<details><summary>Example</summary>
-
-The following example parses the `FlightDate` STRING column from the
`flight-carriers` datasource into a valid timestamp with an offset of `-05:00`
hours.
-
-```sql
-SELECT
- "FlightDate" AS "original_string",
- TIME_PARSE("FlightDate", 'YYYY-MM-dd', '-05:00') AS "timestamp"
-FROM "flight-carriers"
-LIMIT 1
-```
-
-Returns the following:
-
-| `original_string` | `timestamp` |
-| -- | -- |
-| `2005-11-01` | `2005-11-01T05:00:00.000Z` |
-
-</details>
-
-[Learn more](sql-scalar.md#date-and-time-functions)
+Parses a string into a timestamp.
## TIME_SHIFT
-Shifts a timestamp by a given number of time units. The `period` parameter can
be any ISO 8601 period. The `step` parameter can be negative. If provided,
`timezone` should be a time zone name like `America/Los_Angeles` or an offset
like `-08:00`.
-
-* **Syntax:** `TIME_SHIFT(timestamp_expr, period, step[, timezone])`
-* **Function type:** Scalar, date and time
-
-<details><summary>Example</summary>
-
-The following example shifts the `__time` column from the `taxi-trips`
datasource back by 24 hours.
-
-```sql
-SELECT
- "__time" AS "original_timestamp",
- TIME_SHIFT("__time", 'PT1H', -24) AS "shift_back"
-FROM "taxi-trips"
-LIMIT 1
-```
+`TIME_SHIFT(<TIMESTAMP>, <period>, <step>, [<timezone>])`
-Returns the following:
-
-| `original_timestamp` | `shift_back` |
-| -- | -- |
-| `2013-08-01T08:14:37.000Z` | `2013-07-31T08:14:37.000Z` |
-
-</details>
-
-[Learn more](sql-scalar.md#date-and-time-functions)
+**Function type:** [Scalar, date and
time](sql-scalar.md#date-and-time-functions)
+Shifts a timestamp forwards or backwards by a given number of time units.
## TIMESTAMP_TO_MILLIS
-Returns the number of milliseconds since epoch for the given timestamp.
-
-* **Syntax:** `TIMESTAMP_TO_MILLIS(timestamp_expr)`
-* **Function type:** Scalar, date and time
-
-<details><summary>Example</summary>
-
-The following example converts the `__time` column from the `taxi-trips`
datasource into milliseconds since epoch.
-
-```sql
-SELECT
- "__time" AS "original_time",
- TIMESTAMP_TO_MILLIS("__time") AS "miliseconds"
-FROM "taxi-trips"
-LIMIT 1
-```
-
-Returns the following:
+`TIMESTAMP_TO_MILLIS(<TIMESTAMP>)`
-| `original_time` | `miliseconds` |
-| -- | -- |
-| `2013-08-01T08:14:37.000Z` | `1375344877000` |
+**Function type:** [Scalar, date and
time](sql-scalar.md#date-and-time-functions)
-</details>
-
-[Learn more](sql-scalar.md#date-and-time-functions)
+Returns the number of milliseconds since epoch for the given timestamp.
## TIMESTAMPADD
-Add a `unit` of time multiplied by `count` to `timestamp`.
-
-* **Syntax:** `TIMESTAMPADD(unit, count, timestamp)`
-* **Function type:** Scalar, date and time
-
-<details><summary>Example</summary>
-
-The following example adds five months to the timestamp `2000-01-01 00:00:00`.
+`TIMESTAMPADD(<unit>, <count>, <TIMESTAMP>)`
-```sql
-SELECT
- TIMESTAMP '2000-01-01 00:00:00' AS "original_time",
- TIMESTAMPADD (MONTH, 5, TIMESTAMP '2000-01-01 00:00:00') AS "new_time"
-```
+**Function type:** [Scalar, date and
time](sql-scalar.md#date-and-time-functions)
-Returns the following:
-
-| `original_time` | `new_time` |
-| -- | -- |
-| `2000-01-01T00:00:00.000Z` | `2000-06-01T00:00:00.000Z` |
-
-</details>
-
-[Learn more](sql-scalar.md#date-and-time-functions)
+Adds a certain amount of time to a given timestamp.
## TIMESTAMPDIFF
-Returns the difference between two timestamps in a given unit.
-
-* **Syntax:** `TIMESTAMPDIFF(unit, timestamp1, timestamp2)`
-* **Function type:** Scalar, date and time
-
-<details><summary>Example</summary>
+`TIMESTAMPDIFF(<unit>, <TIMESTAMP>, <TIMESTAMP>)`
-The following example calculates the taxi trip length in minutes by
subtracting the `__time` column from the `dropoff_datetime` column in the
`taxi-trips` datasource.
+**Function type:** [Scalar, date and
time](sql-scalar.md#date-and-time-functions)
-```sql
-SELECT
- "__time" AS "pickup_time",
- "dropoff_datetime" AS "dropoff_time",
- TIMESTAMPDIFF (MINUTE, "__time", TIME_PARSE("dropoff_datetime")) AS
"trip_length"
-FROM "taxi-trips"
-LIMIT 1
-```
-
-Returns the following:
-
-| `pickup_time` | `dropoff_time` | `trip_length` |
-| -- | -- | -- |
-| `2013-08-01T08:14:37.000Z` | `2013-08-01 09:09:06` | `54` |
-
-</details>
-
-[Learn more](sql-scalar.md#date-and-time-functions)
+Takes the difference between two timestamps, returning the results in the
given units.
## TO_JSON_STRING
**Function type:** [JSON](sql-json-functions.md)
`TO_JSON_STRING(expr)`
+
Serializes `expr` into a JSON string.
## TRIM
-Trims the leading and/or trailing characters of an expression. Defaults
`chars` to a space if none is provided. Defaults to `BOTH` if no directional
argument is provided.
-
-* **Syntax:** `TRIM([BOTH|LEADING|TRAILING] [chars FROM] expr)`
-* **Function type:** Scalar, string
-
-<details><summary>Example</summary>
-
-The following example trims `_` characters from both ends of the string
expression.
-
-```sql
-SELECT
- '___abc___' AS "original_string",
- TRIM( BOTH '_' FROM '___abc___') AS "trim_expression"
-```
-
-Returns the following:
+`TRIM([BOTH|LEADING|TRAILING] [<chars> FROM] expr)`
-| `original_string` | `trim_expression` |
-| -- | -- |
-| `___abc___` | `abc` |
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-</details>
-
-[Learn more](sql-scalar.md#string-functions)
+Trims the leading or trailing characters of an expression.
## TRUNC
-Alias for [`TRUNCATE`](#truncate).
+`TRUNC(expr[, digits])`
-* **Syntax:** `TRUNC(expr[, digits])`
-* **Function type:** Scalar, numeric
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
-[Learn more](sql-scalar.md#numeric-functions)
+Alias for [`TRUNCATE`](#truncate).
## TRUNCATE
-Truncates a numerical expression to a specific number of decimal digits.
-
-* **Syntax:** `TRUNCATE(expr[, digits])`
-* **Function type:** Scalar, numeric
+`TRUNCATE(expr[, digits])`
-<details><summary>Example</summary>
-
-The following applies the TRUNCATE function to 1 decimal place on the
`pickup_longitude` column from the `taxi-trips` datasource.
-
-```sql
-SELECT
- "pickup_longitude" as "pickup_longitude",
- TRUNCATE("pickup_longitude", 1) as "truncate_pickup_longitude"
-FROM "taxi-trips"
-WHERE "pickup_longitude" IS NOT NULL
-LIMIT 1
-```
-Returns the following:
-
-| `pickup_longitude` | `truncate_pickup_longitude` |
-| -- | -- |
-| `-73.9377670288086` | `-73.9` |
-</details>
+**Function type:** [Scalar, numeric](sql-scalar.md#numeric-functions)
+Truncates a numerical expression to a specific number of decimal digits.
-[Learn more](sql-scalar.md#numeric-functions)
## TRY_PARSE_JSON
@@ -3262,32 +1653,11 @@ For more information, see [UNNEST](./sql.md#unnest).
## UPPER
-Returns the expression in uppercase.
+`UPPER(expr)`
-* **Syntax:** `UPPER(expr)`
-* **Function type:** Scalar, string
+**Function type:** [Scalar, string](sql-scalar.md#string-functions)
-<details><summary>Example</summary>
-
-The following example converts the `OriginCityName` column from the
`flight-carriers` datasource to uppercase.
-
-```sql
-SELECT
- "OriginCityName" AS "origin_city",
- UPPER("OriginCityName") AS "uppercase"
-FROM "flight-carriers"
-LIMIT 1
-```
-
-Returns the following:
-
-| `origin_city` | `uppercase` |
-| -- | -- |
-`San Juan, PR` | `SAN JUAN, PR` |
-
-</details>
-
-[Learn more](sql-scalar.md#string-functions)
+Returns the expression in uppercase.
## VAR_POP
@@ -3312,5 +1682,3 @@ Calculates the sample variance of a set of values.
**Function type:** [Aggregation](sql-aggregations.md)
Alias for [`VAR_SAMP`](#var_samp).
-
-
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
