如何用SQL计算仅购买多品类用户的月度跨品类销售额？

先咱们把需求的核心要点拆解清楚，避免理解偏差：

• 目标用户：交易次数超过1次且购买过不止一个品类的用户
• 统计维度：按月度聚合
• 统计指标：跨品类销售额（这里分两种常见业务定义，我都会给出对应实现）

场景1：统计符合条件用户的当月所有销售额 #

如果“跨品类销售额”指的是满足条件的用户在当月的全部交易销售额（只要用户符合跨品类+多次交易的要求，他当月的所有交易都算入统计），可以用下面的SQL：

WITH user_monthly_stats AS (
    SELECT
        user_id,
        -- 按数据库调整日期截断方式：MySQL用DATE_FORMAT(transaction_date, '%Y-%m-01')，Oracle用TRUNC(transaction_date, 'MM')
        DATE_TRUNC('month', transaction_date) AS month,
        COUNT(DISTINCT transaction_id) AS transaction_count, -- 按交易ID统计次数，避免同一交易多条记录重复计算
        COUNT(DISTINCT category_id) AS category_count, -- 统计用户当月购买的品类数量
        SUM(amount) AS user_monthly_sales
    FROM transaction_data
    GROUP BY user_id, DATE_TRUNC('month', transaction_date)
    -- 筛选符合条件的用户月度数据
    HAVING COUNT(DISTINCT transaction_id) > 1 
        AND COUNT(DISTINCT category_id) > 1
)
-- 按月度聚合总销售额，同时可统计当月符合条件的用户数
SELECT
    month,
    SUM(user_monthly_sales) AS monthly_total_cross_category_sales,
    COUNT(DISTINCT user_id) AS qualified_user_count -- 可选指标，按需保留
FROM user_monthly_stats
GROUP BY month
ORDER BY month;

代码解释： #

1. CTE user_monthly_stats：先按用户+月份分组，计算每个用户当月的核心指标，再通过HAVING筛选出交易次数>1、品类数>1的用户月度记录。
2. 外层查询：对筛选后的用户数据按月份聚合，得到每个月的总跨品类销售额，还可以额外统计当月符合条件的用户数量。
3. 日期函数适配：注意DATE_TRUNC是PostgreSQL等数据库的写法，不同数据库需要调整（比如MySQL用DATE_FORMAT，Oracle用TRUNC）。

场景2：仅统计用户的跨品类交易销售额 #

如果“跨品类销售额”特指用户参与的同一交易包含多个品类的那部分销售额，需要先识别这类交易，再统计：

-- 第一步：标记每笔交易是否跨品类
WITH transaction_category_check AS (
    SELECT
        transaction_id,
        COUNT(DISTINCT category_id) AS category_count_per_trans
    FROM transaction_data
    GROUP BY transaction_id
),
-- 第二步：计算符合条件用户的月度跨品类交易销售额
user_monthly_cross_trans_stats AS (
    SELECT
        td.user_id,
        DATE_TRUNC('month', td.transaction_date) AS month,
        COUNT(DISTINCT td.transaction_id) AS total_transaction_count,
        COUNT(DISTINCT td.category_id) AS total_category_count,
        SUM(td.amount) AS cross_category_trans_sales
    FROM transaction_data td
    JOIN transaction_category_check tcc 
        ON td.transaction_id = tcc.transaction_id
    -- 仅筛选跨品类的交易记录
    WHERE tcc.category_count_per_trans > 1
    GROUP BY td.user_id, DATE_TRUNC('month', td.transaction_date)
    -- 确保用户整体满足交易次数>1、购买品类数>1的条件
    HAVING COUNT(DISTINCT td.transaction_id) > 1 
        AND COUNT(DISTINCT td.category_id) > 1
)
-- 月度聚合统计
SELECT
    month,
    SUM(cross_category_trans_sales) AS monthly_total_cross_trans_sales,
    COUNT(DISTINCT user_id) AS qualified_user_count
FROM user_monthly_cross_trans_stats
GROUP BY month
ORDER BY month;

代码解释： #

1. CTE transaction_category_check：先统计每笔交易涉及的品类数，标记出跨品类的交易（品类数>1）。
2. CTE user_monthly_cross_trans_stats：关联交易数据和跨品类标记，筛选出跨品类交易，再按用户+月份分组，同时确保用户满足总交易次数>1、总品类数>1的要求。
3. 外层查询：按月份聚合得到当月的跨品类交易总销售额。

注意事项 #

• 交易次数的统计：如果你的表中同一笔交易只有一条记录，可以去掉COUNT(DISTINCT transaction_id)中的DISTINCT，直接用COUNT(transaction_id)或COUNT(*)。
• 字段适配：如果你的销售额字段不是amount、品类字段不是category_id，需要替换成实际表中的字段名。

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