Aprender  /  Chuletas

Chuleta de DuckDB

El SQL de DuckDB que usas cada día, actualizado para DuckDB 2.0, en una página para imprimir. Cada consulta se ejecutó en DuckDB 2.0.0.

Chuleta5 min de lecturaActualizado el 16 de noviembre de 2026
SQLBases de datosPython
Todas las chuletas y guías
Todas las guías y chuletas

Consultar archivos directamente

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

Tablas

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 amigable

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

Ventanas y 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 y 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;

Texto y fechas

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;

Listas y lambdas (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 y 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;

Variables

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

Exportar

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

Nube y otras bases de datos

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;

Triggers (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;

Modo servidor (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")

Buenos hábitos

Consulta Parquet en lugar de CSV siempre que puedas: ocupa menos y es mucho más rápido. Filtra y elige columnas pronto, porque DuckDB solo lee lo que pides. Los archivos creados con DuckDB 2.0 no se abren con versiones anteriores, así que actualiza a la vez a todos los que comparten un archivo de base de datos. Usa ORDER BY cuando el orden importe; sin él, el orden de las filas no está garantizado.

Alessandro Ecclesie AgazziEscrito por Alessandro Ecclesie Agazzi, analytics engineer en Londres. Actualizado el 16 de noviembre de 2026. Sobre mí

¿Te ha resultado útil?

Aquí se publican con regularidad nuevas guías y artículos sobre las novedades en datos e IA. Sígueme en LinkedIn o comparte este artículo con un compañero.