Born Too Late

Yuya's old tech blog.

CIEN Pattern で複雑な集計をカジュアルに行う #mysqlcasual

2013-12-17 06:30:05

この記事は MySQL Casual Advent Calendar 2013 の 17 日目です。
前日は @Yuryu さんの MySQLにMHA を導入してハマったところでした。

今日は、私が CIEN Pattern (シャンパターン) と名付けた SQL のイディオムをご紹介します。
これは複雑な集計をカジュアルに行うためのもので、私は日常的に使っています。

イディオムの紹介とか言いつつ、大半は前に社内で書いた便利ツール (オープンソースでない) の紹介だったりします。

本日ご紹介するイディオム

ここでいう「複雑な」というのは、例えば「掲示板に書き込んでいるユーザのうち 20 台男性のユニークユーザ数・30 台女性のユニークユーザ数・全体のユニークユーザ数・全体の投稿数を SQL 一発で全部集計したい」みたいなヤツです。
「サブクエリ 7 重にもネストしている上相関サブクエリだ」とか「なんかテーブルを 30 個ぐらい JOIN している」とかそういうのではありませんが、そういう地獄の SQL にも応用は可能です。

SELECT
  COUNT(1) AS `全体の投稿数`,
  COUNT(DISTINCT p.user_id) AS `全体の UU`,
  COUNT(DISTINCT IF(u.age BETWEEN 20 AND 29 AND u.sex = 'm', u.id, NULL)) AS `20 台男性の UU`,
  COUNT(DISTINCT IF(u.age BETWEEN 30 AND 39 AND u.sex = 'f', u.id, NULL)) AS `30 台女性の UU`
FROM
  bbs_posts AS p
LEFT JOIN
  users AS u
ON
  p.user_id = u.id
;
+--------------+-----------+----------------+----------------+
| 全体の投稿数 | 全体の UU | 20 台男性の UU | 30 台女性の UU |
+--------------+-----------+----------------+----------------+
|        12220 |      1032 |             83 |            211 |
+--------------+-----------+----------------+----------------+
1 row in set

こんな感じの結果が得られます。

ここで注目して欲しいのは SELECT 対象のフィールドです。
最初のふたつはどこのご家庭でも使われているであろうごく普通の COUNT(expression) や COUNT(DISTINCT expiression) です。
今回ご紹介するイディオムで特徴的なのは、COUNT(DISTINCT expression) の expression 中に、何やら複雑な IF() を突っ込んでいるところです。

ちなみに GROUP BY を使った場合も同様のイディオムが使えます。

仕組みとしての詳細は後で紹介するとして、こういったクエリが必要になった背景について紹介します。

背景

そんな MECE でない集計にどんな意味が???みたいなツッコミ等あると思いますが、現実は複雑なものです。
耐えるしかない。

なんか「あれとあれのデータ集計出してもらえる?来週いっぱいまで。毎日」みたいなリクエストに対していちいちクエリ叩いて Excel を更新するには人間の一生はあまりに短い。
かといっていちいち専用のツール実装にかまけてられるほど人生暇でもない。

ソリューション、SQL Executor

というわけで作ったのが「あらかじめ SQL を登録しておくことでいつでもその結果をカジュアルに確認できておまけに結果を保存しておける」みたいなツールです。
仮に SQL Executor と呼称することにします。

予め言っておくと、再利用可能な実装になってないので今のところ公開してませんが、大したものでもないのでオープンな形で公開できればと思ってますし、あわよくば誰か作ってくれないかと強く願っています。
まぁとても簡単なツールなので、新卒エンジニアにでも作らせると良いのではないでしょうか。一晩で。

もうちょっと詳しく機能を紹介すると

というものです。
多分 phpMyAdmin なんかにもそういう機能はありそうですが、如何せん何でもできてしまうところが最大のネックです。
僕はおっかなくて業務内で利用したことはありません。
その点を、このツールでは接続先を、集計専用に用意したスレーブに限定することで、うっかり UPDATE とかでデータが壊されないようになっています。

出力フォーマットは必要に応じてたまに増やしてます。
morris.js で時系列グラフを出力するやつがお気に入りです。
一番目の列を時刻として、以降の列を全て値というルールで、SELECT クエリひとつ書けばすきなメトリクスの時系列グラフがすぐに得られます。

あとは TSV 出力もお気に入りです。
とりあえず SELECT さえ書けば「あとは EXCEL にでもコピペして適当にこねくり回しておいて」という具合に丸投げできます。

