SQL 查询 - 每天确定新访客

Question

我有一个如下所示的模式(简化):

CREATE TABLE MyTable (
   DateTimeOffset HitDate NOT NULL,
   IpAddress varchar(15)
)

示例行可能如下所示:
'7/10/2013 8:05:29 -07:00' '111.222.333.444'
我正在尝试制定一个查询，该查询将为我提供每天(例如 7/10/2013)唯一 IpAddress 的数量。实际上，那部分相当简单，我已经为此创建了一个查询。但是，对于这个查询，我想要的是在当前日期之前从未存在过的唯一 IpAddresses 的数量。我不在乎约会之后，就在约会之前。

例如，假设我有以下数据，这就是我所拥有的:

'7/10/2013 8:05:29 -07:00' '111.222.333.444'
'7/10/2013 12:05:29 -07:00' '111.222.333.222'
'7/9/2013 9:05:29 -07:00' '111.222.333.444'
'7/9/2013 10:05:29 -07:00' '111.222.333.555'
'7/8/2013 11:05:29 -07:00' '111.222.333.222'
'7/8/2013 4:05:29 -07:00' '111.222.333.555'

查询应输出以下内容:

'7/8/2013' 2 (neither IpAddress existed before this date so both are new)
'7/9/2013' 1 (only one of the IpAddresses is new - the one ending in '444')
'7/10/2013' 0 (both IpAddresses had existed before this date)

目标数据库是 SQL Server 2012 。我向第一个正确创建 SQL 语句的人提供 100 分的奖励。

Answer 1

最简单的方法(在我看来)是找到 IP 地址出现的最早日期，然后将其用于聚合:

select cast(minHitDate as Date), count(*) as FirstTimeVisitors
from (select IpAddress, min(HitDate) as minHitDate
      from MyTable t
      group by IpAddress
     ) i
group by cast(minHitDate as Date)
order by 1;

可让您计算第 1 次访问者、第 2 次访问者等的替代形式使用 dense_rank() :

select cast(HitDate as Date),
       count(distinct IpAddress) as NumVisitors,
       sum(case when nth = 1 then 1 else 0 end) as FirstTime,
       sum(case when nth = 2 then 1 else 0 end) as SecondTime,
       sum(case when nth = 3 then 1 else 0 end) as ThirdTime
from (select IpAddress,
             dense_rank() over (partition by IpAddress
                                order by cast(HitDate as date)
                               ) as nth
      from MyTable t
     ) i
group by cast(HitDate as Date)
order by 1;