获取联系人在一分钟内有20个活动的行-sql查询

irtuqstp 于 2021-07-29 发布在 Java

关注(0)|答案(1)|浏览(321)

这个问题在这里已经有答案了：

在每个条目的一分钟内通过联系查找20个或更多条目（2个答案）
9个月前关门了。
我们正在收集一些联系人的分析数据&他们访问的每个页面。很多分析数据来自恶意攻击或机器人程序，所以它们在不到一分钟的时间内就攻击了网站20多个页面。我希望能够每天清除一次这些数据，但是我不知道如何编写一个sql查询来选择联系人在一分钟内访问了20多个页面的所有行，不仅仅是过去一分钟，而是一整天。我该如何编写查询来获取联系人在一分钟内拥有20多个活动的组的活动行？
分析表包含datecreated、contactid、activityname和activityurl
示例数据（假设一分钟内超过3个）：

2020-07-25 23:59:58, 78, Page visit, /home  
2020-07-25 23:59:57, 78, Page visit, /home/1  
2020-07-25 23:59:58, 34, Page visit, /home/2  
2020-07-25 23:59:56, 78, Page visit, /home/3  
2020-07-25 23:59:55, 78, Page visit, /home/4  
2020-07-25 23:59:52, 34, Page visit, /home  
2020-07-25 23:59:52, 78, Page visit, /home/5   
2020-07-25 23:59:51, 34, Page visit, /home/5   
2020-07-25 23:59:50, 34, Page visit, /home/6        
2020-07-25 21:34:02, 764, Page visit, /home   
2020-07-25 22:11:01, 78, Page visit, /home/9

所需数据：

2020-07-25 23:59:58, 78, Page visit, /home  
2020-07-25 23:59:57, 78, Page visit, /home/1  
2020-07-25 23:59:56, 78, Page visit, /home/3  
2020-07-25 23:59:55, 78, Page visit, /home/4   
2020-07-25 23:59:52, 78, Page visit, /home/5   
2020-07-25 23:59:58, 34, Page visit, /home/2  
2020-07-25 23:59:52, 34, Page visit, /home    
2020-07-25 23:59:51, 34, Page visit, /home/5  
2020-07-25 23:59:50, 34, Page visit, /home/6

sql window-functions DateTime sql-server sql-delete

来源：https://stackoverflow.com/questions/63142940/get-rows-where-contact-has-20-activities-within-a-minute-sql-query

1条答案

按热度按时间

axr492tv1#

您可以使用两个级别的窗口函数来实现这一点。第一级计算每个进程的请求数 contactID 分钟，然后第二级取每分钟第一次计算的最大计数 contactID 一天又一天。最后一步是过滤：

select *
from (
    select 
        t.*,
        max(cnt_minute) over(partition by ContactID, date(DateCreated)) max_cnt_minute
    from (
        select 
            t.*,
            count(*) over(partition by 
                ContactID,
                dateadd(minute, datediff(minute, 0, DateCreated), 0)
            ) cnt_minute
        from mytable t
    ) t
) t
where max_cnt_minute > 20

你可以很容易地把它变成一个 delete 语句（这似乎是您的实际意图），使用可更新的cte：

with cte as (

    select 
        t.*,
        max(cnt_minute) over(partition by ContactID, date(DateCreated)) max_cnt_minute
    from (
        select 
            t.*,
            count(*) over(partition by 
                ContactID,
                dateadd(minute, datediff(minute, 0, DateCreated), 0)
            ) cnt_minute
        from mytable t
    ) t
)
delete from cte where max_cnt_minute > 20

赞(0）回复(0）举报 2021-07-29

我来回答

获取联系人在一分钟内有20个活动的行-sql查询

1条答案

相关问题

热门标签

最新问答