duckdb

SELECT 1 / 2;   -- Returns 0.5 (float division, not integer)
SELECT 1 // 2;  -- Returns 0 (integer division)
SELECT 1.0 / 0.0;  -- Returns Infinity (not an error)

FROM my_table;  -- Equivalent to SELECT * FROM my_table
FROM my_table WHERE x > 5 LIMIT 10;

SELECT city, count(*) FROM sales GROUP BY ALL;
SELECT * FROM sales ORDER BY ALL;

SELECT * EXCLUDE (sensitive_col) FROM users;
SELECT * REPLACE (upper(name) AS name) FROM users;

SELECT COLUMNS('sales_.*') FROM data;
SELECT max(COLUMNS('numeric_.*')) FROM tbl;  -- Apply function to matching columns
SELECT COLUMNS(a.*) AS 'left_\0', COLUMNS(b.*) AS 'right_\0'  -- Rename in joins
FROM tbl_a a JOIN tbl_b b ON a.id = b.id;

SELECT i + 1 AS j, j + 2 AS k FROM range(0, 3) t(i);

SELECT x: 42, y: 'hello';  -- Same as 42 AS x, 'hello' AS y

SELECT * FROM tbl LIMIT 10%;

SELECT [x * 2 FOR x IN [1, 2, 3]];  -- [2, 4, 6]
SELECT 'DuckDB'[1:4];  -- 'Duck'
SELECT 'DuckDB'[-2:];  -- 'DB'
SELECT [1,2,3,4,5][-1];  -- 5 (last element)

SELECT 'hello'.upper().reverse();  -- 'OLLEH'

SET VARIABLE my_date = '2024-01-01';
SELECT * FROM events WHERE date > getvariable('my_date');

SELECT TRY(1/0);  -- NULL instead of error
SELECT TRY_CAST('abc' AS INTEGER);  -- NULL instead of error

SELECT * FROM t1 UNION ALL BY NAME SELECT * FROM t2;  -- Match by column names
INSERT INTO target BY NAME SELECT * FROM source;  -- Column order doesn't matter

SELECT * FROM generate_series(DATE '2024-01-01', DATE '2024-12-31', INTERVAL 1 DAY);

SELECT * FROM 'data.parquet';
SELECT * FROM 'data.csv';
SELECT * FROM 'data/*.parquet';  -- Glob patterns
SELECT * FROM read_parquet('s3://bucket/path/*.parquet');

SELECT *, filename FROM read_parquet('data/*.parquet', filename=true);

SELECT * FROM read_csv('file.csv', header=true, delim=',');

SELECT * FROM read_parquet('data/*/*.parquet', hive_partitioning=true);

COPY tbl TO 'output.parquet' (FORMAT parquet, COMPRESSION zstd);
COPY tbl TO 'output' (FORMAT parquet, PARTITION_BY (year, month));

SELECT * FROM read_parquet(
    list_transform(generate_series(1, 12),
        m -> format('s3://bucket/data_{:02d}.parquet', m))
);

# Pipe data through DuckDB
cat data.json | duckdb -c "SELECT * FROM read_json_auto('/dev/stdin')"

# One-liner queries
duckdb -c "SUMMARIZE 'data.parquet'"
duckdb -c "DESCRIBE 'data.csv'"

# Output formats (-markdown is especially useful for readable output)
duckdb -markdown -c "FROM data.parquet LIMIT 10"
duckdb -json -c "FROM data.parquet"
duckdb -csv -c "FROM data.parquet"

# Quick format conversion
duckdb -c "COPY (FROM 'input.csv') TO 'output.parquet'"

duckdb -markdown <<'SQL'
SELECT
    category,
    count(*) AS n,
    avg(price) AS avg_price
FROM 'sales.parquet'
GROUP BY ALL
ORDER BY n DESC
LIMIT 10
SQL

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

SELECT * FROM tbl
QUALIFY row_number() OVER (PARTITION BY key ORDER BY updated_at DESC) = 1;

SELECT
    count(*) AS total,
    count(*) FILTER (WHERE status = 'active') AS active_count
FROM orders;

SELECT city, year, sum(sales)
FROM data GROUP BY CUBE (city, year);

PIVOT sales ON year USING sum(amount);
UNPIVOT monthly ON jan, feb, mar INTO NAME month VALUE amount;

SELECT arg_max(name, score, 3) FROM students GROUP BY class;

FROM trades ASOF JOIN quotes USING (symbol, timestamp);

-- LIST (variable length)
SELECT [1, 2, 3] AS my_list;
SELECT list_aggregate(my_list, 'sum') FROM tbl;

-- STRUCT (named fields)
SELECT {'name': 'Alice', 'age': 30} AS person;
SELECT person.name FROM tbl;

-- MAP
SELECT map([1, 2], ['a', 'b']) AS my_map;

DESCRIBE my_table;
DESCRIBE SELECT * FROM my_table WHERE x > 5;

EXPLAIN SELECT * FROM tbl WHERE x > 5;
EXPLAIN (FORMAT json) SELECT * FROM tbl;  -- JSON output
EXPLAIN (FORMAT graphviz) SELECT * FROM tbl;  -- DOT format

EXPLAIN ANALYZE SELECT * FROM tbl WHERE x > 5;

PRAGMA enable_profiling = 'json';
PRAGMA profiling_output = '/tmp/profile.json';
PRAGMA profiling_mode = 'detailed';  -- Include optimizer metrics
SELECT * FROM tbl;
-- Visualize: python -m duckdb.query_graph /tmp/profile.json

SUMMARIZE my_table;
SUMMARIZE FROM 'data.parquet';

SET memory_limit = '8GB';
SET threads = 4;

-- Statistical
median(x), mode(x), quantile_cont(x, 0.5)
quantile_cont(x, [0.25, 0.5, 0.75])  -- Multiple quantiles at once
stddev_samp(x), corr(y, x), regr_slope(y, x)

-- Collection
list(x), string_agg(x, ', ')
list(item ORDER BY pos ASC)  -- Ordered aggregation

-- Advanced
arg_max(arg, val)  -- Value of arg at max val
arg_min(arg, val)  -- Value of arg at min val
approx_count_distinct(x)  -- HyperLogLog (faster than COUNT DISTINCT)
histogram(x)  -- Frequency distribution

INSTALL httpfs; LOAD httpfs;

-- Query remote files
SELECT * FROM 'https://example.com/data.parquet';

-- S3 access
CREATE SECRET my_s3 (TYPE S3, KEY_ID 'xxx', SECRET 'yyy', REGION 'us-east-1');
SELECT * FROM 's3://bucket/path/*.parquet';

-- Authenticated HTTP APIs
CREATE SECRET http_auth (
    TYPE HTTP,
    EXTRA_HTTP_HEADERS MAP {'Authorization': 'Bearer TOKEN'}
);
SELECT * FROM read_json('https://api.example.com/data');

INSTALL spatial; LOAD spatial;
FROM st_read('file.xlsx', layer='Sheet1');  -- Read
COPY (FROM tbl) TO 'out.xlsx' WITH (FORMAT GDAL, DRIVER 'XLSX');  -- Write

CREATE MACRO filtered_data(start_date, end_date) AS TABLE
SELECT * FROM events WHERE event_date BETWEEN start_date AND end_date;

SELECT * FROM filtered_data('2024-01-01', '2024-12-31');

DESCRIBE my_table;
SUMMARIZE my_table;
SHOW TABLES;
SELECT * FROM duckdb_tables();
SELECT * FROM duckdb_columns();
SELECT * FROM duckdb_settings();