PostgreSQL非聚合场景下，用ROLLUP实现结果末尾列求和的方法咨询

问题说明 #

现有一条PostgreSQL查询语句，返回明细数据，包含多列，其中6个为数值型字段：valor_ordem_venda、comissao、valor_venda、valor_nota、despesa_pedido、receita_pedido、"over"（注意最后一个字段是关键字，用引号包裹）。需求是在结果集最后一行展示这6个数值字段的求和值，非数值列显示null。

目前已通过UNION实现，但希望找到更高效的方案，曾尝试ROLLUP和CUBE未成功。

预期效果示例：

col1|col2|col3|col4|col5|col6|col7|col8|col9
xxxx|xxxx|xxxx|xxxx|xxxx|xxxx|   1|   5|   9
yyyy|yyyy|yyyy|yyyy|yyyy|yyyy|  10|   8|   0
zzzz|zzzz|zzzz|zzzz|zzzz|zzzz|   3|  50|   1
null|null|null|null|null|null|  14|  63|  10

最优解决方案：使用GROUPING SETS #

GROUPING SETS可以在同一查询中同时返回明细行和聚合行，比UNION更高效，因为只需执行一次基础查询。

修改后的完整SQL #

WITH detalhes AS (
    select
       p.numero pedido,
       v.nome nome_vendedor,
       cpr.numero ordem_venda,
       case ppi.tipo_produto_item
          when 'TPI10' then '10'
          when 'TPI20' then '20'
          when 'TPI30' then '30'
          else '??'
       end item,
       ppi.descricao,
       p2.nome nome_cliente,
       upper(c.nome) cidade,
       e.uf,
       round(pov.valor * ppi.percentual / 100, 2) valor_ordem_venda,
       pov.percentual_comissao,
       round(pov.valor * ppi.percentual * pov.percentual_comissao / 10000, 2) comissao,
       round(((pov.valor * ppi.percentual / 100) / p.valor_total_nota) * p.valor_total, 2) valor_venda,
       round(((pov.valor * ppi.percentual / 100) / p.valor_total_nota) * p.valor_nota_redemil, 2) valor_nota,
       case row_number() over(partition by p.numero order by p.numero)
          when 1 then coalesce(pdrd.despesa, 0.00)
          else 0.00
       end despesa_pedido,
       case row_number() over(partition by p.numero order by p.numero)
          when 1 then coalesce(pdrc.receita, 0.00)
          else 0.00
       end receita_pedido,
       case
          when p.tipo_pedido = 'D' and not p.recebe_usado then round(((pov.valor * ppi.percentual / 100) / p.valor_total_nota) * p.valor_total, 2) - round(pov.valor * ppi.percentual / 100, 2)
          when p.tipo_pedido = 'R' and not p.recebe_usado then round(((pov.valor * ppi.percentual / 100) / p.valor_total_nota) * p.valor_total, 2) - round(((pov.valor * ppi.percentual / 100) / p.valor_total_nota) * p.valor_nota_redemil, 2)
          when p.tipo_pedido = 'D' and p.recebe_usado then round(((pov.valor * ppi.percentual / 100) / p.valor_total_nota) * p.valor_total * p.percentual_lucro_pedido / 100, 2) - round(pov.valor * ppi.percentual * pov.percentual_comissao / 10000, 2)
          when p.tipo_pedido = 'R' and p.recebe_usado then round(((pov.valor * ppi.percentual / 100) / p.valor_total_nota) * p.valor_nota_redemil * (100 - p.percentual_pis_cofins - p.percentual_icms) / 100, 2) - round((pov.valor * ppi.percentual / 100) * (100 - pov.percentual_pis_cofins - pov.percentual_icms) / 100, 2)
          else 0.00
       end "over",
       case ppi.tipo_produto_item
          when 'TPI10' then ov.numero_nota_10
          when 'TPI20' then ov.numero_nota_20
          when 'TPI30' then ov.numero_nota_30
          else null
       end nota,
       cpr.data_faturamento
    from
       financeiro.contas_pagar_receber cpr
    inner join financeiro.contas_pagar_receber_itens cpri on cpri.id_conta_pagar_receber = cpr.id_conta_pagar_receber
    inner join pedido.pedidos p on p.id_pedido = cpr.id_pedido
    inner join pedido.pedidos_vendedores pv on pv.id_pedido = p.id_pedido
    inner join cadastro.vendedores v on v.id_vendedor = pv.id_vendedor
    inner join pedido.pedidos_produtos pp on pp.id_pedido = p.id_pedido
    inner join pedido.pedidos_produtos_itens ppi on ppi.id_pedido_produto = pp.id_pedido_produto and ppi.tipo_produto_item = cpri.tipo_produto_item
    inner join cadastro.pessoas p2 on p2.id_pessoa = p.id_cliente
    inner join cadastro.pessoas_enderecos pe on pe.id_pessoa = p.id_cliente
    inner join cadastro.cidades c on c.id_cidade = pe.id_cidade
    inner join cadastro.estados e on e.id_estado = c.id_estado
    inner join pedido.pedidos_ordens_vendas pov on pov.numero_ordem_venda = cpr.numero
    inner join cadastro.ordens_vendas ov on ov.numero_ordem_venda = cpr.numero
    left join
       (
          select
             pdr.id_pedido,
             sum
             (
                case pdr.tipo_lancamento
                   when 'D' then pdr.valor
                   else 0.00
                end
             ) despesa
          from
             pedido.pedidos_despesas_receitas pdr
          group by
             pdr.id_pedido
       ) pdrd on pdrd.id_pedido = p.id_pedido
    left join
       (
          select
             pdr.id_pedido,
             sum
             (
                case pdr.tipo_lancamento
                   when 'C' then pdr.valor
                   else 0.00
                end
             ) receita
          from
             pedido.pedidos_despesas_receitas pdr
          group by
             pdr.id_pedido
       ) pdrc on pdrc.id_pedido = p.id_pedido
    where
       cpr.data_faturamento notnull and
       cpr.data_faturamento between :pidt_faturamentoinicio and :pidt_faturamentofinal and
       cpr.id_pedido_vendedor is null and
       cpri.tipo_produto_item in ('TPI10', 'TPI20', 'TPI30') and
       p.tipo_pedido in ('D', 'R') and
       pp.tipo_produto_pedido = 'V'
)
SELECT
    -- 非数值列：总计行显示null
    CASE WHEN GROUPING(pedido) = 1 THEN NULL ELSE pedido END AS pedido,
    CASE WHEN GROUPING(nome_vendedor) = 1 THEN NULL ELSE nome_vendedor END AS nome_vendedor,
    CASE WHEN GROUPING(ordem_venda) = 1 THEN NULL ELSE ordem_venda END AS ordem_venda,
    CASE WHEN GROUPING(item) = 1 THEN NULL ELSE item END AS item,
    CASE WHEN GROUPING(descricao) = 1 THEN NULL ELSE descricao END AS descricao,
    CASE WHEN GROUPING(nome_cliente) = 1 THEN NULL ELSE nome_cliente END AS nome_cliente,
    CASE WHEN GROUPING(cidade) = 1 THEN NULL ELSE cidade END AS cidade,
    CASE WHEN GROUPING(uf) = 1 THEN NULL ELSE uf END AS uf,
    -- 数值列：合计值，保留两位小数
    ROUND(SUM(valor_ordem_venda), 2) AS valor_ordem_venda,
    CASE WHEN GROUPING(percentual_comissao) = 1 THEN NULL ELSE percentual_comissao END AS percentual_comissao,
    ROUND(SUM(comissao), 2) AS comissao,
    ROUND(SUM(valor_venda), 2) AS valor_venda,
    ROUND(SUM(valor_nota), 2) AS valor_nota,
    ROUND(SUM(despesa_pedido), 2) AS despesa_pedido,
    ROUND(SUM(receita_pedido), 2) AS receita_pedido,
    ROUND(SUM("over"), 2) AS "over",
    CASE WHEN GROUPING(nota) = 1 THEN NULL ELSE nota END AS nota,
    CASE WHEN GROUPING(data_faturamento) = 1 THEN NULL ELSE data_faturamento END AS data_faturamento
FROM detalhes
GROUP BY GROUPING SETS (
    -- 明细行：按所有非聚合字段分组
    (pedido, nome_vendedor, ordem_venda, item, descricao, nome_cliente, cidade, uf, percentual_comissao, nota, data_faturamento, valor_ordem_venda, comissao, valor_venda, valor_nota, despesa_pedido, receita_pedido, "over"),
    -- 总计行：空分组，聚合所有行
    ()
)
ORDER BY
    -- 确保总计行在最后
    GROUPING(pedido),
    pedido,
    ordem_venda,
    item;

关键说明 #

• GROUPING(col)函数：如果当前行是对col进行聚合后的行，返回1，否则返回0。用于判断是否为总计行，控制非数值列显示null；
• GROUPING SETS同时定义了明细分组和总计分组，避免了UNION需要两次执行基础查询的开销；
• 排序时通过GROUPING(pedido)将总计行（值为1）排在所有明细行（值为0）之后；
• 所有数值字段的求和都用SUM()包裹，并保留两位小数，和原查询的精度一致。

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