音楽の趣味: Feb18 今週のおすすめ

■今週の音楽

■ Heart To Heart by Brian McKnight
　・Brian McKnight さんの Periscope を用いて撮影している「Brianized」動画にはまった

様々な中央値の求め方

このような人たちがいたとして、身長の中央値を求めます。

id	name	gender	height
1	anna	female	170
2	danny	male	180
3	tony	male	164
4	kate	female	157
5	jane	female	160
6	scot	male	190

今週以前に読んで面白かった以下の記事内容を用いて求めます。

■HAVING句の力 - codezine.jp
　・これの後編もおすすめです。読んだ翌日に業務で使えました。
■Using AUTO_INCREMENT - dev.mysql.com
　・MySQLだとvariablesを用いてレコードごとにincrementしていく書き方があるけど、テーブルを一から定義したらこんなに楽にできるんだと知りtempテーブルを多用した週でした。
■グループごとの中央値の求め方 - danielsetzermann.com
　・先週紹介してたやつですが、ミックさんのHAVING句の力の記事ではできなかった複数グループの中央値を一度に求めるSQL文をこれで学びました。

SQLで書いて答えを取得するときはこう書きます。
※上記テーブル名がmedianとすると、


SELECT
  AVG(DISTINCT height)
 FROM
  (SELECT
    A.height
   FROM
    median A, median B
   GROUP BY
    A.height
   HAVING
    SUM(CASE
      WHEN B.height >= A.height
      THEN 1
      ELSE 0
    END) >= COUNT(*) / 2 AND
    SUM(CASE
      WHEN B.height <= A.height
      THEN 1
      ELSE 0
    END) >= COUNT(*) / 2) X;

ミック. (2006, November 2). HAVING句の力より回答を編集

仕組みとしては、「母集合を上位と下位の2つの集合に分割し、しかもその際、2つの集合が真ん中の値を共有するようにして」おき、それらの平均を求めるというものです。

ちなみにエクセルだったら

=MEDIAN(範囲)

で済みます。

ただし、性別ごとに中央値を取るのはエクセルだとできないこと知ってました？（どーん）
ほしいデータ

gender	height_median
female	160
male	180

エクセルでできる一番近いのはピボットテーブルですがピボットテーブル上では平均は求められても中央値は求められないんです。
でもSQLだと一発で出せます。

答えのコードは２つあって、僕は先週２つめを知りました。１つめはMySQLでしかサポートしていないvariablesを使います：


SET @ROW_NUMBER := 0;
SET @median_group := '';

SELECT
  median_group, AVG(height) AS median
 FROM
  (SELECT
    -- もし@median_groupがgenderと一致したら前のレコードに1足して、もし違ったら1になる
    @ROW_NUMBER := CASE
      WHEN @median_group = gender
      THEN @ROW_NUMBER + 1
      ELSE 1
    END AS count_of_group,
    -- @median_groupにgenderの値を割当
    @median_group := gender AS median_group,
    gender,
    height,
    (SELECT
      COUNT(*)
     FROM
      median
     WHERE
      A.gender = gender) AS total_of_group
   FROM
    (SELECT
      gender,
      height
     FROM
      median
     ORDER BY
      gender,
      height) AS A) AS B
 WHERE
  count_of_group BETWEEN total_of_group / 2.0 AND total_of_group / 2.0 + 1
 GROUP BY
  median_group;

参考：
Setzermann, D. (2016, July 09). How to calculate the median per group with MySQL.\

答え２つめ、MyISAMエンジンを使ってグループ内でのみAUTO_INCREMENTするPRIMARY KEYを作り、１つめの答えにおけるvariablesを代替します：


CREATE TABLE median2 (
  count_of_group INT AUTO_INCREMENT,
  gender VARCHAR(50),
  height INT,
  PRIMARY KEY (gender, count_of_group)
) ENGINE = MyISAM;  -- MyISAMエンジンを使うこと

INSERT INTO median2 (gender, height)
SELECT
  gender,
  height
 FROM
  median
 ORDER BY
  height
;

SELECT
  gender, AVG(height) AS height_median
 FROM
  (SELECT
    count_of_group,
    gender,
    height,
    (SELECT
      COUNT(*)
     FROM
      median2
     WHERE
      gender = A.gender
     GROUP BY
      gender) AS total_of_group
   FROM
    median2 AS A) AS B
 WHERE
  count_of_group BETWEEN total_of_group / 2.0 AND total_of_group / 2.0 + 1
 GROUP BY
  gender;

両方とも大枠で仕組みは一緒で、まず両方とも性別ごとに身長の高さでソートして通し番号を付けて以下のようなテーブル（Bでラベリングしてあるサブクエリ）を用意します。

count_of_group	gender	height	total_of_group
1	female	157	3
2	female	160	3
3	female	170	3
1	male	164	3
2	male	180	3
3	male	190	3

count_of_groupは性別内での身長順位で、total_of_groupはその性別の合計人数です。
そこからcount_of_groupが以下になる時の身長の平均値を計算します。

total_of_groupを2で割った時の値
total_of_groupを2で割って1を足した時の値

つまりcount_of_groupが1.5と2.5の間の数字（＝2）の時の値の平均値。
ちなみに性別の合計人数が偶数の時、例えば４人ずついると、2の時の値と3の時の値の平均ということになります。

私は１つめの回答のほうが（ほぼ）ひとつのクエリで完結している感じがすっきりしていて好きですが、なんとなくSQLのフルパワーを使っている感のある２つめも嫌いではないです。
ちなみにSTORED PROCEDUREを用いるという手があるとかねがね聞いているのでそのうち勉強します。

Saturday, February 18, 2017

Feb18 今週のおすすめ

様々な中央値の求め方