Results of 1 - 10 of about 29 for Unnest (0.001 sec.)
- Unnesting 15663
- Unnesting is an operation that decomposes values of a composite types into its components. Values of the LIST and STRUCT type may be unnested using the unnest() function . Unnesting turns a LIST -typed value into a table column: each list-e
...lement creates a row, and each element become a column value. Unnesting a STRUCT -typed value creates a column for each member. The member key becomes the column name, and the member value be...comes a column value. Values of a STRUCT -type may also be unnested using dot-star ( ⟨struct⟩.* ) shorthand syntax , but the unnest() function offers some additional functionality. For a T...UPLE (unnamed struct), the unnesting operation is the same as for a named STRUCT , but in this case the column name is generated based on the (1-based) ordi... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/unnest.html
- SELECT Clause 9936
- The SELECT clause specifies the list of columns that will be returned by the query. While it appears first in the clause, logically the expressions here are executed only at the end. The SELECT clause can contain arbitrary expressions that
...defines the frame or context in which the window function is computed. See the window functions page for more information. unnest Function Unnest an array by one level: SELECT unnest([1, 2, 3]); Unnest a struct by one level: SELECT unnest({'a': 42, 'b'...: 84}); The unnest function is a special function that can be used together with arrays , lists , or structs . The unnest function strips one...ample, INTEGER[] is transformed into INTEGER . STRUCT(a INTEGER, b INTEGER) is transformed into a INTEGER, b INTEGER . The unnest function can be used to transform nested types into regular scalar types, which makes them easier to operate on.... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/select.html
- MERGE INTO Statement 9593
- The MERGE INTO statement is an alternative to INSERT INTO ... ON CONFLICT that doesn't need a primary key since it allows for a custom match condition. This is a very useful alternative for upserting use cases ( INSERT + UPDATE ) when the d
...match, the row can be trivially inserted into the table ( WHEN NOT MATCHED THEN INSERT ). MERGE INTO people USING ( SELECT unnest([3, 1]) AS id, unnest(['Sarah', 'John']) AS name, unnest([95_000.0, 105_000.0]) AS salary ) AS upserts ON (upserts.id = pe...n decide to only remove rows that contain a salary bigger or equal than a certain amount. MERGE INTO people USING ( SELECT unnest([3, 2]) AS id, ) AS deletes USING (id) WHEN MATCHED AND people.salary >= 100_000.0 THEN DELETE; FROM people ORDER BY id; i...E statement. -- Let's get John back in! INSERT INTO people VALUES (1, 'John', 105_000.0); MERGE INTO people USING ( SELECT unnest([3, 1]) AS id, unnest([89_000.0, 70_000.0]) AS salary ) AS upserts USING (id) WHEN MATCHED AND people.salary < 100_000.0 T... - https://man.mtool.work/duckdb/2.0/sql/statements/merge_into.html
- GROUP BY Clause 9544
- The GROUP BY clause specifies which grouping columns should be used to perform any aggregations in the SELECT clause. If the GROUP BY clause is specified, the query is always an aggregate query, even if no aggregations are present in the SE
...UP clauses it is possible to group along multiple dimensions. See the GROUPING SETS page for more information. Grouping by UNNEST New Grouping by UNNEST was introduced in DuckDB 2.0. UNNEST can be used directly in the GROUP BY clause to group by the el...ements of a list. This is useful for aggregating over the individual values of a list column: SELECT unnest(tags) AS tag, count(*) AS n FROM (VALUES (['a', 'b']), (['b', 'c']), (['b'])) t(tags) GROUP BY unnest(tags) ORDER BY tag;... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/groupby.html
- CASE Expression 9446
- The CASE expression performs a switch based on a condition. The basic form is identical to the ternary condition used in many programming languages ( CASE WHEN cond THEN a ELSE b END is equivalent to cond ? a : b ). With a single condition
...this can be expressed with IF(cond, a, b) . CREATE OR REPLACE TABLE integers AS SELECT unnest([1, 2, 3]) AS i; SELECT i, CASE WHEN i > 2 THEN 1 ELSE 0 END AS test FROM integers; i test 1 0 2 0 3 1 This is equivalent...rue for a single tuple, the corresponding expression is evaluated and returned. CREATE OR REPLACE TABLE integers AS SELECT unnest([1, 2, 3]) AS i; SELECT i, CASE WHEN i = 1 THEN 10 WHEN i = 2 THEN 20 ELSE 0 END AS test FROM integers; i test 1 10 2 20 3...ovided and none of the conditions match, the CASE expression will return NULL . CREATE OR REPLACE TABLE integers AS SELECT unnest([1, 2, 3]) AS i; SELECT i, CASE WHEN i = 1 THEN 10 END AS test FROM integers; i test 1 10 2 NULL 3 NULL It is also possibl... - https://man.mtool.work/duckdb/2.0/sql/expressions/case.html
- Set Operations 8631
- Set operations allow queries to be combined according to set operation semantics . Set operations refer to the UNION [ALL] , INTERSECT [ALL] and EXCEPT [ALL] clauses. The vanilla variants use set semantics, i.e., they eliminate duplicates,
...ange(6) t2(x); x 0 1 INTERSECT ALL (Bag Semantics) INTERSECT ALL follows bag semantics, so duplicates are returned. SELECT unnest([5, 5, 6, 6, 6, 6, 7, 8]) AS x INTERSECT ALL SELECT unnest([5, 6, 6, 7, 7, 9]); x 5 6 6 7 EXCEPT The EXCEPT clause can be...nge(5) t1(x) EXCEPT SELECT * FROM range(2) t2(x); x 2 3 4 EXCEPT ALL (Bag Semantics) EXCEPT ALL uses bag semantics: SELECT unnest([5, 5, 6, 6, 6, 6, 7, 8]) AS x EXCEPT ALL SELECT unnest([5, 6, 6, 7, 7, 9]); x 5 8 6 6 Syntax... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/setops.html
- query and query_table Functions 8484
- The query_table and query functions enable powerful and more dynamic SQL. The query_table function returns the table whose name is specified by its string argument; the query function returns the table obtained by executing the query specif
...that computes the min and max of every column in a table: CREATE OR REPLACE MACRO my_summarize(table_name) AS TABLE SELECT unnest([*COLUMNS('alias_.*')]) AS column_name, unnest([*COLUMNS('min_.*')]) AS min_value, unnest([*COLUMNS('max_.*')]) AS max_val... - https://man.mtool.work/duckdb/2.0/guides/sql_features/query_and_query_table_func...
- Pivot Internals 8288
- PIVOT Pivoting is implemented as a combination of SQL query re-writing and a dedicated PhysicalPivot operator for higher performance. Each PIVOT is implemented as set of aggregations into lists and then the dedicated PhysicalPivot operator
.... UNPIVOT Internals Unpivoting is implemented entirely as rewrites into SQL queries. Each UNPIVOT is implemented as set of unnest functions, operating on a list of the column names and a list of the column values. If dynamically unpivoting, the COLUMNS...UNPIVOT monthly_sales ON jan, feb, mar, apr, may, jun INTO NAME month VALUE sales; is translated into: SELECT empid, dept, unnest(['jan', 'feb', 'mar', 'apr', 'may', 'jun']) AS month, unnest(["jan", "feb", "mar", "apr", "may", "jun"]) AS sales FROM mon... - https://man.mtool.work/duckdb/2.0/internals/pivot.html
- IN Operator 7978
- The IN operator checks containment of the left expression inside the collection on the right hand side (RHS). Supported collections on the RHS are tuples, lists, and maps, as well as subqueries that return a single column. The single-column
...5}; false IN Subquery The IN operator works with subqueries that return a single column. For example: SELECT 42 IN (SELECT unnest([32, 42, 52]) AS x); true If the subquery returns more than one column, a Binder Error is thrown: SELECT 42 IN (SELECT unn... - https://man.mtool.work/duckdb/2.0/sql/expressions/in.html
- Struct Data Type 7929
- Conceptually, a STRUCT column contains an ordered list of columns called “entries”. The entries are referenced by name using strings. This document refers to those entry names as keys. Each row in the STRUCT column must have the same keys.
...ow() with no arguments returns the empty tuple () ). Note that STRUCT() with empty parentheses remains a syntax error, and unnest and struct_extract raise an error on an empty struct. Persisting an empty struct into a pre-2.0 database format is not sup...uct_extract function is also equivalent. This returns 1: SELECT struct_extract({'x space': 1, 'y': 2, 'z': 3}, 'x space'); unnest / STRUCT.* Rather than retrieving a single key from a struct, the unnest special function can be used to retrieve all keys...eful when a prior operation creates a struct of unknown shape, or if a query must handle any potential struct keys: SELECT unnest(a) FROM (SELECT {'x': 1, 'y': 2, 'z': 3} AS a); x y z 1 2 3 The same can be achieved with the star notation ( * ), which a... - https://man.mtool.work/duckdb/2.0/sql/data_types/struct.html
DuckDB 2.0 Documentation / man.mtool.work