DuckDB 2 cheatsheet

DuckDB 2: SQL features

9 common DuckDB 2 tasks.

Create a table from a file

Load a CSV into a table in one statement.

CREATE TABLE sales AS SELECT * FROM read_csv('sales.csv');

Show column names and types.

DESCRIBE sales;

Get min, max, quartiles, unique counts and null percentages for each column.

SUMMARIZE sales;

Skip listing the grouping columns by hand.

SELECT region, product, sum(amount) AS total FROM sales GROUP BY ALL;

QUALIFY filters rows after window functions are calculated, without a subquery.

SELECT * FROM sales QUALIFY row_number() OVER (PARTITION BY region ORDER BY amount DESC) = 1;

Leave out columns without listing the rest.

SELECT * EXCLUDE (internal_id) FROM sales;

Turn category values into columns.

PIVOT sales ON region USING sum(amount) GROUP BY product;

Turn each list element into its own row.

SELECT unnest([1, 2, 3]) AS n;

Return about 10% of the rows.

SELECT * FROM sales USING SAMPLE 10%;