Results of 1 - 10 of about 73 for group (0.002 sec.)
- GROUP BY Clause 12226
- 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
...LECT clause. When a GROUP BY clause is specified, all tuples that have matching data in the grouping columns (i.e., all tuples that belong to the sa...me group) will be combined. The values of the grouping columns themselves are unchanged, and any other columns can be combined usin...g an aggregate function (such as count , sum , avg , etc). GROUP BY ALL Use GROUP BY ALL to GROUP BY all columns in the SELECT statement that are not wrapped in aggregate functions. This... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/groupby.html
- GROUPING SETS 11402
- GROUPING SETS , ROLLUP and CUBE can be used in the GROUP BY clause to perform a grouping over multiple dimensions within the same query. Note that this syntax is not compatible with GROUP BY ALL . Examples Compute the average income along t
...he provided four different dimensions: -- the syntax () denotes the empty set (i.e., computing an ungrouped aggregate) SELECT city, street_name, avg(income) FROM addresses GROUP BY GROUPING SETS ((city, street_name), (city), (st...eet_name), ()); Compute the average income along the same dimensions: SELECT city, street_name, avg(income) FROM addresses GROUP BY CUBE (city, street_name); Compute the average income along the dimensions (city, street_name) , (city) and () : SELECT...city, street_name, avg(income) FROM addresses GROUP BY ROLLUP (city, street_name); Description GROUPING SETS performs the same aggregate across different GROUP BY clauses in... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/grouping_sets.html
- Parquet Tips 11360
- Below is a collection of tips to help when dealing with Parquet files. Tips for Reading Parquet Files Use union_by_name When Loading Files with Different Schemas The union_by_name option can be used to unify the schema of files that have di
...erformance: COPY (FROM generate_series(10_000_000)) TO 'test.parquet' (FORMAT parquet, PER_THREAD_OUTPUT); Selecting a ROW_GROUP_SIZE The ROW_GROUP_SIZE parameter specifies the minimum number of rows in a Parquet row group, with a minimum value equal...to DuckDB's vector size, 2,048, and a default of 122,880. A Parquet row group is a partition of rows, consisting of a column chunk for each column in the dataset. Compression algorithms are only appli...ed per row group, so the larger the row group size, the more opportunities to compress the data. On the other hand, larger row group sizes... - https://man.mtool.work/duckdb/2.0/data/parquet/tips.html
- PIVOT Statement 10717
- 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
...syntax can be summarized using spreadsheet pivot table naming conventions as: PIVOT ⟨dataset⟩ ON ⟨columns⟩ USING ⟨values⟩ GROUP BY ⟨rows⟩ ORDER BY ⟨columns_with_order_directions⟩ LIMIT ⟨number_of_rows⟩; The ON , USING , and GROUP BY clauses are each...use. In DuckDB, the PIVOT statement currently does not support these and they are silently ignored. PIVOT ON , USING , and GROUP BY By default, the PIVOT statement retains all columns not specified in the ON or USING clauses. To include only certain c...olumns and further aggregate, specify columns in the GROUP BY clause. This is equivalent to the rows parameter of a spreadsheet pivot table. In the below example, the name column is... - https://man.mtool.work/duckdb/2.0/sql/statements/pivot.html
- Regular Expressions 10019
- DuckDB offers pattern matching operators ( LIKE , SIMILAR TO , GLOB ), as well as support for regular expressions via functions. Regular Expression Syntax DuckDB uses the RE2 library as its regular expression engine. For the regular express
...e RE2 docs . Functions All functions accept an optional set of options . Name Description regexp_extract(string, pattern[, group = 0][, options]) If string contains the regexp pattern , returns the capturing group specified by optional parameter group...; otherwise, returns the empty string. The group must be a constant value. If no group is given, it defaults to 0. A set of optional options can be set. regexp_extract(str...ing, pattern, name_list[, options]) If string contains the regexp pattern , returns the capturing groups as a struct with corresponding names from name_list ; otherwise, returns a struct with the same keys and empty strings as... - https://man.mtool.work/duckdb/2.0/sql/functions/regular_expressions.html
- Friendly SQL 9585
- 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
...systems. Many of the features originally introduced by DuckDB (e.g., GROUP BY ALL ) have been since adapted by other systems. Tip We have a Friendly SQL 2026 Calendar with a short explanation, an e...SELECT clause : DuckDB allows queries in the form of FROM tbl which selects all columns (performing a SELECT * statement). GROUP BY ALL : omit the group-by columns by inferring them from the list of attributes in the SELECT clause. ORDER BY ALL : shor...to return 10% of the query results. FETCH FIRST : the SQL-standard alternative to LIMIT , e.g., FETCH FIRST 5 ROWS ONLY . Grouping by UNNEST : use unnest() directly in the GROUP BY clause to group by the elements of a list. Transforming tables: PIVOT... - https://man.mtool.work/duckdb/2.0/sql/dialect/friendly_sql.html
- Reading and Writing Parquet Files 9194
- Examples Read a single Parquet file: SELECT * FROM 'test.parquet'; Figure out which columns/types are in a Parquet file: DESCRIBE SELECT * FROM 'test.parquet'; Create a table from a Parquet file: CREATE TABLE test AS SELECT * FROM 'test.par
...esult-snappy.parquet' (FORMAT parquet); Write the results from a query to a Parquet file with specific compression and row group size: COPY (FROM generate_series(100_000)) TO 'test.parquet' (FORMAT parquet, COMPRESSION zstd, ROW_GROUP_SIZE 100_000); E...s. Parquet datasets differ based on the number of files, the size of individual files, the compression algorithm used, row group size, etc. These have a significant effect on performance. Please consult the Performance Guide for details. read_parquet...range and precision). Added in DuckDB 2.0. VARCHAR timestamp prefetch_strategy Strategy for prefetching column data of row groups (applies unless prefetching is disabled with the disable_parquet_prefetching setting). With auto , DuckDB picks a strateg... - https://man.mtool.work/duckdb/2.0/data/parquet/overview.html
- File Formats 8859
- Handling Parquet Files DuckDB has advanced support for Parquet files, which includes directly querying Parquet files . When deciding on whether to query these files directly or to first load them to the database, you need to consider severa
...un many queries on the same dataset, load the Parquet files into the database first. The compression algorithm and the row group sizes in the Parquet files have a large effect on performance: study these using the parquet_metadata function . The Effec...t of Row Group Sizes DuckDB works best on Parquet files with row groups of 100K-1M rows each. The reason for this is that DuckDB can only...parallelize over row groups – so if a Parquet file has a single giant row group it can only be processed by a single thread. You can use the parquet_... - https://man.mtool.work/duckdb/2.0/guides/performance/file_formats.html
- Relational API 8761
- 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
...a new query from the relation and run nothing. Name Description aggregate Compute the aggregate aggr_expr by the optional groups group_expr on the relation. apply Compute the function of a single column or a list of columns by the optional groups on...relation object with another relation object in other_rel. filter Filter the relation object by the filter in filter_expr. group_by Begin a grouped aggregation; continue with .agg(...) . head The first n rows. intersect Create the set intersection of...w columns. with_columns Add columns, replacing any of the same name in place. aggregate aggregate(self, aggr_expr: object, group_expr: object = '') -> DuckDBPyRelation Compute the aggregate aggr_expr by the optional groups group_expr on the relation.... - https://man.mtool.work/duckdb/2.0/clients/python/relational_api.html
- Variant Functions 8719
- This section describes functions that operate on the VARIANT type . New Most variant_* functions were introduced in DuckDB 2.0. The variant_extract , variant_normalize and variant_typeof functions were already available in DuckDB 1.5. Name
...turn a canonical representation of a VARIANT value, so that values representing the same data share the same form. variant_group_object(key, value) Aggregate. Return a VARIANT object built from all key , value pairs in the group. variant_group_array(x...NT values. | | Example | variant_normalize({'b': [1, 2, 3], 'a': 42}::VARIANT) | | Result | A normalized VARIANT | variant_group_object(key, value) | Description | Aggregate function. Returns a VARIANT object containing all key , value pairs in the gr...oup, where key is a VARCHAR and value is a VARIANT . A NULL key or a duplicate key raises an error. | | Example | variant_group_object('duck', 'goose'::VARIANT) | | Result | {'duck': goose} | New The variant_group_object aggregate was added in DuckDB... - https://man.mtool.work/duckdb/2.0/sql/functions/variant.html
DuckDB 2.0 Documentation / man.mtool.work