MySQL子分组数据查询：统计低于阈值的发票数量

现有销售数据中，每个客户单日可生成多张发票，每张发票包含多条记录。需要统计以下指标：

• 客户总销售额
• 下单天数
• 发票总数
• 单票平均金额
• 总金额低于£25的发票数量

目前已实现前四项统计的SQL：

select Customer,sum(Sales_Total) AS Sales Total,
    count(distinct Invoice_Date) AS Days ordered Count,
    COUNT(distinct Invoice_Number) AS Invoice Count,
    sum(Sales_Total)/count(distinct Invoice_Number) AS Average per Invoice,
from sales_2023
group by Customer
order by Customer;

尝试过窗口函数和子查询，但未成功统计出低于阈值的发票数，寻求解决思路。

数据示例 #

Customer | Invoice Date | Invoice Number | Stock | Total Value
---------------------------------------------------------------
Acme     | 01/01/2023   | 1234           | Cod   | £20
Acme     | 01/01/2023   | 1234           | Hake  | £15
Acme     | 01/01/2023   | 2468           | Cod   | £10
Acme     | 01/01/2023   | 2468           | Hake  | £12
Acme     | 02/01/2023   | 3699           | Cod   | £20
Acme     | 02/01/2023   | 4567           | Hake  | £15
Acme     | 03/01/2023   | 9876           | Cod   | £10
Acme     | 03/01/2023   | 9876           | Hake  | £1
Beta     | 01/01/2023   | 8976           | Cod   | £10
Beta     | 01/01/2023   | 8976           | Hake  | £15
Beta     | 01/01/2023   | 5432           | Cod   | £5
Beta     | 01/01/2023   | 5432           | Hake  | £12
Beta     | 02/01/2023   | 2233           | Cod   | £20
Beta     | 02/01/2023   | 2233           | Hake  | £15
Beta     | 02/01/2023   | 1590           | Cod   | £10
Beta     | 02/01/2023   | 1590           | Hake  | £15

期望结果 #

Customer | Sales Total | Days Ordered Count | Invoice Count | Average per Invoice |  Invoice < £25
-------------------------------------------------------------------------------------------------
Acme     |     £103    |           3        |      5        |          £20.60     |      4
Beta     |     £102    |           2        |      4        |          £25.50     |      1

核心思路是先计算每张发票的总金额，因为原表中每条记录是发票的明细项，单条记录的Total Value只是单个商品的金额，不是整张发票的总额。只有先得到单票总额，才能判断是否低于£25，再基于此做客户维度的汇总统计。

完整SQL（用CTE实现） #

WITH invoice_summary AS (
    SELECT 
        Customer,
        Invoice_Number,
        -- 去掉£符号，转成数值类型再求和，得到单张发票的总金额
        SUM(CAST(REPLACE(Total_Value, '£', '') AS DECIMAL(10,2))) AS invoice_total
    FROM sales_2023
    GROUP BY Customer, Invoice_Number
)
SELECT 
    isum.Customer,
    SUM(isum.invoice_total) AS `Sales Total`,
    -- 关联原表获取去重后的下单天数
    COUNT(DISTINCT s.Invoice_Date) AS `Days Ordered Count`,
    COUNT(isum.Invoice_Number) AS `Invoice Count`,
    -- 保留两位小数贴合期望结果
    ROUND(SUM(isum.invoice_total)/COUNT(isum.Invoice_Number), 2) AS `Average per Invoice`,
    -- 用CASE WHEN标记符合条件的发票，求和得到数量
    SUM(CASE WHEN isum.invoice_total < 25 THEN 1 ELSE 0 END) AS `Invoice < £25`
FROM invoice_summary isum
JOIN sales_2023 s ON isum.Customer = s.Customer AND isum.Invoice_Number = s.Invoice_Number
GROUP BY isum.Customer
ORDER BY isum.Customer;

关键说明 #

• 第一步的CTEinvoice_summary是核心：按Customer和Invoice_Number分组，计算每张发票的总金额，这里必须处理Total Value里的英镑符号，转成数值才能正确求和。
• 关联原表是为了获取Invoice_Date，统计客户的下单天数（去重后计数）。
• 用CASE WHEN判断单票总额是否低于25，符合条件记1，否则0，对这个结果求和就是低额发票的数量。

子查询版本（如果不支持CTE） #

如果你的SQL环境不支持CTE，可以把中间逻辑放到子查询里，效果完全一致：

SELECT 
    sub.Customer,
    SUM(sub.invoice_total) AS `Sales Total`,
    COUNT(DISTINCT s.Invoice_Date) AS `Days Ordered Count`,
    COUNT(sub.Invoice_Number) AS `Invoice Count`,
    ROUND(SUM(sub.invoice_total)/COUNT(sub.Invoice_Number), 2) AS `Average per Invoice`,
    SUM(CASE WHEN sub.invoice_total < 25 THEN 1 ELSE 0 END) AS `Invoice < £25`
FROM (
    SELECT 
        Customer,
        Invoice_Number,
        SUM(CAST(REPLACE(Total_Value, '£', '') AS DECIMAL(10,2))) AS invoice_total
    FROM sales_2023
    GROUP BY Customer, Invoice_Number
) sub
JOIN sales_2023 s ON sub.Customer = s.Customer AND sub.Invoice_Number = s.Invoice_Number
GROUP BY sub.Customer
ORDER BY sub.Customer;

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