如何编写SQL查询获取按月份分组的每位用户平均购买总额

嘿，我来帮你搞定这个SQL查询的问题～先拆解下你遇到的问题，再给出针对性的解决方案：

首先，你的现有语句存在几个明显问题： #

• JOIN语法错误：你写的INNER JOIN Purchases.itemId ON Items.itemId完全不符合SQL规范，正确的关联逻辑应该是用Purchases.itemId匹配Items.itemId，写法是INNER JOIN Items ON Purchases.itemId = Items.itemId。
• 聚合函数嵌套错误：AVG(SUM(Items.price))是不合法的——SUM是对分组内的行做求和，AVG需要在更高层级的分组上计算，这说明你可能混淆了需求的聚合层级。
• GROUP BY缺失正确字段：要按月份+用户分组，必须把这两个字段都放到GROUP BY里，而不是模糊的“按月份分组”。

关于Users表的必要性： #

如果你的需求只是展示用户的月度购买数据，完全不需要Users表——因为Purchases表已经包含userId，足够用来分组和标识用户。只有当你需要基于用户年龄做过滤、或者要展示年龄信息时，才需要关联Users表。

针对你的需求，分两种场景给出SQL： #

场景1：按月份分组，展示每位用户当月的总购买金额（这应该是你大概率需要的结果）

这个查询会输出每个用户在每个月份的总花费：

SELECT
    p.userId,
    -- 注意：不同数据库的日期格式化函数不同，下面列出主流数据库的写法：
    -- PostgreSQL: DATE_TRUNC('month', p.date) AS purchase_month
    -- MySQL: DATE_FORMAT(p.date, '%Y-%m') AS purchase_month
    -- SQL Server: DATEFROMPARTS(YEAR(p.date), MONTH(p.date), 1) AS purchase_month
    -- Oracle: TRUNC(p.date, 'MM') AS purchase_month
    DATE_TRUNC('month', p.date) AS purchase_month,
    SUM(i.price) AS total_purchase_amount
FROM Purchases p
INNER JOIN Items i ON p.itemId = i.itemId
GROUP BY p.userId, DATE_TRUNC('month', p.date)
ORDER BY purchase_month, p.userId;

场景2：按月份分组，展示当月所有用户的平均购买总额（即每个月里，所有用户总花费的平均值）

如果你确实需要“平均”值，那需要先计算每个用户每月的总花费，再对每个月的这些值求平均：

SELECT
    purchase_month,
    AVG(total_purchase_amount) AS avg_user_monthly_spend
FROM (
    -- 子查询先算出每个用户每月的总花费
    SELECT
        p.userId,
        DATE_TRUNC('month', p.date) AS purchase_month,
        SUM(i.price) AS total_purchase_amount
    FROM Purchases p
    INNER JOIN Items i ON p.itemId = i.itemId
    GROUP BY p.userId, DATE_TRUNC('month', p.date)
) AS user_monthly_spend
GROUP BY purchase_month
ORDER BY purchase_month;

额外：如果需要关联Users表（比如过滤或展示年龄） #

如果之后要加入年龄维度，只需要在查询中关联Users表即可：

SELECT
    u.userId,
    u.age,
    DATE_TRUNC('month', p.date) AS purchase_month,
    SUM(i.price) AS total_purchase_amount
FROM Purchases p
INNER JOIN Items i ON p.itemId = i.itemId
INNER JOIN Users u ON p.userId = u.userId
WHERE u.age >= 18 -- 比如只看成年用户
GROUP BY u.userId, u.age, DATE_TRUNC('month', p.date)
ORDER BY purchase_month, u.userId;

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