Results of 1 - 10 of about 10 for unpivot (0.004 sec.)
- UNPIVOT Statement 14230
- The UNPIVOT statement allows multiple columns to be stacked into fewer columns. In the basic case, multiple columns are stacked into two columns: a NAME column (which contains the name of the source column) and a VALUE column (which contain
...s the value from the source column). DuckDB implements both the SQL Standard UNPIVOT syntax and a simplified UNPIVOT syntax. Both can utilize a COLUMNS expression to automatically detect the columns to unpiv...ot. PIVOT_LONGER may also be used in place of the UNPIVOT keyword. For details on how the UNPIVOT statement is implemented, see the Pivot Internals site . The PIVOT statement is th...e inverse of the UNPIVOT statement. Simplified UNPIVOT Syntax The full syntax diagram is below, but the simplified UNPIVOT syntax can be summarized... - https://man.mtool.work/duckdb/2.0/sql/statements/unpivot.html
- Pivot Internals 8903
- 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
...aterializes all values in memory, so you can lower or raise pivot_filter_threshold to trade off time against memory usage. UNPIVOT Internals Unpivoting is implemented entirely as rewrites into SQL queries. Each UNPIVOT is implemented as set of unnest fu...nctions, operating on a list of the column names and a list of the column values. If dynamically unpivoting, the COLUMNS expression is evaluated first to calculate the column list. For example: UNPIVOT monthly_sales ON jan, feb... - https://man.mtool.work/duckdb/2.0/internals/pivot.html
- query and query_table Functions 8688
- 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
...nth 1 9 The query function allows for even more flexibility. For example, users who prefer pandas' stack syntax over SQL's UNPIVOT syntax , may use: CREATE OR REPLACE MACRO stack(table_name, index, name, values) AS TABLE FROM query( 'UNPIVOT ' || table_... - https://man.mtool.work/duckdb/2.0/guides/sql_features/query_and_query_table_func...
- Calculating a Database Checksum 8420
- To calculate a database checksum, use a commutative operation such as bit_xor on all columns, all rows, and all tables. First, create a table macro that performs this for each table: CREATE OR REPLACE MACRO table_checksum(table_name) AS TAB
...LE SELECT bit_xor(value) AS table_checksum FROM ( UNPIVOT ( SELECT bit_xor(md5_number(COLUMNS(*)::VARCHAR)) FROM query_table(table_name) ) ON columns(*) ); You need to call this ma... - https://man.mtool.work/duckdb/2.0/guides/snippets/database_checksum.html
- List Functions 8384
- Function Description list[index] Extracts a single list element using a (1-based) index . list[begin[:end][:step]] Extracts a sublist using slice conventions . Negative values are accepted. list1 && list2 Alias for list_has_any . list1 <->
...vel. Note that this is a special function that alters the cardinality of the result. See the unnest page for more details. unpivot_list(arg, ...) Identical to list_value, but generated as part of unpivot for better error messages. list[index] | Descript...esult. See the unnest page for more details. | | Example | unnest([1, 2, 3]) | | Result | Multiple rows: '1' , '2' , '3' | unpivot_list(arg, ...) | Description | Identical to list_value, but generated as part of unpivot for better error messages. | | Ex...ample | unpivot_list(4, 5, 6) | | Result | [4, 5, 6] | List Operators The following operators are supported for lists: Operator Descriptio... - https://man.mtool.work/duckdb/2.0/sql/functions/list.html
- Relational API 8205
- The Relational API builds a query one step at a time from Python. A relation, a DuckDBPyRelation , is a piece of SQL text: the methods of the Transformation and Functions sections return a new relation whose text extends the one they were c
...ations, matching columns by name; keeps duplicates unless all is false. unnest Expand list columns to one row per element. unpivot Turn the named columns into rows; name and value name the two new columns. with_columns Add columns, replacing any of the...───┬─────────┐ │ id │ amounts │ │ int32 │ int32 │ ├───────┼─────────┤ │ 1 │ 10 │ │ 1 │ 20 │ │ 2 │ 30 │ └───────┴─────────┘ unpivot unpivot(self, *columns: str, name: str = 'name', value: str = 'value') -> DuckDBPyRelation Turn the named columns into row...s; name and value name the two new columns. It runs an UNPIVOT ; the columns not named stay as they are, repeated for each new row. Parameters *columns : str Names of the columns to tur... - https://man.mtool.work/duckdb/2.0/clients/python/relational_api.html
- Migrating Python Code to DuckDB 2.0 8170
- This page lists what Python code written for DuckDB 1.x must change to run on DuckDB 2.0. Each item gives the 1.x form, the 2.0 form and the page that explains it. For changes outside the Python client, see Migrating to DuckDB v2.0 . Python
...e column offset of rel . 2.0: relations have new methods such as offset , sample , head , rename , pivot , drop , unnest , unpivot , preview and group_by , so rel.offset is the method. Write rel["offset"] for the column. See the Relational API . sql() o... - https://man.mtool.work/duckdb/2.0/clients/python/migration.html
- Friendly SQL 8170
- DuckDB offers several advanced SQL features and syntactic sugar to make SQL queries more concise. We refer to these colloquially as “friendly SQL”. Several of these features were first introduced by DuckDB, while some are inspired by other
...in the GROUP BY clause to group by the elements of a list. Transforming tables: PIVOT to turn long tables to wide tables. UNPIVOT to turn wide tables to long tables. Defining SQL-level variables: SET VARIABLE RESET VARIABLE The $name shorthand for read... - https://man.mtool.work/duckdb/2.0/sql/dialect/friendly_sql.html
- PIVOT Statement 8134
- The PIVOT statement allows distinct values within a column to be separated into their own columns. The values within those new columns are calculated using an aggregate function on the subset of rows that match each distinct value. DuckDB i
...d in place of the PIVOT keyword. For details on how the PIVOT statement is implemented, see the Pivot Internals site . The UNPIVOT statement is the inverse of the PIVOT statement. Simplified PIVOT Syntax The full syntax diagram is below, but the simplif... - https://man.mtool.work/duckdb/2.0/sql/statements/pivot.html
- Python Client API 7973
- Connection class duckdb. Appender Bases: object append_row ( self , * args ) → None Buffer one row, one value per column, each converted as an executemany parameter would be append_rows ( self , rows : object ) → None Buffer each row of an
...r ) → DuckDBPyRelation Expand list columns to one row per element. Detailed examples can be found at Relational API page . unpivot ( * columns : str , name : str = 'name' , value : str = 'value' ) → DuckDBPyRelation Turn the named columns into rows; nam... - https://man.mtool.work/duckdb/2.0/clients/python/reference/
PREV
1
NEXT
DuckDB 2.0 Documentation / man.mtool.work