Impara  /  Cheat sheet

Prontuario di DuckDB

L'SQL di DuckDB che usi ogni giorno, aggiornato a DuckDB 2.0, in una pagina da stampare. Ogni query è stata eseguita su DuckDB 2.0.0.

Cheat sheet5 min di letturaAggiornato il 16 novembre 2026
SQLDatabasePython
Tutti i cheat sheet e le guide
Tutte le guide e i cheat sheet

Interrogare i file direttamente

SQL
SELECT * FROM 'sales.csv';                    -- no import step
SELECT * FROM read_csv('sales_*.csv', union_by_name = true);
SELECT * FROM 'sales/*.parquet';
SELECT * FROM read_json('events.json');
DESCRIBE SELECT * FROM 'sales.parquet';       -- column names and types

Tabelle

SQL
CREATE TABLE sales AS FROM 'sales.csv';
CREATE OR REPLACE TABLE sales AS FROM read_csv('sales_*.csv');
INSERT INTO sales BY NAME SELECT 5 AS order_id, 'East' AS region;
SUMMARIZE sales;                 -- min, max, nulls, distinct, quartiles

SQL amichevole

SQL
FROM sales;                                  -- = SELECT * FROM sales
FROM sales SELECT region, amount WHERE amount > 100;
SELECT * EXCLUDE (notes) FROM sales;
SELECT * REPLACE (round(amount) AS amount) FROM sales;
SELECT region, sum(amount) FROM sales GROUP BY ALL ORDER BY ALL;
SELECT max(COLUMNS('amount|tax')) FROM sales;   -- regex on names

Finestre e QUALIFY

SQL
SELECT region, date, amount,
       sum(amount) OVER (PARTITION BY region ORDER BY date) AS running
FROM sales;

-- top order per region, no subquery
SELECT * FROM sales
QUALIFY row_number() OVER (PARTITION BY region ORDER BY amount DESC) = 1;

Join ASOF

SQL
-- the latest price valid at each order date
SELECT s.order_id, p.rate
FROM sales s
ASOF JOIN prices p
  ON s.region = p.region AND s.date >= p.valid_from;
-- ASOF LEFT JOIN keeps orders with no match

Pivot e unpivot

SQL
CREATE TABLE wide AS
PIVOT (SELECT region, amount, month(date) AS month FROM sales)
ON region USING sum(amount) GROUP BY month;

UNPIVOT wide ON COLUMNS(* EXCLUDE (month))
INTO NAME region VALUE total;

Testo e date

SQL
SELECT date_trunc('month', date)          AS month,
       strftime(date, '%d/%m/%Y')         AS uk_date,
       date_diff('day', date, DATE '2026-12-31') AS days_left,
       regexp_extract(code, '\d+')        AS number,
       region ILIKE 'n%'                  AS starts_with_n
FROM sales;

Liste e lambda (2.0)

SQL
SELECT list_transform([1, 2, 3], lambda x: x * 10);   -- [10, 20, 30]
SELECT list_filter([1, 2, 3], lambda x: x > 1);       -- [2, 3]
SELECT [x * 10 FOR x IN [1, 2, 3]];                   -- comprehension
-- 2.0 disables the old x -> x + 1 arrow form by default

JSON e VARIANT (2.0)

SQL
SELECT payload ->> '$.user.id' AS user_id FROM raw_events;
SELECT json_set('{"a": 1}', '$.b', '2');               -- {"a":1,"b":2}

CREATE TABLE events (payload VARIANT);                 -- fast semi-structured
INSERT INTO events VALUES ('{"event": "click", "x": 10}'::JSON::VARIANT);
SELECT variant_type(payload), variant_keys(payload) FROM events;

Variabili

SQL
SET VARIABLE min_amount = 100;
SELECT * FROM sales WHERE amount > getvariable('min_amount');

Esportare

SQL
COPY (FROM sales) TO 'out.parquet' (FORMAT parquet);
COPY sales TO 'out' (FORMAT parquet, PARTITION_BY (region));
COPY sales TO 'out.csv' (HEADER);
SELECT count(*) FROM 'out/**/*.parquet';               -- read it back

Cloud e altri database

SQL
CREATE SECRET (TYPE s3, PROVIDER credential_chain);   -- uses your AWS login
SELECT * FROM 's3://my-bucket/sales/*.parquet';

ATTACH 'shop.duckdb' AS shop;                          -- another DuckDB file
ATTACH 'dbname=shop host=localhost' AS pg (TYPE postgres, READ_ONLY);
CREATE TABLE shop.sales_copy AS FROM sales;

Trigger (2.0)

SQL
CREATE TRIGGER trg_audit
  AFTER UPDATE ON target
  REFERENCING OLD TABLE AS o NEW TABLE AS n
  FOR EACH STATEMENT
    INSERT INTO audit
    SELECT n.id, o.val, n.val FROM o JOIN n ON o.id = n.id;

Modalità server (2.0)

SQL
-- on the server
CALL quack_serve(token = 'my_token');

-- on a client
ATTACH 'quack:localhost' AS qk (TOKEN 'my_token');
CONNECT qk;
SELECT * FROM events;
-- listens on localhost only; use a TLS proxy for remote access

Python

Python
import duckdb

duckdb.sql("SELECT region, sum(amount) FROM 'sales.csv' GROUP BY ALL").df()

df = duckdb.sql("FROM 'sales.parquet'").df()   # to pandas
duckdb.sql("SELECT count(*) FROM df")           # query a DataFrame by name
duckdb.sql("FROM df").pl()                      # to Polars

con = duckdb.connect("shop.duckdb")             # a file on disk
con.sql("CREATE OR REPLACE TABLE t AS FROM df")

Buone abitudini

Interroga Parquet invece di CSV quando puoi: è più piccolo e molto più veloce. Filtra e scegli le colonne presto, perché DuckDB legge solo ciò che chiedi. I file creati con DuckDB 2.0 non si aprono con le versioni precedenti, quindi aggiorna insieme tutti quelli che condividono un file di database. Usa ORDER BY quando l'ordine conta; senza, l'ordine delle righe non è garantito.

Alessandro Ecclesie AgazziScritto da Alessandro Ecclesie Agazzi, analytics engineer a Londra. Aggiornato il 16 novembre 2026. Chi sono

Ti è stato utile?

Qui escono regolarmente nuove guide e articoli sulle novità di dati e AI. Seguimi su LinkedIn o condividi questo articolo con un collega.