example - select group by mysql

Mysql sum distinct basado en otras columnas que contienen múltiples LEFT JOIN

Question

Tengo 5 mesas que me gustaría DEJAR JUNTAR juntas. Las tablas son: visitantes, ofertas, contratos1, contratos2 y contratos3.

CONSULTA:

SELECT 
        count(DISTINCT visitors.ID) as visitors, 
        sum(
        CASE
        WHEN offers.ACTIVE = 1 THEN 1
        ELSE 0
        END) as offers, 
        count(contracts1.ID) as contracts1, sum(contracts1.PRICE) as sum_contracts1, 
        count(contracts2.ID) contracts2, 
        sum(
        CASE
        WHEN contracts2.PAYMENT = ''YEARLY'' THEN contracts2.PRICE
        WHEN contracts2.PAYMENT = ''TWICE'' THEN contracts2.PRICE*2
        ELSE contracts2.PRICE*4
        END) as sum_contracts2,
        count(contracts3.ID) as contracts3, sum(contracts3.PRICE) as sum_contracts3
        FROM visitors 
        LEFT JOIN offersON offers.VISITOR_ID = visitors.ID AND (offers.IP > 100 OR offers.IP < 0)
        LEFT JOIN contracts1 ON 
        (offers.ID = contracts1.ID_OFFER)
        LEFT JOIN contracts2 ON 
        (offers.ID = contracts2.ID_OFFER)
        LEFT JOIN contracts3 ON 
        (offers.ID = contracts3.ID_OFFER)
        WHERE  visitors.TIME >= ''2017-01-01 00:00:00'' AND visitors.TIME <= ''2017-05-25 23:59:59''

El problema aquí es que los contratos1, los contratos2 y los contratos3 no tienen una columna común para unirse. Entonces en lugar de 20 filas para contratos1, 30 para contracs2 y 50 para contratos3, obtengo toda la combinación para todos ellos. Porque se unen según los visitantes y ofrece tablas. El simple GROUP BY al final de la consulta normalmente resolvería el problema, pero si uso GROUP BY en el END para una de esas tablas (o todas ellas), creará VARIAS FILAS en lugar de 1 que quiero. Y también borrará todos los otros resultados para la parte donde cuento visitantes por ID y también ofertas por ID ... Puedo usar DISTINCT en count () partes de SELECT pero ninguna en la suma () porque PRICE de los contratos puede ser el mismo a pesar de que los ID no lo son (usted sabe, por ejemplo, que 2 chocolates son 2 filas con diferentes ID pero el mismo PRECIO por cada 10 dólares).

Entonces mi pregunta es:

¿Hay alguna forma de SUMAR solo los PRECIOS de los contratos1, los contratos2 y los contratos3, que tienen DISTINCT ID y, aunque se deshacen de sumar los duplicados? ¿Y es posible sin crear VIEW?

También probé GROUP BY dentro de IZQUIERDA, pero de nuevo cuando dejé de juntarme, las 3 mesas de contratos juntas, aunque las agrupé antes de terminar con duplicados.

Ejemplo de resultado esperado:

En ese horizonte de tiempo que mencioné anteriormente, esperaría: 80 visitantes que tienen 35 ofertas y 5 contratos1 con un importe de 1000 euros, 12 contratos2 con un importe de 686 euros y 3 contratos3 con un importe de 12 euros. Es UNA FILA con 8 columnas de datos.

En lugar del resultado esperado obtuve: 80 visitantes, 35 ofertas, 180 contratos1 (la suma también es mala), 180 contratos2 (la suma también es mala), 180 contratos3 (la suma también es mala).

Answer 1

Solo una prueba de concepto en la que no tengo en cuenta las limitaciones de tiempo y actividad, así como el tipo de pago, pero ¿no podría ser algo así?

SELECT
   VISITOR_ID,
   SUM(CASE WHEN TYPE="contract1" THEN 1 else 0 END) as c1_count,
   SUM(CASE WHEN TYPE="contract1" THEN PRICE else 0 END) as c1_total_price,
   SUM(CASE WHEN TYPE="contract2" THEN 1 else 0 END) as c2_count,
   SUM(CASE WHEN TYPE="contract2" THEN PRICE else 0 END) as c2_total_price,
   SUM(CASE WHEN TYPE="contract3" THEN 1 else 0 END) as c3_count,
   SUM(CASE WHEN TYPE="contract3" THEN PRICE else 0 END) as c3_total_price 
