[Redshift] ウィンドウ関数：MEDIANを使用して中央値を取得する

はじめに

あるリストから、値の平均値では無く中央値を取得したい事がありますが、その場合にはウィンドウ関数のMEDIANを使用します。

環境

MacOSX 10.10.5 Yosemite
Redshift 1.0.1125

中央値とは

平均値とは違い、順番に並べた場合の中央に来る値を「中央値」と呼びます。
Wikipedia | 中央値

構文

Amazon Redshift | MEDIAN

MEDIAN ( median_expression ) OVER ( [ PARTITION BY partition_expression ] )

実行例１：特定グループの年収の中央値を表示させる

目的

Wikipediaの「平均値との関係」で例として挙げられている挙げられている年収の中央値を表示させる。
人数は100人では無く10人に変更。

準備

CREATE SCHEMA IF NOT EXISTS blog;

CREATE TABLE blog.income1 (
  id INTEGER NOT NULL
  , name VARCHAR(16)
  , group_id INTEGER
  , income INTEGER
);

INSERT INTO blog.income1 VALUES
(1,'阿部',1,200),(2,'井上',1,200),(3,'上田',1,200),(4,'遠藤',1,200),(5,'太田',1,200)
,(6,'加藤',1,200),(7,'木下',1,200),(8,'工藤',1,200),(9,'近藤',1,200),(10,'佐藤',1,5000);

testdb=# SELECT * FROM blog.income1 ORDER BY id;
 id | name | group_id | income 
----+------+----------+--------
  1 | 阿部 |        1 |    200
  2 | 井上 |        1 |    200
  3 | 上田 |        1 |    200
  4 | 遠藤 |        1 |    200
  5 | 太田 |        1 |    200
  6 | 加藤 |        1 |    200
  7 | 木下 |        1 |    200
  8 | 工藤 |        1 |    200
  9 | 近藤 |        1 |    200
 10 | 佐藤 |        1 |   5000
(10 rows)

平均値を確認してみる

関数AVGでFLOATにCASTしてますが、カラム「income」がINTEGERなので、こうしないと小数点以下が出ないためです。

testdb=# SELECT AVG(CAST(income AS FLOAT)) FROM blog.income1;
 avg 
-----
 680
(1 row)

中央値を見てみる

SELECT
  id
  , income
  , MEDIAN(income) OVER(PARTITION BY group_id)
FROM
  blog.income1
ORDER
  BY id;

testdb=# SELECT id, name, income, MEDIAN(income) OVER(PARTITION BY group_id) FROM blog.income1 ORDER BY id;
 id | name | income | median 
----+------+--------+--------
  1 | 阿部 |    200 |  200.0
  2 | 井上 |    200 |  200.0
  3 | 上田 |    200 |  200.0
  4 | 遠藤 |    200 |  200.0
  5 | 太田 |    200 |  200.0
  6 | 加藤 |    200 |  200.0
  7 | 木下 |    200 |  200.0
  8 | 工藤 |    200 |  200.0
  9 | 近藤 |    200 |  200.0
 10 | 佐藤 |   5000 |  200.0
(10 rows)

平均値の「680.0」でしたが、中央値は「200.0」となりました。
GROUP BYしなくても良いのも特徴ですね。

実行例２：

目的

グループ毎に年収の中央値を表示させる。

準備

CREATE TABLE blog.income2 (
  id INTEGER NOT NULL
  , name VARCHAR(16)
  , group_id INTEGER
  , income INTEGER
);

INSERT INTO blog.income2 VALUES
(1,'阿部',1,200),(2,'井上',1,1000),(3,'上田',2,200),(4,'遠藤',2,300)
,(5,'太田',2,350),(6,'加藤',2,1000),(7,'木下',3,200),(8,'工藤',3,200)
,(9,'近藤',3,450),(10,'佐藤',3,500),(11,'志村',3,5000);

testdb=# SELECT * FROM blog.income2 ORDER BY id;
 id | name | group_id | income 
----+------+----------+--------
  1 | 阿部 |        1 |    200
  2 | 井上 |        1 |   1000
  3 | 上田 |        2 |    200
  4 | 遠藤 |        2 |    300
  5 | 太田 |        2 |    350
  6 | 加藤 |        2 |   1000
  7 | 木下 |        3 |    200
  8 | 工藤 |        3 |    200
  9 | 近藤 |        3 |    450
 10 | 佐藤 |        3 |    500
 11 | 志村 |        3 |   5000
(11 rows)

３つのグループに分かれています。

平均値を見てみる

group_id毎に平均値を出しました。

testdb=# SELECT group_id, AVG(CAST(income AS FLOAT)) FROM blog.income2 GROUP BY group_id ORDER BY group_id;
 group_id |  avg  
----------+-------
        1 |   600
        2 | 462.5
        3 |  1270
(3 rows)

中央値を見てみる

MEDIANではCASTは不要です。

SELECT
  group_id
  , id
  , name
  , income
  , MEDIAN(income) OVER(PARTITION BY group_id)
FROM
  blog.income2
ORDER BY
  group_id, id;

testdb=# SELECT group_id, id, name, income, MEDIAN(income) OVER(PARTITION BY group_id) FROM blog.income2 ORDER BY group_id, id;
 group_id | id | name | income | median 
----------+----+------+--------+--------
        1 |  1 | 阿部 |    200 |  600.0
        1 |  2 | 井上 |   1000 |  600.0
        2 |  3 | 上田 |    200 |  325.0
        2 |  4 | 遠藤 |    300 |  325.0
        2 |  5 | 太田 |    350 |  325.0
        2 |  6 | 加藤 |   1000 |  325.0
        3 |  7 | 木下 |    200 |  450.0
        3 |  8 | 工藤 |    200 |  450.0
        3 |  9 | 近藤 |    450 |  450.0
        3 | 10 | 佐藤 |    500 |  450.0
        3 | 11 | 志村 |   5000 |  450.0
(11 rows)

中央値の解説。
・group_id：１
　人数は二人、200と1000の中間の「600.0」となりました。
・group_id：2
　人数は４人、300と350の中間の「325.0」となりました。
・group_id：3
　人数は５人、５人の真ん中の３人目の年収「450.0」となりました。

さいごに

平均値とは違う点を理解して使いましょう。