MySQL实现商品Listing复购次数统计及报表生成

定义说明（匹配你的需求示例） #

• purchases：Listing的总交易次数（对应原表中该listing_id的记录行数总和；若需统计购买数量总和，可替换为数量累加逻辑）
• repurchases：同一买家对该Listing的第2次及以后交易次数总和（即每个买家对该Listing的交易次数减1后的累加值）
• repurchase_rate：复购次数/总购买次数，以百分比格式展示

完整SQL代码 #

WITH buyer_listing_purchases AS (
    -- 统计每个买家对每个Listing的交易次数
    SELECT 
        buyer_id,
        listing_id,
        COUNT(*) AS buyer_transaction_count,
        SUM(quantity) AS buyer_quantity_sum -- 若按购买数量统计则用此字段
    FROM items_purchased
    GROUP BY buyer_id, listing_id
)
SELECT 
    listing_id,
    -- 总购买次数（按交易行数统计）
    SUM(buyer_transaction_count) AS purchases,
    -- 复购次数：仅累加买家超过首次购买的交易次数
    SUM(CASE WHEN buyer_transaction_count > 1 THEN buyer_transaction_count - 1 ELSE 0 END) AS repurchases,
    -- 复购率：处理除数为0的情况，转为百分比字符串
    CASE 
        WHEN SUM(buyer_transaction_count) = 0 THEN '0%'
        ELSE CONCAT(ROUND((SUM(CASE WHEN buyer_transaction_count > 1 THEN buyer_transaction_count - 1 ELSE 0 END) * 100.0) / SUM(buyer_transaction_count)), '%')
    END AS repurchase_rate
FROM buyer_listing_purchases
GROUP BY listing_id
ORDER BY listing_id;

代码逻辑解释 #

1. CTE子查询：先按buyer_id和listing_id分组，统计每个买家对每个Listing的交易次数（同时计算了购买数量总和，方便切换统计维度）
2. 主查询：
   • 用SUM(buyer_transaction_count)得到每个Listing的总交易次数
   • 用CASE语句筛选出买家交易次数大于1的部分，累加后得到复购次数
   • 用CASE处理复购率计算时的除数为0问题，确保结果格式为百分比字符串

测试数据验证 #

代入你提供的测试数据，最终结果将与你的示例描述完全匹配：

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