使用MySQL计算签出转换

Question

我正试图使用以下查询来计算签出转换，但我一直收到错误：

SELECT
(SELECT seller.name, count(checkout.id) AS 'Completed_checkout'
FROM checkout
JOIN seller
ON seller.id = checkout.seller_id
WHERE checkout.completed = 1
GROUP BY seller.name)
/
(SELECT seller.name, count(checkout.id) AS 'Total_Checkouts'
FROM checkout
JOIN seller
ON seller.id = checkout.seller_id
GROUP BY seller.name) * 100 
AS 'Checkout_Converstion';

能够计算后的最终目标是：

• 按卖家筛选，总结账次数超过15次
• 按DESC排序
• 到那时限制5条第一条记录

关于如何实现这一点，有什么想法吗？

Answer 1

简化一下如何？

SELECT s.name, AVG(co.completed) as completed_ratio
FROM checkout co JOIN
seller s
ON s.id = co.seller_id
GROUP BY s.name;

如果你想要计数：

SELECT s.name, AVG(co.completed = 1) as completed_ratio,
SUM(c.completed = 1) as num_completed,
COUNT(*) as total
FROM checkout co JOIN
seller s
ON s.id = co.seller_id
GROUP BY s.name;

这些利用了MySQL将布尔表达式视为数字上下文中的数字的事实；1〃；对于真和"；0"；为false。

Answer 2

您可以使用1个查询：

SELECT s.name, 
100 * AVG(c.completed) AS Checkout_Conversion
FROM checkout c JOIN seller s
ON s.id = c.seller_id
GROUP BY s.id, s.name
HAVING SUM(c.completed) > 15
ORDER BY Checkout_Conversion DESC
LIMIT 5