0
135
Viewsmysql cuenta el número de nuevos visitantes por día
Versión de MySQL: 5.7 Aquí está la tabla de usuarios :
+------------+------+ | date | uid | +------------+------+ | 2020-06-29 05:00:00 | 352 | | 2020-06-29 08:00:00 | 354 | | 2020-06-29 09:25:53 | 354 | | 2020-06-30 08:00:00 | 863 | | 2020-06-30 09:00:01 | 352 | | 2020-06-30 09:59:59 | 352 | | 2020-07-01 07:00:00 | 358 | | 2020-07-01 09:00:00 | 358 | +------------+------+Quiero contar la cantidad de nuevos visitantes por día, pero aquí hay una condición importante: los nuevos visitantes del día no pueden ser visitados antes.
quiero el resultado :
Result: +------------+------------------+ | date | new_user_count | +------------+------------------+ | 2020-06-29 | 2 | | 2020-06-30 | 1 | | 2020-07-01 | 1 | +------------+------------------+El resultado anterior es equivalente a estos tres sql:
2020-06-29 (352,354) : select count( distinct uid ) as new_user_count from users where DATE(date) = '2020-06-29' and uid not in ( select distinct uid from users where date < '2020-06-29 05:00:00'); #2 2020-06-30 (863): select count( distinct uid ) as new_user_count from users where DATE(date)= '2020-06-30' and uid not in ( select distinct uid from users where date < '2020-06-30 08:00:00'); # 1 2020-07-01 (358): select count( distinct uid ) as new_user_count from users where DATE(date)= '2020-07-01' and uid not in ( select distinct uid from users where date < '2020-07-01 07:00:00'); # 1No lo había pensado hasta ahora, gracias Aquí está la tabla de usuarios en línea
3 answers
Answer question0
Puede intentar usar una subconsulta correlacionada para verificar si cada visita de usuario es la primera o no:
SELECT date, SUM(CASE WHEN NOT EXISTS (SELECT 1 FROM users u2 WHERE u2.date < u1.date AND u2.uid = u1.uid) THEN 1 ELSE 0 END) AS new_user_count FROM (SELECT DISTINCT date, uid FROM users) u1 GROUP BY date; 
Manifestación
La lógica anterior en realidad se lee de manera sencilla y dice que cuente un registro de usuario solo si no podemos encontrar que el mismo usuario aparezca en la tabla en una fecha posterior. Tenga en cuenta que uso selecciones distintas, porque parece que en sus datos un usuario determinado puede aparecer más de una vez en la misma fecha. Estos datos falsificarían la subconsulta correlacionada anterior, por lo que nos aseguramos de que un usuario determinado aparezca solo una vez en una fecha determinada (y además, de todos modos, un usuario solo se puede contar una vez al día).
0
SELECT date, ( SELECT COUNT(DISTINCT u1.uid) FROM users u1 WHERE NOT EXISTS( SELECT * FROM users u2 WHERE u2.uid = u1.uid AND u2.date < u0.date ) AND u1.date = u0.date ) FROM users u0 GROUP BY date ;0
-- get date and the amount of distinct users SELECT date, COUNT(DISTINCT uid) -- from users table FROM users -- only when there not exists a row WHERE NOT EXISTS ( SELECT NULL -- may use any literal value instead of NULL -- in the table FROM users u -- with this user id WHERE users.uid = u.uid -- but earlier (less) date AND users.date > u.date ) GROUP BY date;