MySQLで延べ・実質数(重複除外)の集計

ユーザの来場ログから、日付別の延べ・実質数(重複の来場を除外した値)を出す必要があり、その実装でやや苦労したのでメモを残しておきます。

サンプルテーブル `checkin`

+----+---------+---------------------+
| id | user_id | checked_on          |
+----+---------+---------------------+
|  1 |       1 | 2017-04-01 16:01:41 |
|  2 |       2 | 2017-04-01 16:05:54 |
|  3 |       3 | 2017-04-01 18:12:14 |
|  4 |       1 | 2017-04-02 10:45:34 |
|  5 |       4 | 2017-04-02 15:45:37 |
|  6 |       3 | 2017-04-02 18:21:12 |
|  7 |       5 | 2017-04-02 19:02:05 |
+----+---------+---------------------+

user_idが来場者のIDで、checked_onが来場時間だとする。

 

日付別延べ

コレは簡単。重複なしでカウントするので普通に・・・

SELECT
 COUNT(*),
 DATE_FORMAT(`checked_on`, '%Y/%m/%d') AS `date`
FROM `checkin`
GROUP BY `date`
ORDER BY `date` ASC
+----------+------------+
| COUNT(*) | date       |
+----------+------------+
|        3 | 2017/04/01 |
|        4 | 2017/04/02 |
+----------+------------+

 

日付別実質数(重複の来場を除外した値)

DISTINCTも考えたが、私のスキル不足ゆえかうまくカウントできない。。。
そこで考えをちょっとシフトさせて、
”重複を除くという事は、同じuser_idで1回目の来場だけをカウントすればいいじゃない”
という考えに至る。
で、その時にかいたSQLがこんな感じ。

SELECT
 COUNT(*),
 DATE_FORMAT(`checked_on`, '%Y/%m/%d') AS `date` 
FROM `checkin`
WHERE
 (
  SELECT count(*)
   FROM `checkin` AS `T1`
   WHERE
    `T1`.`user_id` = `checkin`.`user_id`
    AND `T1`.`checked_on`<=`checkin`.`checked_on`
 )=1 
GROUP BY `date` 
ORDER BY `date` ASC
+----------+------------+
| COUNT(*) | date       |
+----------+------------+
|        3 | 2017/04/01 |
|        2 | 2017/04/02 |
+----------+------------+

サブクエリで何回目の来場かを計算し、1回目なら(=1なら)カウントする。
サブクエリで書いている部分をViewで実装してあげるとシンプルになる。

CREATE VIEW `checkin_view` AS 
 SELECT
  *,
  DATE_FORMAT(`checked_on`, '%Y/%m/%d') AS `date`,
  (
   SELECT count(*)
    FROM `checkin` AS `T1`
    WHERE
    `T1`.`user_id` = `checkin`.`user_id`
    AND `T1`.`checked_on`<=`checkin`.`checked_on`
  ) AS num_count
 FROM `checkin`

Viewの中身は…

+----+---------+---------------------+------------+-----------+
| id | user_id | checked_on          | date       | num_count |
+----+---------+---------------------+------------+-----------+
|  1 |       1 | 2017-04-01 16:01:41 | 2017/04/01 |         1 |
|  2 |       2 | 2017-04-01 16:05:54 | 2017/04/01 |         1 |
|  3 |       3 | 2017-04-01 18:12:14 | 2017/04/01 |         1 |
|  4 |       1 | 2017-04-02 10:45:34 | 2017/04/02 |         2 |
|  5 |       4 | 2017-04-02 15:45:37 | 2017/04/02 |         1 |
|  6 |       3 | 2017-04-02 18:21:12 | 2017/04/02 |         2 |
|  7 |       5 | 2017-04-02 19:02:05 | 2017/04/02 |         1 |
+----+---------+---------------------+------------+-----------+

なので、

SELECT COUNT(*), `date` FROM `checkin_view` WHERE `num_count`= 1 GROUP BY `date` ORDER BY `date` ASC

で同じ結果が得られる。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です