带排序分页时能否用SQL ROLLUP()替代两次DB请求计算指标总值

使用SingleStore/MemSQL数据库时，原本希望通过ROLLUP()语法单次请求同时完成分页明细查询和指标总值计算，替代两次独立数据库请求。
测试用表结构与初始化数据：

CREATE TABLE sales(state VARCHAR(30), product_id INT, quantity INT);
INSERT sales VALUES
  ("Oregon", 1, 10), ("Washington", 1, 15), ("California", 1, 40),
  ("Oregon", 2, 15), ("Washington", 2, 25), ("California", 2, 70);

初始测试查询：

SELECT state, product_id,
       SUM(quantity) as quantity
FROM sales
WHERE product_id = 1
GROUP BY ROLLUP(state, product_id)
HAVING (GROUPING(state) = 0 and GROUPING(product_id) = 0) OR (GROUPING(state) = 1 and GROUPING(product_id) = 1)
ORDER BY state, product_id
limit 2 offset 0;

测试中发现，当分页参数调整为limit 2 offset 2;时，ROLLUP生成的总计行会被分页规则排除，无法在第二页及后续分页结果中拿到总计值。在使用动态SQL构建器、存在自定义排序规则的场景下，需要确认该需求是否可以仅通过SQL实现。

该需求可以完全通过SQL实现，核心问题是原有写法将明细行和ROLLUP生成的总计行放在同一结果集参与排序分页，总计行位置固定，分页偏移后自然会被排除。可以通过CTE+窗口函数的方式拆分逻辑，避免总计行受分页规则影响：

• 先基于筛选条件查询全量匹配数据，通过窗口函数一次性计算全量总计值，同时按业务排序规则生成行号用于分页
• 单独对明细数据做分页裁剪
• 独立生成固定的总计行，与分页后的明细结果拼接，保证任意分页下都能返回正确的总计值

对应示例SQL：

WITH filtered_base AS (
    SELECT
        state,
        product_id,
        SUM(quantity) AS quantity,
        -- 窗口函数直接计算符合筛选条件的全量总计，不受分页影响
        SUM(SUM(quantity)) OVER () AS total_quantity
    FROM sales
    WHERE product_id = 1
    GROUP BY state, product_id
),
sorted_detail AS (
    SELECT
        state,
        product_id,
        quantity,
        total_quantity,
        -- 按业务要求的规则排序，生成分页用的行号
        ROW_NUMBER() OVER (ORDER BY state, product_id) AS row_seq
    FROM filtered_base
),
paged_data AS (
    SELECT state, product_id, quantity
    FROM sorted_detail
    -- 分页参数按需替换，示例为第二页 limit 2 offset 2
    LIMIT 2 OFFSET 2
),
summary_row AS (
    SELECT
        NULL AS state,
        NULL AS product_id,
        MAX(total_quantity) AS quantity
    FROM sorted_detail
    LIMIT 1
)
-- 拼接分页明细与总计行返回
SELECT * FROM paged_data
UNION ALL
SELECT * FROM summary_row;

方案说明 #

• 适配动态SQL场景，不管排序规则、分页参数怎么调整，总计值始终基于全量符合筛选条件的数据集计算，不会随分页偏移变化
• 性能与单次ROLLUP查询基本持平，窗口函数计算全量总计的开销远低于两次独立查询的网络与重复计算开销
• 如果不需要把总计行作为单独行返回，也可以直接将total_quantity作为公共字段附加到每一条分页明细中，前端取任意行的该字段即可拿到全量总计值，无需UNION拼接逻辑

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