既存継続率の割合を雑に出すクエリ

# 前置き

前に新規継続率を知りたかったので[クエリ](http://qiita.com/mattak/items/dd4d0d240355ed19addd)を書いた。
ユーザの定着比率も知りたかったので調べる.
今回もSQLのパフォーマンスとかは特に考えない。

# 既存継続率

新規を除くその日にログインした人が母数

- http://analytics.hatenadiary.com/entry/20131118/p1

# テーブル定義

ログインしたユーザと、ログイン日が記録されているテーブルを想定する。

```sql
CREATE TABLE user_login_log(
  `user_id` int(10) unsigned NOT NULL,
  `login_on` date NOT NULL,
  PRIMARY KEY (`user_id`, `login_on`)
);
```

# データ

- 04/01 ~ 04/06まで毎日ログインする人
- 04/02 ~ 04/07まで毎日ログインする人

```sql
INSERT INTO user_login_log (user_id, login_on) VALUE (1, '2017-04-01');
INSERT INTO user_login_log (user_id, login_on) VALUE (1, '2017-04-02');
INSERT INTO user_login_log (user_id, login_on) VALUE (1, '2017-04-03');
INSERT INTO user_login_log (user_id, login_on) VALUE (1, '2017-04-04');
INSERT INTO user_login_log (user_id, login_on) VALUE (1, '2017-04-05');
INSERT INTO user_login_log (user_id, login_on) VALUE (1, '2017-04-06');

INSERT INTO user_login_log (user_id, login_on) VALUE (2, '2017-04-02');
INSERT INTO user_login_log (user_id, login_on) VALUE (2, '2017-04-03');
INSERT INTO user_login_log (user_id, login_on) VALUE (2, '2017-04-04');
INSERT INTO user_login_log (user_id, login_on) VALUE (2, '2017-04-05');
INSERT INTO user_login_log (user_id, login_on) VALUE (2, '2017-04-06');
INSERT INTO user_login_log (user_id, login_on) VALUE (2, '2017-04-07');
```

# クエリ

## step1. 最長ログイン日を算出

```sql
SELECT
  log1.user_id,
  log1.login_on AS login_on,
  MAX(DATEDIFF(log1.login_on,log2.login_on)) AS max_duration
FROM user_login_log AS log1
INNER JOIN user_login_log AS log2
ON log2.login_on < log1.login_on AND
   log1.user_id = log2.user_id
GROUP BY log1.user_id, log1.login_on
;
```

結果

```
+---------+------------+--------------+
| user_id | login_on   | max_duration |
+---------+------------+--------------+
|       1 | 2017-04-02 |            1 |
|       1 | 2017-04-03 |            2 |
|       1 | 2017-04-04 |            3 |
|       1 | 2017-04-05 |            4 |
|       1 | 2017-04-06 |            5 |
|       2 | 2017-04-03 |            1 |
|       2 | 2017-04-04 |            2 |
|       2 | 2017-04-05 |            3 |
|       2 | 2017-04-06 |            4 |
|       2 | 2017-04-07 |            5 |
+---------+------------+--------------+
10 rows in set (0.00 sec)
```

## step2. 各日のログイン数を出す

```sql
SELECT 
  log.login_on,
  COUNT(log.max_duration = 1 or NULL) AS day1,
  COUNT(log.max_duration = 2 or NULL) AS day2,
  COUNT(log.max_duration = 3 or NULL) AS day3,
  COUNT(log.max_duration = 4 or NULL) AS day4,
  COUNT(log.max_duration = 5 or NULL) AS day5
  FROM (
    SELECT
      log1.user_id,
      log1.login_on AS login_on,
      MAX(DATEDIFF(log1.login_on,log2.login_on)) AS max_duration
    FROM user_login_log AS log1
    INNER JOIN user_login_log AS log2
    ON log2.login_on < log1.login_on AND
       log1.user_id = log2.user_id
    GROUP BY log1.user_id, log1.login_on
  ) AS log
  GROUP BY login_on
;
```

結果

```sql
+------------+------+------+------+------+------+
| login_on   | day1 | day2 | day3 | day4 | day5 |
+------------+------+------+------+------+------+
| 2017-04-02 |    1 |    0 |    0 |    0 |    0 |
| 2017-04-03 |    1 |    1 |    0 |    0 |    0 |
| 2017-04-04 |    0 |    1 |    1 |    0 |    0 |
| 2017-04-05 |    0 |    0 |    1 |    1 |    0 |
| 2017-04-06 |    0 |    0 |    0 |    1 |    1 |
| 2017-04-07 |    0 |    0 |    0 |    0 |    1 |
+------------+------+------+------+------+------+
6 rows in set (0.00 sec)
```

## step3. その日の既存継続率を出す

```sql
SELECT 
  log.login_on,
  COUNT(log.max_duration = 1 or NULL) / COUNT(*) AS day1,
  COUNT(log.max_duration = 2 or NULL) / COUNT(*) AS day2,
  COUNT(log.max_duration = 3 or NULL) / COUNT(*) AS day3,
  COUNT(log.max_duration = 4 or NULL) / COUNT(*) AS day4,
  COUNT(log.max_duration = 5 or NULL) / COUNT(*) AS day5
  FROM (
    SELECT
      log1.user_id,
      log1.login_on AS login_on,
      MAX(DATEDIFF(log1.login_on,log2.login_on)) AS max_duration
    FROM user_login_log AS log1
    INNER JOIN user_login_log AS log2
    ON log2.login_on < log1.login_on AND
       log1.user_id = log2.user_id
    GROUP BY log1.user_id, log1.login_on
  ) AS log
  GROUP BY login_on
;
```

結果

```sql
+------------+--------+--------+--------+--------+--------+
| login_on   | day1   | day2   | day3   | day4   | day5   |
+------------+--------+--------+--------+--------+--------+
| 2017-04-02 | 1.0000 | 0.0000 | 0.0000 | 0.0000 | 0.0000 |
| 2017-04-03 | 0.5000 | 0.5000 | 0.0000 | 0.0000 | 0.0000 |
| 2017-04-04 | 0.0000 | 0.5000 | 0.5000 | 0.0000 | 0.0000 |
| 2017-04-05 | 0.0000 | 0.0000 | 0.5000 | 0.5000 | 0.0000 |
| 2017-04-06 | 0.0000 | 0.0000 | 0.0000 | 0.5000 | 0.5000 |
| 2017-04-07 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 1.0000 |
+------------+--------+--------+--------+--------+--------+
6 rows in set (0.00 sec)
```

その日にログインした人の最大継続日数の割合がこれで見れる