This is an automated email from the ASF dual-hosted git repository.
dzamo pushed a commit to branch gh-pages
in repository https://gitbox.apache.org/repos/asf/drill.git
The following commit(s) were added to refs/heads/gh-pages by this push:
new bdaf0db Document bitwise logical functions.
bdaf0db is described below
commit bdaf0db9ad703b637f8c02ef5c0df90bda1b0e3b
Author: James Turton <[email protected]>
AuthorDate: Thu Jun 24 13:47:53 2021 +0200
Document bitwise logical functions.
---
.../sql-functions/010-math-and-trig.md | 450 +++++++------
.../050-aggregate-and-aggregate-statistical.md | 713 ++++++++++++---------
2 files changed, 662 insertions(+), 501 deletions(-)
diff --git a/_docs/en/sql-reference/sql-functions/010-math-and-trig.md
b/_docs/en/sql-reference/sql-functions/010-math-and-trig.md
index 87b6f12..af9cec4 100644
--- a/_docs/en/sql-reference/sql-functions/010-math-and-trig.md
+++ b/_docs/en/sql-reference/sql-functions/010-math-and-trig.md
@@ -3,254 +3,334 @@ title: "Math and Trig"
slug: "Math and Trig"
parent: "SQL Functions"
---
-Drill supports the math functions shown in the following table of math
functions plus trig functions listed at the end of this section. Most math
functions and all trig functions take these input types:
-* INTEGER
-* BIGINT
-* FLOAT
-* DOUBLE
-* SMALLINT*
-* DECIMAL**
+Drill supports the math functions shown in the following table of math
functions
+plus trig functions listed at the end of this section. Most math functions and
+all trig functions take these input types:
-\* Not supported. Drill treats SMALLINT as INT when reading from Parquet.
-\** Drill implicitly casts DECIMAL to DOUBLE for functions that take DOUBLE.
+- INTEGER
+- BIGINT
+- FLOAT
+- DOUBLE
+- SMALLINT\*
+- DECIMAL\*\*
-Exceptions are the LSHIFT and RSHIFT functions, which take all types except
FLOAT and DOUBLE types. DEGREES, EXP, RADIANS, and the multiple LOG functions
take the input types in this list plus the DECIMAL type.
+\* Not supported. Drill treats SMALLINT as INT when reading from Parquet.\
+\*\*
+Drill implicitly casts DECIMAL to DOUBLE for functions that take DOUBLE.
-## Table of Math Functions
-
-| Function | Return Type | Description
|
-|--------------|---------------|---------------------------------------------------------------------------|
-| ABS(x) | Same as input | Returns the absolute value of the input
argument x. |
-| CBRT(x) | FLOAT8 | Returns the cubic root of x.
|
-| CEIL(x) | Same as input | Returns the smallest integer not less than x.
|
-| CEILING(x) | Same as input | Same as CEIL.
|
-| DEGREES(x) | FLOAT8 | Converts x radians to degrees.
|
-| E() | FLOAT8 | Returns 2.718281828459045.
|
-| EXP(x) | FLOAT8 | Returns e (Euler's number) to the power of x.
|
-| FLOOR(x) | Same as input | Returns the largest integer not greater than
x. |
-| LOG(x) | FLOAT8 | Returns the natural log (base e) of x.
|
-| LOG(x, y) | FLOAT8 | Returns log base x to the y power.
|
-| LOG10(x) | FLOAT8 | Returns the common log of x.
|
-| LSHIFT(x, y) | Same as input | Shifts the binary x by y times to the left.
|
-| MOD(x, y) | FLOAT8 | Returns the remainder of x divided by y.
|
-| NEGATIVE(x) | Same as input | Returns x as a negative number.
|
-| PI | FLOAT8 | Returns pi.
|
-| POW(x, y) | FLOAT8 | Returns the value of x to the y power.
|
-| RADIANS(x) | FLOAT8 | Converts x degrees to radians.
|
-| RAND | FLOAT8 | Returns a random number from 0-1.
|
-| ROUND(x) | Same as input | Rounds to the nearest integer.
|
-| ROUND(x, y) | DECIMAL | Rounds x to y decimal places.
|
-| RSHIFT(x, y) | Same as input | Shifts the binary x by y times to the right.
|
-| SIGN(x) | INT | Returns the sign of x.
|
-| SQRT(x) | Same as input | Returns the square root of x.
|
-| TRUNC(x, y) | DOUBLE | Truncates x to y decimal places. Specifying y
is optional. Default is 0. |
+Exceptions are the LSHIFT and RSHIFT functions, which take all types except
+FLOAT and DOUBLE types. DEGREES, EXP, RADIANS, and the multiple LOG functions
+take the input types in this list plus the DECIMAL type.
+## Table of Math Functions
+| Function | Return Type | Description
|
+| ------------ | ------------- |
------------------------------------------------------------------------ |
+| \`&\`(x, y) | Same as input | Returns the bitwise AND of x with y.
|
+| \`\|\`(x, y) | Same as input | Returns the bitwise OR of x with y.
|
+| \`^\`(x, y) | Same as input | Returns the bitwise XOR of x with y.
|
+| ABS(x) | Same as input | Returns the absolute value of the input
argument x. |
+| CBRT(x) | FLOAT8 | Returns the cubic root of x.
|
+| CEIL(x) | Same as input | Returns the smallest integer not less than x.
|
+| CEILING(x) | Same as input | Same as CEIL.
|
+| DEGREES(x) | FLOAT8 | Converts x radians to degrees.
|
+| E() | FLOAT8 | Returns 2.718281828459045.
|
+| EXP(x) | FLOAT8 | Returns e (Euler's number) to the power of x.
|
+| FLOOR(x) | Same as input | Returns the largest integer not greater than
x. |
+| LOG(x) | FLOAT8 | Returns the natural log (base e) of x.
|
+| LOG(x, y) | FLOAT8 | Returns log base x to the y power.
|
+| LOG10(x) | FLOAT8 | Returns the common log of x.
|
+| LSHIFT(x, y) | Same as input | Shifts the binary x by y times to the left.
|
+| MOD(x, y) | FLOAT8 | Returns the remainder of x divided by y.
|
+| NEGATIVE(x) | Same as input | Returns x as a negative number.
|
+| PI | FLOAT8 | Returns pi.
|
+| POW(x, y) | FLOAT8 | Returns the value of x to the y power.
|
+| RADIANS(x) | FLOAT8 | Converts x degrees to radians.
|
+| RAND | FLOAT8 | Returns a random number from 0-1.
|
+| ROUND(x) | Same as input | Rounds to the nearest integer.
|
+| ROUND(x, y) | DECIMAL | Rounds x to y decimal places.
|
+| RSHIFT(x, y) | Same as input | Shifts the binary x by y times to the right.
|
+| SIGN(x) | INT | Returns the sign of x.
|
+| SQRT(x) | Same as input | Returns the square root of x.
|
+| TRUNC(x, y) | DOUBLE | Truncates x to y decimal places. Specifying y
is optional. Default is 0. |
## Math Function Examples
-Examples in this section use the `input2.json` file. Download the
`input2.json` file from the [Drill source
code](https://github.com/apache/drill/tree/master/exec/java-exec/src/test/resources/jsoninput)
page. You need to use a FROM clause in Drill queries.
+Examples in this section use the `input2.json` file. Download the `input2.json`
+file from the
+[Drill source
code](https://github.com/apache/drill/tree/master/exec/java-exec/src/test/resources/jsoninput)
+page.
+
+The following snippet of input2.json shows the relevant numeric content:
+
+```json
+{ "integer" : 2010,
+ "float" : 17.4,
+ "x": {
+ "y": "kevin",
+ "z": "paul"
+. . .
+}
+{ "integer" : -2002,
+ "float" : -1.2
+}
+. . .
+```
+
+```sql
+SELECT `integer` FROM dfs.`/Users/drill/input2.json`;
+```
+
+The output shows integer values not shown in the snippet. You can take
+a look at all the values in the input2.json file.
+
+```
+|------------|
+| integer |
+|------------|
+| 2010 |
+| -2002 |
+| 2001 |
+| 6005 |
+|------------|
+4 rows selected (0.113 seconds)
+```
+
+### Bitwise Logical Function Examples
+
+Note that backticks are required around function names which include
+special characters.
+
+```sql
+SELECT `&`(`integer`, 15) as last_nibble FROM dfs.`Users/drill/input2.json`
+```
+
+```
+|-------------|
+| last_nibble |
+|-------------|
+| 10 |
+| 14 |
+| 1 |
+| 5 |
+|-------------|
+4 rows selected (0.354 seconds)
+```
### ABS Example
-Get the absolute value of the integer key in `input2.json`. The following
snippet of input2.json shows the relevant integer content:
- { "integer" : 2010,
- "float" : 17.4,
- "x": {
- "y": "kevin",
- "z": "paul"
- . . .
- }
- { "integer" : -2002,
- "float" : -1.2
- }
- . . .
+Get the absolute value of the integer key in `input2.json`.
+
+```sql
+SELECT ABS(`integer`) FROM dfs.`/Users/drill/input2.json`;
+```
+```
+|------------|
+| EXPR$0 |
+|------------|
+| 2010 |
+| 2002 |
+| 2001 |
+| 6005 |
+|------------|
+4 rows selected (0.357 seconds)
+```
- SELECT `integer` FROM dfs.`/Users/drill/input2.json`;
-
-The output shows values not shown in the snippet. You can take a look at all
the values in the input2.json file.
-
- |------------|
- | integer |
- |------------|
- | 2010 |
- | -2002 |
- | 2001 |
- | 6005 |
- |------------|
- 4 rows selected (0.113 seconds)
+### CEIL Example
- SELECT ABS(`integer`) FROM dfs.`/Users/drill/input2.json`;
+Get the ceiling of float key values in input2.json. The input2.json file
+contains these float key values:
- |------------|
- | EXPR$0 |
- |------------|
- | 2010 |
- | 2002 |
- | 2001 |
- | 6005 |
- |------------|
- 4 rows selected (0.357 seconds)
+- 17.4
-### CEIL Example
-Get the ceiling of float key values in input2.json. The input2.json file
contains these float key values:
+- -1.2
-* 17.4
-* -1.2
-* 1.2
-* 1.2
+- 1.2
- SELECT CEIL(`float`) FROM dfs.`/Users/drill/input2.json`;
+- 1.2
- |------------|
- | EXPR$0 |
- |------------|
- | 18.0 |
- | -1.0 |
- | 2.0 |
- | 2.0 |
- |------------|
- 4 rows selected (0.647 seconds)
+```sql
+SELECT CEIL(`float`) FROM dfs.`/Users/drill/input2.json`;
+```
+```
+ |------------|
+ | EXPR$0 |
+ |------------|
+ | 18.0 |
+ | -1.0 |
+ | 2.0 |
+ | 2.0 |
+ |------------|
+ 4 rows selected (0.647 seconds)
+```
### FLOOR Example
+
Get the floor of float key values in input2.json.
- SELECT FLOOR(`float`) FROM dfs.`/Users/drill/input2.json`;
+```sql
+SELECT FLOOR(`float`) FROM dfs.`/Users/drill/input2.json`;
+```
+```
+|------------|
+| EXPR$0 |
+|------------|
+| 17.0 |
+| -2.0 |
+| 1.0 |
+| 1.0 |
+|------------|
+4 rows selected (0.11 seconds)
+```
+
+### ROUND Examples
+
+Open input2.json and change the first float value from 17.4 to 3.14159. Get
+values of the float columns in input2.json rounded as follows:
+- Rounded to the nearest integer.
+
+- Rounded to the fourth decimal place.
+
+```sql
+SELECT ROUND(`float`) FROM dfs.`/Users/drill/input2.json`;
+```
+```
|------------|
| EXPR$0 |
|------------|
- | 17.0 |
- | -2.0 |
+ | 3.0 |
+ | -1.0 |
| 1.0 |
| 1.0 |
|------------|
- 4 rows selected (0.11 seconds)
-
-### ROUND Examples
-Open input2.json and change the first float value from 17.4 to 3.14159. Get
values of the float columns in input2.json rounded as follows:
-
-* Rounded to the nearest integer.
-* Rounded to the fourth decimal place.
-
- SELECT ROUND(`float`) FROM dfs.`/Users/drill/input2.json`;
-
- |------------|
- | EXPR$0 |
- |------------|
- | 3.0 |
- | -1.0 |
- | 1.0 |
- | 1.0 |
- |------------|
- 4 rows selected (0.061 seconds)
-
- SELECT ROUND(`float`, 4) FROM dfs.`/Users/drill/input2.json`;
-
- |------------|
- | EXPR$0 |
- |------------|
- | 3.1416 |
- | -1.2 |
- | 1.2 |
- | 1.2 |
- |------------|
- 4 rows selected (0.059 seconds)
+ 4 rows selected (0.061 seconds)
+```
+```sql
+SELECT ROUND(`float`, 4) FROM dfs.`/Users/drill/input2.json`;
+```
+```
+ |------------|
+ | EXPR$0 |
+ |------------|
+ | 3.1416 |
+ | -1.2 |
+ | 1.2 |
+ | 1.2 |
+ |------------|
+ 4 rows selected (0.059 seconds)
+```
### LOG Examples
Get the base 2 log of 64.
- SELECT LOG(2, 64) FROM (VALUES(1));
-
- |------------|
- | EXPR$0 |
- |------------|
- | 6.0 |
- |------------|
- 1 row selected (0.069 seconds)
+```sql
+SELECT LOG(2, 64) FROM (VALUES(1));
+```
+```
+|------------|
+| EXPR$0 |
+|------------|
+| 6.0 |
+|------------|
+1 row selected (0.069 seconds)
+```
Get the common log of 100.
- SELECT LOG10(100) FROM (VALUES(1));
-
- |------------|
- | EXPR$0 |
- |------------|
- | 2.0 |
- |------------|
- 1 row selected (0.203 seconds)
+```sql
+SELECT LOG10(100) FROM (VALUES(1));
+```
+```
+|------------|
+| EXPR$0 |
+|------------|
+| 2.0 |
+|------------|
+1 row selected (0.203 seconds)
+```
Get the natural log of 7.5.
- SELECT LOG(7.5) FROM (VALUES(1));
-
- |---------------------|
- | EXPR$0 |
- |---------------------|
- | 2.0149030205422647 |
- |---------------------|
- 1 row selected (0.139 seconds)
+```sql
+SELECT LOG(7.5) FROM (VALUES(1));
+```
+```
+|---------------------|
+| EXPR$0 |
+|---------------------|
+| 2.0149030205422647 |
+|---------------------|
+1 row selected (0.139 seconds)
+```
## Trig Functions
Drill supports the following trig functions, which return a FLOAT8 result.
-* SIN(x)
+- SIN(x)\
Sine of angle x in radians
-* COS(x)
+- COS(x)\
Cosine of angle x in radians
-* TAN(x)
+- TAN(x)\
Tangent of angle x in radians
-* ASIN(x)
+- ASIN(x)\
Inverse sine of angle x in radians
-* ACOS(x)
+- ACOS(x)\
Inverse cosine of angle x in radians
-* ATAN(x)
+- ATAN(x)\
Inverse tangent of angle x in radians
-* SINH()
+- SINH()\
Hyperbolic sine of hyperbolic angle x in radians
-* COSH()
+- COSH()\
Hyperbolic cosine of hyperbolic angle x in radians
-* TANH()
+- TANH()\
Hyperbolic tangent of hyperbolic angle x in radians
### Trig Examples
-Find the sine and tangent of a 45 degree angle. First convert degrees to
radians for use in the SIN() function.
-
- SELECT RADIANS(30) AS Degrees FROM (VALUES(1));
-
- |------------|
- | Degrees |
- |------------|
- | 0.7853981633974483 |
- |------------|
- 1 row selected (0.045 seconds)
-
- SELECT SIN(0.7853981633974483) AS `Sine of 30 degrees` FROM (VALUES(1));
-
- |-----------------------|
- | Sine of 45 degrees |
- |-----------------------|
- | 0.7071067811865475 |
- |-----------------------|
- 1 row selected (0.059 seconds)
-
- SELECT TAN(0.7853981633974483) AS `Tangent of 30 degrees` from (VALUES(1));
-
- |-----------------------|
- | Tangent of 45 degrees |
- |-----------------------|
- | 0.9999999999999999 |
- |-----------------------|
-
+Find the sine and tangent of a 45 degree angle. First convert degrees to
radians
+for use in the SIN() function.
+
+```sql
+SELECT RADIANS(45) AS Radians FROM (VALUES(1));
+```
+```
+|--------------------|
+| Radians |
+|--------------------|
+| 0.7853981633974483 |
+|--------------------|
+1 row selected (0.045 seconds)
+```
+```sql
+SELECT SIN(0.7853981633974483) AS `Sine of 45 degrees` FROM (VALUES(1));
+```
+```
+|-----------------------|
+| Sine of 45 degrees |
+|-----------------------|
+| 0.7071067811865475 |
+|-----------------------|
+1 row selected (0.059 seconds)
+```
+```sql
+SELECT TAN(0.7853981633974483) AS `Tangent of 45 degrees` from (VALUES(1));
+```
+```
+|-----------------------|
+| Tangent of 45 degrees |
+|-----------------------|
+| 0.9999999999999999 |
+|-----------------------|
+```
diff --git
a/_docs/en/sql-reference/sql-functions/050-aggregate-and-aggregate-statistical.md
b/_docs/en/sql-reference/sql-functions/050-aggregate-and-aggregate-statistical.md
index 539e9f9..139ab82 100644
---
a/_docs/en/sql-reference/sql-functions/050-aggregate-and-aggregate-statistical.md
+++
b/_docs/en/sql-reference/sql-functions/050-aggregate-and-aggregate-statistical.md
@@ -1,92 +1,107 @@
----
-title: "Aggregate and Aggregate Statistical"
-slug: "Aggregate and Aggregate Statistical"
-parent: "SQL Functions"
----
+______________________________________________________________________
+
+## title: "Aggregate and Aggregate Statistical" slug: "Aggregate and Aggregate
Statistical" parent: "SQL Functions"
## Aggregate Functions
-The following table lists the aggregate functions that you can use in Drill
queries.
+The following table lists the aggregate functions that you can use in Drill
+queries.
+
+|\-------------------------------------------|-----------------------------------------------------------------------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------|
+
+| **Function** | **Argument
Type**
| **Return Type**
|
+| ------------------------------------------------------------ |
-----------------------------------------------------------------------------------------------------------------------------------------
|
------------------------------------------------------------------------------------------------------------------------------------
|
+| ANY_VALUE(expression) | BIT, INT,
BIGINT, FLOAT4, FLOAT8, DATE, TIMESTAMP, TIME, VARCHAR, VARBINARY, LIST, MAP,
INTERVAL, INTERVALDAY, INTERVALYEAR, VARDECIMAL | Same as argument type
|
+| AVG(expression) | SMALLINT,
INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL, INTERVAL
| DECIMAL for DECIMAL argument,
DOUBLE for all other arguments
|
+| BOOL_AND(expression), BOOL_OR(expression) | BIT
| BIT
|
+| BIT_AND(expression), BIT_OR(expression), BIT_XOR(expression) | INT, BIGINT
| Same as argument type
|
+| COUNT(\*) | -
| BIGINT
|
+| COUNT(\[DISTINCT\] expression) | any
| BIGINT
|
+| MAX(expression), MIN(expression) | BINARY,
DECIMAL, VARCHAR, DATE, TIME, or TIMESTAMP
| Same as argument type
|
+| SUM(expression) | SMALLINT,
INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL, INTERVAL
| DECIMAL for DECIMAL
argument, BIGINT for any integer-type argument (including BIGINT), DOUBLE
for floating-point arguments |
+| ------------------------------------------- |
-----------------------------------------------------------------------------------------------------------------------------------------
|
------------------------------------------------------------------------------------------------------------------------------------
|
+
+- Drill 1.14 and later supports the ANY_VALUE function.
+- Starting in Drill 1.14, the DECIMAL data type is enabled by default.
+- AVG, COUNT, MIN, MAX, and SUM accept ALL and DISTINCT keywords. The default
is
+ ALL.
+- The aggregate function examples use the `cp` storage plugin to access the
+ [`employee.json`](%7B%7Bsite.baseurl%7D%7D/docs/querying-json-files/) file
+ installed with Drill. By default, JSON reads numbers as double-precision
+ floating point numbers. These examples assume that you are using the default
+ option
+
[all_text_mode](%7B%7Bsite.baseurl%7D%7D/docs/json-data-model/#handling-type-differences)
+ set to false.
-|-------------------------------------------|-----------------------------------------------------------------------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------|
-| **Function** | **Argument Type**
| **Return Type**
|
-|-------------------------------------------|-----------------------------------------------------------------------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------|
-| ANY_VALUE(expression) | BIT, INT, BIGINT, FLOAT4,
FLOAT8, DATE, TIMESTAMP, TIME, VARCHAR, VARBINARY, LIST, MAP, INTERVAL,
INTERVALDAY, INTERVALYEAR, VARDECIMAL | Same as argument type
|
-| AVG(expression) | SMALLINT, INTEGER, BIGINT,
FLOAT, DOUBLE, DECIMAL, INTERVAL
| DECIMAL for DECIMAL argument, DOUBLE for all
other arguments
|
-| BOOL_AND(expression), BOOL_OR(expression) | BIT
| BIT
|
-| BIT_AND(expression), BIT_OR(expression) | INT, BIGINT
|
|
-| COUNT(*) | -
| BIGINT
|
-| COUNT([DISTINCT] expression) | any
| BIGINT
|
-| MAX(expression), MIN(expression) | BINARY, DECIMAL, VARCHAR, DATE,
TIME, or TIMESTAMP
| Same as argument type
|
-| SUM(expression) | SMALLINT, INTEGER, BIGINT,
FLOAT, DOUBLE, DECIMAL, INTERVAL
| DECIMAL for DECIMAL argument, BIGINT for
any integer-type argument (including BIGINT), DOUBLE for floating-point
arguments |
-|-------------------------------------------|-----------------------------------------------------------------------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------|
+## ANY_VALUE
+Supported in Drill 1.14 and later. Returns one of the values of value across
all
+input values. This function is NOT specified in the SQL standard.
-- Drill 1.14 and later supports the ANY_VALUE function.
-- Starting in Drill 1.14, the DECIMAL data type is enabled by default.
-- AVG, COUNT, MIN, MAX, and SUM accept ALL and DISTINCT keywords. The default
is ALL.
-- The aggregate function examples use the `cp` storage plugin to access the
[`employee.json`]({{site.baseurl}}/docs/querying-json-files/) file installed
with Drill. By default, JSON reads numbers as double-precision floating point
numbers. These examples assume that you are using the default option
[all_text_mode]({{site.baseurl}}/docs/json-data-model/#handling-type-differences)
set to false.
+### ANY_VALUE Syntax
-## ANY_VALUE
-Supported in Drill 1.14 and later. Returns one of the values of value across
all input values. This function is NOT specified in the SQL standard.
-
-### ANY_VALUE Syntax
- ANY_VALUE([ ALL | DISTINCT ] value)
-
-### ANY_VALUE Examples
-
- SELECT ANY_VALUE(employee_id) AS anyemp FROM cp.`employee.json`;
- |--------|
- | anyemp |
- |--------|
- | 1156 |
- |--------|
-
- SELECT ANY_VALUE(ALL employee_id) AS anyemp FROM cp.`employee.json`;
- |--------|
- | anyemp |
- |--------|
- | 1156 |
- |--------|
-
- SELECT ANY_VALUE(DISTINCT employee_id) AS anyemp FROM cp.`employee.json`;
- |--------|
- | anyemp |
- |--------|
- | 1156 |
- |--------|
-
- SELECT ANY_VALUE(employee_id) as anyemp, salary as empsal FROM
cp.`employee.json` GROUP BY salary;
- |--------|---------|
- | anyemp | empsal |
- |--------|---------|
- | 1155 | 20.0 |
- | 197 | 3700.0 |
- | 1115 | 4200.0 |
- | 589 | 4300.0 |
- | 403 | 4400.0 |
- | 204 | 4500.0 |
- ...
-
- SELECT ANY_VALUE(employee_id) as anyemp FROM cp.`employee.json` GROUP BY
salary ORDER BY anyemp;
- |--------|
- | anyemp |
- |--------|
- | 1 |
- | 4 |
- | 6 |
- | 8 |
- | 10 |
- | 13 |
- ...
-
-## AVG
+```
+ANY_VALUE([ ALL | DISTINCT ] value)
+```
+
+### ANY_VALUE Examples
+
+```
+SELECT ANY_VALUE(employee_id) AS anyemp FROM cp.`employee.json`;
+|--------|
+| anyemp |
+|--------|
+| 1156 |
+|--------|
+
+SELECT ANY_VALUE(ALL employee_id) AS anyemp FROM cp.`employee.json`;
+|--------|
+| anyemp |
+|--------|
+| 1156 |
+|--------|
+
+SELECT ANY_VALUE(DISTINCT employee_id) AS anyemp FROM cp.`employee.json`;
+|--------|
+| anyemp |
+|--------|
+| 1156 |
+|--------|
+
+SELECT ANY_VALUE(employee_id) as anyemp, salary as empsal FROM
cp.`employee.json` GROUP BY salary;
+|--------|---------|
+| anyemp | empsal |
+|--------|---------|
+| 1155 | 20.0 |
+| 197 | 3700.0 |
+| 1115 | 4200.0 |
+| 589 | 4300.0 |
+| 403 | 4400.0 |
+| 204 | 4500.0 |
+...
+
+SELECT ANY_VALUE(employee_id) as anyemp FROM cp.`employee.json` GROUP BY
salary ORDER BY anyemp;
+|--------|
+| anyemp |
+|--------|
+| 1 |
+| 4 |
+| 6 |
+| 8 |
+| 10 |
+| 13 |
+...
+```
+
+## AVG
Returns the average of a numerical expression.
### AVG Syntax
- AVG([ALL | DISTINCT] expression)
+```
+AVG([ALL | DISTINCT] expression)
+```
### AVG Examples
@@ -100,236 +115,274 @@ ALTER SESSION SET `store.json.all_text_mode` = false;
1 row selected (0.073 seconds)
```
-Take a look at the salaries of employees having IDs 1139, 1140, and 1141.
These are the salaries that subsequent examples will average and sum.
-
- SELECT * FROM cp.`employee.json` WHERE employee_id IN (1139, 1140, 1141);
-
|-------------|-----------------|------------|-----------|-------------|-------------------------|----------|---------------|------------|-----------------------|------------|---------------|-----------------|----------------|--------|----------------------|
- | employee_id | full_name | first_name | last_name | position_id |
position_title | store_id | department_id | birth_date | hire_date
| salary | supervisor_id | education_level | marital_status |
gender | management_role |
-
|-------------|-----------------|------------|-----------|-------------|-------------------------|----------|---------------|------------|-----------------------|------------|---------------|-----------------|----------------|--------|----------------------|
- | 1139 | Jeanette Belsey | Jeanette | Belsey | 12 |
Store Assistant Manager | 18 | 11 | 1972-05-12 | 1998-01-01
00:00:00.0 | 10000.0000 | 17 | Graduate Degree | S | M
| Store Management |
- | 1140 | Mona Jaramillo | Mona | Jaramillo | 13 |
Store Shift Supervisor | 18 | 11 | 1961-09-24 | 1998-01-01
00:00:00.0 | 8900.0000 | 1139 | Partial College | S | M
| Store Management |
- | 1141 | James Compagno | James | Compagno | 15 |
Store Permanent Checker | 18 | 15 | 1914-02-02 | 1998-01-01
00:00:00.0 | 6400.0000 | 1139 | Graduate Degree | S | M
| Store Full Time Staf |
-
|-------------|-----------------|------------|-----------|-------------|-------------------------|----------|---------------|------------|-----------------------|------------|---------------|-----------------|----------------|--------|----------------------|
- 3 rows selected (0.284 seconds)
-
- SELECT AVG(salary) FROM cp.`employee.json` WHERE employee_id IN (1139,
1140, 1141);
- |-------------------|
- | EXPR$0 |
- |-------------------|
- | 8433.333333333334 |
- |-------------------|
- 1 row selected (0.208 seconds)
-
- SELECT AVG(ALL salary) FROM cp.`employee.json` WHERE employee_id IN (1139,
1140, 1141);
- |-------------------|
- | EXPR$0 |
- |-------------------|
- | 8433.333333333334 |
- |-------------------|
- 1 row selected (0.17 seconds)
-
- SELECT AVG(DISTINCT salary) FROM cp.`employee.json`;
- |--------------------|
- | EXPR$0 |
- |--------------------|
- | 12773.333333333334 |
- |--------------------|
- 1 row selected (0.384 seconds)
-
- SELECT education_level, AVG(salary) FROM cp.`employee.json` GROUP BY
education_level;
- |---------------------|--------------------|
- | education_level | EXPR$1 |
- |---------------------|--------------------|
- | Graduate Degree | 4392.823529411765 |
- | Bachelors Degree | 4492.404181184669 |
- | Partial College | 4047.1180555555557 |
- | High School Degree | 3516.1565836298932 |
- | Partial High School | 3511.0852713178297 |
- |---------------------|--------------------|
- 5 rows selected (0.495 seconds)
+Take a look at the salaries of employees having IDs 1139, 1140, and 1141. These
+are the salaries that subsequent examples will average and sum.
+
+```
+SELECT * FROM cp.`employee.json` WHERE employee_id IN (1139, 1140, 1141);
+|-------------|-----------------|------------|-----------|-------------|-------------------------|----------|---------------|------------|-----------------------|------------|---------------|-----------------|----------------|--------|----------------------|
+| employee_id | full_name | first_name | last_name | position_id |
position_title | store_id | department_id | birth_date | hire_date
| salary | supervisor_id | education_level | marital_status |
gender | management_role |
+|-------------|-----------------|------------|-----------|-------------|-------------------------|----------|---------------|------------|-----------------------|------------|---------------|-----------------|----------------|--------|----------------------|
+| 1139 | Jeanette Belsey | Jeanette | Belsey | 12 | Store
Assistant Manager | 18 | 11 | 1972-05-12 | 1998-01-01
00:00:00.0 | 10000.0000 | 17 | Graduate Degree | S | M
| Store Management |
+| 1140 | Mona Jaramillo | Mona | Jaramillo | 13 | Store
Shift Supervisor | 18 | 11 | 1961-09-24 | 1998-01-01
00:00:00.0 | 8900.0000 | 1139 | Partial College | S | M
| Store Management |
+| 1141 | James Compagno | James | Compagno | 15 | Store
Permanent Checker | 18 | 15 | 1914-02-02 | 1998-01-01
00:00:00.0 | 6400.0000 | 1139 | Graduate Degree | S | M
| Store Full Time Staf |
+|-------------|-----------------|------------|-----------|-------------|-------------------------|----------|---------------|------------|-----------------------|------------|---------------|-----------------|----------------|--------|----------------------|
+3 rows selected (0.284 seconds)
+
+SELECT AVG(salary) FROM cp.`employee.json` WHERE employee_id IN (1139, 1140,
1141);
+|-------------------|
+| EXPR$0 |
+|-------------------|
+| 8433.333333333334 |
+|-------------------|
+1 row selected (0.208 seconds)
+
+SELECT AVG(ALL salary) FROM cp.`employee.json` WHERE employee_id IN (1139,
1140, 1141);
+|-------------------|
+| EXPR$0 |
+|-------------------|
+| 8433.333333333334 |
+|-------------------|
+1 row selected (0.17 seconds)
+
+SELECT AVG(DISTINCT salary) FROM cp.`employee.json`;
+|--------------------|
+| EXPR$0 |
+|--------------------|
+| 12773.333333333334 |
+|--------------------|
+1 row selected (0.384 seconds)
+
+SELECT education_level, AVG(salary) FROM cp.`employee.json` GROUP BY
education_level;
+|---------------------|--------------------|
+| education_level | EXPR$1 |
+|---------------------|--------------------|
+| Graduate Degree | 4392.823529411765 |
+| Bachelors Degree | 4492.404181184669 |
+| Partial College | 4047.1180555555557 |
+| High School Degree | 3516.1565836298932 |
+| Partial High School | 3511.0852713178297 |
+|---------------------|--------------------|
+5 rows selected (0.495 seconds)
+```
## BOOL_AND and BOOL_OR
+
Returns the result of a logical AND (resp. OR) over the specified expression.
### BOOL_AND and BOOL_OR Syntax
- BOOL_AND(expression)
- BOOL_OR(expression)
+```
+BOOL_AND(expression)
+BOOL_OR(expression)
+```
### BOOL_AND and BOOL_OR Examples
- SELECT BOOL_AND(last_name = 'Spence') FROM cp.`employee.json`;
- |--------|
- | EXPR$0 |
- |--------|
- | false |
- |--------|
-
- SELECT BOOL_OR(last_name = 'Spence') FROM cp.`employee.json`;
- |--------|
- | EXPR$0 |
- |--------|
- | true |
- |--------|
+```
+SELECT BOOL_AND(last_name = 'Spence') FROM cp.`employee.json`;
+|--------|
+| EXPR$0 |
+|--------|
+| false |
+|--------|
+
+SELECT BOOL_OR(last_name = 'Spence') FROM cp.`employee.json`;
+|--------|
+| EXPR$0 |
+|--------|
+| true |
+|--------|
+```
### BOOL_AND and BOOL_OR Usage Notes
-1. EVERY is nearly an alias for BOOL_AND but returns a TINYINT rather than a
BIT.
-
+1. EVERY is nearly an alias for BOOL_AND but returns a TINYINT rather than a
+ BIT.
-## BIT_AND and BIT_OR
-Returns the result of a bitwise AND (resp. OR) over the specified expression.
+## BIT_AND, BIT_OR and BIT_XOR
-### BIT_AND and BIT_OR Syntax
+Returns the result of a bitwise AND (resp. OR, XOR) over the specified
expression.
- BIT_AND(expression)
- BIT_OR(expression)
+### BIT_AND, BIT_OR and BIT_XOR Syntax
-### BIT_AND and BIT_OR Examples
-
- SELECT BIT_AND(position_id) FROM cp.`employee.json`;
- |--------|
- | EXPR$0 |
- |--------|
- | 0 |
- |--------|
+```
+BIT_AND(expression)
+BIT_OR(expression)
+BIT_XOR(expression)
+```
- SELECT BIT_OR(position_id) FROM cp.`employee.json`;
- |--------|
- | EXPR$0 |
- |--------|
- | 31 |
- |--------|
+### BIT_AND, BIT_OR, BIT_XOR Examples
+```
+SELECT BIT_AND(position_id) FROM cp.`employee.json`;
+|--------|
+| EXPR$0 |
+|--------|
+| 0 |
+|--------|
+
+SELECT BIT_OR(position_id) FROM cp.`employee.json`;
+|--------|
+| EXPR$0 |
+|--------|
+| 31 |
+|--------|
+
+SELECT BIT_XOR(position_id) FROM cp.`employee.json`;
+|--------|
+| EXPR$0 |
+|--------|
+| 4 |
+|--------|
+```
## COUNT
+
Returns the number of rows that match the given criteria.
### COUNT Syntax
- SELECT COUNT([ALL | DISTINCT] expression) FROM . . .
- SELECT COUNT(*) FROM . . .
-
-* expression
- Returns the number of values of the specified expression.
-* DISTINCT expression
- Returns the number of distinct values in the expression.
-* ALL expression
- Returns the number of values of the specified expression.
-* * (asterisk)
- Returns the number of records in the table.
+```
+SELECT COUNT([ALL | DISTINCT] expression) FROM . . .
+SELECT COUNT(*) FROM . . .
+```
+- expression\
+ Returns the number of values of the specified expression.
+- DISTINCT expression\
+ Returns the number of distinct values in the expression.
+- ALL expression\
+ Returns the number of values of the specified expression.
+- - (asterisk) Returns the number of records in the table.
### COUNT Examples
- SELECT COUNT(DISTINCT salary) FROM cp.`employee.json`;
- |--------|
- | EXPR$0 |
- |--------|
- | 48 |
- |--------|
- 1 row selected (0.159 seconds)
-
- SELECT COUNT(ALL salary) FROM cp.`employee.json`;
- |--------|
- | EXPR$0 |
- |--------|
- | 1155 |
- |--------|
- 1 row selected (0.106 seconds)
-
- SELECT COUNT(salary) FROM cp.`employee.json`;
- |--------|
- | EXPR$0 |
- |--------|
- | 1155 |
- |--------|
- 1 row selected (0.102 seconds)
-
- SELECT COUNT(*) FROM cp.`employee.json`;
- |--------|
- | EXPR$0 |
- |--------|
- | 1155 |
- |--------|
- 1 row selected (0.174 seconds)
+```
+SELECT COUNT(DISTINCT salary) FROM cp.`employee.json`;
+|--------|
+| EXPR$0 |
+|--------|
+| 48 |
+|--------|
+1 row selected (0.159 seconds)
+
+SELECT COUNT(ALL salary) FROM cp.`employee.json`;
+|--------|
+| EXPR$0 |
+|--------|
+| 1155 |
+|--------|
+1 row selected (0.106 seconds)
+
+SELECT COUNT(salary) FROM cp.`employee.json`;
+|--------|
+| EXPR$0 |
+|--------|
+| 1155 |
+|--------|
+1 row selected (0.102 seconds)
+
+SELECT COUNT(*) FROM cp.`employee.json`;
+|--------|
+| EXPR$0 |
+|--------|
+| 1155 |
+|--------|
+1 row selected (0.174 seconds)
+```
## MIN and MAX
-These functions return the smallest and largest values of the selected
expressions, respectively.
+
+These functions return the smallest and largest values of the selected
+expressions, respectively.
### MIN and MAX Syntax
- MIN(expression)
- MAX(expression)
+```
+MIN(expression)
+MAX(expression)
+```
### MIN and MAX Examples
- SELECT MIN(salary) FROM cp.`employee.json`;
- |--------|
- | EXPR$0 |
- |--------|
- | 20.0 |
- |--------|
- 1 row selected (0.138 seconds)
-
- SELECT MAX(salary) FROM cp.`employee.json`;
- |---------|
- | EXPR$0 |
- |---------|
- | 80000.0 |
- |---------|
- 1 row selected (0.139 seconds)
-
-Use a correlated subquery to find the names and salaries of the lowest paid
employees:
-
- SELECT full_name, SALARY FROM cp.`employee.json` WHERE salary = (SELECT
MIN(salary) FROM cp.`employee.json`);
- |-----------------|--------|
- | full_name | SALARY |
- |-----------------|--------|
- | Leopoldo Renfro | 20.0 |
- | Donna Brockett | 20.0 |
- | Laurie Anderson | 20.0 |
- . . .
+```
+SELECT MIN(salary) FROM cp.`employee.json`;
+|--------|
+| EXPR$0 |
+|--------|
+| 20.0 |
+|--------|
+1 row selected (0.138 seconds)
+
+SELECT MAX(salary) FROM cp.`employee.json`;
+|---------|
+| EXPR$0 |
+|---------|
+| 80000.0 |
+|---------|
+1 row selected (0.139 seconds)
+```
+
+Use a correlated subquery to find the names and salaries of the lowest paid
+employees:
+
+```
+SELECT full_name, SALARY FROM cp.`employee.json` WHERE salary = (SELECT
MIN(salary) FROM cp.`employee.json`);
+|-----------------|--------|
+| full_name | SALARY |
+|-----------------|--------|
+| Leopoldo Renfro | 20.0 |
+| Donna Brockett | 20.0 |
+| Laurie Anderson | 20.0 |
+. . .
+```
## SUM
+
Returns the sum of a numerical expresion.
### SUM syntax
- SUM([DISTINCT | ALL] expression)
+```
+SUM([DISTINCT | ALL] expression)
+```
### Examples
- SELECT SUM(ALL salary) FROM cp.`employee.json`;
- |-----------|
- | EXPR$0 |
- |-----------|
- | 4642640.0 |
- |-----------|
- 1 row selected (0.123 seconds)
-
- SELECT SUM(DISTINCT salary) FROM cp.`employee.json`;
- |----------|
- | EXPR$0 |
- |----------|
- | 613120.0 |
- |----------|
- 1 row selected (0.309 seconds)
-
- SELECT SUM(salary) FROM cp.`employee.json` WHERE employee_id IN (1139,
1140, 1141);
- |---------|
- | EXPR$0 |
- |---------|
- | 25300.0 |
- |---------|
- 1 row selected (1.995 seconds)
+```
+SELECT SUM(ALL salary) FROM cp.`employee.json`;
+|-----------|
+| EXPR$0 |
+|-----------|
+| 4642640.0 |
+|-----------|
+1 row selected (0.123 seconds)
+
+SELECT SUM(DISTINCT salary) FROM cp.`employee.json`;
+|----------|
+| EXPR$0 |
+|----------|
+| 613120.0 |
+|----------|
+1 row selected (0.309 seconds)
+
+SELECT SUM(salary) FROM cp.`employee.json` WHERE employee_id IN (1139, 1140,
1141);
+|---------|
+| EXPR$0 |
+|---------|
+| 25300.0 |
+|---------|
+1 row selected (1.995 seconds)
+```
## Aggregate Statistical Functions
-The following table lists the aggregate statistical functions that you can use
in Drill queries.
+The following table lists the aggregate statistical functions that you can use
+in Drill queries.
| **Function** | **Argument Type**
| **Return Type** |
-|-------------------------------|---------------------------------------------------|-------------------------------------------------|
+| ----------------------------- |
------------------------------------------------- |
----------------------------------------------- |
| APPROX_COUNT_DUPS(expression) | any
| BIGINT |
| STDDEV(expression) | SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE,
DECIMAL | DECIMAL for DECIMAL arguments, otherwise DOUBLE |
| STDDEV_POP(expression) | SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE,
DECIMAL | DECIMAL for DECIMAL arguments, otherwise DOUBLE |
@@ -342,67 +395,82 @@ Returns an approximate count of the values that are
duplicates (not unique).
### APPROX_COUNT_DUPS Syntax
- APPROX_COUNT_DUPS( expression )
+```
+APPROX_COUNT_DUPS( expression )
+```
### APPROX_COUNT_DUPS Examples
- select
- COUNT(*),
- APPROX_COUNT_DUPS(e1.employee_id),
- APPROX_COUNT_DUPS(e1.gender)
- FROM cp.`employee.json` e1
-
- |--------|--------|--------|
- | EXPR$0 | EXPR$1 | EXPR$2 |
- | ------ | ------ | ------ |
- | 1155 | 0 | 1153 |
- |--------|--------|--------|
+```
+select
+ COUNT(*),
+ APPROX_COUNT_DUPS(e1.employee_id),
+ APPROX_COUNT_DUPS(e1.gender)
+FROM cp.`employee.json` e1
+
+|--------|--------|--------|
+| EXPR$0 | EXPR$1 | EXPR$2 |
+| ------ | ------ | ------ |
+| 1155 | 0 | 1153 |
+|--------|--------|--------|
+```
Use COUNT - APPROX_COUNT_DUPS to approximate a distinct count.
- select
- COUNT(*),
- COUNT(salary) - APPROX_COUNT_DUPS(salary),
- COUNT(distinct salary)
- from cp.`employee.json`;
-
- |--------|--------|--------|
- | EXPR$0 | EXPR$1 | EXPR$2 |
- |--------|--------|--------|
- | 1155 | 48 | 48 |
- |--------|--------|--------|
+```
+select
+ COUNT(*),
+ COUNT(salary) - APPROX_COUNT_DUPS(salary),
+ COUNT(distinct salary)
+from cp.`employee.json`;
+
+|--------|--------|--------|
+| EXPR$0 | EXPR$1 | EXPR$2 |
+|--------|--------|--------|
+| 1155 | 48 | 48 |
+|--------|--------|--------|
+```
### APPROX_COUNT_DUPS Usage Notes
-The underlying Bloom filter is a probabilistic data structure that may return
a false positive when an element is tested for duplication. Consequently, the
approximate count returned _overestimates_ the true duplicate count. In return
for this inaccuracy, Bloom filters are highly space- and time-efficient at
large scales with the specifics determined by the parameters of the filter (see
below).
+The underlying Bloom filter is a probabilistic data structure that may return a
+false positive when an element is tested for duplication. Consequently, the
+approximate count returned _overestimates_ the true duplicate count. In return
+for this inaccuracy, Bloom filters are highly space- and time-efficient at
large
+scales with the specifics determined by the parameters of the filter (see
+below).
### Configuration options
-{% include startnote.html %}
-The APPROX_COUNT_DUPS function is used internally by Drill when it computes
table statistics. As a result, setting configuration options that affect it in
the global configuration scope will affect the computation of table statistics
accordingly.
-{% include endnote.html %}
+{% include startnote.html %} The APPROX_COUNT_DUPS function is used internally
+by Drill when it computes table statistics. As a result, setting configuration
+options that affect it in the global configuration scope will affect the
+computation of table statistics accordingly. {% include endnote.html %}
- exec.statistics.ndv_extrapolation_bf_elements
- exec.statistics.ndv_extrapolation_bf_fpprobability
-
## STDDEV
Returns the sample standard deviation.
### STDDEV Syntax
- STDDEV(expression)
+```
+STDDEV(expression)
+```
### STDDEV Examples
- SELECT STDDEV(salary) from cp.`employee.json`;
+```
+SELECT STDDEV(salary) from cp.`employee.json`;
- |-------------------|
- | EXPR$0 |
- |-------------------|
- | 5371.847873988941 |
- |-------------------|
+|-------------------|
+| EXPR$0 |
+|-------------------|
+| 5371.847873988941 |
+|-------------------|
+```
### STDDEV Usage Notes
@@ -410,21 +478,26 @@ Returns the sample standard deviation.
## STDDEV_POP
-Returns the estimate of the population standard deviation obtained by applying
Bessel's correction to the sample standard deviation.
+Returns the estimate of the population standard deviation obtained by applying
+Bessel's correction to the sample standard deviation.
### STDDEV_POP Syntax
- STDDEV_POP(expression)
+```
+STDDEV_POP(expression)
+```
### STDDEV_POP Examples
- SELECT STDDEV_POP(salary) from cp.`employee.json`;
+```
+SELECT STDDEV_POP(salary) from cp.`employee.json`;
- |-------------------|
- | EXPR$0 |
- |-------------------|
- | 5369.521895151171 |
- |-------------------|
+|-------------------|
+| EXPR$0 |
+|-------------------|
+| 5369.521895151171 |
+|-------------------|
+```
## VARIANCE
@@ -432,17 +505,21 @@ Returns the sample variance.
### VARIANCE Syntax
- VARIANCE(expression)
+```
+VARIANCE(expression)
+```
### VARIANCE Examples
- SELECT VARIANCE(salary) from cp.`employee.json`;
+```
+SELECT VARIANCE(salary) from cp.`employee.json`;
- |--------------------|
- | EXPR$0 |
- |--------------------|
- | 28856749.581279505 |
- |--------------------|
+|--------------------|
+| EXPR$0 |
+|--------------------|
+| 28856749.581279505 |
+|--------------------|
+```
### VARIANCE Usage Notes
@@ -450,19 +527,23 @@ Returns the sample variance.
## VAR_POP
-Returns the estimate of the population variance obtained by applying Bessel's
correction to the sample variance.
+Returns the estimate of the population variance obtained by applying Bessel's
+correction to the sample variance.
### VAR_POP Syntax
- VAR_POP(expression)
+```
+VAR_POP(expression)
+```
### VAR_POP Examples
- SELECT VAR_POP(salary) from cp.`employee.json`;
-
- |--------------------|
- | EXPR$0 |
- |--------------------|
- | 28831765.382507823 |
- |--------------------|
+```
+SELECT VAR_POP(salary) from cp.`employee.json`;
+|--------------------|
+| EXPR$0 |
+|--------------------|
+| 28831765.382507823 |
+|--------------------|
+```
