SQL: подсчет вернувшихся и новых посетителей на основе группы IP по дате

Вы можете найти возвращаемые IP-адреса, сгруппировав по IP-адресам и отфильтровав их, количество которых больше одного.

SELECT [ip]
FROM [logging]
group by [ip]
having count(*)>1

Затем вы можете использовать этот запрос в качестве фильтра для вашего основного запроса.

SELECT [ip], [date],count(*) as [Count]
FROM [logging]
where [ip] in
(
SELECT [ip]
FROM [logging]
group by [ip]
having count(*)>1
)
group by [ip],[date]

Что касается последней части вашего обновленного вопроса;

1. Ваш текущий запрос согласован. Вы показываете количество различных IP-адресов и сеансов, но ваше предложение не отражает этого. Ваше предложение Have рассчитывается по дате и группировке IP. Не уверен, специально ли вы это сделали. Может быть, ваша оговорка о наличии должна быть having count(distinct ip, session_id)>1.

2. Для окончательного набора результатов, не изменяя окончательный запрос, используйте его как подзапрос, например

   select DATE_FORMAT([date], '%Y-%m-%d %H') as [date] , count(*) from (
   SELECT [date], ip, count(distinct ip, session_id) as 'count' FROM logging GROUP BY ip HAVING count > 1 AND date(date) = date(now())
       ) as sub
       group by DATE_FORMAT([date], '%Y-%m-%d %H') 
   

ОБНОВЛЕНИЕ №2. Сначала совет: по возможности не используйте зарезервированные слова в качестве имен столбцов (например, дату). Пытался разбить проблему на части. Итоговый запрос выглядит грязным, его можно улучшить и сократить. Но для прояснения опасений я использовал несколько запросов. Ваша первая проблема - это подсчет уникальных sid и ip. Для простоты (поскольку оба являются строками) я использовал sid + ':' + ip как единственную уникальную строку. Еще одна проблема - группировка даты и часа. Итак, шаг за шагом:

1. Создание базовых справочных данных:

        SELECT FORMAT(dt, 'yyyy-MM-dd HH') as dt_H,
          [sid]+':'+[ip] as uniq
   
      FROM [mytable]
      where cast(dt as date)=cast(getdate() as date)
   

2. Поиск возвращаемых товаров по моей уникальной стоимости и дате - час:

   выберите dt_H, uniq, count () как раз из (SELECT FORMAT (dt, 'yyyy-MM-dd HH') as dt_H, [sid] + ':' + [ip] as uniq FROM [mytable] где cast (dt as date) = cast (getdate () as date)) как вспомогательная группа по dt_H, uniq имеет count () ›1

3. Если вы используете php, вы можете позже разделить поле uniq символом:. Но если вы хотите, чтобы это было в sql, мы присоединимся к основной таблице без отличительных столбцов:

   выберите отдельный return.dt_H, main. [sid], main. [ip], return.times из [mytable] в качестве основного внутреннего соединения (выберите dt_H, uniq, count (*) как раз из (SELECT FORMAT (dt, 'yyyy -MM-dd HH ') как dt_H, [sid] +': '+ [ip] как uniq

          FROM [mytable]
          where cast(dt as date)=cast(getdate() as date)
    ) as helper
      group by dt_H,uniq
      having count(*)>1
   

   ) как возвращается при возврате. uniq = main. [sid] + ':' + main. [ip] и FORMAT (main.dt, 'yyyy-MM-dd HH') = return.dt_H

4. Вы сказали, что вы заполняете пробелы с помощью PHP. Вы также можете сделать это в sql. Я бы предложил создать определяемую пользователем функцию (которая может пригодиться во многих других случаях), которая будет создавать числовые значения в заданном диапазоне: CREATE FUNCTION [dbo].[GetNumbers](@Start int, @Stop int) RETURNS TABLE AS RETURN WITH Numbers (N) AS ( SELECT @Start UNION ALL SELECT @Start + N-@Start+1 FROM Numbers WHERE N < @Stop ) SELECT N FROM Numbers GO

Использование:

SELECT N FROM [dbo].[GetNumbers] (0,23)

Это вернет таблицу со всеми часами дня. Вы можете использовать эту таблицу в сочетании с датой и левым соединением с указанным выше запросом. Таким образом, вы можете отображать все часы и 0 для времени возврата, если оно равно нулю.

PS: извините, не удалось заставить работать форматирование.