PostgreSQL中按客户统计订单ID数量的异常问题排查

我编写了如下PostgreSQL查询语句：

SELECT
    fato_pedido_promob.id_pedido_promob,
    dim_tempo.data,
    fato_pedido_promob.valor_total_pedido,
    dim_cliente_promob.cpf_cnpj,
    dim_cliente_promob.email,
    dim_cliente_promob.segmento,
    dim_cliente_promob.razao_social,
    data_ultimo_pedido,
    extract(day from current_date::timestamp - data_ultimo_pedido::timestamp) as dias_ultimo_pedido,
    count(fato_pedido_promob.id_pedido_promob) as pedidos_por_cpf
    
FROM
    dim_cliente_promob
LEFT JOIN
    fato_pedido_promob ON dim_cliente_promob.id_cliente = fato_pedido_promob.fk_cliente_promob
LEFT JOIN
            dim_tempo ON dim_tempo.id_tempo = fato_pedido_promob.fk_data_emissao
LEFT JOIN
    (
        SELECT
            cpf_cnpj,
            MAX(dim_tempo.data) AS data_ultimo_pedido
        FROM
            dim_cliente_promob
        LEFT JOIN
            fato_pedido_promob ON dim_cliente_promob.id_cliente = fato_pedido_promob.fk_cliente_promob
        LEFT JOIN
            dim_tempo ON dim_tempo.id_tempo = fato_pedido_promob.fk_data_emissao
        WHERE
            cpf_cnpj <> 'Desconhecido'
            AND id_pedido_promob IS NOT NULL
            AND dim_tempo.data < CURRENT_DATE
        GROUP BY
            cpf_cnpj
    ) AS max_data ON dim_cliente_promob.cpf_cnpj = max_data.cpf_cnpj
WHERE
    dim_cliente_promob.cpf_cnpj <> 'Desconhecido'
    AND id_pedido_promob IS NOT NULL
    AND data < CURRENT_DATE 
GROUP BY    
    dim_cliente_promob.cpf_cnpj,
    fato_pedido_promob.id_pedido_promob,
    dim_tempo.data,
    fato_pedido_promob.valor_total_pedido,
    dim_cliente_promob.email,
    dim_cliente_promob.segmento,
    dim_cliente_promob.razao_social,
    data_ultimo_pedido
ORDER BY
    data DESC;

其中客户ID为dim_cliente_promob.cpf_cnpj，订单ID为fato_pedido_promob.id_pedido_promob。我需要统计每个客户的订单ID数量，但使用count(fato_pedido_promob.id_pedido_promob) as pedidos_por_cpf时，所有客户的统计结果都返回1，请问问题出在哪里？

问题出在GROUP BY子句的字段选择：你把唯一标识单个订单的fato_pedido_promob.id_pedido_promob加入了分组条件中。因为每个订单ID都是唯一的，所以每个分组只会包含该订单ID对应的一条记录，count()自然会返回1，无法实现按客户统计总订单数的需求。

根据你的需求，有两种常见的修正方式：

方式一：使用窗口函数保留订单明细，同时统计客户总订单数 #

如果你需要保留每条订单的明细数据，同时在每行显示该客户的总订单数，可以用窗口函数COUNT() OVER (PARTITION BY ...)替代普通的聚合count()：

SELECT
    fato_pedido_promob.id_pedido_promob,
    dim_tempo.data,
    fato_pedido_promob.valor_total_pedido,
    dim_cliente_promob.cpf_cnpj,
    dim_cliente_promob.email,
    dim_cliente_promob.segmento,
    dim_cliente_promob.razao_social,
    data_ultimo_pedido,
    extract(day from current_date::timestamp - data_ultimo_pedido::timestamp) as dias_ultimo_pedido,
    -- 用窗口函数按客户分组统计总订单数
    COUNT(fato_pedido_promob.id_pedido_promob) OVER (PARTITION BY dim_cliente_promob.cpf_cnpj) as pedidos_por_cpf
    
FROM
    dim_cliente_promob
