はじめに

基本統計量の算出は、ExcelやSPSSのようなツールや、あるいはRのような言語を使って求めることが多いと思います。
そのため、DB内のデータについて基本統計量を求める場合、一旦DBからデータを抽出してエクスポートするような形になるケースが多いのではないかと思います。

SQLには加減乗除の計算だけでなく、SUMやAVGなどの集計関数も揃っているので、今回は初歩的な集計関数を使ってSQLで基本統計量を求める方法を探ってみました。

使用した環境

OS
CentOS7.7（1908）
RDBMS
MySQL8.0.19

テストデータ

テストデータとして、Wikipediaに掲載されている八ヶ岳の標高データを利用しました。

テーブル

CREATE文

-- 山情報テーブルの作成。
CREATE TABLE `nkojima`.`mountain` (
  `id` INT AUTO_INCREMENT comment '山ID',
  `name` VARCHAR(50) NOT NULL comment '山名', 
  `altitude` double NOT NULL comment '標高',
  `note` VARCHAR(200) comment '備考',
  PRIMARY KEY (id),
  INDEX name_idx(name)
)
ENGINE = InnoDB,
default charset=utf8mb4
comment='山情報';

データ

INSERT文

INSERT INTO mountain
  (name, altitude, note)
VALUES
  ('編笠山',2524,'南八ヶ岳'),
  ('西岳',2398,'南八ヶ岳'),
  ('三ッ頭',2580,'南八ヶ岳'),
  ('権現岳',2715,'南八ヶ岳'),
  ('赤岳',2899,'南八ヶ岳'),
  ('中岳',2700,'南八ヶ岳'),
  ('阿弥陀岳',2805,'南八ヶ岳'),
  ('横岳',2829,'南八ヶ岳'),
  ('硫黄岳',2760,'南八ヶ岳'),
  ('赤岩の頭',2656,'南八ヶ岳'),
  ('峰の松目',2567,'南八ヶ岳'),
  ('箕冠山',2590,'北八ヶ岳'),
  ('根石岳',2603,'北八ヶ岳'),
  ('天狗岳',2646,'北八ヶ岳'),
  ('中山',2496,'北八ヶ岳'),
  ('丸山',2330,'北八ヶ岳'),
  ('茶臼山',2384,'北八ヶ岳'),
  ('縞枯山',2403,'北八ヶ岳'),
  ('北横岳',2480,'北八ヶ岳'),
  ('大岳',2381,'北八ヶ岳'),
  ('双子山',2224,'北八ヶ岳'),
  ('蓼科山',2530,'北八ヶ岳'),
  ('八子ヶ峰',1833,'北八ヶ岳');

基本統計量の算出

平均値、最小値、最大値

平均値、最小値、最大値は、それぞれAVG、MAX、MINという初歩的な関数を使って簡単に求められます。

平均値、最小値、最大値を求めるSQL

SELECT
  AVG(altitude) AS 平均値,
  MAX(altitude) AS 最大値,
  MIN(altitude) AS 最小値
FROM
  mountain;

実行結果

+-------------------+-----------+-----------+
| 平均値            | 最大値    | 最小値    |
+-------------------+-----------+-----------+
| 2536.217391304348 |      2899 |      1833 |
+-------------------+-----------+-----------+

中央値

以下のSQLは、mysqlで中央値(メジアン）を出す方法に記載されていた方法を、少しだけ改変した方法となります。
データの個数が偶数の時にも対応しています。
幾つものSQLに分かれて複雑化している原因は、LIMIT句やOFFSET句に変数を設定できないというMySQLの仕様によるところが大きいです。

中央値を求めるSQL

-- 1. データの個数を求める。
SELECT COUNT(*) INTO @row_count FROM mountain;

-- 2. offset値とlimit値を取得する。
-- データの個数が奇数の時：中央値は「昇順に並べた際の順位が中央となる値」とする。
-- データの個数が偶数の時：中央値は「中央の前後のレコードの件数の平均値」とする。
SET @offset = 0;
SET @limit = 0;
SELECT 
  CASE WHEN @row_count % 2 = 0 THEN
    FLOOR( @row_count / 2 ) - 1
  ELSE
    FLOOR( @row_count / 2 )
  END INTO @offset
FROM dual;

SELECT 
  CASE WHEN @row_count % 2 = 0 THEN
    2
  ELSE
    1
  END INTO @limit
FROM dual;

-- 3. PreparedStatementにオフセット値をバインドして実行する。
PREPARE
  pstmt_median
FROM
  'SELECT
     AVG(temp.altitude) AS 中央値
   FROM
     (select altitude from mountain order by altitude asc limit ? offset ?) AS temp';
EXECUTE pstmt_median USING @limit, @offset;

実行結果

+-----------+
| 中央値    |
+-----------+
|      2567 |
+-----------+

分散

分散は「『平均値からの差の2乗』の総和をデータ数で割ったもの」です。
1つのSQLでまとめるのが厄介そうでしたが、インラインビュー（FROM句内での副問い合わせ）を使ったら1つのSQLにまとめられました。
- MySQLには分散（母標準分散）を求めるVARIANCEという関数が用意されているので、学習目的でなければVARIANCEを使った方が良いと思います。

分散を求めるSQL

-- 1. インラインビューで平均値を求める。
-- 2. 「平均値からの差の2乗」の平均値で分散を求める。
SELECT
  AVG(
    POW(T1.altitude-T2.avg_alt, 2)
  ) AS 分散
FROM
  mountain T1,
  (
    SELECT
      AVG(altitude) AS avg_alt
    FROM
      mountain
  ) AS T2;

実行結果

+------------------+
| 分散             |
+------------------+
| 50376.5179584121 |
+------------------+

標準偏差

標準偏差は「分散の平方根」なので、「分散」のSQLをベースにして作りました。
- MySQLには標準偏差（母標準偏差）を求めるSTDDEVという関数が用意されているので、学習目的でなければSTDDEVを使った方が良いと思います。

標準偏差を求めるSQL

-- 1. インラインビューで平均値を求める。
-- 2. 「平均値からの差の2乗」の平均値で分散を求める。
SELECT
  AVG(
    POW(T1.altitude-T2.avg_alt, 2)
  ) AS 分散
INTO
  @variance
FROM
  mountain T1,
  (
    SELECT
      AVG(altitude) AS avg_alt
    FROM
      mountain
  ) AS T2;

-- 3. 分散の平方根を求める。
SELECT SQRT(@variance) AS 標準偏差;

実行結果

+-------------------+
| 標準偏差          |
+-------------------+
| 224.4471384500415 |
+-------------------+

【MySQL】SQLで基本統計量を求める

はじめに

使用した環境

テストデータ

テーブル

データ

基本統計量の算出

平均値、最小値、最大値

中央値

分散

標準偏差