HN 日本語サマリー

← 一覧へ戻る
プログラミング

SQL内で利用できる統計

Statistics that live in your SQL (kolistat.com)

34 pointsby caerbannogwhite3 コメント

要約

the-stats-duckは、DuckDBのオープンソース拡張機能で、SQLを離れることなく統計分析(分布、検定、回帰、プロットなど)を実行できます。v0.6.0リリースでは、全テーブルのプロファイル、Rスタイルの回帰分析、ブートストラップによる信頼区間計算、SQLからの直接描画機能、10種類の新しい分布関数などが追加され、SAS/SPSS/Stataファイルの読み込みが大幅に高速化されました。

全文翻訳

the-stats-duck v0.6.0 — SQL内で利用できる統計機能

the-stats-duck — 私たちのオープンソースDuckDB拡張機能 — がv0.6.0 をリリースしました。今回のリリース名は、プロジェクト全体の方針に沿って「i-m-not-dead」です。まさにその通り、まだ生きています!

まだご存知ない方のために説明すると、the-stats-duck は Bedevere と KoliLang が依存している統計エンジンです。これにより、DuckDBはSQLを離れることなく、実際の統計(分布、検定、回帰、さらにはプロット)を実行できます。MITライセンスで、ブラウザを含むDuckDBが動作するあらゆる場所で実行可能です。

これは非常に便利です。なぜなら、以下のすべてのデモは、ブラウザ内でthe-stats-duckを実行しているライブな Bedevere インスタンスだからです。データセットは有名な Palmer Penguins です — SQLを変更して再実行してみてください。

SELECT * FROM 'penguins';

新しいデータセットで最初に誰かが行うことは、それをじっと見つめることでしょう。meta() は、列ごとに1行で完全なプロファイルをあなたのためにじっと見つめてくれます:

SELECT
  column_name, kind, n_missing
  , n_distinct, mean, median, stddev, top
FROM meta('penguins')
;

これはDuckDBの組み込み関数 SUMMARIZE と重複しますが、meta() はテーブル関数なので、結合したり、フィルタリングしたり、CTE(共通テーブル式)で組み合わせたりできます。1 「数値型の列がいくつあり、合計でいくつの欠損値があるか?」を知る必要がある場合、それは単に meta() を集計するだけです。

SQLを離れずに回帰分析: lm + Rスタイルの数式

はい、最小二乗法(Ordinary Least Squares)をSQLクエリ内で、あなたがすでに知っている数式構文で実行できます:

SELECT *
FROM lm_summary(
  'penguins'
  , formula := 'body_mass_g ~ flipper_length_mm + bill_length_mm'
);

lm() は係数テーブル(各項目の estimate, std_error, t_statistic, p_value)を返します。lm_summary() はモデルレベルの行(R², 調整済みR², F値とそのp値、残差自由度、sigma)を返します。数式言語は加法的な予測変数と切片の除去(- 1)を扱います。交互作用やインライン変換はv0.6にはまだありませんが、近いうに追加するかもしれません!

内部的には、これは X'X のコレスキー分解(Cholesky solve)であり、完全ケースフィルタリングされ、Rの cars および mtcars データセットで4桁まで検証されています。

総当たりで信頼区間を計算: bootstrap()

正規性の仮定をしたくないですか?リサンプリングしましょう:

WITH b AS (
  SELECT bootstrap(body_mass_g, 'mean', 2000, 42) AS samples
  FROM penguins
)
SELECT
  list_aggregate(samples, 'quantile_cont', 0.025) AS lo
  , list_aggregate(samples, 'quantile_cont', 0.975) AS hi
FROM b
;

bootstrap(value, statistic, n_iters [, seed]) は復元抽出でリサンプリングを行い、各リサンプルからの統計量(mean, median, stddev など)の LIST<DOUBLE> を返します。パーセンタイル区間を得るために list_aggregate(list, 'quantile_cont', p) と組み合わせて使用します。シードを渡せば再現可能 — GROUP BY グループ間でも同様です。

SQLから直接グラフを作成: VISUALIZE … DRAW

the-stats-duckは、現在(まだ)小さなプロット文法(ggsql)も扱っており、これはVega-Liteにコンパイルされ、Bedevereによってレンダリングされます。v0.6.0では、バイオリンプロット、2次元ファセット(FACET BY row, col)、およびレイヤーごとのSTAT smooth(LOESS)が追加されました — これにより、DRAW point DRAW line STAT smooth は1行でトレンド付き散布図を作成できます。

VISUALIZE
  species AS x
  , body_mass_g AS y
FROM penguins
DRAW violin
TITLE 'Body Mass (g) by species'
;

