Database JUNKY

MySQL,MariaDBを中心としたブログです

スゴ技! MySQLでレコードの異なる日時の差(引き算)を求めるSQL

やってみたら、かなり私には、高度すぎて死にかけました。

やりたいことは、

MySQLで同列の異なる行と行の引き算をSQLで書くとこうなるってことです。

やってみたいことを文書で表すと、ユーザーのログインは前回のアクセスからどれくらいの感覚でログインしているのかを算出する場合、SQLでそれをどのように表現するかって感じになるのかなというのを試して見ました

f:id:hit10231023:20180309104332j:plain

サンプルデータについて

例えば以下のようなテーブルがあり、これを題材にやってみたいと思います

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関数なるものが登場して、この手の集計がもっと楽になります!!!

hit.hateblo.jp