求助：基于三表关联查询客户指定时段采购统计及总花费问题

数据表结构 #

• products(id, title, price)：产品表，存储产品ID、名称、单价
• customers(id, name, last_name)：客户表，存储客户ID、名字、姓氏
• purchases(id, customer_id, product_id, purchase_date)：采购记录表，存储采购ID、关联客户ID、关联产品ID、采购日期

需求目标 #

获取指定时间段内的客户采购统计数据，具体包括：

• 每个客户的完整姓名
• 该客户时段内各产品的累计采购花费
• 该客户时段内的总花费
• 所有客户在该时段的总花费、平均花费

你尝试的错误SQL语句 #

String query =  "SELECT SUM(price) last_name, name, title, price FROM customers 
             JOIN purchases ON customers.id=purchases.customer_id 
             JOIN products ON products.id=purchases.product_id 
             WHERE purchase_date BETWEEN ? AND ? 
             GROUP BY customers.id";

原SQL的问题 #

1. 字段别名逻辑错误：把SUM(price)的别名设为last_name，导致字段对应关系完全混乱
2. GROUP BY不符合SQL标准：按customers.id分组时，title、price这类非聚合字段未出现在GROUP BY中（除非数据库开启非标准兼容模式），标准SQL会直接报错
3. 缺少产品维度分组：无法统计每个客户下不同产品的花费情况
4. 未计算全局汇总数据：无法获取所有客户的总花费和平均花费

正确解决方案 #

方案1：使用窗口函数一次性获取全量数据 #

通过窗口函数可以在一次查询中拿到所有维度的数据，方便后续在代码中做分组整理：

SELECT
    CONCAT(c.name, ' ', c.last_name) AS customer_name,
    prod.title AS product_title,
    SUM(prod.price) AS product_expenses,
    -- 计算当前客户的总花费
    SUM(SUM(prod.price)) OVER (PARTITION BY c.id) AS customer_total_expenses,
    -- 计算所有客户的总花费
    SUM(SUM(prod.price)) OVER () AS global_total_expenses,
    -- 计算所有客户的平均花费
    AVG(SUM(prod.price)) OVER () AS global_avg_expenses
FROM customers c
JOIN purchases pur ON c.id = pur.customer_id
JOIN products prod ON pur.product_id = prod.id
WHERE pur.purchase_date BETWEEN ? AND ?
GROUP BY c.id, c.name, c.last_name, prod.title
ORDER BY c.id;

方案2：分两次查询（逻辑更直观） #

如果觉得窗口函数复杂，可以拆分为两次查询：

第一步：查询每个客户的产品采购明细及个人总花费

SELECT
    CONCAT(c.name, ' ', c.last_name) AS customer_name,
    prod.title AS product_title,
    SUM(prod.price) AS product_expenses,
    SUM(SUM(prod.price)) OVER (PARTITION BY c.id) AS customer_total_expenses
FROM customers c
JOIN purchases pur ON c.id = pur.customer_id
JOIN products prod ON pur.product_id = prod.id
WHERE pur.purchase_date BETWEEN ? AND ?
GROUP BY c.id, c.name, c.last_name, prod.title;

第二步：查询所有客户的总花费和平均花费

SELECT
    SUM(customer_total) AS global_total_expenses,
    AVG(customer_total) AS global_avg_expenses
FROM (
    -- 先统计每个客户的总花费
    SELECT
        SUM(prod.price) AS customer_total
    FROM customers c
    JOIN purchases pur ON c.id = pur.customer_id
    JOIN products prod ON pur.product_id = prod.id
    WHERE pur.purchase_date BETWEEN ? AND ?
    GROUP BY c.id
) AS customer_totals;

结果处理提示 #

拿到数据库返回的结果后，需要在代码中做分组整理：比如用Map将同一客户的产品记录归为一组，组成purchases数组，再将个人总花费、全局总花费和平均花费对应到你想要的JSON结构中。

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