检查满足特定条件的最大连续天数 [英] Checking for maximum length of consecutive days which satisfy specific condition

问题描述

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

I have a MySQL table with the structure:

beverages_log(ID，users_id，brinkles_id，时间戳)

beverages_log(id, users_id, beverages_id, timestamp)

我正在尝试计算用户(标识为1)每天至少记录5次饮料(标识为1)的连续几天的最大条纹.我很确定可以使用以下视图来完成此操作:

I'm trying to compute the maximum streak of consecutive days during which a user (with id 1) logs a beverage (with id 1) at least 5 times each day. I'm pretty sure that this can be done using views as follows:

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中是否有一种方法可以在单个查询中执行此计算，而无需创建视图或多次重复调用同一子查询?

However, repeatedly creating views for different users every time I run this check seems pretty inefficient. Is there a way in MySQL to perform this computation in a single query, without creating views or repeatedly calling the same subqueries a bunch of times?

推荐答案

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

This solution seems to perform quite well as long as there is a composite index on users_id and beverages_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;

这篇关于检查满足特定条件的最大连续天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！