LEFT JOIN
    fato_pedido_promob ON dim_cliente_promob.id_cliente = fato_pedido_promob.fk_cliente_promob
LEFT JOIN
            dim_tempo ON dim_tempo.id_tempo = fato_pedido_promob.fk_data_emissao
LEFT JOIN
    (
        SELECT
            cpf_cnpj,
            MAX(dim_tempo.data) AS data_ultimo_pedido
        FROM
            dim_cliente_promob
        LEFT JOIN
            fato_pedido_promob ON dim_cliente_promob.id_cliente = fato_pedido_promob.fk_cliente_promob
        LEFT JOIN
            dim_tempo ON dim_tempo.id_tempo = fato_pedido_promob.fk_data_emissao
        WHERE
            cpf_cnpj <> 'Desconhecido'
            AND id_pedido_promob IS NOT NULL
            AND dim_tempo.data < CURRENT_DATE
        GROUP BY
            cpf_cnpj
    ) AS max_data ON dim_cliente_promob.cpf_cnpj = max_data.cpf_cnpj
WHERE
    dim_cliente_promob.cpf_cnpj <> 'Desconhecido'
    AND id_pedido_promob IS NOT NULL
    AND data < CURRENT_DATE 
ORDER BY
    data DESC;

这种方式不需要修改GROUP BY，因为窗口函数是对结果集进行分组统计，不会改变原有的行结构。

方式二：调整GROUP BY字段，按客户维度聚合统计 #

如果你只需要按客户维度展示总订单数，不需要每条订单的明细，可以修改GROUP BY，只保留客户相关的分组字段，并对订单相关字段做聚合处理：

SELECT
    dim_cliente_promob.cpf_cnpj,
    dim_cliente_promob.email,
    dim_cliente_promob.segmento,
    dim_cliente_promob.razao_social,
    max_data.data_ultimo_pedido,
    extract(day from current_date::timestamp - max_data.data_ultimo_pedido::timestamp) as dias_ultimo_pedido,
    COUNT(fato_pedido_promob.id_pedido_promob) as pedidos_por_cpf,
    -- 如果需要订单相关的聚合值，比如总金额、最新订单日期等
    SUM(fato_pedido_promob.valor_total_pedido) as total_gasto,
    MAX(dim_tempo.data) as ultima_data_pedido
FROM
    dim_cliente_promob
LEFT JOIN
    fato_pedido_promob ON dim_cliente_promob.id_cliente = fato_pedido_promob.fk_cliente_promob
LEFT JOIN
            dim_tempo ON dim_tempo.id_tempo = fato_pedido_promob.fk_data_emissao
LEFT JOIN
    (
        SELECT
            cpf_cnpj,
            MAX(dim_tempo.data) AS data_ultimo_pedido
        FROM
            dim_cliente_promob
        LEFT JOIN
            fato_pedido_promob ON dim_cliente_promob.id_cliente = fato_pedido_promob.fk_cliente_promob
        LEFT JOIN
            dim_tempo ON dim_tempo.id_tempo = fato_pedido_promob.fk_data_emissao
        WHERE
            cpf_cnpj <> 'Desconhecido'
            AND id_pedido_promob IS NOT NULL
            AND dim_tempo.data < CURRENT_DATE
        GROUP BY
            cpf_cnpj
    ) AS max_data ON dim_cliente_promob.cpf_cnpj = max_data.cpf_cnpj
WHERE
    dim_cliente_promob.cpf_cnpj <> 'Desconhecido'
    AND id_pedido_promob IS NOT NULL
    AND dim_tempo.data < CURRENT_DATE 
GROUP BY    
    dim_cliente_promob.cpf_cnpj,
    dim_cliente_promob.email,
    dim_cliente_promob.segmento,
    dim_cliente_promob.razao_social,
    max_data.data_ultimo_pedido
ORDER BY
    max_data.data_ultimo_pedido DESC;

这种方式会将同一客户的所有订单合并为一行，展示该客户的总订单数及其他聚合信息。

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