0
141
Vistasmysql counts the number of new visitors by day
MySQL version: 5.7 Here is users table:
+------------+------+
| 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 |
+------------+------+
I want to count the number of new visitors per day,But there is an important condition here that new visitors of the day cannot be visited before.
I want the result:
Result:
+------------+------------------+
| date | new_user_count |
+------------+------------------+
| 2020-06-29 | 2 |
| 2020-06-30 | 1 |
| 2020-07-01 | 1 |
+------------+------------------+
The above result is equivalent to these three 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'); # 1
I haven't thought of it until now, thanks Here is Online users table
3 Respuestas
Responde la pregunta0
You could try using a correlated subquery to check if each user visit be the first or not:
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;
The above logic actually reads straightforward, and says to count a user record only if we cannot find that same user appearing in the table at some later date. Note that I use distinct selects, because it appears that in your data a given user might appear more than once on the same date. This data would spoof the above correlated subquery, so we ensure that a given user appear only once on a given date (and besides, one user can only be counted once per day anyway).
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;