Upgrade to Pro — share decks privately, control downloads, hide ads and more …

DuckDB雑紹介(1.1対応版)@DuckDB座談会

Ktz
September 13, 2024

 DuckDB雑紹介(1.1対応版)@DuckDB座談会

2024年6月に発表した(https://speakerdeck.com/ktz/duckdbza-shao-jie)内容にDuckDB バージョン1.1の変更点を追記した版

Ktz

September 13, 2024
Tweet

More Decks by Ktz

Other Decks in Technology

Transcript

  1. 構文 データ型 だいたい PostgreSQL と同じ Enum や PostgreSQL 式の型キャスト( ::

    )も使える https://duckdb.org/docs/sql/data_types/overview 11/61
  2. 構文 CTEの式内materialization たぶん PostgreSQL 独自機能 マテリアライズの強制 or 解除 WITH v

    AS MATERIALIZED (...) SELECT * v https://duckdb.org/docs/sql/query_syntax/with#cte- materialization https://www.postgresql.jp/document/16/html/queries- with.html#QUERIES-WITH-CTE-MATERIALIZATION 12/61
  3. 構文 CTEの式内materialization 実は WITH v AS (...) とした時の規定値が NOT MATERIALIZED

    MATERIALIZED を明示した時のみ強制される仕組み 勝手にマテリアライズされないのでご安心を 14/61
  4. 構文 LATERAL JOIN 公式サイトでは直積での説明しかなくて残念 相関サブクエリの JOIN もちゃんとできるので安心 SELECT * FROM

    t1 JOIN LATERAL (...) t2 ON true https://duckdb.org/docs/sql/query_syntax/from#lateral-joins ちなみに LATERAL は飾りでしかなく、相関サブクエリの JOIN はなく てもできる 16/61
  5. 構文 Window関数 もちのろん、Windowフレームも使える ただし、 Row と Range のみ Group は使えない

    (使えるの PostgreSQL くらいだけど・・・) https://duckdb.org/docs/sql/functions/window_functions.html#row- framing 特にWindowフレームの説明がわかりやすいので一見の価値あり 17/61
  6. SELECT文の独自機能 - JOIN拡張 ASOF JOIN 用途としては、指定日以前・以後の直近のログデータをもってくる とか SELECT t1.some_date, ...

    FROM t1 ASOF JOIN t2 ON t1 t1.some_date > t2.some_date https://duckdb.org/docs/sql/query_syntax/from#as-of-joins 22/61
  7. SELECT文の独自機能 - JOIN拡張 ASOF JOIN 標準SQLで書くとスッゲーめんどいやつ SELECT t1.some_date, ... WHERE

    t1.some_date = ( SELECT max(t2.some_date) FROM t2 WHERE t2.some_date < t1.some_date ) 23/61
  8. SELECT文の独自機能 - JOIN拡張 POSITIONAL JOIN 2つのデータソースの同一行で結合する 欠損行は NULL に 実体は

    FULL OUTER JOIN なので zip演算を思い浮かべるとわかりやすいかも SELECT * FROM t1 POSITIONAL JOIN t2 https://duckdb.org/docs/sql/query_syntax/from#positional-joins 24/61
  9. SELECT文の独自機能 - 集約関数 ソート可能な集約関数 CREATE TABLE tbl AS SELECT s

    FROM range(1, 4) r(s); SELECT string_agg(s, ', ' ORDER BY s DESC) AS countdown FROM tbl PostgreSQLの「順序集合集約関数」に似てるけど別物っぽい DuckDBも「順序集合集約関数」をサポートしているわけで https://duckdb.org/docs/sql/functions/aggregates.html#distinct- clause-in-aggregate-functions 25/61
  10. SELECT文の独自機能 - 集約関数 配列・リスト型用の関数でラムダ式が使える さらにメソッドチェインもできる SELECT [1, 2, 3].apply(x ->

    [4, 5, 6].reduce((a, b) -> a + b) + x) https://duckdb.org/docs/sql/functions/lambda ただしメソッドチェインは、配列・リスト型用の関数のみ UPPER とか他の関数はできなかった。残念! 26/61
  11. SELECT文の独自機能 - スター拡張 COLUMNS(*) * の指定で、全ての列にまとめて同じ関数を適用できる CREATE TABLE numbers (id

    INTEGER, number INTEGER); INSERT INTO numbers VALUES (1, 10), (2, 20), (3, NULL); /* 4列帰ってくる */ SELECT min(COLUMNS(*)), count(COLUMNS(*)) FROM numbers; https://duckdb.org/docs/sql/expressions/star#columns-expression 28/61
  12. SELECT文の独自機能 - スター拡張 COLUMNS(<パターン>) 正規表現による列名のパターンマッチ ラムダ式も使える CREATE TABLE t (yobi001

    VARCHAR, ..., yobi256 VARCHAR); SELECT COLUMNS( c -> substr(c, 5)::int BETWEEN 10 AND 20) FROM t; https://duckdb.org/docs/sql/expressions/star#columns-expression 29/61
  13. SELECT文の独自機能 - スター拡張 REPLACE(<式 AS 列名>) 特定列だけ変更できる INSERT-SELECT で大活躍の予感 INSERT

    INTO t2 SELECT * REPLACE(id*100 AS id) FROM t1 https://duckdb.org/docs/sql/expressions/star#replace-clause 31/61
  14. SELECT文の独自機能 - ファイルインポート 配列で複数のパスも指定可能 SELECT * FROM read_json_auto([ 'https://example.com/nanchara', 'https://example.com/kanchara'

    ]) 同じフォーマットにする必要はあるだろうけど https://duckdb.org/docs/data/multiple_files/overview#list-parameter 35/61
  15. SELECT文の独自機能 - from first syntax FROM句から先に書ける。ただし・・・ FROM + SELECT or

    FROM + WHERE のみ もしくは FROM のみで全列選択 FROM WHERE SELECT は書けない残念な子 /* FROMのみ (SELECT * FROM foo と同等) */ FROM foo; /* FROM WHEREの例 */ SELECT EXISTS (FROM foo WHERE id > 5); https://duckdb.org/docs/sql/query_syntax/from#from-first-syntax 37/61
  16. SELECT文の独自機能 - GROUP BY GROUP BY ALL 集約関数を適用した列以外でグループ化 SELECT a,

    b, max(c), count(c) FROM t GROUP BY ALL https://duckdb.org/docs/sql/query_syntax/groupby#group-by-all 38/61
  17. SELECT文の独自機能 - ORDER BY ORDER BY ALL SELECT句のすべてのカラム一括指定 ASC /

    DESCが揃ってる場合は便利かも SELECT A1, A2, A3 FROM t ORDER BY ALL https://duckdb.org/docs/sql/query_syntax/orderby#order-by-all 39/61
  18. INSERT文の独自機能 - Upsert INSERT OR REPLACE SQLiteで提供されてるやつ なかったら INSERT あったら

    UPDATE PostgreSQLの ON CONFLICT DO も使えるがより直感的 INSERT OR REPLACE INTO movies VALUES (1, 'The Phantom Menace'); https://duckdb.org/2023/02/13/announcing-duckdb-070.html#new- sql-features 43/61
  19. INSERT文の独自機能 - BY NAME INSERT SELECT で、いちいち位置を合わせなくても列名でマッチ ングしてくれる CREATE TABLE

    tbl (a INTEGER, b INTEGER); INSERT INTO tbl BY NAME (SELECT 42 AS b, 32 AS a); https://duckdb.org/docs/sql/statements/insert#insert-into--by-name 44/61
  20. 独自STATEMENT - PIVOT / UNPIVOT 行と列の転置 文として提供されている 大抵のRDBは関数として提供 PIVOT Cities

    ON Year USING sum(Population); PIVOT: https://duckdb.org/docs/sql/statements/pivot UNPIVOT: https://duckdb.org/docs/sql/statements/unpivot 48/61
  21. できなかったこと ネストした Derived Table で複数回、 unnest するとデータが欠損 先頭行のみもってくる SELECT *

    FROM ( SELECT unnest(bar) FROM ( SELECT unnest(foo) FROM t ) ) 遭遇したのはデータソースとして JSON を使った時 テンポラリテーブルで、分けて対処した 55/61
  22. できなかったこと - JS/TSバインディング SQL実行APIで、 Array や Object をパラメータとして使えない 渡せるのは、 数値

    、 文字列 、 boolean 、 NULL のみ 上記以外は、 「Invalid column type encountered for argument」 ってエラーになる IN句 や ANY句 に渡す場合は展開する必要あり CLI ではできるのでバインディングの制限か? 57/61
  23. おわり あばうとみー X(旧トゥイッタ) - https://x.com/ktz_alias Qiita - https://qiita.com/ktz_alias Zenn -

    https://zenn.dev/ktz_alias ギッハブ - https://github.com/ritalin 61/61