Results of 1 - 10 of about 29 for Compute (0.001 sec.)
- Sharing Macros 13395
- DuckDB has a powerful macro mechanism that allows creating shorthands for common tasks. Sharing a Scalar Macro First, we define a macro that pretty-prints a non-negative integer as a short string with thousands, millions, and billions (with
...ring a Table Macro It's also possible to share table macros. For example, we created the checksum macro as follows: duckdb compute_table_checksum.duckdb CREATE MACRO checksum(table_name) AS TABLE SELECT bit_xor(md5_number(COLUMNS(*)::VARCHAR)) FROM quer...installed: INSTALL httpfs; You can attach to the remote endpoint and use the macro: ATTACH 'https://blobs.duckdb.org/data/compute_table_checksum.duckdb' AS compute_table_checksum_db; CREATE TABLE stations AS FROM 'https://blobs.duckdb.org/stations.parq...uet'; .mode line FROM compute_table_checksum_db.checksum('stations'); id = 207501589970998739957162925925638303138 code = 126327004005066229305810236187... - https://man.mtool.work/duckdb/2.0/guides/snippets/sharing_macros.html
- Prepared Statements 12497
- DuckDB supports prepared statements where parameters are substituted when the query is executed. This can improve readability and is useful for preventing SQL injections . Syntax There are three syntaxes for denoting parameters in prepared
...An explicit argument always takes precedence over the variable. SET VARIABLE multiplier = 10; SET VARIABLE x = 42; PREPARE compute AS SELECT $multiplier * $x AS result; With no arguments, both parameters come from the variables, computing 10 * 42 : EXEC...UTE compute; result 420 Supplying an argument overrides the matching variable, while omitted parameters still fall back to theirs: EXE...CUTE compute(multiplier := 3); result 126 Because the fallback is resolved at execution time, re-executing the statement after changing... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/prepared_statements.html
- Window Functions 9952
- DuckDB supports window functions , which can use multiple rows to calculate a value for each row. Window functions are blocking operators , i.e., they require their entire input to be buffered, making them one of the most memory-intensive o
...rows, ordered by time and partitioned by region : SELECT row_number() OVER (PARTITION BY region ORDER BY time) FROM sales; Compute the difference between the current and the previous-by- time amount : SELECT amount - lag(amount) OVER (ORDER BY time) FRO...M sales; Compute the percentage of the total amount of sales per region for each row: SELECT amount / sum(amount) OVER (PARTITION BY region...n rows preceding or peer with current row) / total partition rows. If an ORDER BY clause is specified, the distribution is computed within the frame using the provided ordering instead of the frame ordering. | | Return type | DOUBLE | | Example | cume_d... - https://man.mtool.work/duckdb/2.0/sql/functions/window_functions.html
- GROUP BY Clause 9773
- 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
...of entries in the addresses table that belong to each different city: SELECT city, count(*) FROM addresses GROUP BY city; Compute the average income per city per street_name : SELECT city, street_name, avg(income) FROM addresses GROUP BY city, street_n...amples Group by city and street_name to remove any duplicate values: SELECT city, street_name FROM addresses GROUP BY ALL; Compute the average income per city per street_name. Since income is wrapped in an aggregate function, do not include it in the GR... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/groupby.html
- Array Type 9234
- An ARRAY column stores fixed-sized arrays. All fields in the column must have the same length and the same underlying type. Arrays are typically used to store arrays of numbers, but can contain any uniform data type, including ARRAY , LIST
...ray_value(1.0::FLOAT, 2.0::FLOAT, 3.0::FLOAT)); INSERT INTO y VALUES (1, array_value(2.0::FLOAT, 3.0::FLOAT, 4.0::FLOAT)); Compute cross product: SELECT array_cross_product(x.v, y.v) FROM x, y WHERE x.i = y.i; Compute cosine similarity: SELECT array_cos... - https://man.mtool.work/duckdb/2.0/sql/data_types/array.html
- GROUPING SETS 9234
- 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
...ECT city, street_name, avg(income) FROM addresses GROUP BY GROUPING SETS ((city, street_name), (city), (street_name), ()); Compute the average income along the same dimensions: SELECT city, street_name, avg(income) FROM addresses GROUP BY CUBE (city, st...reet_name); Compute the average income along the dimensions (city, street_name) , (city) and () : SELECT city, street_name, avg(income) FROM a... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/grouping_sets.html
- SQL on Apache Arrow 8875
- 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
...filtering. This is similar to how DuckDB pushes column selections and filters down into an Arrow Dataset, but using Arrow compute operations instead. Arrow can use asynchronous IO to quickly access files. import duckdb import pyarrow as pa import tempf...ile import pyarrow.parquet as pq import pyarrow.dataset as ds import pyarrow.compute as pc # connect to an in-memory database con = duckdb.connect() my_arrow_table = pa.Table.from_pydict({'i': [1, 2, 3, 4],... - https://man.mtool.work/duckdb/2.0/guides/python/sql_on_arrow.html
- HAVING Clause 8875
- The HAVING clause can be used after the GROUP BY clause to provide filter criteria after the grouping has been completed. In terms of syntax the HAVING clause is identical to the WHERE clause, but while the WHERE clause occurs before the gr
...ty , filtering out cities with a count below 50: SELECT city, count(*) FROM addresses GROUP BY city HAVING count(*) >= 50; Compute the average income per city per street_name , filtering out cities with an average income bigger than twice the median inc... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/having.html
- SELECT Clause 8679
- 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
...sses; Select all columns matching the given regular expression from the table: SELECT COLUMNS('number\d+') FROM addresses; Compute a function on all given columns of a table: SELECT min(COLUMNS(*)) FROM addresses; To select columns with spaces or specia...ions Generate a row_number column containing incremental identifiers for each row: SELECT row_number() OVER () FROM sales; Compute the difference between the current amount, and the previous amount, by order of time: SELECT amount - lag(amount) OVER (OR...contains the window specification . The window specification 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... - https://man.mtool.work/duckdb/2.0/sql/query_syntax/select.html
- CREATE SCHEMA Statement 8679
- The CREATE SCHEMA statement creates a schema in the catalog. The default schema is main . Examples Create a schema: CREATE SCHEMA s1; Create a schema if it does not exist yet: CREATE SCHEMA IF NOT EXISTS s2; Create a schema or replace a sch
...emas: CREATE TABLE s1.t (id INTEGER PRIMARY KEY, other_id INTEGER); CREATE TABLE s2.t (id INTEGER PRIMARY KEY, j VARCHAR); Compute a join between tables from two schemas: SELECT * FROM s1.t s1t, s2.t s2t WHERE s1t.other_id = s2t.id; Nested Schemas New S... - https://man.mtool.work/duckdb/2.0/sql/statements/create_schema.html
DuckDB 2.0 Documentation / man.mtool.work