DuckDB Statements

官方DOC

• struct and unnest

select UNNEST([1,2,3,4,5]) as a;
select UNNEST([({'i': 3, 's': 'string'}), ({'i': 3, 's': 'string'})]) as a;
SELECT unnest([1, 2, 3]) as a, unnest([10, 11]) as b;
SELECT unnest(l) + 10 FROM (VALUES ([1, 2, 3]), ([4, 5])) tbl(l);
select user.user_id, user.user_name from (
    select unnest('[
        {
            "user_id": "0001",
            "user_name": "张三"
        },
        {
            "user_id": "0002",
            "user_name": "李四"
        },
        {
            "user_id": "0003",
            "user_name": "王五"
        }
    ]'::JSON::STRUCT(user_id VARCHAR, user_name VARCHAR)[]) as user
)

• recursive unnest

SELECT unnest([[1, 2, 3], [4, 5]], recursive := true);

• RECURSIVE

create table test as
WITH RECURSIVE cte (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1
    FROM cte
    WHERE n < 5
)
SELECT
    *
FROM cte;

• split

SELECT unnest(SPLIT('2024-01-23,2024-01-22,2024-01-21,2024-01-20', ',')) AS split_result;

• write csv

COPY test TO '/tmp/de-test.csv' WITH (FORMAT CSV, HEADER);

• read csv

SELECT *
FROM read_csv(
    '/tmp/de-test.csv',
    delim = ',',
    header = true
  );

• read text

SELECT regexp_split_to_table(content, '\n') as line FROM read_text('/tmp/de-test.csv');

• xlsx

INSTALL spatial;
LOAD spatial;

• write xlsx

COPY test TO '/tmp/de-test.xlsx' WITH (FORMAT GDAL, DRIVER 'xlsx');

• read xlsx

SELECT * FROM st_read('/tmp/de-test.xlsx');

• create table

CREATE TABLE employees (
    id INT PRIMARY KEY NOT NULL,
    name VARCHAR(100) NOT NULL,
    manager_id INT NULL
);

• insert into

INSERT INTO employees  (id, name, manager_id)
VALUES
(333, 'Yasmina', NULL),
(198, 'John', 333),
(692, 'Tarek', 333),
(29, 'Pedro', 198),
(4610, 'Sarah', 29),
(72, 'Pierre', 29),
(123, 'Adil', 692);

• recursive query

WITH RECURSIVE employee_paths (id, name, path) AS (
    SELECT
        id,
        name,
        CAST(id AS CHAR(200))
    FROM
        employees
    WHERE
        manager_id IS NULL
    UNION ALL
    SELECT
        e.id,
        e.name,
        CONCAT(ep.path, ',', e.id)
    FROM
        employee_paths AS ep
    JOIN employees AS e
      ON ep.id = e.manager_id
)
SELECT
    *
FROM
    employee_paths
ORDER BY path;

• regexp_matche

select regexp_matches(unnest(['2024-01-213','2024-0122','2024-01-21','202401-20']), '^[0-9]{4}-[0-9]{2}-[0-9]{2}$')

select regexp_matches(day, '^[0-9]{4}-[0-9]{2}-[0-9]{2}$') from (
  SELECT unnest(SPLIT('2024-01-213,2024-0122,2024-01-21,202401-20', ',')) AS day
)

• date/time

select TIMESTAMP '1992-03-02 20:32:45';
SELECT strftime(DATE '1992-03-02', '%d/%m/%Y');
SELECT strftime(DATE '1992-03-02', '%Y-%m-%d %H:%M:%S');
SELECT strptime('02-03-1992', '%d-%m-%Y');
SELECT strftime(TIMESTAMP '1992-03-02 20:32:45', '%A, %-d %B %Y - %I:%M:%S %p');

• information_schema

select * from information_schema.tables;
select * from information_schema.columns;
SELECT * FROM duckdb_views;

• utility functions

SELECT random();
SELECT floor(random() * 10) AS random_integer;
SELECT uuid();
select text(cast('43824' as double), 'yyyy-mm-dd hh:mm:ss');
select text(cast('111.1' as double), '###');
with users as (
    select '[
        {"user_id": 1, "user_name": "张三"},
        {"user_id": 2, "user_name": "李四"},
        {"user_id": 3, "user_name": "王五"}
    ]'::JSON::STRUCT(user_id int, user_name VARCHAR)[] as items
)
select
    list_filter(items, x -> x.user_id = 2) as filtered_items
from users;