MySQL查询需求：按每5行分组计算指定server的secret_number总和

需求说明 #

现有一张数据库表，id为自增主键，需编写MySQL查询：按id排序，将指定server_id对应的secret_number按每5行一组求和，不足5行的分组直接忽略。支持单查询同时处理多server，或分查询单独处理。

表结构示例 #

+-----+------------+---------------+-------------------------+
| id  | server_id  | secret_number | ts                      |
+-----+------------+---------------+-------------------------+
| 161 | R_SERVER   |             1 | 2023-06-22 15:04:09.549 |
| 162 | X_R_SERVER |            49 | 2023-06-22 15:04:10.570 |
| 163 | R_SERVER   |             2 | 2023-06-22 15:04:11.574 |
| 164 | X_R_SERVER |            48 | 2023-06-22 15:04:12.584 |
| 165 | R_SERVER   |             3 | 2023-06-22 15:04:13.588 |
| 166 | X_R_SERVER |            47 | 2023-06-22 15:04:14.602 |
| 167 | R_SERVER   |             4 | 2023-06-22 15:04:15.610 |
| 168 | X_R_SERVER |            46 | 2023-06-22 15:04:16.616 |
| 169 | R_SERVER   |             5 | 2023-06-22 15:04:17.628 |
| 170 | X_R_SERVER |            45 | 2023-06-22 15:04:18.637 |
| 171 | R_SERVER   |             6 | 2023-06-22 15:04:19.641 |
| 172 | X_R_SERVER |            44 | 2023-06-22 15:04:20.648 |
| 173 | R_SERVER   |             7 | 2023-06-22 15:04:21.658 |
| 174 | X_R_SERVER |            43 | 2023-06-22 15:04:22.669 |
| 175 | R_SERVER   |             8 | 2023-06-22 15:04:23.673 |
| 176 | X_R_SERVER |            42 | 2023-06-22 15:04:24.683 |
| 177 | R_SERVER   |             9 | 2023-06-22 15:04:25.685 |
| 178 | X_R_SERVER |            41 | 2023-06-22 15:04:26.690 |
| 179 | R_SERVER   |            10 | 2023-06-22 15:04:27.696 |
| 180 | X_R_SERVER |            40 | 2023-06-22 15:04:28.704 |
| 181 | R_SERVER   |            11 | 2023-06-22 15:04:29.713 |
| 182 | X_R_SERVER |            39 | 2023-06-22 15:04:30.724 |
| 183 | R_SERVER   |            12 | 2023-06-22 15:04:31.734 |
| 184 | X_R_SERVER |            38 | 2023-06-22 15:04:32.744 |
| 185 | R_SERVER   |            13 | 2023-06-22 15:04:33.754 |
| 186 | X_R_SERVER |            37 | 2023-06-22 15:04:34.762 |
+-----+------------+---------------+-------------------------+

解决方案 #

1. 单查询同时处理所有server #

使用窗口函数为每个server_id的行按id排序编号，再按每5行分组，最后过滤掉不足5行的组：

SELECT
    server_id,
    SUM(secret_number) AS sum,
    CONCAT('(ids: ', GROUP_CONCAT(id ORDER BY id SEPARATOR ', '), ')') AS group_details
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY server_id ORDER BY id) AS rn
    FROM your_table_name  -- 替换为你的表名
) t
GROUP BY server_id, FLOOR((rn - 1) / 5)
HAVING COUNT(*) = 5  -- 只保留刚好5行的分组
ORDER BY server_id, FLOOR((rn - 1) / 5);

2. 分查询单独处理每个server #

如果单查询理解困难，可以分别针对R_SERVER和X_R_SERVER编写查询：

针对R_SERVER：

SELECT
    'R_SERVER' AS server_id,
    SUM(secret_number) AS sum,
    CONCAT('(ids: ', GROUP_CONCAT(id ORDER BY id SEPARATOR ', '), ')') AS group_details
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM your_table_name  -- 替换为你的表名
    WHERE server_id = 'R_SERVER'
) t
GROUP BY FLOOR((rn - 1) / 5)
HAVING COUNT(*) = 5
ORDER BY FLOOR((rn - 1) / 5);

针对X_R_SERVER：

SELECT
    'X_R_SERVER' AS server_id,
    SUM(secret_number) AS sum,
    CONCAT('(ids: ', GROUP_CONCAT(id ORDER BY id SEPARATOR ', '), ')') AS group_details
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM your_table_name  -- 替换为你的表名
    WHERE server_id = 'X_R_SERVER'
) t
GROUP BY FLOOR((rn - 1) / 5)
HAVING COUNT(*) = 5
ORDER BY FLOOR((rn - 1) / 5);

示例输出 #

执行单查询后，输出结果类似：

server_id      sum    group_details
R_SERVER       15     (ids: 161, 163, 165, 167, 169)
R_SERVER       40     (ids: 171, 173, 175, 177, 179)
X_R_SERVER     235    (ids: 162, 164, 166, 168, 170)
X_R_SERVER     210    (ids: 172, 174, 176, 178, 180)

不足5行的分组会被自动过滤，不会出现在结果中。

内容的提问来源于stack exchange，提问作者curious_brain