You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@druid.apache.org by ab...@apache.org on 2024/01/26 08:33:08 UTC
(druid) branch 29.0.0 updated: Document pivot and unpivot operators (#15669) (#15763)
This is an automated email from the ASF dual-hosted git repository.
abhishek pushed a commit to branch 29.0.0
in repository https://gitbox.apache.org/repos/asf/druid.git
The following commit(s) were added to refs/heads/29.0.0 by this push:
new e243be67829 Document pivot and unpivot operators (#15669) (#15763)
e243be67829 is described below
commit e243be67829d0fb5e8e8764669747c21536e7e28
Author: Victoria Lim <vt...@users.noreply.github.com>
AuthorDate: Fri Jan 26 00:33:01 2024 -0800
Document pivot and unpivot operators (#15669) (#15763)
Co-authored-by: Katya Macedo <38...@users.noreply.github.com>
---
docs/querying/sql-operators.md | 7 +++
docs/querying/sql.md | 125 ++++++++++++++++++++++++++++++++++++++---
website/.spelling | 4 ++
3 files changed, 129 insertions(+), 7 deletions(-)
diff --git a/docs/querying/sql-operators.md b/docs/querying/sql-operators.md
index da295821ecf..ebf68099019 100644
--- a/docs/querying/sql-operators.md
+++ b/docs/querying/sql-operators.md
@@ -108,3 +108,10 @@ Also see the [CONCAT function](sql-scalar.md#string-functions).
|`x NOT IN (values)`|True if _x_ is not one of the listed values|
|`x IN (subquery)`|True if _x_ is returned by the subquery. This will be translated into a join; see [Query translation](sql-translation.md) for details.|
|`x NOT IN (subquery)`|True if _x_ is not returned by the subquery. This will be translated into a join; see [Query translation](sql-translation.md) for details.|
+
+## Other operators
+
+|Operator|Description|
+|--------|-----------|
+|`PIVOT (aggregation_function(column_to_aggregate) FOR column_with_values_to_pivot IN (pivoted_column1 [, pivoted_column2 ...]))`|Carries out an aggregation and transforms rows into columns in the output.|
+|`UNPIVOT (values_column FOR names_column IN (unpivoted_column1 [, unpivoted_column2 ... ]))`|Transforms existing column values into rows.|
\ No newline at end of file
diff --git a/docs/querying/sql.md b/docs/querying/sql.md
index ce438cbddc9..6da894e4e67 100644
--- a/docs/querying/sql.md
+++ b/docs/querying/sql.md
@@ -57,6 +57,8 @@ Druid SQL supports SELECT queries with the following structure:
[ WITH tableName [ ( column1, column2, ... ) ] AS ( query ) ]
SELECT [ ALL | DISTINCT ] { * | exprs }
FROM { <table> | (<subquery>) | <o1> [ INNER | LEFT ] JOIN <o2> ON condition }
+[PIVOT (aggregation_function(column_to_aggregate) FOR column_with_values_to_pivot IN (pivoted_column1 [, pivoted_column2 ...]))]
+[UNPIVOT (values_column FOR names_column IN (unpivoted_column1 [, unpivoted_column2 ... ]))]
[ CROSS JOIN UNNEST(source_expression) as table_alias_name(column_alias_name) ]
[ WHERE expr ]
[ GROUP BY [ exprs | GROUPING SETS ( (exprs), ... ) | ROLLUP (exprs) | CUBE (exprs) ] ]
@@ -85,6 +87,121 @@ FROM clause, metadata tables are not considered datasources. They exist only in
For more information about table, lookup, query, and join datasources, refer to the [Datasources](datasource.md)
documentation.
+## PIVOT
+
+:::info
+The PIVOT operator is an [experimental feature](../development/experimental-features.md).
+:::
+
+The PIVOT operator carries out an aggregation and transforms rows into columns in the output.
+
+The following is the general syntax for the PIVOT operator. Note that the PIVOT operator is enclosed in parentheses and forms part of the FROM clause of the query.
+
+```sql
+PIVOT (aggregation_function(column_to_aggregate)
+ FOR column_with_values_to_pivot
+ IN (pivoted_column1 [, pivoted_column2 ...])
+)
+```
+
+PIVOT syntax parameters:
+
+* `aggregation_function`: An aggregation function, such as SUM, COUNT, MIN, MAX, or AVG.
+* `column_to_aggregate`: The source column to be aggregated.
+* `column_with_values_to_pivot`: The column that contains values for the pivoted column names.
+* `pivoted_columnN`: The list of values to pivot into headers in the output.
+
+The following example demonstrates how to transform `cityName` values into column headers `ba_sum_deleted` and `ny_sum_deleted`:
+
+```sql
+SELECT user, channel, ba_sum_deleted, ny_sum_deleted
+FROM "wikipedia"
+PIVOT (SUM(deleted) AS "sum_deleted" FOR "cityName" IN ( 'Buenos Aires' AS ba, 'New York' AS ny))
+WHERE ba_sum_deleted IS NOT NULL OR ny_sum_deleted IS NOT NULL
+LIMIT 15
+```
+
+<details>
+<summary> View results </summary>
+
+|`user`|`channel`|`ba_sum_deleted`|`ny_sum_deleted`|
+|------|---------|----------------|----------------|
+|181.230.118.178|`#en.wikipedia`|0|null|
+|181.230.118.178|`#en.wikipedia`|0|null|
+|69.86.6.150|`#en.wikipedia`|null|1|
+|190.123.145.147|`#es.wikipedia`|0|null|
+|181.230.118.178|`#en.wikipedia`|16|null|
+|181.230.118.178|`#en.wikipedia`|0|null|
+|181.230.118.178|`#en.wikipedia`|0|null|
+|181.230.118.178|`#en.wikipedia`|0|null|
+|181.230.118.178|`#en.wikipedia`|0|null|
+|181.230.118.178|`#en.wikipedia`|0|null|
+|181.230.118.178|`#en.wikipedia`|0|null|
+|190.192.179.192|`#en.wikipedia`|0|null|
+|181.230.118.178|`#en.wikipedia`|0|null|
+|181.230.118.178|`#en.wikipedia`|0|null|
+|181.230.118.178|`#en.wikipedia`|0|null|
+
+</details>
+
+## UNPIVOT
+
+:::info
+The UNPIVOT operator is an [experimental feature](../development/experimental-features.md).
+:::
+
+The UNPIVOT operator transforms existing column values into rows.
+Note that UNPIVOT isn't the exact reverse operation of PIVOT. The PIVOT operator carries out an aggregation and merges rows as needed. UNPIVOT doesn't reproduce the original rows that have been merged.
+
+The following is the general syntax for the UNPIVOT operator. Note that the UNPIVOT operator is enclosed in parentheses and forms part of the FROM clause of the query.
+
+```sql
+UNPIVOT (values_column
+ FOR names_column
+ IN (unpivoted_column1 [, unpivoted_column2 ... ])
+)
+```
+
+UNPIVOT syntax parameters:
+
+* `values_column`: The column that contains the values of the unpivoted columns.
+* `names_column`: The column that contains the names of the unpivoted columns.
+* `unpivoted_columnN`: The list of columns to transform into rows in the output.
+
+The following example demonstrates how to transform the columns `added` and `deleted` into row values that correspond to a particular `channel`:
+
+```sql
+SELECT channel, user, action, SUM(changes) AS total_changes
+FROM "wikipedia"
+UNPIVOT ( changes FOR action IN ("added", "deleted") )
+WHERE channel LIKE '#ar%'
+GROUP BY channel, user, action
+LIMIT 15
+```
+
+<details>
+<summary> View results </summary>
+
+|`channel`|`user`|`action`|`total_changes`|
+|---------|------|--------|---------------|
+|`#ar.wikipedia`|156.202.189.223|added|0|
+|`#ar.wikipedia`|156.202.189.223|deleted|30|
+|`#ar.wikipedia`|156.202.76.160|added|0|
+|`#ar.wikipedia`|156.202.76.160|deleted|0|
+|`#ar.wikipedia`|156.212.124.165|added|451|
+|`#ar.wikipedia`|156.212.124.165|deleted|0|
+|`#ar.wikipedia`|160.166.147.167|added|1|
+|`#ar.wikipedia`|160.166.147.167|deleted|0|
+|`#ar.wikipedia`|185.99.32.50|added|1|
+|`#ar.wikipedia`|185.99.32.50|deleted|0|
+|`#ar.wikipedia`|197.18.109.148|added|0|
+|`#ar.wikipedia`|197.18.109.148|deleted|24|
+|`#ar.wikipedia`|`2001:16A2:3C7:6C00:917E:AD28:FAD3:FD5C`|added|1|
+|`#ar.wikipedia`|`2001:16A2:3C7:6C00:917E:AD28:FAD3:FD5C`|deleted|0|
+|`#ar.wikipedia`|41.108.33.83|added|0|
+
+</details>
+
## UNNEST
The UNNEST clause unnests ARRAY typed values. The source for UNNEST can be an array type column, or an input that's been transformed into an array, such as with helper functions like [`MV_TO_ARRAY`](./sql-multivalue-string-functions.md) or [`ARRAY`](./sql-array-functions.md).
@@ -139,7 +256,6 @@ WHERE stringDim IN ('1', '2', '3')
Note that explicit type casting does not lead to significant performance improvement when comparing strings and numbers involving numeric dimensions since numeric dimensions are not indexed.
-
## GROUP BY
The GROUP BY clause refers to columns in the FROM table. Using GROUP BY, DISTINCT, or any aggregation functions will
@@ -283,9 +399,4 @@ To solve this issue, explicitly provide the type of the dynamic parameter using
```
SELECT * FROM druid.foo WHERE dim1 like CONCAT('%', CAST (? AS VARCHAR), '%')
-```
-
-
-
-
-
+```
\ No newline at end of file
diff --git a/website/.spelling b/website/.spelling
index 175774e4ac2..04862c06d6f 100644
--- a/website/.spelling
+++ b/website/.spelling
@@ -780,6 +780,10 @@ APPROX_COUNT_DISTINCT_DS_THETA
APPROX_QUANTILE_DS
DS_QUANTILES_SKETCH
APPROX_QUANTILE_FIXED_BUCKETS
+# Operators
+pivoted
+UNPIVOT
+unpivoted
# File specific overrides
100x
_common
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org