检查满足特定条件的最大连续天数

小编典典

检查满足特定条件的最大连续天数

sql

我有一个具有以下结构的MySQL表：

drinks_log（id，users_id，brinkles_id，timestamp）

我正在尝试计算用户（ID为1）每天至少记录5次饮料（ID为1）的连续几天的最大连胜纪录。我很确定可以使用以下视图来完成此操作：

CREATE or REPLACE VIEW daycounts AS
SELECT count(*) AS n, DATE(timestamp) AS d FROM beverages_log
WHERE users_id = '1' AND beverages_id = 1 GROUP BY d;

CREATE or REPLACE VIEW t AS SELECT * FROM daycounts WHERE n >= 5;

SELECT MAX(streak) AS current FROM ( SELECT DATEDIFF(MIN(c.d), a.d)+1 AS streak
FROM t AS a LEFT JOIN t AS b ON a.d = ADDDATE(b.d,1)
LEFT JOIN t AS c ON a.d <= c.d
LEFT JOIN t AS d ON c.d = ADDDATE(d.d,-1)
WHERE b.d IS NULL AND c.d IS NOT NULL AND d.d IS NULL GROUP BY a.d) allstreaks;

但是，每次运行此检查时都为不同的用户重复创建视图似乎效率很低。MySQL中是否有一种方法可以在单个查询中执行此计算，而无需创建视图或多次重复调用同一子查询？

阅读 205

2021-03-23

共1个答案

小编典典

只要在users_id和brinkles_id上有一个复合索引，此解决方案的效果似乎就很好-

SELECT *
FROM (
    SELECT t.*, IF(@prev + INTERVAL 1 DAY = t.d, @c := @c + 1, @c := 1) AS streak, @prev := t.d
    FROM (
        SELECT DATE(timestamp) AS d, COUNT(*) AS n
        FROM beverages_log
        WHERE users_id = 1
        AND beverages_id = 1
        GROUP BY DATE(timestamp)
        HAVING COUNT(*) >= 5
    ) AS t
    INNER JOIN (SELECT @prev := NULL, @c := 1) AS vars
) AS t
ORDER BY streak DESC LIMIT 1;

2021-03-23