SQL查询:连续30天每天访问站点

使用创建表

Id, LastVisit, DaysConsecutivelyVisited

每次访问都要适当地更新表。逻辑清晰,不需要丑陋的SQL查询来提取所需的信息。

你可以通过阅读标有“发烧友”徽章的问题来了解这里的挑战。 meta.stackoverflow.com .

一个重要问题是 什么构成“访问” 到现场。我们中的许多人一次登录几天。所以他们不计算登录次数。他们对什么是访问有一个特殊的定义。您必须定义访问是什么。

困惑的根源是 日期的定义 . 我相信使用格林威治标准时间也是如此,这意味着许多用户的日期会在本地日期的中间发生变化。如果您在其他时区有用户,则必须为您的日期指定正在使用的时区。

如果你足够幸运,能够以登录次数为基础,并且你的用户在同一时区,那么你的记录保存就相对容易了。您只需记录每个登录名,然后按照JeremiahPeschka的建议使用SQL来查找缺口。

如果必须跟踪访问,而不是登录,则必须定义访问是什么,并创建单独的数据库表来记录访问。显然,将有大量的日志记录到该表中,您可能希望 考虑所有这些数据库调用的性能影响与拥有此信息的价值 .

只要您有一列记录用户访问站点的原始日期,就可以编写查询来查找 gaps in the data . 在这里输入一个特定的查询会很长(毫无疑问,比我聪明的人会想出一个简短的例子来证明我是错的),但是很有可能,只需要花一点时间来编写要检测的代码。

您只需拥有一个跟踪登录的表,以及自上次登录以来的天数(字面上说,不超过: 用户ID,登录日期,daysincelastlogin )这意味着您只需查询该表,确保自上次登录以来的天数在过去30天内小于或等于1。

假设你有一个表访问(uid,timestamp),我会采取慷慨的路线,并说如果最近的访问间隔超过48小时,这个人就不会每天都访问。另一方面,我认为他们是这样做的(事实上,他们可以在一个晚上和两个早上之后去,或者做些什么,但无论如何,这是可以调整的)。您可以很容易地使用日的其他定义,因为这只返回两点之间的最大距离。

对于大型数据集,Jeremiah的链接可能更高效,但更简单的查询是:

SELECT uid, MAX(dist) FROM
    (SELECT v1.uid AS uid, MIN(v1.timestamp-v2.timestamp) AS dist
        FROM visits v1 LEFT JOIN visits v2
        ON v1.uid = v2.uid
        WHERE $dateRangeLimiter
        GROUP BY v1.uid, v1.timestamp) WHERE uid = $targetUid

这将得到给定的WHERE子句和uid规范中两个时间点之间的最大距离。如果您只是在寻找一个uid,那么应该将该uid推送到内部select中;此时,查询将为所有用户执行此操作。

这不是最有效的方法,但是如果这就是你所拥有的,而且它不是一个大数据集,那么它应该可以很好地工作。

我们假设你已经有了一张桌子或视图 logindays 具有 (user, day) 每个用户和访问日包含一行(通过四舍五入登录时间和分组实现)。dok已经解释了“day”的定义问题。

下面是:

select user, min(day) enthusiast_since from
( select user, day, lag(day, 29) over (partition by user order by day) daydiff from logindays )
where day - daydiff = 29
group by user;

(29因为第1天比第30天早29天)

最准确的方法是记录用户的活动。这样,对于那些在非活动状态下保持登录数天(不应计算在内)的用户,就不会混淆“他是否登录了”。

对于维护,请删除所有日志记录>30天。

查询表以获取过去30天内任何给定用户的任何两个日志条目之间的最大时间距离。如果最大结果为<=1,他将获胜。

我相信你能想出如何写这个查询。