Mysql计算重复行的百分比

Question

我正在尝试计算每个餐厅在我的系统中的重复订单数量。这被定义为使用其电子邮件地址（eo_email）下单超过一次的用户数量。以下是模式示例：

这是代表我的餐厅的表格：

CREATE TABLE IF NOT EXISTS `lf_restaurants` (
  `r_id` int(8) NOT NULL AUTO_INCREMENT,
  `r_name` varchar(128) DEFAULT NOT NULL,
  PRIMARY KEY (`r_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

INSERT INTO `lf_restaurants` (`eo_id`, `eo_ref_id`) VALUES
('1', 'Restaurant X'),
('2', 'Cafe Y');

这是我的订单表:

CREATE TABLE IF NOT EXISTS `ecom_orders` (
  `eo_id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `eo_ref_id` varchar(12) DEFAULT NOT NULL,
  `eo_email` varchar(255) DEFAULT NOT NULL,
  `eo_order_parent` int(11) NOT NULL,
  PRIMARY KEY (`eo_id`),
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;

INSERT INTO `ecom_orders` (`eo_id`, `eo_ref_id`, `eo_email`, `eo_order_parent`) VALUES
('', '', '[e-mail]', '1'),
('', '', '[e-mail]', '1'),
('', '', '[e-mail]', '1'),
('', '', '[e-mail]', '1'),
('', '', '[e-mail]', '1'),
('', '', '[e-mail]', '1'),
('', '', '[e-mail]', '1'),
('', '', '[e-mail]', '1'),
('', '', '[e-mail]', '1'),
('', '', '[e-mail]', '1'),
('', '', '[e-mail]', '2'),
('', '', '[e-mail]', '2'),
('', '', '[e-mail]', '2'),
('', '', '[e-mail]', '2');

餐厅X（r_id 1）有10个订单。用户[e-mail]和[e-mail]多次从该餐厅订购，而[e-mail]、[e-mail]和[e-mail]只订购了一次，因此需要返回40％。

Cafe Y（r_id 2）有4个订单。用户[e-mail]已经下单两次，而用户[e-mail]和[e-mail]只下单了一次，因此需要返回33％。

我不确定已经得到的内容是否有用，因为我一直遇到“子查询有多个结果”的问题。如果我将该子查询包装在其自己的虚拟查询中并进行计数，它将不允许我使用主查询中需要的字段，例如r_id。但是，以下是我的尝试：

SELECT r_name,
    (SELECT COUNT(*) AS cnt_users
        FROM (
            SELECT *
            FROM ecom_orders
            WHERE eo_order_parent = r_id
            GROUP BY eo_email
        ) AS cnt_dummy
    ) AS num_orders,
    (SELECT COUNT(*) AS cnt
        FROM ecom_orders
        WHERE eo_order_parent = r_id
        GROUP BY eo_order_parent, eo_email

    ) AS num_rep_orders
    FROM lf_restaurants
    ORDER BY num_orders DESC

子查询表示它无法识别，我猜这是由于执行顺序的原因。 子查询返回多行，但实际上我只想返回一个单一值，如果我像子查询一样处理，那么就会遇到不存在的问题。 所以我的问题是：如何在不遇到“子查询有多个行”和“不存在”的问题情况下获得所需的这两个值？

然后，我可以从这两个值中计算出百分比，一切都应该很顺利 :) 非常感谢您的帮助！

- Horse

Answer 1

餐厅X（r_id 1）有10个订单。用户[e-mail]和[e-mail]已经多次从该餐厅订餐，而[e-mail]、[e-mail]和[e-mail]仅订餐一次，因此需要返回40％。 Cafe Y（r_id 2）有4个订单。用户[e-mail]已经订餐两次，而用户[e-mail]和[e-mail]仅订餐一次，因此需要返回33％。

好的，那么我们先从获取重复客户的数量开始。

SELECT eo_order_parent, eo_email, COUNT(eo_email) AS orders FROM ecom_orders
    GROUP BY eo_order_parent, eo_email
    HAVING orders > 1;

不同客户总数

SELECT eo_order_parent, COUNT(eo_email) FROM ecom_orders
    GROUP BY eo_order_parent;

但是我们可以一次性完成这个操作：

SELECT eo_order_parent,
    SUM(CASE WHEN orders > 1 THEN 1 ELSE 0 END) AS repeats,
    SUM(1) AS total FROM
    (
        SELECT eo_order_parent, eo_email, COUNT(*) AS orders FROM ecom_orders
            GROUP BY eo_order_parent, eo_email
    ) AS eo_group_1
GROUP BY eo_order_parent;

这将会得到：

+-----------------+---------+-------+
| eo_order_parent | repeats | total |
+-----------------+---------+-------+
|               1 |       2 |     5 |
|               2 |       1 |     3 |
+-----------------+---------+-------+
2 rows in set (0.00 sec)

那么2/5就是你的40%，1/3是33%。

- LSerni

---

哥们儿...！回答得非常好，解决方案非常优雅 :) 非常感谢！ - Horse

Answer 2

以下查询计算每个餐厅的重复顾客数量和总顾客数量。

SELECT
  u.r_id,
  u.r_name,
  SUM(u.no_orders > 1) AS repeats,
  SUM(u.no_orders) AS orders,
  COUNT(u.eo_email) AS customers
FROM (
    SELECT
      r.*,
      o.eo_email,
      COUNT(o.eo_id) AS no_orders
    FROM lf_restaurants r
    LEFT JOIN ecom_orders o ON o.eo_order_parent = r.r_id
    GROUP BY o.eo_email
) u
GROUP BY
  r.r_id;

子查询首先计算每个客户/餐厅对的订单数量。外部查询从此计算每个餐厅的客户数量、重复客户数量和总客户数量。您还可以计算百分比（但这不必在查询中完成）。

- Martijn

---

谢谢您的回答，但是在字段列表中找不到“u.no_order”列。 - Horse