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()新しいデータセットに触れたとき、誰もがまずその内容をじっくり見ることでしょう。meta()は、そのデータセットの完全なプロファイルを、カラムごとに1行で表示してくれます。 SELECT column_name, kind, n_missing , n_distinct, mean, median, stddev, top FROM meta('penguins') ; これはDuckDBに組み込まれているSUMMARIZEと重複する部分もありますが、meta()はテーブル関数であるため、結合したり、フィルタリングしたり、CTEsで構成したりできます。「数値カラムはいくつあり、欠損値の合計はいくつあるか？」を知りたい場合、それはmeta()に対する単なる集計で実現できます。 SQLを離れることなく回帰分析: lm + Rスタイルの式はい、あなたが既に知っている式構文で、SQLクエリ内で通常の最小二乗法が可能です。 SELECT * FROM lm_summary( 'penguins' , formula := 'body_mass_g ~ flipper_length_mm + bill_length_mm' ); lm()は係数テーブル（各項目の推定値、標準誤差、t統計量、p値）を提供し、lm_summary()はモデルレベルの行（R²、調整済みR²、F値とそのp値、残差自由度、シグマ）を提供します。この式言語は加法的予測子と切片の除去（-1）を扱えます。相互作用とインライン変換はv0.6にはまだありませんが、近いうちに追加されるかもしれません！内部では、X'Xのコレスキー分解により解かれ、完全なケースでフィルタリングされ、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(値, 統計量, 繰り返し回数 [, シード])は、リサンプリングを置き換えありで行い、各リサンプルからの統計量（平均、中央値、標準偏差など）のLIST<DOUBLE>を返します。これをlist_aggregate(リスト, 'quantile_cont', p)と組み合わせてパーセンタイル区間を求めます。シードを渡せば再現可能です—GROUP BYグループ全体でも同様です。 SQLから直接チャートを生成: VISUALIZE … DRAWthe-stats-duckは、現時点では小規模なプロット文法（ggsql）もサポートしており、これはBedevereがレンダリングするVega-Liteにコンパイルされます。v0.6.0では、バイオリンマーク、2Dファセット（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 smoothv0.6.0のチャート機能のもう半分は、レイヤーごとのSTATモディファイアです。これは、DRAW <マーク> STAT <名前>のようにマークの後に付加されます。このモディファイアはそのレイヤーのみを変換するため、生データを統計的なレイヤーの下に重ねることができます。典型的な使用例は、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変換を挿入し、マッピングされている場合は色でグループ化します—上記の例では種ごとに1つの適合曲線が描かれます。summaryは、レイヤーのデータSQLをAVG(y) GROUP BY x [, color, facet, facet2] ORDER BY xに書き換え、各セルをその平均に集約します。identityは明示的なno-op（何もしない操作）です。smoothは、回帰、密度、バイオリン、ヒストグラムなど、すでに独自の変換を出力するマークには適用されません。これらは統計情報をそれ自体に組み込んでいるためです。 直線近似: DRAW regressionSTAT smoothがデータの局所的な形状をなぞるのに対し、regressionマークは単一の直線—最小二乗法によるy ~ xの近似—を、色でグループ化して描画します。上記と同じ軸ですが、各魚種は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トリプルとともに加わりました。さらに、乱数サンプラーの完全なセット（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では、各ファイルを一度だけバッファリングされ、スピル可能なカラムストアに解析し、そこからストリームします。以下がその数値です: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に統計部門が加わります。死んでいません。あなたの統計を処理しています。