带有 ROLLUP 和 LIMIT 的 MYSQL 聚合

Question

我需要获取每页的前 10 行，返回按 roomId 分组的值的总和。并有一个页脚来显示超出限制的聚合值。

SELECT  roomId, SUM(value) FROM table A
GROUP BY roomId
WITH ROLLUP
LIMIT 0,10

问题是我需要汇总生成的摘要以返回所有房间的总和值，即使是那些超出限制(页面)的房间，(聚合不应使用限制)

示例数据

+--------+------------+ 
| roomId | Sum(value) |
+========+============+
| 1      | 10         |
+--------+------------+
| 1      | 10         |
+--------+------------+
| 1      | 20         |
+--------+------------+
| 1      | 5          |
+--------+------------+
| 2      | 10         |
+--------+------------+
| 2      | 20         |
+--------+------------+
| 2      | 30         |
+--------+------------+
| 3      | 10         |
+--------+------------+
| 3      | 20         |
+--------+------------+
| NULL   | 135        |
+--------+------------+

但是我希望汇总所有房间的值，而不仅仅是显示的前 10 行，聚合应该对所有数据库行求和，即使用户只是阅读前 10 页。

因此汇总应该如下所示:

+--------+------------+
| roomId | Sum(value) |
+========+============+
| ...    | ...        |
+--------+------------+
| NULL   | 58935      |
+--------+------------+

我知道我可以执行 2 个不同的查询，或者使用 UNION ALL，但这是真的吗？用rollup做不了？汇总会比使用 2 个不同的查询更快。

Answer 1

难点在于LIMIT是在GROUP BY ... WITH ROLLUP之后应用的，所以NULL的行只是要限制的行中的一行.

一种解决方案是对行进行排序，使汇总行排在第一位。然后您可以使用 LIMIT 并获得汇总行和 N-1 行。

mysql> SELECT roomId, SUM(value) FROM A GROUP BY roomId WITH ROLLUP 
       ORDER BY roomId LIMIT 3;
+--------+------------+
| roomId | SUM(value) |
+--------+------------+
|   NULL |        135 |
|      1 |         45 |
|      2 |         60 |
+--------+------------+
 

但是这些行可能不是你想要显示的顺序。您必须使用应用程序代码获取结果并操纵显示顺序。

此外，根据文档中的说明，您必须使用 MySQL 8.0.12 或更高版本才能在带有 ROLLUP 的查询中使用 ORDER BY:https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html

如果不使用 MySQL 8.0.12，则可以使用子查询来完成:

mysql> SELECT * FROM (SELECT roomId, SUM(value) FROM A GROUP BY roomId WITH ROLLUP) result ORDER BY roomId LIMIT 3;
+--------+------------+
| roomId | SUM(value) |
+--------+------------+
|   NULL |        300 |
|      1 |         25 |
|      2 |         25 |
+--------+------------+

---

回复你的评论:

LIMIT 在聚合之后应用，包括在 ROLLUP 计算之后。所以 ROLLUP 计算的总和包括所有的结果，而不仅仅是那些包含在有限行集合中的结果。这就是您想要的，对吧？

这是一个演示:

insert into A values (1, 10), (1, 15), (2, 10), (2, 15), (3, 10), (3, 15), (4, 10), (4, 15), (5, 10), (5, 15), (6, 10), (6, 15), (7, 10), (7, 15), (8, 10), (8, 15), (9, 10), (9, 15), (10, 10), (10, 15), (11, 10), (11, 15), (12, 10), (12, 15);

现在每个 roomId 的总和应该是 25，对于 12 个房间，总和应该是 12 * 25 或 300。

mysql> SELECT roomId, SUM(value) FROM A GROUP BY roomId WITH ROLLUP 
    ->        ORDER BY roomId LIMIT 3;
+--------+------------+
| roomId | SUM(value) |
+--------+------------+
|   NULL |        300 |
|      1 |         25 |
|      2 |         25 |
+--------+------------+

如果 ROLLUP 只是那些在 LIMIT 范围内的总和，则汇总总和应该是 50，而不是 300。上面的示例显示汇总包括所有这些，因为汇总是在 LIMIT 之前完成的。

如果您确实希望汇总仅包含限制中的那些房间，则必须在子查询中执行 LIMIT，然后在外部查询中使用 ROLLUP 重新进行聚合，以便它仅适用于返回的那些子查询。

mysql> SELECT roomId, SUM(value) FROM (
    -> SELECT roomId, SUM(value) AS value FROM A
    ->  GROUP BY roomId ORDER BY roomId LIMIT 3) AS t 
    -> GROUP BY roomId WITH ROLLUP;
+--------+------------+
| roomId | SUM(value) |
+--------+------------+
|      1 |         50 |
|      2 |         50 |
|      3 |         50 |
|   NULL |        150 |
+--------+------------+