私は分析官。MySQLが嫌い。

三連休最終日。
休日出勤をして、ちょっとした集計をしようと思っただけなのに。
MySQLを通じて嫌な思いをしたので、書きなぐる、、、

何をしたかったか

Userの[年齢]情報の統計を確認したかった。
※ 実際には年齢の集計ではないが、安全のために

正規分布しておらず、むしろロングテールなのが今回のデータの特徴で、
代表値として平均を使うのは危険で、中央値を集計したかった。

準備

とりあえずこんな感じで一時テーブルを作った。
年齢が例だと冗長だけど、実際には重い処理だったので、必要だった。

drop temporary table if exists user_age
;
create temporary table user_age
select
	user_id
	, age
from
	user
;

平均、合計、カウント

かんたんである

select
  avg(age) average
  , count(distinct user_id) count
  , sum(age) sum 
from user_age
;

中央値の集計でイラッとした

本件データは100万レコードをゆうに超えており、Excelにうつしてmedian()するわけにもいかない。
そもそも、SQLの世界から飛び出てしまうのは、分析の再現性や、ポータビリティの観点から好ましくない。
というわけで、今回はいやいやMySQL内での完結を試みたのである。

イラッと①：MySQLにはmedian()がない。

medianを出すには、わざわざこんな書き方をしないといけない

select avg(distinct age) median
from(
  select t1.age
  from
    user_age t1
    , user_age t2  
  group by t1.age
  having
    sum(case when t2.age>= t1.age then 1 else 0 end) >= count(*) / 2 
    and sum(case when t2.age <= t1.age then 1 else 0 end) >= count(*) / 2 
  )tmp
;

可読性が終わっている。
どんな操作をしているか、全く読者に伝わらない。
どういうふうにコメントすればいいのだろうか。解説サイトのURLでも貼るか。

イラッと②：一時テーブルを複数回参照できない

上記コードは動かない。mysql can't reopen tableって怒られた。
いやいやいや、なんでダメなの、、、
したがって、先のコードに、

drop temporary table if exists ua_copy
;
create temporary table ua_copy
select * from user_age
;

というおまけがつき最終的な出来上がりは、

drop temporary table if exists ua_copy
;
create temporary table ua_copy
select * from user_age
;
select avg(distinct age) median
from(
  select t1.age
  from
    user_age t1
    , ua_copy t2  
  group by t1.age
  having
    sum(case when t2.age>= t1.age then 1 else 0 end) >= count(*) / 2 
    and sum(case when t2.age <= t1.age then 1 else 0 end) >= count(*) / 2 
  )tmp
;

イラッと③：遅い

なお、この記事書いてずいぶん時間がたつが、まだ実行が終わっていない。
もちろん、数百万行のcross joinをしているのが悪いわけだが、それにしてもしかしひどい。
100万行以下だったら、SQLでmedian求めずに、Excelでmedian()できたのに…
言い換えると、実質MySQLでmedianは求めるのは常に筋悪である、というのが所感である。

最後に

MySQL以外の環境に詳しくはない。
もしかしたらPostgreSQLでも同じこと起きるのかもしれない。
MySQLの名指しで叩くのはアンフェアかもしれない。

分析専業でもないので、何か知識不足で拙いことをしているのかもしれない。
しかし、数百人規模のIT企業の非エンジニアで1,2のSQLスキルを持っている。

私はデータ分析文化を育むには、一定数、ビジネス職がSQLを叩く必要があると思っている。
データ分析に興味を持ってくれた人が、こんなmedian一つで、躓いてほしくない。

技術選定にあたっては、そういった、データ分析環境のことも考えてもらえると嬉しい。
もしくは、積極的にBigQueryなどモダンな環境にレプリしてくれると嬉しい。
SASなどを入れるとUIの力で解決するのかもしれないが、どうなのだろうか。