repetidos - Mysql calcula el porcentaje de filas repetidas

select campo repetido mysql

Question

Así que estoy tratando de calcular la cantidad de pedidos repetidos en mi sistema por restaurante. Esto se define como la cantidad de usuarios (según su dirección de correo electrónico, eo_email) que han pedido más de una vez desde ese restaurante. Ejemplos bajo el esquema

Aquí está la tabla que representa mis restaurantes

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'');

Y esta es mi tabla de pedidos

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
('''', '''', ''[email protected]'', ''1''),
('''', '''', ''[email protected]'', ''1''),
('''', '''', ''[email protected]'', ''1''),
('''', '''', ''[email protected]'', ''1''),
('''', '''', ''[email protected]'', ''1''),
('''', '''', ''[email protected]'', ''1''),
('''', '''', ''[email protected]'', ''1''),
('''', '''', ''[email protected]'', ''1''),
('''', '''', ''[email protected]'', ''1''),
('''', '''', ''[email protected]'', ''1''),
('''', '''', ''[email protected]'', ''2''),
('''', '''', ''[email protected]'', ''2''),
('''', '''', ''[email protected]'', ''2''),
('''', '''', ''[email protected]'', ''2'');

Entonces Restaurant X (r_id 1) tiene 10 pedidos. Los usuarios [email protected] y [email protected] han pedido varias veces a ese restaurante, y [email protected], [email protected] y [email protected] solo han ordenado una vez, por lo que tendrían que regresar. 40%

Cafe Y (r_id 2) tiene 4 pedidos. El usuario [email protected] ha ordenado dos veces, los usuarios [email protected] y [email protected] solo han pedido una vez, por lo que tendrían que devolver el 33%

No estoy seguro de publicar lo que tengo ya va a ser muy bueno, ya que sigo encontrándome con ''Subconsulta tiene más de 1 resultado'' o si envuelvo esa subconsulta en su propia consulta ficticia con un recuento, no me deja usar campos I necesidad de la consulta principal como r_id. Pero aquí va:

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

La subconsulta num_orders dice que no reconoce r_id, ya que supongo que esto se debe al orden en que se ejecutan las cosas

La subconsulta num_rep_orders regresa como varias filas, pero realmente quiero que vuelva con un solo valor, lo cual podría hacer si lo hiciera como la subconsulta num_orders pero luego se ejecutaría en el problema r_id doesnt exists.

Entonces mi pregunta es: ¿Cómo obtengo estos valores que necesito sin ejecutar en la subconsulta tiene más de 1 fila, y r_id no existe?

Luego, a partir de esos 2 valores, puedo calcular el porcentaje y todos deberían ser gravy :) ¡Cualquier ayuda muy apreciada!

Answer 1

Entonces Restaurant X (r_id 1) tiene 10 pedidos. Los usuarios [email protected] y [email protected] han pedido varias veces a ese restaurante, y [email protected], [email protected] y [email protected] solo han ordenado una vez, por lo que tendrían que regresar. 40%

Cafe Y (r_id 2) tiene 4 pedidos. El usuario [email protected] ha ordenado dos veces, los usuarios [email protected] y [email protected] solo han pedido una vez, por lo que tendrían que devolver el 33%

Bueno. Comencemos por obtener el número de clientes que se repiten.

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

Y el número total de clientes diferentes

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

Pero podemos hacer esto de una vez:

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;

Esto da:

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

Entonces 2/5 es su 40%, y 1/3 es 33%.

Answer 2

La siguiente consulta calcula el número de clientes recurrentes y el número total de clientes por restaurante

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;

La subconsulta primero calcula el número de pedidos por par de cliente / restaurante. La consulta externa calcula a partir de esto el número de clientes, el número de clientes que se repiten y el número total de clientes por restaurante. También puede calcular el porcentaje (pero esto no tiene que hacerse en la consulta).