How to Efficiently Count Unique Website Visitors Per Site in MySQL?

Determining Unique Visitor Count from MySQL Data

To count the unique visitors to a website yesterday using MySQL, you might initially consider a query that groups by the user and timestamp:

SELECT
    DISTINCT `user_id` AS user,
    `site_id` AS site,
    `ts` AS time
FROM
    `cp_visits`
WHERE
    ts >= DATE_SUB(NOW(), INTERVAL 1 DAY)

However, relying solely on this distinction can lead to multiple results with the same site_id value. To resolve this, you need to aggregate the results by site_id and count the unique users and visits for each site:

SELECT
    COUNT(DISTINCT user_id) AS countUsers,
    COUNT(site_id) AS countVisits,
    site_id AS site
FROM
    cp_visits
WHERE
    ts >= DATE_SUB(NOW(), INTERVAL 1 DAY)
GROUP BY
    site_id

This query will provide a count of unique users (countUsers) and the total number of visits (countVisits) for each unique site_id. By grouping the results by site_id, you ensure that only distinct site logins are counted, avoiding multiple results for the same site.

The above is the detailed content of How to Efficiently Count Unique Website Visitors Per Site in MySQL?. For more information, please follow other related articles on the PHP Chinese website!