1. piyoSakai

    Posted

    piyoSakai
Changes in title
+私は分析官。MySQLが嫌い。
Changes in tags
Changes in body
Source | HTML | Preview
@@ -0,0 +1,122 @@
+三連休最終日。
+休日出勤をして、ちょっとした集計をしようと思っただけなのに。
+MySQLを通じて嫌な思いをしたので、書きなぐる、、、
+
+# 何をしたかったか
+`User`の[年齢]情報の統計を確認したかった。
+※ 実際には年齢の集計ではないが、安全のために
+
+正規分布しておらず、むしろロングテールなのが今回のデータの特徴で、
+代表値として平均を使うのは危険で、中央値を集計したかった。
+
+# 準備
+とりあえずこんな感じで一時テーブルを作った。
+年齢が例だと冗長だけど、実際には重い処理だったので、必要だった。
+
+```SQL
+drop temporary table if exists user_age
+;
+create temporary table user_age
+select
+ user_id
+ , age
+from
+ user
+group by 1
+;
+```
+
+# 平均、合計、カウント
+かんたんである
+```sql
+select
+ avg(age) average
+ , count(distinct user_id) count
+ , sum(age) sum
+from user_age
+;
+```
+
+# 中央値の集計でイラッとした
+本件データは100万レコードをゆうに超えており、Excelにうつしてmedian()するわけにもいかない。
+そもそも、SQLの世界から飛び出てしまうのは、分析の再現性や、ポータビリティの観点から好ましくない。
+というわけで、今回はいやいやMySQL内での完結を試みたのである。
+
+## イラッと①:MySQLには`median()`がない。
+medianを出すには、わざわざこんな書き方をしないといけない
+
+```sql
+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`って怒られた。
+いやいやいや、なんでダメなの、、、
+したがって、先のコードに、
+
+```sql
+drop temporary table if exists ua_copy
+;
+create temporary table ua_copy
+select * from user_age
+;
+```
+
+というおまけがつき最終的な出来上がりは、
+
+```sql
+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の力で解決するのかもしれないが、どうなのだろうか。
+