Firebird 3.0中统计商品在不同单据中的去重出现次数

示例数据 #

nota | produto | descricao | vendida | total | ...
     1         A         AAA       100    1000
     1         B         BBB        20     200
     1         A         AAA       200    2000
     1         C         CCC        50     500
     2         A         AAA       500    5000
     3         Z         ZZZ       100    1000
     4         X         XXX       100    1000
     4         B         BBB      1000   10000

现有查询（已实现销量排名） #

with a as (
     select
        trim(ni.cd_produto) cd_produto,
        trim(p.ds_produto) ds_produto,
        sum(ni.qt_vendida) qt_vendida,
        sum(ni.vr_totalliquido) vr_totapagar
     from
        notaitem ni
     inner join nota n on n.cd_empresa = 1 and
                          n.cd_filial = 1 and
                          n.nr_nota = ni.nr_nota and
                          n.nr_desdobramento = ni.nr_desdobramento
     inner join produto p on p.cd_produto = ni.cd_produto
     where
        n.fg_situacao = 'N' and
        n.fg_situacaonfe = '1' and
        n.cd_tipomovto = 1 and
        n.dt_emissao between :pidt_inicio and :pidt_final
     group by
        ni.cd_produto,
        p.ds_produto
     order by
        qt_vendida desc
     )
select
     dense_rank() over(order by qt_vendida desc) nr_rank,
     cd_produto,
     ds_produto,
     qt_vendida,
     vr_totapagar,
     cast(round(100 * qt_vendida / sum(qt_vendida) over(), 4) as numeric(7, 4)) pc_participacaoqtd,
     cast(round(100 * vr_totapagar / sum(vr_totapagar) over(), 4) as numeric(7, 4)) pc_participacaovr
from
     a
order by
     nr_rank,
     ds_produto

需求 #

新增appearance字段，统计每个商品在不同nota（单据）中的出现次数：同一单据内同一商品多次出现仅计1次。例如商品A在nota1出现2次、nota2出现1次，appearance值应为2。

此前尝试count(1)会统计所有行导致结果偏大，count(distinct)使用不当导致所有商品的appearance为1，均不符合需求。

解决方案 #

核心是在CTE的分组统计中，针对每个商品统计不同单据号的数量，即使用count(distinct n.nr_nota)来实现。修改后的完整查询如下：

with a as (
     select
        trim(ni.cd_produto) cd_produto,
        trim(p.ds_produto) ds_produto,
        sum(ni.qt_vendida) qt_vendida,
        sum(ni.vr_totalliquido) vr_totapagar,
        -- 新增：统计当前商品出现过的不同单据数量
        count(distinct n.nr_nota) as appearance
     from
        notaitem ni
     inner join nota n on n.cd_empresa = 1 and
                          n.cd_filial = 1 and
                          n.nr_nota = ni.nr_nota and
                          n.nr_desdobramento = ni.nr_desdobramento
     inner join produto p on p.cd_produto = ni.cd_produto
     where
        n.fg_situacao = 'N' and
        n.fg_situacaonfe = '1' and
        n.cd_tipomovto = 1 and
        n.dt_emissao between :pidt_inicio and :pidt_final
     group by
        ni.cd_produto,
        p.ds_produto
     order by
        qt_vendida desc
     )
select
     dense_rank() over(order by qt_vendida desc) nr_rank,
     cd_produto,
     ds_produto,
     qt_vendida,
     vr_totapagar,
     appearance, -- 新增字段
     cast(round(100 * qt_vendida / sum(qt_vendida) over(), 4) as numeric(7, 4)) pc_participacaoqtd,
     cast(round(100 * vr_totapagar / sum(vr_totapagar) over(), 4) as numeric(7, 4)) pc_participacaovr
from
     a
order by
     nr_rank,
     ds_produto

说明 #

• count(distinct n.nr_nota)会针对每个分组（即每个商品），统计所有不重复的单据号数量，自动忽略同一单据内的重复出现，完全符合需求。
• 只需在CTE的select列表中添加该统计项，再在最终查询中输出appearance字段即可。

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