ちょっとニッチなMySQLの使い方

MySQL
おはこんにちばん、チーフのMです。

最近技術的な記事が足りないということで、
今日はSQLについて書きたいと思います。

Adwaysでは主にMySQLを使っています。
MySQLにはrow id (行番号)がありません。まぁ、ほとんどのケースでは使わないと思いますが、

これがないと、ごく一部の実装をSQLだけで完結させることができません！

ということで今回は、MySQLでrow idを付ける方法を説明したいと思います。

といっても超簡単。

スキーマはこれです
CREATE TABLE `loginlog` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` varchar(64) NOT NULL,
`addtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
);

set @rowid = 0;
select user_id, addtime, @rowid:=@rowid+1 as rowid
from loginlog order by user_id;
sn0


これだけです！
ユーザ変数を使うことで、Oracleの行番号がMySQLでも実現できます。


今日の記事、これだけじゃつまらないので、さらに深堀をしたいと思います。
このユーザ変数を使うと、もっと面白い実装ができます。


上のスキーマで、
「ユーザのログイン履歴から、直近の３件だけ抽出して欲しい。」
と、ディレクターさんからリクエストされることがあります。

これが意外と面倒なんですよね。
ユーザがログインするタイミングはそれぞれ違うし、単純にaddtimeをsortしてデータを取り出すこともできません。
去年しかログインしていないユーザの場合、直近のデータ３件は去年のaddtimeになるのでsortしても全然前に出てくれないんですよね。

もちろんこのリクエスト、scriptを書いてループを回せば余裕で抽出できると思いますが、ちょっとかっこ悪いんですよね！かっこよさは大事。


で、ここでさっきのrow idのユーザ変数を使うと、かっこよくSQL１行で済ませることができます。

set @rowid = 1, @seed = 0;
select
user_id,if(user_id=@seed,@rowid:=@rowid+1,@rowid:=1) as rowid,if(user_id=@seed,@seed,@seed:=user_id) as tmp,addtime
from loginlog
order by user_id, addtime desc;

こんな結果になります
sn1


各ユーザ毎にログイン順で、row idを付けることができました！

ちょっとやり方説明しましょう。

まず、各行のuser_idを見ます。user_idが変わったら@seedに新しいuser_idの値を保存します。user_idが変わってなければそのまま返します。
if(user_id=@seed,@seed,@seed:=user_id) as tmp

それと、各行で@seedとuser_idが変わっていないかチェックします。最新の@seedとこの行のuser_idが一致していたら@rowidを +1 します。変更されていたら@rowidを１にリセットします。
if(user_id=@seed,@rowid:=@rowid+1,@rowid:=1) as rowid

ここで重要なのは@seedと@rowidの順番です。必ず@rowidを前に書かないと評価順位が変わります。
 
 
そこからは簡単ですね。ちょっと工夫してrow idを1から3までに絞って、はい終わり！

set @rowid = 1, @seed = 0;
select t.* from (
select user_id,if(user_id=@seed,@rowid:=@rowid+1,@rowid:=1) as rowid,if(user_id=@seed,@seed,@seed:=user_id) as tmp,addtime from loginlog order by user_id,addtime desc
) t
where t.rowid <= 3;


こんな結果になります。
sn2



これでcsvにアウトプットして、ディレクターさんに渡せば、定時でも帰れます！

今日のネタここまでです。
以上