フィットした線付き散布図: STAT smooth

v0.6.0のチャート機能のもう半分は、レイヤーごとのSTAT修飾子です — これはマークの後に DRAW <mark> STAT <name> として追加されます。これはそのレイヤーのみを変換するため、生のマークを統計的なマークの下に重ねることができます。典型的な使用例は、LOESSオーバーレイ付きの散布図です:

VISUALIZE
  bill_depth_mm AS x
  , bill_length_mm AS y
  , species AS color
FROM penguins
DRAW point
DRAW line STAT smooth
SCALE x ZERO false
SCALE x LABEL 'Bill Depth (mm)'
SCALE y ZERO false
SCALE y LABEL 'Bill Length (mm)'
TITLE 'Penguins Bill Depth VS Bill Length (Smooth)'
;

3つの修飾子が提供されます。smooth は、(x, y) にVega-LiteのLOESS変換を注入し、color がマッピングされている場合はグループ化します — 上記では種ごとに1つのフィット曲線が表示されます。summary は、レイヤーのデータSQLを AVG(y) GROUP BY x [, color, facet, facet2] ORDER BY x に書き換え、各セルを平均値に集約します。identity は明示的な何もしない操作です。smooth は、すでに独自の変換を生成するマーク(regression, density, violin, histogram)では拒否されます。これらは統計をそれ自体に組み込んでいます。

直線フィット: DRAW regression

STAT smooth がデータの局所的な形状をトレースするのに対し、regression マークは単一の直線 — 最小二乗法による y ~ x フィット — を描画します。これは color でグループ化されます。上記のグラフと同じ軸を使用しますが、各種はLOESS曲線ではなく線形トレンドを受け取ります:

VISUALIZE
  bill_depth_mm AS x
  , bill_length_mm AS y
  , species AS color
FROM penguins
DRAW point
DRAW regression
SCALE x ZERO false
SCALE x LABEL 'Bill Depth (mm)'
SCALE y ZERO false
SCALE y LABEL 'Bill Length (mm)'
TITLE 'Penguins Bill Depth VS Bill Length (Regression)'
;

10種類の新しい分布(および完全なd/p/q/r)

分布のライブラリが拡張されました:負の二項分布、超幾何分布、ワイブル分布、対数正規分布、ポアソン分布。それぞれにRスタイルの d / p / q の3つの関数があり、さらに乱数生成関数の完全なセット(rnorm, rt, rchisq, rf, rgamma, rbeta, rexp, rweibull, rlnorm, rpois)も含まれています。すべてRに対して6桁まで検証されています。別の言語へのラウンドトリップなしで、シミュレーション、フィット、テストが可能です。

これは、ポアソン分布(λ = 3)の確率質量関数 — 0…10 の範囲で評価された dpois を棒グラフとして描画したものです。すべてSQLで実行されます:

WITH pois AS (
  SELECT k, dpois(k, 3) AS pmf
  FROM range(0, 11) AS t(k)
)
VISUALIZE
  k AS x
  , pmf AS y
FROM pois
DRAW bar
;

そして高速化されました: read_stat が52倍高速に

最後に、これは XPTファイル のユーザー向けです — SAS / SPSS / Stata ファイルの読み込みは、以前は意図せず O(N²) でした。リーダーが2,048行のチャンクごとにファイル全体をバイトゼロから再解析していたのです!😱

v0.6.0では、各ファイルを正確に1回だけ解析し、バッファリングされ、スピル可能なカラムストアに格納し、そこからストリーミングします。以下がその数値です:

  • 20万行、7MBのXPTファイル: 約67秒 → 約1.3秒 (52倍)
  • 実際のCDISCパイロットデータ qs.xpt (12万2千行): 約39秒 → 約1.1秒 (36倍)
  • 160万行: 約70分 → 約15秒

遊んでみよう

the-stats-duck はオープンソースです: github.com/KoliStat/the-stats-duck (完全な v0.6.0 のリリースノートはこちら)。これは Bedevere と KoliLang を支えていますが、単なるDuckDB拡張機能です。任意のDuckDBにドロップするだけで、あなたのSQLに統計部門が追加されます。

死んでいません。あなたの統計を行っています。

脚注

  1. 読者からの指摘によると、DuckDBの SUMMARIZE もCTE内で使用できます — FROM のサブクエリでラップするだけです: WITH some_table AS (FROM range(10)), x AS (FROM (SUMMARIZE some_table)) FROM x;。実質的な違いは使いやすさです。なぜなら meta() はテーブル関数なので、FROM (…) ラッパーなしで(結合、フィルタリング、CTEで)構成できるからです。↩