SQL滅ぶべし

SQL 滅ぶべし 2024/5/6 hoxo-m

今日のおはなし • SQL つらい • もっと簡単に DB 操作したい

SQL • リレーショナルデータベースシステムと会話するための言語 • 1970年 Codd が RDB モデルと同時に提案 (Alpha言語) • 1974年 Chamberlin と Boyce が改良 • 元々は SEQUEL (Structured English Query Language) だったが、商標登録されていた • 読み方は エスキューエル とそのまま読む (Glliespie 2012)

SQL • SQL は目的別に 4つに分けられる • DCL (データ制御言語) GRANT とか • DDL (データ定義言語) CREATE TABLE とか • TCL (トランザクション制御言語) ROLLBACK とか • DML (データ操作言語) • INSERT, UPDATE, DELETE, SELECT • データ分析者にとって重要なのは SELECT 文

SELECT の例 SELECT date, count(*) AS n FROM my_table WHERE ("date" BETWEEN ‘2024-01-01’ AND ‘2024-01-03’) GROUP BY date ORDER BY date

SELECT の例 SELECT date, count(*) AS n date 2024-01-01 2024-01-02 2024-01-03 FROM my_table WHERE ("date" BETWEEN ‘2024-01-01’ AND ‘2024-01-03’) GROUP BY date ORDER BY date n 123 456 789

SELECT の書き方 結果として何が得たい？ SELECT date, count(*) AS n FROM my_table WHERE ("date" BETWEEN ‘2024-01-01’ AND ‘2024-01-03’) GROUP BY date ORDER BY date

SELECT の書き方 SELECT date, どのテーブルから？ count(*) AS n FROM my_table WHERE ("date" BETWEEN ‘2024-01-01’ AND ‘2024-01-03’) GROUP BY date ORDER BY date

SELECT の書き方 SELECT date, count(*) AS n どういう条件？ FROM my_table WHERE ("date" BETWEEN ‘2024-01-01’ AND ‘2024-01-03’) GROUP BY date ORDER BY date

SELECT の書き方 SELECT date, count(*) AS n FROM my_table WHERE ("date" BETWEEN ‘2024-01-01’ AND ‘2024-01-03’) GROUP BY date ORDER BY date 集計グループの指定 結果の並び替え

わかりにくくないですか？ 出力 SELECT date, count(*) AS n 入力 FROM my_table 条件 WHERE ("date" BETWEEN ‘2024-01-01’ AND ‘2024-01-03’) 条件 GROUP BY date 出力 ORDER BY date

SQL つらい • SELECT 文を書こうとすると、 • 最初に「結果はどうなるか」を考える必要がある • 出力 → 入力 → 条件 → 出力 • しかし、集計するときは、結果がどうなるかは最後に考えたい • 入力 → 条件 → 出力 • SQL の文法は、データ操作について、 人間がどのように考えながら行うか、まったく考慮されていない

いつまで 50年前の書き方をしているの？

dplyr (2014) • テーブルデータの操作を行うための R パッケージ • 人間がどのように考えながらデータ操作を行うかについて 考慮されて設計されている • Pandas のパイプ関数などはこれにインスパイアされて開発

dplyr の例 my_data_frame |> filter(between(date, "2024-01-01", "2024-01-03")) |> group_by(date) |> summarise(n = n()) |> arrange(date)

dplyr の例 入力 条件 my_data_frame |> filter(between(date, "2024-01-01", "2024-01-03")) |> 条件 group_by(date) |> 出力 summarise(n = n()) |> 出力 arrange(date) 人間の考え方に沿った順番で集計コードが書ける

データベース操作で同じことできたら 便利と思いません？

dbplyr (2017) • RDBMS の操作を dplyr と同じように行うためのパッケージ my_table |> filter(between(date, "2024-01-01", "2024-01-03")) |> group_by(date) |> summarise(n = n()) |> arrange(date) |> collect()

dbplyr • 主要な RDBMS に対応 • MySQL • PostgreSQL • SQLite • BigQuery • Teradata • さらに ODBC にも対応

dbplyr の使い方 ① DBコネクションの作成 con <- DBI::dbConnect(RSQLite::SQLite(), path="db.sqlite") con <- DBI::dbConnect(RMySQL::MySQL(), host = "myhost.com", user = "hoxom", password = rstudioapi::askForPassword("Password?") )

dbplyr の使い方 ② テーブルオブジェクトの作成 my_table <- tbl(con, "my_table") ③ テーブルオブジェクトをデータフレームと同様に操作可能 my_table |> filter(between(date, "2024-01-01", "2024-01-03")) |> group_by(date) |> summarise(n = n()) |> arrange(date) |> collect()

dbplyr の仕組み • 作成されたオブジェクトを SQL に変換しているだけ • collect() 関数で RDBMS にクエリを投げている • show_query() 関数で変換後の SQL を確認できる • 冗長な SQL となることが多いが、コンパイラが最適化する

まとめ • SQL は脳に悪い • dbplyr は便利 • 機能が不足していると感じる場合は拡張ツールを作ると良い • Teradata 拡張を自作した例： https://github.com/hoxo-m/dplyr.teradata

• https://github.com/hoxo-m/dplyr.teradata