何よりもお気に入りなのはパーマリンク機能です。
URL さえ渡せばみんなでクエリや集計データを共有できるので、メールやチャットでカジュアルにやり取りできます。
データを収集するツールにはパーマリンクが欠かせない、ということ事実に Cacti はいい加減気付いて欲しいところです。

SQL Executor を作って見て

この SQL Executor を作って見てなんですが、日々のストレスのうちごく一部ではありますが、確実に減りました。
SQL 叩いて Excel に貼りつけてメールして、みたいな業務をかなり減らすことができました。

もちろんこれまでも何でもかんでも手動で出していたわけではなく、よく使われるものは何かしらのツールを実装して自動化していました。
ですが、例えば数日間しか必要無いもののような、専用ツール化するほどでもないものについて、実装の手間をかける必要が無くなりました。

また、今時のフルスタックでイケてるエンジニアの皆様は Fluentd で収集したログを MongoDB なり ElasticSearch につっこんで Kibana で眺めてはニヤニヤしていることかと思われますが、そういった素敵なツールに組み込む前の段階のプロトタイピングとしても、なかなかの威力を発揮します。
とりあえず SELECT 文をこねくりまわして非エンジニアの人にも見せつつ、本当に必要なデータがわかってきたところで、上記のような素敵ツールに組み込んでいくと便利だと思います。

Treasure Data はそういった用途にも対応できる機能が揃っていると思いますが、如何せんそんなに安いものではない (個人の感想です) ですし、データ規模によっては Hadoop/Hive に頼るのは大袈裟だったりすると思うので、適材適所使い分けていければ良いんじゃないかと思います。

あとよかったのは、今までそれぞれの手元で眠りがちだった SQL が共有されるようになったことです。
これまでも Wiki に便利 SQL 集みたいな形で共有していたりしましたが、その場で実行できると凄く楽なので、情報共有が加速したんじゃないかと思います。

まぁそんなこんなで「とりあえず何でも SQL 一発でゴチャゴチャと集計しちゃおうぜ!」みたいな文化が醸成され、このイディオムが発見されるに至りました。
とは言え、なんでもかんでもゴチャ混ぜにした集計は神 EXCEL 問題と同様のアレを孕むものなので、使いどころは限定するのが良いんでしょうが。

というわけでようやくイディオムの解説に戻ります。

COUNT() とは何だったのか

まず COUNT(expression) や COUNT(DISTINCT expression) について振りかえる必要があります。
なお、COUNT(DISTINCT expression, [expression...]) という具合に、集計対象の expression は複数指定することもできますが、ここでは単純化のために考えないことにします。

COUNT(expression) はどういう関数だったかというと、「expression が NULL でない行の数を数える」みたいな感じだと思います。
つまり user_id が 1, 2, 2, NULL となっている場合、COUNT(user_id) の結果は 3 となります。
実装を読んだわけじゃないしマニュアルも意外とそこまで詳しく書いてないのですが、手元でいろいろ試した結果そういう感じっぽかったので、そういうものとして話を進めます。

次に COUNT(DISTINCT expression) はというと、expression のユニーク数を数えます。もちろん NULL は除外します。
user_id が 1, 2, 2, NULL となっている場合、COUNT(DISTINCT user_id) の結果は 2 となります。

IF() の結果を COUNT() する

これで「COUNT(expression) は expression が NULL でない行の数を数える」ということがわかりました。
つまり、集計対象から除外したい行は NULL にすれば良いのです。

というわけで、20 台の男性だけを集計したいときはこうです。

IF(DISTINCT u.age BETWEEN 20 AND 29 AND u.sex = 'm', u.id, NULL)

こうすることで 年齢が 20 ~ 29 でかつ性別が m の行以外は NULL として除外されるようになります。
また、u.id による DISTINCT な COUNT なので、該当ユーザのユニーク数が集計されるというわけです。

多分このイディオムを使っている方は少なからずいると思うんですが、如何せん呼び名が無いので、ノウハウとして共有されていない気がしています。名前重要。
COUNT IF EXPRESSION NULL を略して CIEN Pattern (シャンパターン) とか呼ぶと格好が良いのではないでしょうか。
並び変えたら NICE になりますし、CIEN パターンで NICE かつカジュアルな集計!とかどうでしょうか。
だめでしょうか。

まとめ

カジュアルにクエリ叩ける Web ベースのツールあると便利なのでオススメという話でした (イディオムはオマケ)。

明日は @RKajiyama さんです。