更新时间:2023-02-10 09:59:33
只要在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;