FROM (
    (SELECT "contract1" as TYPE, ID, PRICE, ID_OFFER, PAYMENT FROM contracts1) 
    UNION
    (SELECT "contract2" as TYPE, ID, PRICE, ID_OFFER, PAYMENT FROM contracts2)
    UNION
    (SELECT "contract3" as TYPE, ID, PRICE, ID_OFFER, PAYMENT FROM contracts3)
 ) as all_contracts 
 JOIN offers on offers.id = all_contracts.ID_OFFER
 JOIN visitors on visitors.ID = offers.VISITOR_ID
 GROUP BY visitors.ID

La idea es que primero fusione los diferentes contratos en un resultado donde almacene su tipo en una columna llamada "TYPE" (ese es el propósito de las consultas UNION) y una vez que tenga una tabla tan agradable donde cada contrato sea exactamente una vez, usted puede obtener el resultado deseado bastante sencillo. Acabo de resumir cómo se obtiene la suma y el recuento para cada tipo de contrato. Por supuesto, la consulta final sería un poco más complicada, pero la idea central debería ser la misma.

Pero a pesar de su afirmación de que no desea utilizar vistas (temporales), le sugiero que lo intente. Tengo la sensación de que al poner esos "all_contracts" unidos a ofertas y visitantes en una vista temporal mejoraría el rendimiento, si esa es su preocupación, sin hacer que la consulta sea demasiado fea, principalmente en el caso en que desee ver las estadísticas solo para un visitante o filtrarlas más (por tiempo, actividad, etc.), porque las filas innecesarias no se materializarán . Pero eso es solo una impresión ya que no he intentado la consulta en un conjunto de datos más grande: puedes jugar con él.

Answer 2

Con CTE ( Compatible con MariaDB 10.2.1 ) escribiría algo como esto:

WITH v AS (
    SELECT ID as VISITOR_ID
    FROM visitors 
    WHERE visitors.TIME >= ''2017-01-01 00:00:00''
      AND visitors.TIME <= ''2017-05-25 23:59:59''
), o AS (
    SELECT offers.ID as ID_OFFER
    FROM v
    JOIN offers USING(VISITOR_ID)
    WHERE offers.ACTIVE = 1
      AND (offers.IP > 100 OR offers.IP < 0)
), c1 AS (
    SELECT count(*) as contracts1, sum(contracts1.PRICE) as sum_contracts1
    FROM o JOIN contracts1 USING(ID_OFFER)
), c2 AS (
    SELECT
        count(*) contracts2, 
        sum(CASE contracts2.PAYMENT
            WHEN ''YEARLY'' THEN contracts2.PRICE
            WHEN ''TWICE''  THEN contracts2.PRICE*2
            ELSE contracts2.PRICE*4
        END) as sum_contracts2
    FROM o JOIN contracts2 USING(ID_OFFER)
), c3 AS (
    SELECT count(*) as contracts3, sum(contracts3.PRICE) as sum_contracts3
    FROM o JOIN contracts3 USING(ID_OFFER)
)
    SELECT c1.*, c2.*, c3.*,
        (SELECT count(*) FROM v) as visitors,
        (SELECT count(*) FROM o) as offers,
    FROM c1, c2, c3;

Sin CTE puedes reescribirlo para usar tablas temporales:

CREATE TEMPORARY TABLE v AS
    SELECT ID as VISITOR_ID
    FROM visitors 
    WHERE visitors.TIME >= ''2017-01-01 00:00:00''
      AND visitors.TIME <= ''2017-05-25 23:59:59'';

CREATE TEMPORARY TABLE o AS
    SELECT offers.ID as ID_OFFER
    FROM v
    JOIN offers USING(VISITOR_ID)
    WHERE offers.ACTIVE = 1
      AND (offers.IP > 100 OR offers.IP < 0);

CREATE TEMPORARY TABLE c1 AS
    SELECT count(*) as contracts1, sum(contracts1.PRICE) as sum_contracts1
    FROM o JOIN contracts1 USING(ID_OFFER);

CREATE TEMPORARY TABLE c2 AS
    SELECT
        count(*) contracts2, 
        sum(CASE contracts2.PAYMENT
            WHEN ''YEARLY'' THEN contracts2.PRICE
            WHEN ''TWICE''  THEN contracts2.PRICE*2
            ELSE contracts2.PRICE*4
        END) as sum_contracts2
    FROM o JOIN contracts2 USING(ID_OFFER);

CREATE TEMPORARY TABLE c3 AS
    SELECT count(*) as contracts3, sum(contracts3.PRICE) as sum_contracts3
    FROM o JOIN contracts3 USING(ID_OFFER);

SELECT c1.*, c2.*, c3.*,
    (SELECT count(*) FROM v) as visitors,
    (SELECT count(*) FROM o) as offers,
FROM c1, c2, c3;