SQL如何统计客户月度购买次数及累计、当月消费相关指标？

原有查询的核心问题是聚合粒度只到单条交易明细，没有通过窗口函数在保留明细行的基础上追加跨维度统计值，调整后代码可直接输出需要的所有字段，同时保留原有单笔交易的明细信息。

WITH fact_data AS (
    SELECT 
        Descrip AS Customer_Name
        , CodClie AS ID_Customer
        -- 预处理单笔实际交易金额：购买记正，退货记负
        , CASE WHEN TipoFac = 'A' THEN Monto ELSE -Monto END AS Purchase_Amount$
        , CONVERT(varchar, FechaE, 1) AS Date
        , FechaE -- 保留原始日期类型字段，避免字符串格式导致月度计算错误
        , CodUbic AS Store_Location
        , NumeroD -- 账单号，作为购买次数的统计依据
    FROM dbo.SAFACT
    WHERE TipoFac IN ('A', 'B')
)
SELECT 
    Customer_Name
    , ID_Customer
    , Purchase_Amount$
    , Date
    , Store_Location
    -- 开业至今累计购买次数：按客户去重统计历史账单总数
    , COUNT(DISTINCT NumeroD) OVER (PARTITION BY ID_Customer) AS Times_bought_since_1st_Day
    -- 当月购买次数：按客户+交易年月去重统计当月账单数
    , COUNT(DISTINCT NumeroD) OVER (PARTITION BY ID_Customer, YEAR(FechaE), MONTH(FechaE)) AS Times_bought_current_month
    -- 开业至今累计总消费：按客户汇总所有历史实际交易金额
    , SUM(Purchase_Amount$) OVER (PARTITION BY ID_Customer) AS Total_spent_since_1st_day
    -- 当月总消费：按客户+交易年月汇总当月实际交易金额
    , SUM(Purchase_Amount$) OVER (PARTITION BY ID_Customer, YEAR(FechaE), MONTH(FechaE)) AS Total_spent_this_month
FROM fact_data
ORDER BY YEAR(FechaE) DESC, MONTH(FechaE) DESC, DAY(FechaE) DESC;

关键调整点 #

• 新增CTE层做字段预处理，统一计算单笔交易实际金额，保留原始日期类型字段做时间维度分区，避免字符串转义导致的月度统计错误
• 所有统计指标均通过窗口函数实现，不修改原有查询的明细粒度，每笔交易的单笔金额、交易日期、所属门店信息都会完整保留
• 累计类指标（累计购买次数、累计总消费）以客户ID为分区键做全历史聚合
• 月度类指标（当月购买次数、当月总消费）以客户ID+交易年份+交易月份为分区键做当月范围聚合
• 替换原有双向DENSE_RANK计算累计购买次数的逻辑，改用去重账单号计数的方式，避免账单号断号、跳号导致的统计误差，逻辑更易维护
• 移除原语句中冗余的GROUP BY，避免不必要的排序开销，窗口函数可直接在明细行上完成聚合计算

可选适配 #

• 如果需要按门店维度拆分客户统计值（即同个客户在不同门店的消费、购买次数独立计算），只需要给所有窗口函数的PARTITION BY子句末尾追加Store_Location字段即可
• 如果使用的SQL Server版本不支持窗口函数内的COUNT(DISTINCT)语法，可以先按客户、账单号聚合到单账单粒度，再基于账单粒度数据集做窗口统计，最终结果完全一致

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