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

Related Posts:

Actualizar información con ajax php mysql – php jquery mysql
Pregunta: Tengo esta simple consulta que lo que simplemente me muestra las visitas de la web, pero quisiera que fuera automático con ajax ya que ...
Hacer update a registros de mensajes para marcarlos como leido – mysql
Pregunta: Tengo este proceso almacenado, necesito hacer varios update para marcar como leidos los mensajes. El problema es que solo se hace 1 update y ...
¿Location manager Android? Experiencias – android mysql android-studio
Pregunta: He desarrollado una aplicación Gps que envía información a Mysql cada 10 seg atraves de locationmanager. ¿Según su experiencia hasta cuanto es posible disminuir ...
¿Cómo utilizar `count` para contabilizar la cantidad de registros para distintos valores de un campo en mysql? – php mysql sql
Pregunta: Resulta que tengo un campo llamado ESTADO en mi tabla. En este campo hay varias estados de pedido que son: LOGÍSTICA, PACKING, PICKING, CARTERA, ...
Insertar en una tabla registros basados en el resultado de una consulta – mysql sql
Pregunta: Estoy intentando que insertar una fila nueva con unos datos por cada valor que de un SELECT. Os dejo la última query que se me ...
¿Como usar una sentencia Case en Mysql? – mysql sql
Pregunta: Tengo dos tablas una es la de detalle de producto y la otra es la de producto, el problema radica en que yo quiero ...
buscar las 3 fechas más antiguas en SQL – mysql sql
Pregunta: Tengo un problema con una consulta. La tabla tiene los siguientes campos: Tabla1 --------+-----------+---------+------------------ Id | Inicio | Fin ...
Update con MYSQL PHP – php mysql mysqli
Pregunta: Tengo este código, pero no hace el update, y no sé porque. <?php // if ($_POST == "" || $_POST == "" || $_POST == ""){ ...
¿Cómo vincular la tabla usuarios con empresas en bases de datos? – php mysql base-de-datos
Pregunta: Tengo una tabla de usuarios y empresas, con relación uno a muchos (una empresa tiene varios usuarios). Pero al realizar un formulario en PHP, un ...
Recoger variable localStorage AJAX desde archivo PHP – php mysql ajax
Pregunta: Mi duda es saber si es posible recoger una variable en localStorage desde PHP sin necesidad de enviarla. Pretendo recoger datos en una consulta donde ...
Sentencia SQL para saber si el usuario y contraseña ingresados en el login son iguales a los guardados en mi base de datos, programa en Java – java mysql
Pregunta: En la tabla "usuario" de la base de datos habrá un registro siempre con un usuario y contraseña para poder iniciar sesión por primera ...
solo me muestra el primer registro de mi consulta – php mysql sql
Pregunta: Hola amigos tengo un problema con mi consulta, resulta que estoy haciendo una consulta con tablas relacionadas mi tabla: inscripción_proyectos la relaciono con mi ...
Clonar base de datos en MySQL – mysql
Pregunta: Estaba clonando una base de datos en MySQL mediante el siguiente comando: [email protected]: mysqldump -u root -p MyOriginalDatabase | mysql -u root -p MyDatabaseCopy Y el ...
Mostrar dos datos de una misma tabla en diferentes INPUT – php mysql
Pregunta: Poseo una tabla ARTICULOS que tiene dos campos: el primero Articulo (numero entero) el segundo Descripcion (varchar) En mi sistema muestro un select con todos los ...
Clausula condicional AND Mysql – mysql
Pregunta: Esta consulta recibe dos parámetros, lo que pretendo es que la segunda condicional participe de la consulta solo si el valor que recibe es ...
Tags:

Add a Comment

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