How to find condition when sum is specific value by MySQL-CodePudding

I'd like to know some condition from this table.

date value
2022-01-01 5
2022-01-02 1
2022-01-03 3
2022-01-04 0
2022-01-05 2
2022-01-06 2

When is the date if sum of values exceed 10? Actually, the answer is '2022-01-05'. Because sum from '2022-01-01' to '2022-01-05' is 11. It's easy for us as a human. But how do I express in MySQL? Please let me know.

CodePudding user response：

If you are using MySQL 8 then window functions makes your requirement easy:

WITH cte AS (
    SELECT *, SUM(value) OVER (ORDER BY date) sum_value
    FROM yourTable
)

SELECT date
FROM cte
WHERE sum_value > 10
ORDER BY date
LIMIT 1;

On earlier versions of MySQL we can express the rolling sum with a correlated subquery:

SELECT date
FROM yourTable t1
WHERE (SELECT SUM(t2.value)
       FROM yourTable t2
       WHERE t2.date <= t1.date) >= 10
ORDER BY date
LIMIT 1;

CodePudding user response：

Another approach for MySQL < 8, using a user variable to store the rolling sum -

SELECT `date`
FROM (
    SELECT t.*, @sum_value := @sum_value   `value` AS `sum_value`
    FROM t, (SELECT @sum_value := 0) z
    ORDER BY `date` ASC
) y
WHERE `sum_value` >= 10
ORDER BY `date` ASC
LIMIT 1;