1. piyoSakai

    No comment

    piyoSakai
Changes in body
Source | HTML | Preview
@@ -1,122 +1,123 @@
三連休最終日。
休日出勤をして、ちょっとした集計をしようと思っただけなのに。
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の力で解決するのかもしれないが、どうなのだろうか。