MySQL按买家类型统计各用户畅销产品的查询问题

核心思路拆解 #

需要分五步完成：处理无效订单（order_id为null的情况）→ 划分买家类型 → 提取每个用户的Top1产品 → 按买家类型聚合产品销量 → 筛选各类型Top10产品。

假设你的订单明细表结构为 order_details(user_id, order_id, order_date, product_id, quantity)，以下是完整实现SQL：

-- 1. 生成用户的有效订单列表（去重处理）
WITH valid_orders AS (
    SELECT DISTINCT
        user_id,
        -- 对order_id为null的订单：同一用户同一日期的多笔订单视为1个有效订单
        CASE 
            WHEN order_id IS NOT NULL THEN order_id
            ELSE CONCAT(user_id, '_', DATE(order_date))
        END AS effective_order_id
    FROM order_details
),
-- 2. 统计用户有效订单数，划分买家类型
user_order_counts AS (
    SELECT
        user_id,
        COUNT(effective_order_id) AS order_count,
        CASE
            WHEN COUNT(effective_order_id) = 1 THEN '首次买家'
            WHEN COUNT(effective_order_id) = 2 THEN '二次买家'
            WHEN COUNT(effective_order_id) >=3 THEN '三次及以上买家'
        END AS buyer_type
    FROM valid_orders
    GROUP BY user_id
),
-- 3. 统计每个用户各产品的总销量，给产品排名
user_top_products AS (
    SELECT
        od.user_id,
        od.product_id,
        SUM(od.quantity) AS total_sales, -- 若无quantity字段，替换为COUNT(*)统计购买次数
        -- 若要保留并列Top1产品，用RANK()替换ROW_NUMBER()
        ROW_NUMBER() OVER (PARTITION BY od.user_id ORDER BY SUM(od.quantity) DESC) AS rn
    FROM order_details od
    GROUP BY od.user_id, od.product_id
    HAVING total_sales > 0
),
-- 4. 筛选每个用户的Top1产品
user_top1_product AS (
    SELECT
        utp.user_id,
        utp.product_id,
        utp.total_sales AS user_product_sales
    FROM user_top_products utp
    WHERE utp.rn = 1
),
-- 5. 按买家类型聚合产品总销量
buyer_type_product_sales AS (
    SELECT
        uoc.buyer_type,
        utp.product_id,
        SUM(utp.user_product_sales) AS total_type_sales,
        COUNT(DISTINCT utp.user_id) AS buyer_count -- 可选：统计该产品被多少该类型买家选为Top
    FROM user_order_counts uoc
    JOIN user_top1_product utp ON uoc.user_id = utp.user_id
    GROUP BY uoc.buyer_type, utp.product_id
)
-- 6. 提取各买家类型的Top10畅销产品
SELECT * FROM (
    SELECT
        buyer_type,
        product_id,
        total_type_sales,
        buyer_count,
        ROW_NUMBER() OVER (PARTITION BY buyer_type ORDER BY total_type_sales DESC) AS rank_in_type
    FROM buyer_type_product_sales
) t
WHERE rank_in_type <= 10;

关键细节说明 #

• order_id为null的处理：通过user_id+日期生成唯一标识，确保同一用户同一日期的多笔无order_id订单只算1次有效订单。
• 用户Top产品筛选：使用窗口函数ROW_NUMBER()给每个用户的产品按销量排名，若需保留并列第一的产品，替换为RANK()即可。
• 可视化适配：将查询结果导出后，可直接在MySQL Workbench的可视化模块、Tableau或PowerBI中，按buyer_type分组展示product_id与total_type_sales的Top10排名。

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