はじめに

PostgreSQL ではデータサイエンスに適用できるような機能を次々とリリースしています。

PostgreSQL 9.4 で導入された最頻値やメディアンを取得できるような集約関数、
PostgreSQL 9.5 では GROUPING SETS のようなグループ化関数、
PostgreSQL 9.6 でのパラレルクエリ導入によって、統計処理・データサイエンス関係の SQL の高速化が行われています。

本稿では、ボストンの住宅価格のデータを使って、簡単な平均、メディアンなどの計算の例を取り上げます。

ボストンデータセットとは

1978年に取得された米国ボストン市郊外における地域別の住宅価格のデータセットです。
scikit-learn や　R にも添付されているデータサイエンスでよく使われるデータセットです。

ボストンデータセットのダウンロード

まずはボストンデータセットをダウンロードします。

wget https://archive.ics.uci.edu/ml/machine-learning-databases/housing/housing.data

ボストンデータセットのテーブルを作成します。

create table boston (
  "crim" real,
  "zn" real,
  "indus" real,
  "chas" integer,
  "nox" real,
  "rm" real,       -- 部屋の数の平均
  "age" real,
  "dis" real,
  "rad" integer,
  "tax" real,
  "ptratio" real,  -- 町毎の児童と教師の比率
  "black" real,
  "lstat" real,    -- 給与の低い職業に従事する人口の割合 (%)
  "medv" real      -- 住宅価格のメディアン（ 1,000USD 単位）
);

ボストンデータセットをインポート

なお、このコマンドを実行するときは、事前に PGUSER 等の環境変数が適切に設定されている必要があります。

cat housing.data | psql -c 'COPY boston FROM STDIN;'

統計分析する。

前提

medv が 50.0 のデータが 16個ありますが、これは外れ値と考えられるので除きます。
同様に部屋の数が 8つ以上あるデータも外れ値と考えられるので除きます。
最小値、最大値、25%パーセンタイル、メディアン、75%パーセンタイル、特徴量との相関係数を表示します。

作ったSQL

WITH
  preprocessed AS (
    SELECT
      medv AS price,
      rm AS room_number,
      lstat AS lower_status_percentage,
      ptratio AS student_teacher_ratio
    FROM
      boston
    WHERE
      medv != 50 AND rm < 8
  )
SELECT
  COUNT(1),
  AVG(price),
  MIN(price),
  MAX(price),
  PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER by price) AS median_price,
  STDDEV_SAMP(price) AS stddev_price,
  PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER by price) AS first_quartile,
  PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER by price) AS third_quartile,
  PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER by price) -
    PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER by price) AS inter_quartile,
  CORR(price, room_number) AS corr_rm,
  CORR(price, lower_status_percentage) AS corr_lstat,
  CORR(price, student_teacher_ratio) AS corr_ptratio
FROM
  preprocessed;

列名	値
count	482
avg	21.3215768109713
min	5
max	48.5
median_price	20.75
stddev_price	7.46736990320816
first_quartile	16.6000003814697
third_quartile	24.4749999046326
inter_quartile	7.87499952316284
corr_rm	0.655629871072213
corr_lstat	-0.763613751990255
corr_ptratio	-0.523482478209307

これから、部屋の数が多くなれば住宅価格は高くなること、低所得者層の割合が大きくなれば住宅価格は安くなること、が分かります。

異常値を省く前処理は、WITH 句を使った Common Table Expression で行っています。

PERCENTILE_CONT(fraction) WITHIN GROUP (ORDER BY sort_expression)

というのは指定された割合に対応するパーセンタイルの値を返します。
順序集合を指定する必要があります。

CORR は相関係数を返す関数です。

おわりに

PostgreSQL は行指向のデータベースなので、データ分析には不向きといわれることもありますが、。
データベースからアプリケーションサーバにデータを転送するのにも一定のコストがかかりますし、多くの環境ではデータベースサーバに有用な情報が多く蓄積されていることでしょう。

今回は単一テーブルなので、あまり恩恵はありませんでしたが、複数のテーブルのデータなどを組み合わせて、データ分析する場合などは特にふだん使い慣れた SQL を使えることには利点があります。

Foreign Data Wrapper の機能も年々充実されており、データマートとしても利用可能な基盤となっています。

PostgreSQL での CSV からのインポート、平均、メディアン、相関係数の計算