SQL:根据IP组的日期统计返回和新访客

Question

我有一个图表，显示了基于IP组的独立访问者按日期(今天每小时)。

现在我想将这些数据根据IP和session_id分离到新访客和回访访客，并按日期(今天每小时)分组。我如何用SQL查询做到这一点?这可能吗?

查询应该在表中查找是否已经存在带有session_id的IP。那么它就是一个回头客。否则就是新访客。我不知道该怎么做。

查询我必须计算唯一的IP并按今天每小时对它们进行分组:

SELECT DISTINCT DATE_FORMAT(`date`, '%Y-%m-%d %H') as 'dates', COUNT(DISTINCT `ip`) as 'count' FROM `logging` WHERE DATE(date) = DATE(NOW()) GROUP BY `dates`

现在它显示我:

Dates            Count
2021-02-04 00    10
2021-02-04 01    8
2021-02-04 02    5

等。

我要的是:

Dates            Count new IP      Count returning IP
2021-02-04 00    2                 8
2021-02-04 01    4                 4
2021-02-04 02    2                 3

新IP:检查IP是否存储在只有一个已知session_id的表中。返回的IP:检查表中是否存储有多个不同的session_id

提前感谢!

UPDATE # 1:

现在我有以下查询来统计今天的回访人数:

SELECT date, ip, count(distinct ip, session_id) as 'count' FROM logging GROUP BY ip HAVING count > 1 AND date(date) = date(now())

结果例如:

date                    ip                   count (returning visitors)
2021-02-05 08:24:56     62.163.91.178        2
2021-02-05 10:24:15     77.163.91.223        6
2021-02-05 08:49:51     77.173.17.157        13

我如何改变这个查询按日期分组来得到这个?:

date                    count (returning visitors)
2021-02-05 08           15
2021-02-05 10           6

更新# 2:

多亏了Tsungur，我得到了以下查询，但每次运行它都会显示不同的结果。

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(distinct ip, session_id) > 1 AND date(date) = date(now())
) as sub
group by DATE_FORMAT([date], '%Y-%m-%d %H') 

这里有一些数据可以使用:

ID        session_id                   ip               date
10752     454747k5k45l23h3b5n6k432nn   44.56.123.123    2021-01-01 09:15:54   
10950     kmcoq3glgm187uhsfmo3r71h9q   86.85.131.246    2021-02-11 13:19:22
10958     kmcoq3glgm187uhsfmo3r71h9q   86.85.131.246    2021-02-12 12:10:52
10960     dfh78dfh7fdh7fdh6sd55dsd88   86.85.131.246    2021-02-12 13:00:02
10967     87s97sfh57sh6sh6s6sdsd44d3   11.56.873.560    2021-02-13 13:00:00
10968     rkdrgjsd7gjsd5jskjd46kjdsk   66.35.127.435    2021-02-13 13:01:00
10977     rkdrgjsd7gjsd5jskjd46kjdsk   66.35.127.435    2021-02-13 13:03:11
10978     dfajesj9sdj0dfh78sgd57sd5d   44.56.123.123    2021-02-13 13:05:12
10979     fhdf7f7hdf6fd44fdf3ffdf321   86.85.131.246    2021-02-13 14:05:02
10980     fhdf7f7hdf6fd44fdf3ffdf321   86.85.131.246    2021-02-13 14:06:13

以上数据应该告诉我:

date             count (new visitor)
2021-02-13 13    2
2021-02-13 14    0
date             count (returning visitor)
2021-02-13 13    1
2021-02-13 14    1

Answer 1

您可以通过对IP进行分组并过滤计数大于1的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和会话的计数，但您的having子句没有反映这一点。您的Having子句按日期和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首先是一些建议，如果可能的话，不要使用保留词作为列名(例如date)。我试图把这个问题分解。最后的查询看起来很脏，它可以被改进和缩短。但为了澄清问题，我使用了多个查询。你的第一个问题是计算唯一的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. 根据我的唯一值和日期-小时查找返回的物品:

   select dt_H, unique,count() as times from ()SELECT FORMAT(dt， 'yyyy-MM-dd HH') as dt_H， [sid]+':'+[ip] as unique从[mytable]其中cast(dt作为日期)=cast(getdate()作为日期))作为帮手group by dt_H, unique在计数()在1

3. 如果您使用php，您可以稍后用:字符分隔唯一字段。但如果你想在sql中使用，我们将不区分列地连接主表:

   select distinct return。dt_H,主要。(sid)主要。(ip),返回。从[mytable]作为主表的次数内连接(select dt_H,uniq,count(*) as times从(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)
   ) as helper
   group by dt_H,uniq
   having count(*)>1
   

   )作为返回returning.uniq =主。(sid) +":"+主要。[ip]和FORMAT(主)。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)

这将返回一个包含一天中所有小时的表。您可以将此表与日期部分和左连接结合使用以执行上述查询。因此，如果为null，则可以显示所有的小时数和返回时间的0。

PS:对不起，无法使格式化工作。