SQL查询需求：计算Store表中用户购买与销售次数的比值

数据老陈

2026-8-25

解决方案

针对指定RegionID下用户购买次数与销售次数的比值统计，以下是可行的SQL实现：

WITH AllUsers AS (
    -- 提取该区域所有出现过的唯一用户
    SELECT Buyer AS UserID FROM Store WHERE RegionID = 1
    UNION
    SELECT Seller AS UserID FROM Store WHERE RegionID = 1
),
PurchaseStats AS (
    -- 统计每个用户的购买次数
    SELECT Buyer AS UserID, COUNT(*) AS BuyTimes
    FROM Store
    WHERE RegionID = 1
    GROUP BY Buyer
),
SaleStats AS (
    -- 统计每个用户的销售次数
    SELECT Seller AS UserID, COUNT(*) AS SellTimes
    FROM Store
    WHERE RegionID = 1
    GROUP BY Seller
)
SELECT
    au.UserID,
    COALESCE(ps.BuyTimes, 0) AS BuyTimes,
    COALESCE(ss.SellTimes, 0) AS SellTimes,
    -- 处理销售次数为0的情况，避免除以0报错
    CASE
        WHEN COALESCE(ss.SellTimes, 0) = 0 THEN NULL
        ELSE CAST(ps.BuyTimes AS DECIMAL) / ss.SellTimes
    END AS BuySellRatio
FROM AllUsers au
LEFT JOIN PurchaseStats ps ON au.UserID = ps.UserID
LEFT JOIN SaleStats ss ON au.UserID = ss.UserID
ORDER BY au.UserID;

关键说明

AllUsers：通过UNION去重，确保覆盖所有在该区域有购买或销售行为的用户，不会遗漏只买不卖或只卖不买的用户。
PurchaseStats/SaleStats：分别统计购买和销售次数，通过WHERE RegionID = 1限定目标区域，提升查询效率。
主查询：
- 使用LEFT JOIN保证每个用户都能出现在结果中，哪怕没有对应购买/销售记录。
- COALESCE将NULL的次数转换为0，让结果展示更直观。
- CASE语句处理销售次数为0的场景，避免触发除以0的错误，此时比值设为NULL。