时间戳

首页 > 解决方案 > MySQL中自定义排序的累积和

mysql - MySQL中自定义排序的累积和

问题描述

我有一张看起来像这样的桌子

id   remaining   expiry_date
1    200         2019-11-15
2     10         2019-11-23
3     10         2019-11-16
4     10         2019-11-16
5      7         2019-11-16

我想获取按顺序排序的运行总数为 215 的expiry_date结果ascending。

到目前为止我能做到什么？

SELECT *, @sum := (@sum + remaining) AS csum 
FROM tickets 
JOIN (SELECT @sum := 0) r 
WHERE @sum < 215 
ORDER BY id;

此查询返回以下正确的结果。

id   remaining   expiry_date   csum
1    200         2019-11-15    200
2     10         2019-11-23    210
3     10         2019-11-16    220

但是当我尝试对其进行排序时，expiry_date它会返回所有记录。

SELECT *, @sum := (@sum + remaining) AS csum 
FROM tickets 
JOIN (SELECT @sum := 0) r 
WHERE @sum < 215 
ORDER BY expiry_date;

结果：

id   remaining   expiry_date   csum
1    200         2019-11-15    200
3    10          2019-11-16    210
4    10          2019-11-16    220
5    7           2019-11-16    227
2    10          2019-11-23    237

排序是正确的，但结果比我需要的要多。

我想要的是

我想返回以下结果。

id   remaining   expiry_date   csum
1    200         2019-11-15    200
3    10          2019-11-16    210
4    10          2019-11-16    220

此外，数字215可以动态更改，因此返回的行数可以根据该数字而变化。如何更改查询以便实现此目的？

编辑

对于不清楚我在结果集中实际想要的内容，我深表歉意。请让我对此编辑进行澄清。我不希望运行总计小于给定数量的记录。我想要记录，直到运行总计等于或超过给定数量。

标签： mysqlsqlcumulative-sum

解决方案

首先，每个日期有多个条目。因此，仅靠日期不足以获得稳定的排序顺序。我建议ORDER BY expiry_date, id直截了当。

然后，将使用任何现代 RDBMS 中的窗口函数进行运行总计。从版本 8 开始，它们在 MySQL 中可用。

select id, remaining, expiry_date, csum
from
(
  select
    id, remaining, expiry_date, 
    sum(remaining) over (order by expiry_date, id) as csum,
    sum(remaining) over (order by expiry_date, id 
                         rows between unbounded preceding and 1 preceding) as lag_csum
  from tickets
) summed
where coalesce(lag_csum, 0) < 215
order by expiry_date, id;

如果窗口函数不可用，您可以改用相关聚合子查询。这可能要慢得多，但应该也能正常工作。

select *
from
(
  select
    id, remaining, expiry_date,
    (
      select sum(remaining)
      from tickets t2
      where t2.expiry_date < t1.expiry_date
        or (t2.expiry_date = t1.expiry_date and t2.id <= t1.id)
    ) as csum,
    (
      select sum(remaining)
      from tickets t2
      where t2.expiry_date < t1.expiry_date
        or (t2.expiry_date = t1.expiry_date and t2.id < t1.id)
    ) as lag_csum
  from tickets t1
) summed
where coalesce(lag_csum, 0) < 215
order by expiry_date, id;

这两个查询都是标准 SQL，因此不限于 MySQL。

推荐阅读