Closed2023/09/29にクローズ1

mysqlでのdatediffと-(マイナス)演算子の挙動の違い

miroscular 2023/09/26

select
  round(
    count(distinct a1.player_id) / (
      select
        count(distinct player_id)
      from
        Activity
    ),
    2
  ) as fraction
from
  Activity a1
  join (
    select
      player_id,
      min(event_date) as first_login
    from
      Activity
    group by
      player_id
  ) a2 on a1.event_date - a2.first_login = 1
  and a1.player_id = a2.player_id

以下テストで落ちる。

| player_id | device_id | event_date | games_played |
| --------- | --------- | ---------- | ------------ |
| 1         | 2         | 2016-02-29 | 5            |
| 1         | 2         | 2016-03-01 | 6            |
| 2         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |

一見すると、連続ログインしているユーザーはいないように見えるが...
よく見ると2-29と3-01が連続ログインとして判定しなければならないことがわかる。

試しに以下のようなSQLを書くと

select
date('2016-03-01') - date('2016-02-29'),
date('2016-03-02') - date('2016-03-01')

このような結果になる。

| date('2016-03-01') - date('2016-02-29') | date('2016-03-02') - date('2016-03-01') |
| --------------------------------------- | --------------------------------------- |
| 72                                      | 1                                       |

なぜ差分が72になるのかまでは調査できていないが、やはり月をまたいだ差分の計算では、（うるう年等関係なく）datediffを使わないとダメな模様。
単純な引き算でなんとかなってしまっていたテストケースしかなかったので、気づかなかった。

このスクラップは2023/09/29にクローズされました