Как выбрать только количество повторяющихся данных, которые появляются более одного раза, и считать каждую строку только один раз?

#mysql #count

#mysql #количество

Вопрос:

Я хочу подсчитать возвращающихся посетителей для заданного диапазона дат. Каждое ipAddress появление более одного раза должно вычисляться как возвращающийся посетитель. Как это можно сделать?

Таблица

 ipAddress       | last_update
416.246.227.151 | 2020-11-11 19:29:45
416.246.227.151 | 2020-11-11 20:29:45
173.252.127.119 | 2020-11-11 21:29:45
816.246.227.151 | 2020-11-13 13:53:16
816.246.227.151 | 2020-11-13 15:53:16
816.246.227.151 | 2020-11-13 19:53:16
373.252.127.119 | 2020-11-13 22:53:16
673.252.127.119 | 2020-11-13 20:53:16

Запрос

 SELECT last_update, COUNT(ipAddress) as returningVisitor 
FROM geolocation 
WHERE last_update BETWEEN '2020-11-01' AND '2020-12-01' 
GROUP BY date(last_update)
HAVING (returningVisitor>1)

Результат

 last_update         | returningVisitor
2020-11-11 19:29:45 | 3
2020-11-13 19:53:16 | 5

Желаемый результат

 last_update         | returningVisitor
2020-11-11 19:29:45 | 1
2020-11-13 19:53:16 | 1

На самом деле результатом должен быть 1 возвращающийся посетитель для обеих дат.
Я пробовал, COUNT(DISTINCT ipAddress) и результат все еще не очень хорош.

1. Вы не задали очень полный вопрос, потому что нет образцов данных. Можете ли вы добавить это тоже?

2. @TimBiegeleisen: обновлено с табличной информацией для облегчения понимания.

3. Все еще неясно, каковы ваши критерии. «Сколько адресов имели более 1 посещения в этом диапазоне дат» легко выполняется с помощью count в подзапросе, но ваш «желаемый результат» имеет столбец datetime, неясно, откуда это должно исходить.

4. @hobbs: Я не знаю, насколько это может быть понятнее. Мне нужно отображать возвращающихся посетителей за дату, подсчитывая повторяющийся IP-адрес. Если IP-адрес появляется более одного раза в день, что ж, это возвращающийся посетитель. Мы считаем это один раз.

Ответ №1:

Мы можем попробовать справиться с этим с помощью двухуровневой агрегации. Сначала агрегируйте по дате и IP-адресу, чтобы сгенерировать количество посещений для каждой даты / IP, а затем ограничьте только повторные посещения на каждую дату. Затем объедините этот результат только по дате и подсчитайте количество повторных посетителей со всех IP-адресов.

 WITH cte AS (
    SELECT DATE(last_update) AS last_update, ipAddress
    FROM geolocation
    WHERE last_update >= '2020-11-01' AND last_update < '2020-12-01' 
    GROUP BY DATE(last_update), ipAddress
    HAVING COUNT(*) > 1
)

SELECT last_update, COUNT(*) AS returningVisitor
FROM cte
GROUP BY last_update;