sql - 如何在特定时间范围内计算任何 N 秒持续时间的 SQL MAX()

Question

我需要回答这样一个问题:

For each user, what is the most items that user viewed in any 60 second time frame between START_TIMESTAMP and END_TIMESTAMP?

60 秒时间范围是一个滑动窗口。这不仅仅是每分钟“查看的项目”计数的问题。此外，60 秒只是一个示例，它应该适用于任何秒数。

我的数据是这样存储的:

-- Timestamped log of users viewing items
CREATE TABLE user_item_views (
  user_id integer,
  item_id integer,
  timestamp timestamp
);

每整分钟都这样做很容易，只需将时间戳格式设置为类似 YYYY-MM-DD hh:mm 的格式，然后按该格式的时间戳和 user_id 分组进行计数。

为滑动窗口做这件事，我不知道如何接近。

如果这在 SQL 之外会更容易，我愿意将数据导出为另一种格式，或使用另一种语言。

期望的输出是这样的:

User ID  Max items viewed in N seconds, between START and END.
...      ...
...      ...
...      ...

我该怎么做？

Answer 1

这是我的做法(注意，未经测试的代码，这只是为了概述这个想法)。

您需要一个帮助表，其行数与 START_TIMESTAMP 和 END_TIMESTAMP 之间的秒数一样多。在开始查询之前将其创建为临时表。

为了示例，我们称它为every_second。我假设您的最小时间分辨率是一秒。

然后做:

SELECT
  s.timestamp,
  v.user_id,
  (
   SELECT COUNT(*) FROM user_item_views 
    WHERE timestamp BETWEEN s.timestamp AND ADDTIME(s.timestamp, '00:00:59')
          AND user_id = v.user_id
  ) item_count
FROM
  every_second s
  LEFT JOIN user_item_views v ON v.timestamp = s.timestamp
GROUP BY
  s.timestamp, 
  v.user_id

将其存储在另一个临时表中并从中选择所需的最大值(这是必要的，因为“从组中选择最大值”问题)。