如何在数据库中组织基于时间的事件进行聚合（例如访问）？

Question

我需要为我的用户提供有关其页面的不同类型访问者的基于时间的统计数据，例如上周/上个月/总体有多少访问者，以及这些访问者来自哪里。理想情况下，数据应足够丰富，以便在可能的情况下进行扩展/定制/重新聚合。

我的问题是如何最好地在数据库中组织它。一种选择是为每次访问创建一个新记录，其中包含访问者的 ID 和日期。

我怀疑这种方法虽然为我提供了功能，但会占用大量存储空间。有更好的方法来解决这个问题吗？

Answer 1

公认的行业惯例是拥有跟踪级表和汇总级表。跟踪数据是指事件的详细日志。尽管存储不是问题，但计数所需的时间将是一个问题。按小时查询访问次数比统计跟踪表的记录要快得多。

假设您的跟踪级别表是

Trace = [datetime, userId, domain], ipaddr;

当您的进程插入跟踪表时，它还会创建/更新汇总表的记录。

确定您希望累积事件的最精细时间粒度。它可以是分钟、小时、天等。

您还需要确定是否希望按会话或页面请求进行计数。您会将页面上的重复访问视为多次访问吗？您必须确定所有此类分母。

定义一个计数器：

class Counter{
  datetime start;
  int count;
}

假设您已确定按小时的粒度最适合网站上的流量。

按整个网站的会话进行累积
在每小时开始时，计数器会重置，并将开始日期时间设置为当前日期时间。每次发生事件计数都会增加。就您而言，这将是一个新的会话事件。

当下一小时到来时，计数器数据将写入表中的新记录中

ByHour = [start], count;

，并且计数器将再次重置并将开始日期时间设置为当前日期时间。

您现在拥有的是一个按小时累积新访问事件的表。因此，现在您可以选择指定范围内给定日、周、月、年内所有按小时记录的范围内的所有按小时记录的计数总和。

为了不影响 Web 服务的响应，Web 服务应将这些事件写入总线或队列，由单独的数据加载进程拾取。

每个访问页面的累计
但是，您可能希望按页进行累积

累积器进程需要为每个 pageId 维护一个累积器类的实例：

class Counter{
  datetime start;
  int pageId;
  int count;
}

并且，计数记录将是：

ByHour = [start, pageId], count;
PageID = [pageId], url;

每个 userId、pageId、sessionId、clientIp 进行累积< /strong>
然后，您进一步确定需要累积每个用户 ID 的访问次数。在这种情况下，您可能会认为按天累积比按小时累积更好。如果您按小时累积，您可能还需要依赖跟踪表，因为计算每小时启动新会话或访问的用户的稀缺性与依赖跟踪表一样具有良好的粒度。

累积器进程需要为每个 paramId 维护一个累积器类的实例：

class Counter{
  datetime start;
  int paramId;
  int count;
}

并且，计数记录将是：

ByDay = [start, paramId], count;
ParamID = [paramId], paramName;

其中 paramName 可以是“pageId”、“userId”、“clientIp”等。

为了防止丢失数据，您可以更新每十分钟对 ByDay 表进行一次更改的任何计数器实例，而不重置计数器实例。仅当这一天跨越到新的一天时才重置开始和计数。

class Counter{
  datetime start;
  boolean modified;
  int paramId;
  int count;
}

“修改”字段不会写入数据库表。它是一个标志，用于确定 Counter 实例是否需要更新 ByDay 表。如果发生更新，“已修改”字段将重置为“假”。

Answer 2

我会追求“每次访问都有新记录”。不然编程会很复杂。

您可以根据站点的流量来估计需要多少存储空间。执行此操作后，您可能会决定不再担心存储问题，因为对于现代 DBMS 来说，存储通常不是问题。

如果存储确实是一个问题，那么您需要事先决定（并修复）您需要哪些统计数据。然后，您可以记录一个月的每次访问。在每个月末，您总结该月并将此摘要添加到应根据前几个月的统计数据编制的现有摘要中。