はい!今やってます!

Work Pertly, Live Idly

「連続〇〇日〇〇しました」みたいなStreakを計算したいときのSQLをMySQLで

Streakを計算したいときに思い出すSQL

SELECT MAX(streak) AS streak
FROM (
   SELECT 
     tmp1.*, 
     IF(
       @prev + INTERVAL 1 DAY = tmp1.date, 
       @streak := @streak + 1, 
       @streak := 1
     ) AS streak, 
     @prev := tmp1.date
   FROM (
       SELECT 
         date
       FROM logs
       WHERE user_id = ?
       group by date
       order by date asc 
   ) AS tmp1
   INNER JOIN (SELECT @prev := NULL, @streak := 1) AS vars
) AS tmp2

MySQL5.7系では動作確認済み。