您可以：

select *, 1.0 * repurchases / purchases as repurchase_rate
from (
  select listing_id, sum(cnt) as purchases, sum(cnt - 1) as repurchases
  from (
    select listing_id, buyer_id, count(*) as cnt
    from items_purchased
    group by listing_id, buyer_id
  ) x
  group by listing_id
) y

字符串
测试结果：

listing_id  purchases  repurchases  repurchase_rate        
 ----------- ---------- ------------ ---------------------- 
 5678        3          1            0.33333
 3456        2          0            0.00000

型
请参见db<>fiddle上的运行示例。

在我的脑海中，你可以构建一个CTE，将特定listing_id的购买计数汇总为buyer_id，每个减去1以包括 * 仅 * 回购：

WITH repurchases AS
(
    SELECT
        listing_id,
        SUM(repurchases) AS repurchases
    FROM
    (
        SELECT
            listing_id,
            buyer_id,
            COUNT(1) - 1 AS repurchases
        FROM
            items_purchased
        GROUP BY
            listing_id,
            buyer_id
    ) repurchases_by_buyer
    GROUP BY
        listing_id
)
SELECT
    items_purchased.listing_id,
    COUNT(1) AS purchases,
    (SELECT repurchases FROM repurchases WHERE repurchases.listing_id = items_purchased.listing_id) AS repurchases
FROM
    items_purchased
GROUP BY
    items_purchased.listing_id

字符串
DB Fiddle
考虑到子查询的无偿使用，这可能不是计算效率最高的方法，但我相信还有进一步优化的空间来适应规模化的使用。

使用count()和count() - count(distinct)分别计算购买和回购。

SELECT listing_id, COUNT(buyer_id) purchases, 
COUNT(buyer_id) - COUNT(DISTINCT buyer_id) repurchases,
CONCAT(ROUND(100 * (1 - COUNT(DISTINCT buyer_id)/COUNT(buyer_id))), '%')  repurchase_rate
FROM items_purchased 
GROUP BY listing_id;

字符串
| listing_id|购买|回购|回购利率|
| --|--|--|--|
| 3456 | 2 | 0 |0%的百分比|
| 5678 | 3 | 1 |百分之三十三|
db fiddle

SELECT
  p.listing_id,
  COUNT(p.listing_id) AS purchases,
  COUNT(r.listing_id) AS repurchases,
  ROUND((COUNT(r.listing_id) / COUNT(p.listing_id)) * 100, 2) AS repurchase_rate
FROM
  (SELECT listing_id, buyer_id, MIN(quantity) AS first_purchase
   FROM items_purchased
   GROUP BY listing_id, buyer_id) AS first_purchases
LEFT JOIN items_purchased AS p
  ON first_purchases.listing_id = p.listing_id
LEFT JOIN items_purchased AS r
  ON first_purchases.listing_id = r.listing_id
  AND first_purchases.buyer_id = r.buyer_id
  AND r.quantity > first_purchases.first_purchase
GROUP BY p.listing_id;

字符串
此查询执行以下操作：

• 子查询first_purchases标识每个buyer_id和listing_id的第一次购买
• 然后，主查询LEFT JOIN items_purchased表两次：一次是计算所有购买（p），一次是计算回购（r）
• repurchases计数经过筛选，仅包括quantity数量大于first_purchase数量的采购
• 最后，它计算repurchase_rate并格式化输出

需要注意的是，上面的查询假设回购是指后续的采购数量大于第一次采购数量，请根据您具体的回购定义调整逻辑。