如何实现“选择直到”达到总和的功能

Question

我想要找到一种方法直到达到一个总数为止。

我的“documents”表有“tag_id”和“size”字段。

我希望选择所有具有“tag_id = 26”的文档，但我只能处理600个单位的大小。 因此，在选择100个文档并丢弃其中90个时，我知道没有意义，因为我本可以知道前10个已经添加了> 600个单位。

因此，目标是：在我将其大部分丢弃时，不返回大量要解析的数据。

...但我也非常希望避免将使用游标引入到此应用中。

我正在使用MySQL。

- jhalb

---

MySQL有分析函数吗？ - Mark Brady

由于有许多文档的组合会超过600，你怎么知道你想要使用哪些文档？或者这无关紧要吗？ - Kev

首选给予最近添加的文档（我们将按照 date_created 字段进行降序排序）。 - jhalb

这听起来像是你的业务逻辑/组件/与数据库交互的代码的工作，而不是查询本身的工作。 - matt b

Matt... 通过分析，可以在查询中完成。如果可以在数据库中完成，速度会更快。 - Mark Brady

筛选要返回的记录正是应该在数据库中完成的工作，因为数据库对此任务进行了高度优化。 - Joel Coehoorn

Answer 1

您需要一种方法来确定哪些记录优先级更高，以便在达到最大单位时进行累加。否则，您如何知道保留哪组总计为600的记录？

SELECT d.id, d.size, d.date_created
FROM documents d
INNER JOIN documents d2 ON d2.tag_id=d.tag_id AND d2.date_created >= d.date_created
WHERE d.tag_id=26
GROUP BY d.id, d.size, d.date_created
HAVING sum(d2.size) <= 600
ORDER BY d.date_created DESC

这只是一个基本的查询，帮助您入门，但仍有许多问题需要解决：

• 它在<= 600处停止，所以在大多数情况下，您不会完全填满大小限制。这意味着您可能需要调整它以允许一个以上的记录。例如，如果第一条记录> 600，则查询将返回空值，这可能是一个问题。
• 它不会检查后来可能仍适合上限的其他较小记录。
• 具有相同date_created值的记录可能会在某些地方“重复计算”。

- Joel Coehoorn

Answer 2

这种方法效率较低，但可以避免使用游标（假设您的文档表也有一个序列 ID 列）：

select a.id, (select sum(b.size) from documents b where b.id <= a.id and b.tag_id = 26)
from documents a
where a.tag_id = 26
order by a.id

此外，这是在pgsql中完成的，所以我不确定这个确切的语法是否适用于mysql。

然后，您可以将其包装在另一个查询中，查找那些具有总和> 600（您必须命名总和列）并取第一个id。 然后处理所有低于该id的id。

- Kev

---

如果没有ID，则使用创建时间戳。 - Kev

Answer 3

首先，您需要将文档存储在一个表变量中，并按照您想要检索的顺序对它们进行排序，然后更新每一行的累积值，以便您可以选择它。

 declare @documents_temp table (
    tag_id int,
    size int,
    cumulative_size int null)

insert into @documents_temp
select tag_id, size, size from documents order by tag_id

update @documents_temp d set d.cumulative_size = d.size + 
    (select top 1 cumulative_size from @documents_temp 
     where tag_id < d.tag_id order by tag_id desc)

select tag_id, size from @documents_temp where cumulative_size <= 600

不知道它是否值得。

- Patrick Szalapski