Help us understand the problem. What is going on with this article?

私は分析官。MySQLが嫌い。

More than 1 year has passed since last update.

三連休最終日。
休日出勤をして、ちょっとした集計をしようと思っただけなのに。
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の力で解決するのかもしれないが、どうなのだろうか。

piyoSakai
ちょっとかじってるビジネス職
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away