按ProviderId统计CPTCode使用次数及总金额的SQL需求

核心需求拆解 #

• 按ProviderId和发票月份分组
• 统计每组内各CPTCode的使用次数（需明确是记录数还是不同患者的使用次数）
• 计算对应金额（单CPTCode金额或分组总金额）
• 以CPTCode(次数)的格式聚合展示CPTCode信息

分步SQL实现 #

1. 基础统计（多维度分组） #

先获取每个Provider、月份、CPTCode维度下的次数和金额：

-- 以MySQL为例，其他数据库需调整日期处理函数
SELECT
    ProviderId,
    DATE_FORMAT(InvoiceDate, '%Y-%m') AS InvoiceMonth, -- 格式化年月为YYYY-MM格式
    CPTCode,
    COUNT(*) AS UsageCount, -- 若统计「不同患者使用次数」，替换为COUNT(DISTINCT PatientId)
    SUM(Amount) AS CPTTotalAmount -- 该CPTCode对应的总金额
FROM tblinvoice
GROUP BY ProviderId, DATE_FORMAT(InvoiceDate, '%Y-%m'), CPTCode;

2. 聚合为目标展示格式 #

将同一Provider+月份下的CPTCode信息合并为指定格式，并计算分组总金额：

SELECT
    t.ProviderId,
    t.InvoiceMonth,
    GROUP_CONCAT(CONCAT(t.CPTCode, '(', t.UsageCount, ')') SEPARATOR ', ') AS CPTCodeDetails,
    SUM(t.CPTTotalAmount) AS GroupTotalAmount -- 该Provider对应月份的总金额
FROM (
    -- 子查询复用基础统计逻辑
    SELECT
        ProviderId,
        DATE_FORMAT(InvoiceDate, '%Y-%m') AS InvoiceMonth,
        CPTCode,
        COUNT(*) AS UsageCount,
        SUM(Amount) AS CPTTotalAmount
    FROM tblinvoice
    GROUP BY ProviderId, DATE_FORMAT(InvoiceDate, '%Y-%m'), CPTCode
) t
GROUP BY t.ProviderId, t.InvoiceMonth
ORDER BY t.ProviderId, t.InvoiceMonth;

适配不同数据库的调整 #

• 日期格式化：
  • SQL Server：FORMAT(InvoiceDate, 'yyyy-MM')
  • PostgreSQL：TO_CHAR(InvoiceDate, 'YYYY-MM')
• 字符串聚合：
  • SQL Server 2017+/Azure SQL：替换GROUP_CONCAT为STRING_AGG(CONCAT(t.CPTCode, '(', t.UsageCount, ')'), ', ')
  • PostgreSQL：替换GROUP_CONCAT为STRING_AGG(CONCAT(t.CPTCode, '(', t.UsageCount, ')'), ', ')

单独按ProviderId分组无效的原因 #

仅按ProviderId分组会把该Provider的所有月份、所有CPTCode数据聚合为一条记录，丢失了月份和CPTCode的核心维度，无法区分不同时间、不同编码的使用情况，因此必须增加月份和CPTCode作为分组维度，再按需聚合展示。

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