やってみたら、かなり私には、高度すぎて死にかけました。
やりたいことは、
MySQLで同列の異なる行と行の引き算をSQLで書くとこうなるってことです。
やってみたいことを文書で表すと、ユーザーのログインは前回のアクセスからどれくらいの感覚でログインしているのかを算出する場合、SQLでそれをどのように表現するかって感じになるのかなというのを試して見ました
サンプルデータについて
例えば以下のようなテーブルがあり、これを題材にやってみたいと思います
mysql> desc login ; +---------+------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+------------+------+-----+-------------------+-------+ | userId | bigint(20) | NO | PRI | NULL | | | loginAt | datetime | NO | PRI | CURRENT_TIMESTAMP | | +---------+------------+------+-----+-------------------+-------+
で、以下のようなデータが入っているものとします。ユーザーIDは、1,2,3とあり、それぞれログインの間隔が異なるものとします。 loginAtはログインした日付がはいっており、useridと、loginAtのセットで、primaryキーだとします。 つまり、同じユーザーidで複数、ログイン日付が入っていると想像してください
mysql> SELECT * FROM login ; +--------+---------------------+ | userId | loginAt | +--------+---------------------+ | 1 | 2018-03-08 14:56:33 | | 1 | 2018-03-08 14:56:51 | | 1 | 2018-03-08 14:57:07 | | 1 | 2018-03-08 15:01:12 | | 1 | 2018-03-08 15:05:13 | | 1 | 2018-03-08 15:05:22 | | 1 | 2018-03-08 15:07:17 | | 1 | 2018-03-08 15:16:30 | | 1 | 2018-03-08 15:58:03 | | 2 | 2018-03-08 15:01:33 | | 2 | 2018-03-08 15:01:51 | | 2 | 2018-03-08 15:02:07 | | 2 | 2018-03-08 15:06:12 | | 2 | 2018-03-08 15:10:13 | | 2 | 2018-03-08 15:10:22 | | 2 | 2018-03-08 15:12:17 | | 2 | 2018-03-08 15:21:30 | | 2 | 2018-03-08 16:03:03 | | 3 | 2018-03-08 15:11:33 | | 3 | 2018-03-08 15:11:51 | | 3 | 2018-03-08 15:12:07 | | 3 | 2018-03-08 15:16:12 | | 3 | 2018-03-08 15:20:13 | | 3 | 2018-03-08 15:20:22 | | 3 | 2018-03-08 15:22:17 | | 3 | 2018-03-08 15:31:30 | | 3 | 2018-03-08 16:13:03 | +--------+---------------------+
やりたいこと
上記データの例でたとえばuseridが1の人だけ抽出みますと、下記のような結果になりました。この時間の差をそれぞれで取得したいというのが今回の課題です
mysql> SELECT * FROM login WHERE userid = 1 ; +--------+---------------------+ | userId | loginAt | +--------+---------------------+ | 1 | 2018-03-08 14:56:33 | | 1 | 2018-03-08 14:56:51 | | 1 | 2018-03-08 14:57:07 | | 1 | 2018-03-08 15:01:12 | | 1 | 2018-03-08 15:05:13 | | 1 | 2018-03-08 15:05:22 | | 1 | 2018-03-08 15:07:17 | | 1 | 2018-03-08 15:16:30 | | 1 | 2018-03-08 15:58:03 | +--------+---------------------+
考えなければいけないことを上記一部データから説明しますと、 2018-03-08 14:56:51 - 2018-03-08 14:56:33 = 00:00:18 という値をとりたいのです。ただし、これはレコード内で計算する日時があるわけではないので、単純に引き算をすることはできません、そう、問題は、レコードがとなりあう、loginAt間での引き算をする必要があるのです。
どうするか?
前のレコード、次のレコードがわからない
上記の時間は、まちまちでかつ、ユーザーが1人ではなく複数存在するという課題があります。整理すると。
useridでグループ化し、それぞれに連番をふる必要が発生するのです
SQL
上記を実現するSQLは以下のようになります、なお、あとの説明をわかりやすくする為に、下記のようなSQLでsum_loginというテーブルを作成しました(CREATE ... SELECT) ですね。
SET @no:=0; SET @userId:=null; CREATE TABLE sum_login SELECT *, IF(@userId != userId, @no:=1, @no:=@no+1) AS no, @userId:=userId AS grp_userid FROM login ORDER BY userId,loginAt ;
上記でのポイントをいくつか説明しますと
noと、useridを変数化
SET @no:=0; SET @userId:=null;
上記を変数かして、下に書いてある、ブレーク条件に利用します
前回読み込んだ、useridが異なっていたら、noをブレークする条件文
noは、userid毎に付番したいので、このように、noのリセット条件を記載します
IF(@userId != userId, @no:=1, @no:=@no+1) AS no
です。では早速実行結果を確認してみます。以下のようなデータになりました。noが、useridごとに連番になっているのがわかるかと思います
+--------+---------------------+------+------------+ | userId | loginAt | no | grp_userid | +--------+---------------------+------+------------+ | 1 | 2018-03-08 14:56:33 | 1 | 1 | | 1 | 2018-03-08 14:56:51 | 2 | 1 | | 1 | 2018-03-08 14:57:07 | 3 | 1 | | 1 | 2018-03-08 15:01:12 | 4 | 1 | | 1 | 2018-03-08 15:05:13 | 5 | 1 | | 1 | 2018-03-08 15:05:22 | 6 | 1 | | 1 | 2018-03-08 15:07:17 | 7 | 1 | | 1 | 2018-03-08 15:16:30 | 8 | 1 | | 1 | 2018-03-08 15:58:03 | 9 | 1 | | 2 | 2018-03-08 15:01:33 | 1 | 2 | | 2 | 2018-03-08 15:01:51 | 2 | 2 | | 2 | 2018-03-08 15:02:07 | 3 | 2 | | 2 | 2018-03-08 15:06:12 | 4 | 2 | | 2 | 2018-03-08 15:10:13 | 5 | 2 | | 2 | 2018-03-08 15:10:22 | 6 | 2 | | 2 | 2018-03-08 15:12:17 | 7 | 2 | | 2 | 2018-03-08 15:21:30 | 8 | 2 | | 2 | 2018-03-08 16:03:03 | 9 | 2 | | 3 | 2018-03-08 15:11:33 | 1 | 3 | | 3 | 2018-03-08 15:11:51 | 2 | 3 | | 3 | 2018-03-08 15:12:07 | 3 | 3 | | 3 | 2018-03-08 15:16:12 | 4 | 3 | | 3 | 2018-03-08 15:20:13 | 5 | 3 | | 3 | 2018-03-08 15:20:22 | 6 | 3 | | 3 | 2018-03-08 15:22:17 | 7 | 3 | | 3 | 2018-03-08 15:31:30 | 8 | 3 | | 3 | 2018-03-08 16:13:03 | 9 | 3 | +--------+---------------------+------+------------+
まず、ここで、前のレコード、次のレコードがわからない問題は、noによって解決できるようになりました。
そして、いよいよ、集計へ。。
集計できる準備は整いました、こちらで、レコードをまたがる日時の差を求める準備が整ったかと思います。
SQL
。。とその前に、日時の差を求めるには、TIMEDIFF関数を利用します。
SELECT S1.userId AS s1_userId, S1.no AS s1_no, S1.loginAt AS s1_createdAt, S2.userId AS s2_userId, S2.no AS s2_no, S2.loginAt AS s2_createdAt, TIMEDIFF(S1.loginAt,S2.loginAt) AS diff_time FROM sum_login S1, sum_login S2 WHERE S2.userId = S1.userId AND S2.no = S1.no - 1 ORDER BY S1.userId,S1.no ;
ここでのポイントは
- sum_loginをS1,S2という別名で定義し、それを自己結合でJOINしている
- WHERE句で、S2.no = S1.no - 1 でアクティブなレコードとその前のレコードを指定している
といった部分になります。
SQL実行結果
diff_time の列をみていただくとわかるかと思いますが、アクティブなレコードとその前のレコードの時間の差が出ていることがわかるかと思います。
+-----------+-------+---------------------+-----------+-------+---------------------+-----------+ | s1_userId | s1_no | s1_createdAt | s2_userId | s2_no | s2_createdAt | diff_time | +-----------+-------+---------------------+-----------+-------+---------------------+-----------+ | 1 | 2 | 2018-03-08 14:56:51 | 1 | 1 | 2018-03-08 14:56:33 | 00:00:18 | | 1 | 3 | 2018-03-08 14:57:07 | 1 | 2 | 2018-03-08 14:56:51 | 00:00:16 | | 1 | 4 | 2018-03-08 15:01:12 | 1 | 3 | 2018-03-08 14:57:07 | 00:04:05 | | 1 | 5 | 2018-03-08 15:05:13 | 1 | 4 | 2018-03-08 15:01:12 | 00:04:01 | | 1 | 6 | 2018-03-08 15:05:22 | 1 | 5 | 2018-03-08 15:05:13 | 00:00:09 | | 1 | 7 | 2018-03-08 15:07:17 | 1 | 6 | 2018-03-08 15:05:22 | 00:01:55 | | 1 | 8 | 2018-03-08 15:16:30 | 1 | 7 | 2018-03-08 15:07:17 | 00:09:13 | | 1 | 9 | 2018-03-08 15:58:03 | 1 | 8 | 2018-03-08 15:16:30 | 00:41:33 | | 2 | 2 | 2018-03-08 15:01:51 | 2 | 1 | 2018-03-08 15:01:33 | 00:00:18 | | 2 | 3 | 2018-03-08 15:02:07 | 2 | 2 | 2018-03-08 15:01:51 | 00:00:16 | | 2 | 4 | 2018-03-08 15:06:12 | 2 | 3 | 2018-03-08 15:02:07 | 00:04:05 | | 2 | 5 | 2018-03-08 15:10:13 | 2 | 4 | 2018-03-08 15:06:12 | 00:04:01 | | 2 | 6 | 2018-03-08 15:10:22 | 2 | 5 | 2018-03-08 15:10:13 | 00:00:09 | | 2 | 7 | 2018-03-08 15:12:17 | 2 | 6 | 2018-03-08 15:10:22 | 00:01:55 | | 2 | 8 | 2018-03-08 15:21:30 | 2 | 7 | 2018-03-08 15:12:17 | 00:09:13 | | 2 | 9 | 2018-03-08 16:03:03 | 2 | 8 | 2018-03-08 15:21:30 | 00:41:33 | | 3 | 2 | 2018-03-08 15:11:51 | 3 | 1 | 2018-03-08 15:11:33 | 00:00:18 | | 3 | 3 | 2018-03-08 15:12:07 | 3 | 2 | 2018-03-08 15:11:51 | 00:00:16 | | 3 | 4 | 2018-03-08 15:16:12 | 3 | 3 | 2018-03-08 15:12:07 | 00:04:05 | | 3 | 5 | 2018-03-08 15:20:13 | 3 | 4 | 2018-03-08 15:16:12 | 00:04:01 | | 3 | 6 | 2018-03-08 15:20:22 | 3 | 5 | 2018-03-08 15:20:13 | 00:00:09 | | 3 | 7 | 2018-03-08 15:22:17 | 3 | 6 | 2018-03-08 15:20:22 | 00:01:55 | | 3 | 8 | 2018-03-08 15:31:30 | 3 | 7 | 2018-03-08 15:22:17 | 00:09:13 | | 3 | 9 | 2018-03-08 16:13:03 | 3 | 8 | 2018-03-08 15:31:30 | 00:41:33 | +-----------+-------+---------------------+-----------+-------+---------------------+-----------+
いかがだったでしょうか?結構わかりやすく書いたつもりですが、あまり説明がうまくない部分は許してください汗
今回は、となりあうレコードの日時の差を求めましたが、同じ要領で。
前日と比較した、アクセス件数でもいいですし、先月と比較した、売り上げの差を出すでもいいですし、利用する関数は別になりますが、基本的には同じになります。
また、MySQL8Xになると、Window関数なるものが登場して、この手の集計がもっと楽になります!!!