MySQL中自定义排序的累积总和

2024-05-19 • 问答

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

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 可以动态更改，因此返回的行数可以根据该数字而变化。如何更改查询以实现此目的？

修改

我很抱歉，不清楚我对结果集中的实际需求。请让我澄清一下此编辑。我不希望总运行记录少于给定数量的记录。我想要记录，直到总和等于或超过给定的数量。

首先，每个日期有多个条目。因此，仅靠日期不足以获得稳定的排序顺序。我建议ORDER BY expiry_date,id来解决这个问题。

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

select id,remaining,expiry_date,csum
from
(
  select
    id,sum(remaining) over (order by expiry_date,id) as csum,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,(
      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,id;

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

尝试使用限制3，并将此结果作为新订单的子查询：

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

或者根据您更新的问题，可能是您只需要按日期排序的最后3个

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

否则，如果您不希望使用限制，但想要过滤csum的结果，则可以尝试将查询用作子查询并过滤所需的值，例如：225

SELECT * 
FROM
    (SELECT t.*,@sum := (@sum + t.remaining) AS csum 
     FROM tickets t
     JOIN (SELECT @sum := 0) r 
     ORDER BY expiry_date ) t1 
WHERE t1.csum < 225

检查

SELECT * 
FROM
    (SELECT t.*,@sum := (@sum + t.remaining) AS csum 
     FROM 
         (SELECT 1 id,200 remaining,'2019-11-15' expiry_date
          UNION ALL
          SELECT 2,10,'2019-11-23'
          UNION ALL
          SELECT 3,'2019-11-16'
          UNION ALL
          SELECT 4,'2019-11-16'
          UNION ALL 
          SELECT 5,7,'2019-11-16') t
     JOIN (SELECT @sum := 0) r 
     ORDER BY expiry_date ) t1 
WHERE t1.csum < 225

MySQL中自定义排序的累积总和

wangziguang111 回答：MySQL中自定义排序的累积总和

大家都在问