mysql - 从表中获取总和小于值的行

Question

假设我在表中有以下行

|  id |   user_id   |  amount |      date      |
------------------------------------------------
|  1  |     1       |  100    |   2019-09-30   |
------------------------------------------------
|  2  |     2       |  100    |   2019-09-30   |
------------------------------------------------
|  3  |     1       |  100    |   2019-09-30   |
------------------------------------------------
|  4  |     3       |  100    |   2019-10-01   |
------------------------------------------------
|  5  |     1       |  75     |   2019-10-01   |
------------------------------------------------
|  6  |     3       |  100    |   2019-10-01   |
------------------------------------------------
|  7  |     1       |  35     |   2019-10-01   |
------------------------------------------------

我正在尝试找到一种方法来获取 user_id = 1 的所有行sum(amount) < 300在哪里和 date <= '2019-10-01' .

我想做的是只处理满足特定阈值总和的记录。我不太确定从哪里开始。

预期结果

|  id |   user_id   |  amount |      date      |
------------------------------------------------
|  1  |     1       |  100    |   2019-09-30   |
------------------------------------------------
|  3  |     1       |  100    |   2019-09-30   |
------------------------------------------------
|  5  |     1       |   75    |   2019-10-01   |
------------------------------------------------

这是我目前的尝试

SELECT id, SUM(amount) as total_sum 
   FROM table 
   WHERE date <= '2019-10-01' AND user_id = 1
     ORDER BY date ASC
   HAVING total_sum <= 300

根据上述查询，我​​没有得到所需的输出。

目前使用的MySQL版本:5.7.25

我确实看过这个问题MySQL select records with sum greater than threshold假设他们正在尝试做同样的事情，但这不是我要看的

Answer 1

这是一个滚动总和问题。在 MySQL 8.0.2 及更高版本中，您可以使用 Window functions with Frames 解决此问题.在旧版本中，我们可以使用用户定义的 session 变量来做同样的事情。

• 我们首先使用 Session variables 计算滚动总和.
• 然后，使用 Derived table 中的结果集, 并找到总和超过 300 的“障碍”的 id。当新滚动总和大于 300 时达到障碍。此时我们将障碍值设置为 1，之前的行为 0，以及 2 和更多, 对于之后的行。
• 我们只会考虑 barrier 为 0 的行。

尝试(适用于所有 MySQL 版本):

查询#1

SELECT dt.id, 
       dt.user_id, 
       dt.amount, 
       dt.date
FROM 
(
  SELECT 
    t.id, 
    t.user_id, 
    t.amount, 
    t.date, 
    @barrier := CASE
                  WHEN 
                       (@tot_qty := @tot_qty + t.amount) > 300
                  THEN (@barrier + 1)
                  ELSE 0
                END AS barrier 
  FROM 
    your_table AS t 
  CROSS JOIN (SELECT @tot_qty := 0, 
                     @barrier := 0) AS user_init
    WHERE t.user_id = 1 
        AND t.date <= '2019-10-01' 
  ORDER BY t.user_id, t.date, t.id
) AS dt 
WHERE dt.barrier = 0 
ORDER BY dt.user_id, dt.date, dt.id;

结果

| id  | user_id | amount | date       |
| --- | ------- | ------ | ---------- |
| 1   | 1       | 100    | 2019-09-30 |
| 3   | 1       | 100    | 2019-09-30 |
| 5   | 1       | 75     | 2019-10-01 |

---

View on DB Fiddle