Início / coding / Perguntas / 76930264
Accepted
ALS
Asked: 2023-08-18 22:32:01 +0800 CST

MySQL: Obter contagem de registros consecutivos (data)?

  • 772

Necessidade de obter a quantidade de dias que um usuário concluiu um hábito seguido (streak). Isso significa a sequência mais recente (hoje ao contrário) e não necessariamente a sequência mais longa.

Com as seguintes datas inseridas, estou esperando 4

  • 14/09/2023
  • 15/09/2023
  • 16/09/2023
  • 17/09/2023

Esta consulta está retornando 1 consistentemente, onde estou errando?

SELECT s.habit, MAX(s.streak) AS currentStreak
  FROM ( SELECT IF(@prev = o.habit_id AND o.date = @prevDate + INTERVAL 1 DAY
                  , @streak := @streak + 1
                  , @streak := 1
                ) AS streak
              , @prev := o.habit_id AS habit
           FROM ( SELECT t.habit_id
                       , DATE(t.created_at) AS `date`
                    FROM habit_progress_logs t
                   CROSS
                    JOIN (SELECT @prev := NULL, @prevDate := NULL, @streak := 1) i
                   GROUP BY t.habit_id, DATE(t.created_at)
                   ORDER BY t.habit_id, DATE(t.created_at)
                ) o
       ) s
  WHERE s.habit = 4
 GROUP BY s.habit

SQLFiddle com esquema e dados

mysql

1 respostas

  1. Best Answer

    Você esqueceu de definir @prevDatepara cada linha e está retornando a seqüência máxima em vez da seqüência mais recente.

    Se você estiver usando MySQL >= 8.0, poderá usar funções de janela , em vez da configuração obsoleta de variáveis ​​de usuário em expressões :

    WITH streaks AS (
    SELECT
        habit_id,
        DATE(created_at) AS dt,
        DATE(created_at) - INTERVAL DENSE_RANK() OVER (PARTITION BY habit_id ORDER BY DATE(created_at) ASC) DAY AS grp
    FROM habit_progress_logs
    WHERE habit_id = 4
),
streaks2 AS (
    SELECT
        habit_id,
        grp,
        COUNT(DISTINCT dt) AS length,
        ROW_NUMBER() OVER (PARTITION BY habit_id ORDER BY grp DESC) AS rn
    FROM streaks
    GROUP BY habit_id, grp
)
SELECT habit_id, length AS currentStreak
FROM streaks2
WHERE rn = 1;

    O primeiro CTE nos dá o agrupamento da sequência de datas, usando DENSE_RANK() ( rnkadicionado para ilustração):

    habit_id dt rnk grp
    4 14/07/2023 1 13/07/2023
    4 15/07/2023 2 13/07/2023
    4 16/07/2023 3 13/07/2023
    4 17/07/2023 4 13/07/2023
    4 19/07/2023 5 14/07/2023
    4 2023-08-20 6 14/08/2023
    4 2023-08-21 7 14/08/2023
    4 2023-08-23 8 15/08/2023
    4 2023-08-24 9 15/08/2023
    4 2023-08-25 10 15/08/2023
    4 2023-08-26 11 15/08/2023
    4 2023-08-27 12 15/08/2023
    4 2023-08-28 13 15/08/2023
    4 13/09/2023 14 30/08/2023
    4 14/09/2023 15 30/08/2023
    4 15/09/2023 16 30/08/2023
    4 15/09/2023 16 30/08/2023
    4 16/09/2023 17 30/08/2023
    4 17/09/2023 18 30/08/2023

    A segunda CTE faz o GROUP BY e adiciona o ROW_NUMBER() :

    habit_id grp comprimento rn
    4 30/08/2023 5 1
    4 15/08/2023 6 2
    4 14/08/2023 2 3
    4 14/07/2023 1 4
    4 13/07/2023 4 5

    Se você está preso no MySQL < 8.0:

    SELECT habit_id, COUNT(*) AS currentStreak
FROM (
  SELECT
    habit_id,
    dt,
    @grp := IF(@prevHabitId = habit_id AND dt = @prevDate + INTERVAL 1 DAY, @grp, @grp + 1) AS grp,
    @prevDate := dt,
    @prevHabitId := habit_id
  FROM (
    SELECT habit_id, DATE(created_at) AS dt
    FROM habit_progress_logs
    JOIN (SELECT @prevHabitId := NULL, @prevDate := NULL, @grp := 0) i
    WHERE habit_id = 4
    GROUP BY habit_id, dt
    ORDER BY habit_id, dt
  ) s
) t1
GROUP BY habit_id, grp
ORDER BY habit_id, grp DESC
LIMIT 1;

    Aqui está um db<>fiddle

    • 1

relate perguntas