我有以下表格:
输入:
domain ip time
Google 101 2020-03-31 14:55:37
Google 101 2020-03-31 14:56:12
Facebook 101 2020-03-31 14:57:36
Amazon 101 2020-03-31 14:57:45
Yahoo 102 2020-02-28 12:15:15
Yahoo 102 2020-03-01 15:20:12
Hotmail 102 2020-03-02 14:52:31
我想在创建列switches的地方得到以下输出。它基本上计算IP切换域的次数。例如，IP 101从谷歌到Facebook再到亚马逊，所以它切换了两次(我们不计算第一个域名)。假设数据都是有序的，我如何在SQL Server中得到以下输出?
预期输出
domain ip time switches
Google 101 2020-03-31 14:55:37 2
Google 101 2020-03-31 14:56:12 2
Facebook 101 2020-03-31 14:57:36 2
Amazon 101 2020-03-31 14:57:45 2
Yahoo 102 2020-02-28 12:15:15 1
Yahoo 102 2020-03-01 15:20:12 1
Hotmail 102 2020-03-02 14:52:31 1
如果您想计算切换回之前的值(而不是不同的值)，您需要使用LAG:
with cte (domain, ip_addr, time_col) as
(
select 'Google',101,'2020-03-31 14:55:37'
UNION select 'Google',101,'2020-03-31 14:56:12'
union select 'Facebook',101,'2020-03-31 14:57:36'
union select 'Amazon',101,'2020-03-31 14:57:45'
)
select
domain,
ip_addr,
time_col,
sum(switches) over (partition by ip_addr) -1
from (
select *,
case when lag (domain) over (partition by ip_addr order by time_col) = domain then 0 else 1 end as switches
from cte
) t
你可以使用count语句和partition by——因为你想要改变次数，那么你想要1小于计数，所以减去1。
SELECT domain, ip_addr, time_col,
(COUNT(domain) OVER (partition by ip_addr)) - 1 as switches
FROM tableyoudidnotname