Cómo agrupar 3 tablas mysql para obtener visitas diarias únicas para un autor de articulos (items)? – mysql

Pregunta:


Tengo 3 tablas de Mysql.

Visitas

Registra los IP de los usuario, fecha, los ID de los item que han visitado.

| id | item_id |   ip_address    |       fecha         |
|------------------------------------------------------|
| 1  |    3    | 123.123.123.123 | 2018-03-01 17:00:30 |
| 1  |    1    | 123.123.123.123 | 2018-03-03 17:00:30 |
| 1  |    5    | 000.000.000.000 | 2018-03-04 17:00:30 |
| 1  |    4    | 123.123.123.123 | 2018-03-01 17:00:30 |
| 1  |    4    | 999.999.999.999 | 2018-03-02 17:00:30 |
| 1  |    1    | 888.888.888.888 | 2018-03-12 17:00:30 |

Items

Tiene datos sobre los items, entre ellos: tiene el author_id (el ID del creador del item).

| id | autor_id |
-----------------
| 1  |     2    |
| 2  |     1    |
| 3  |     1    |
| 4  |     2    |
| 5  |     1    |

Autores

Esta tabla tiene la lista de autores (creadores) con sus datos.

| id | name |  surname  |
-------------------------
| 1  | Jose | Rodriguez |
| 2  | Jesus|   Abadia  |

Necesito obtener: cuantas visitas diarias unicas un autor ha obtenido en los ultimo 30 dias. Esto es a lo que he llegado:

SELECT item_id, ip_address FROM `visitas`
WHERE 1
    AND date > DATE_SUB(NOW(),INTERVAL 30 DAY)
GROUP BY item_id, ip_address

Por ejemplo:

|    fecha   |  visitas  | 
--------------------------
| 2018-03-01 |     2     |
| 2018-03-02 |     10    |
| 2018-03-02 |     8     |
...

Preguntado por: Lukas

the-breaker

si lo único que necesitas es la consulta SQL, puedes utilizar la siguiente:

SELECT DATE_FORMAT(W.fecha,  '%Y-%m%-%d' ) AS fecha, COUNT( * ) AS visitas
FROM (
    SELECT DISTINCT V.fecha, V.ip_address
    FROM visita V, Item I
    WHERE V.item_id = I.id
    AND I.autor_id =1
) as W
GROUP BY DATE_FORMAT(W.fecha,  '%Y-%m%-%d' )
ORDER BY W.fecha 
LIMIT 0 , 30

Con esta consulta obtienes el numero de visitas que un autor recibió por día de direcciones IP diferentes en los últimos 30 dias, el único dato de entrada es el id del autor, si necesitas que te explique alguna parte del código puedo editar la respuesta, espero que te sea de ayuda, saludos.

La solucion propuesta por @the-beaker funciona perfectamente excepto las ultimas dos lineas. Me Causan el siguiente error:

#1055 – Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘W.date’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Pero modificando un poco funciona bien!

SELECT DATE_FORMAT(W.date,  '%Y-%m%-%d' ) AS date, COUNT( * ) AS visitas
FROM (
    SELECT DISTINCT V.date, V.ip_address
    FROM views_unique V, items I
    WHERE V.item_id = I.id
    AND I.author_id = 7
    AND V.date > DATE_SUB(NOW(),INTERVAL 30 DAY)
) as W
GROUP BY DATE_FORMAT(W.date,  '%Y-%m%-%d' )

Fuente

Tags:

Add a Comment

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *