在历史数据中查找 30 天内再次访问的用户的记录 [英] Finding records for users who re-visited within 30 days across historical data

问题描述

我正在尝试创建一个 MySQL 查询，当一个人 VisitorID 在 30 天内返回 DateOfService 时，该查询将返回所有行.理想情况下，我希望查询返回所有字段，但示例具有相关字段以使查询工作.

I'm trying to create a MySQL query that will return all rows for when a person VisitorID returned within 30 days DateOfService. Ideally, I would like for the query to return all fields, but the sample has the relevant fields to make the query work.

VisitorID DateOfService Cost   TransactionID
001       2008-08-15    150.00 11-235-24 
001       2008-09-07    175.00 11-272-50
002       2008-08-15    155.00 11-688-14 
002       2008-09-16    181.00 11-841-67
001       2011-01-07    190.00 12-851-41
001       2011-01-18    100.00 12-901-55
003       2011-02-05    150.00 12-951-14
003       2011-02-15    180.00 12-998-51

理想的查询结果

VisitorID DateOfService Cost   TransactionID
001       2008-09-07    175.00 11-272-50
001       2011-01-18    100.00 12-901-55
003       2011-02-15    180.00 12-998-51

我尝试过使用 group by 进行变体，但我似乎无法在 30 天的滚动窗口中做到这一点.

I've tried variations using group by, but I can't seem to get it to do it in a rolling 30 day window.

推荐答案

SELECT t1.*
FROM table t1 
INNER JOIN table t2 
  ON t1.VisitorID = t2.VisitorID AND
     t1.TransactionID != t2.TransactionID AND
     t1.DateOfService > t2.DateOfService AND 
     datediff(t1.DateOfService, t2.DateOfService) < 30

sqlfiddle 演示

这篇关于在历史数据中查找 30 天内再次访问的用户的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！