Results of 1 - 10 of about 57 for filter (0.002 sec.)
- FILTER Clause 15096
- The FILTER clause may optionally follow an aggregate function in a SELECT statement. This will filter the rows of data that are fed into the aggregate function in the same way that a WHERE clause filters rows, but localized to the specific
...There are multiple types of situations where this is useful, including when evaluating multiple aggregates with different filters, and when creating a pivoted view of a dataset. FILTER provides a cleaner syntax for pivoting data when compared with the...more traditional CASE WHEN approach discussed below. Some aggregate functions also do not filter out NULL values, so using a FILTER clause will return valid results when at times the CASE WHEN approach will not. This oc...peration where the goal is to simply re-orient the data into columns rather than re-aggregate it. For list and array_agg , FILTER excludes rows that do not satisfy its condition, whereas a CASE WHEN expression can contribute NULL placeholders for those... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/filter.html
- Lambda Functions 13495
- DuckDB lambda functions (a.k.a. 'lambdas' or 'lambda expressions') are expressions that define an inline anonymous function. Lambdas are passed as argument to certain list functions , which evaluate the passed lambda expression against its
...o create its return value. Lambdas may also be used in the COLUMNS clause . In that context, the lambda function acts as a filter: if it evaluates to true , the column is retained, and discarded otherwise. COLUMNS -clause lambdas have a mandatory param...escription apply(list, lambda(x)) Alias for list_transform . array_apply(list, lambda(x)) Alias for list_transform . array_filter(list, lambda(x)) Alias for list_filter . array_reduce(list, lambda(x,y)[, initial_value]) Alias for list_reduce . array_tr...ansform(list, lambda(x)) Alias for list_transform . filter(list, lambda(x)) Alias for list_filter . list_apply(list, lambda(x)) Alias for list_transform . list_filter(list, lambda(x... - https://man.mtool.work/duckdb/2.0/sql/functions/lambda.html
- QUALIFY Clause 9046
- The QUALIFY clause is used to filter the results of WINDOW functions . This filtering of results is similar to how a HAVING clause filters the results of aggregate functions applied based on the GROUP BY clause . The QUALIFY clause avoids t
...he need for a subquery or WITH clause to perform this filtering (much like HAVING avoids a subquery). An example using a WITH clause instead of QUALIFY is included below the QUALIFY e...xamples. Note that this is filtering based on WINDOW functions , not necessarily based on the WINDOW clause . The WINDOW clause is optional and can be used...o be specified), and before the ORDER BY . Examples Each of the following examples produce the same output, located below. Filter based on a window function defined in the QUALIFY clause: SELECT schema_name, function_name, -- In this example the functi... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/qualify.html
- List Functions 8928
- 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 <->
...nct(list) Alias for list_distinct . array_extract(list, index) Extracts the index th (1-based) value from the list . array_filter(list, lambda(x)) Alias for list_filter . array_grade_up(list[, col1][, col2]) Alias for list_grade_up . array_has(list, el...s. NULL inputs are skipped. See also operator || . contains(list, element) Returns true if the list contains the element . filter(list, lambda(x)) Alias for list_filter . flatten(nested_list) Flattens a nested list by one level. generate_series(start[,...ggregate function entropy to the list . list_extract(list, index) Extract the index th (1-based) value from the list. list_filter(list, lambda(x)) Constructs a list from those elements of the input list for which the lambda function returns true . Duck... - https://man.mtool.work/duckdb/2.0/sql/functions/list.html
- File Formats 8165
- 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
...ain basic statistics such as zonemaps . Thanks to these features, DuckDB can leverage optimizations such as projection and filter pushdown on Parquet files. Therefore, workloads that combine projection, filtering, and aggregation tend to perform quite...iles moderate. Bestpractice The ideal range is between 100 MB and 10 GB per individual Parquet file. Hive Partitioning for Filter Pushdown When querying many files with filter conditions, performance can be improved by using a Hive-format folder struct...ure to partition the data along the columns used in the filter condition. DuckDB will only need to read the folders and files that meet the filter criteria. This can be especially helpf... - https://man.mtool.work/duckdb/2.0/guides/performance/file_formats.html
- Integration with Ibis 8165
- Ibis is a Python dataframe library that supports 20+ backends, with DuckDB as the default. Ibis with DuckDB provides a Pythonic interface for SQL with great performance. Warning Ibis 12.0.0 and earlier do not support DuckDB 2.0. The example
...────┴───────┘ Common Operations Ibis has a collection of useful table methods to manipulate and query the data in a table. filter filter allows you to select rows based on a condition or set of conditions. We can filter so we only have penguins of the...species Gentoo: penguins.filter(penguins.species == "Gentoo") ┏━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━...│ … │ … │ … │ └─────────┴────────┴────────────────┴───────────────┴───────────────────┴─────────────┴────────┴───────┘ Or filter for Gentoo penguins that have a body mass larger than 6 kg. penguins.filter((penguins.species == "Gentoo") & (penguins.bod... - https://man.mtool.work/duckdb/2.0/guides/python/ibis.html
- SQL on Apache Arrow 8047
- DuckDB can query multiple different types of Apache Arrow objects. DuckDB pushes the filters of a query that it can translate, such as comparisons, into the scan of an Arrow dataset. Arrow tables, record batches, scanners and RecordBatchRea
...ders are read in full and filtered afterwards. A RecordBatchReader can be scanned only once; to query its data more than once, read it into a table with re...ul to point towards directories of Parquet files to analyze large datasets. DuckDB will push column selections and the row filters it can translate down into the dataset scan operation so that only the necessary data is pulled into memory. import duckd...also be queried as if they were regular tables. Scanners read over a dataset and select specific columns or apply row-wise filtering. This is similar to how DuckDB pushes column selections and filters down into an Arrow Dataset, but using Arrow compute... - https://man.mtool.work/duckdb/2.0/guides/python/sql_on_arrow.html
- NULL Values 8047
- NULL values are special values that are used to represent missing data in SQL. Columns of any type can contain NULL values. Logically, a NULL value can be seen as “the value of this field is unknown”. A NULL value can be inserted to any fie
...at do not ignore NULL values include: first , last , list and array_agg . To exclude NULL values from these results, use a FILTER clause with a predicate that excludes the relevant null rows, such as list(x) FILTER (WHERE x IS NOT NULL) . A different f... - https://man.mtool.work/duckdb/2.0/sql/data_types/nulls.html
- Querying Parquet Metadata 7930
- Parquet Metadata The parquet_metadata function can be used to query the metadata contained within a Parquet file, which reveals various internal details of the Parquet file such as the statistics of the different columns. This can be useful
...ta_page_offset BIGINT total_compressed_size BIGINT total_uncompressed_size BIGINT key_value_metadata MAP(BLOB, BLOB) bloom_filter_offset BIGINT bloom_filter_length BIGINT min_is_exact BOOLEAN max_is_exact BOOLEAN row_group_compressed_bytes BIGINT Parqu...quet_kv_metadata STRUCT(...)[] Each struct array contains the same columns as the corresponding standalone function. Bloom Filters DuckDB supports Bloom filters for pruning the row groups that need to be read to answer highly selective queries. Current...ly, Bloom filters are supported for the following types: Integer types: TINYINT , UTINYINT , SMALLINT , USMALLINT , INTEGER , UINTEGER , BI... - https://man.mtool.work/duckdb/2.0/data/parquet/metadata.html
- Metrics 7856
- DuckDB provides a set of metrics that can be used to monitor the performance and health of the database. Metrics are named ⟨group⟩.⟨metric⟩ , e.g., query.total_time or operator.timing . The set of collected metrics is controlled with the tr
...ailable optimizers can be queried using the duckdb_optimizers() table function : optimizer.expression_rewriter , optimizer.filter_pullup , optimizer.filter_pushdown , optimizer.empty_result_pullup , optimizer.cte_filter_pusher , optimizer.regex_range ,...r.limit_pushdown , optimizer.top_n , optimizer.compressed_materialization , optimizer.duplicate_groups , optimizer.reorder_filter , optimizer.sampling_pushdown , optimizer.join_filter_pushdown , optimizer.extension , optimizer.materialized_cte , optimi... - https://man.mtool.work/duckdb/2.0/dev/metrics.html
DuckDB 2.0 Documentation / man.mtool.work