将登录的页面视图分组到访问量中

Question

我有一个表格，我跟踪我的访问者在我的网站上做的所有页面浏览量。现在，我正在尝试将页面视图按访问量分组。一次访问是由同一个访问者完成的，如果两个页面的访问间隔超过60分钟，我将其定义为两次单独的访问。pageviewID只是一个随机数

Id  visitorid   timestamp   page
1   1   2013-10-10 18:00    /
2   2   2013-10-10 18:10    /
3   2   2013-10-10 18:12    /about
4   1   2013-10-10 18:14    /contact
5   3   2013-10-10 18:34    /
6   3   2013-10-10 18:37    /contact
7   4   2013-10-10 20:12    /
8   1   2013-10-10 20:14    /about

例如，我希望能够查询最近5次完整的访问，并获得类似以下内容的结果：

Id  visitorid   timestamp   page    visitid
1   1   2013-10-10 18:00    /           1
4   1   2013-10-10 18:14    /contact    1
2   2   2013-10-10 18:10    /           2
3   2   2013-10-10 18:12    /about      2
5   3   2013-10-10 18:34    /           3
6   3   2013-10-10 18:37    /contact    3
7   4   2013-10-10 20:12    /           4
8   1   2013-10-10 20:14    /about      5

该怎么做呢？我不知道从哪里开始构建查询。我希望我包含了足够的信息！

Answer 1

我认为问题是这样的。SQL可以同时处理多个对象集。您可以获取一些行，并对它们执行操作，以更改信息、获取不同的信息或获取信息的摘要。你想要做的事情本质上需要一个人一个接一个地迭代项目。SQL不会一个接一个地做任何事情。它就是不会这么做。

我认为您需要将信息从数据库中转移到其他工具中。然后做一些迭代的事情。然后，您可以保存新信息并再次执行数据库操作。

假设你有：

 Id  visitorid   timestamp   page    visitid
 1   1   2013-10-10 18:00    /           1
 4   1   2013-10-10 18:14    /contact    1
 2   2   2013-10-10 18:10    /           2
 3   2   2013-10-10 19:12    /about      2
 5   2   2013-10-10 19:17    /contact    2

您可以获取数据并将其传递到perl脚本或任何其他脚本中。如果有必要，您甚至可以在awk中执行此操作。该脚本将遍历各行。它将按顺序查看visitid的时间。如果两个时间间隔大于一个小时，它将创建一个新的visitid，并将后续请求设置为该id。数据将变成：

 Id  visitorid   timestamp   page    visitid
 1   1   2013-10-10 18:00    /           1
 4   1   2013-10-10 18:14    /contact    1
 2   2   2013-10-10 18:10    /           2
 3   2   2013-10-10 19:12    /about      101
 5   2   2013-10-10 19:17    /contact    101

对于它找到的每一个小时过去的时间点，您的脚本可以生成SQL，该SQL如下所示：

update mytable set visitid = 101 where visitId = 2 and timestamp >= '2013-10-10 19:12';

您可以在每次间隔大于一个小时时生成类似这样的SQL命令，即使在一个visitid中有多个间隔时也是如此。

将SQL传递回您的数据库。然后你就可以在你的数据库中进行普通的查询了。

另一种选择是，如果定义了访问并且此时正在记录visitid，则让最初记录信息的东西注意到已经存在小时间隔，并转到新的visitid。

简而言之，SQL本身并不能做所有的事情。例如，这就是为什么在Oracle中定义PL/SQL语言的原因。它可以处理数据库行，但允许您执行诸如迭代之类的操作。

Answer 2

你实际上可以使用SQLite window function来做这件事，它可以绕过雷·基迪所描述的“迭代”部分。

假设您的时间戳是简单的unix时间戳，这将计算每个访问者的每个页面查看之间的非活动时间：

SELECT
  utc_time,
  visitor_id,
  -- The window function: resolves the expression for the preceding row of the current partition
  LAG(utc_time) OVER (
    -- The window defition: Partitions all rows per visitor_id and orders each partition's rows by timestamps
    PARTITION BY visitor_id
    ORDER BY
      utc_time
  ) -- Substract the utc_time of the current row from the utc_time of the preceding row to get the time between rows
  - utc_time AS inactivity_time
FROM page_view
ORDER BY
  visitor_id,
  utc_time;

上述查询的结果可用于后续查询实际分配会话ids。使用非活动时间大于所需阈值的那些行，或者如果是第一个会话，则使用NULL，您可以使用另一个窗口函数(row_number)来唯一标识会话，包括会话的开始时间和下一个会话的开始时间：

SELECT
  -- Calculate the session id based on the visitor and the consecutive row number (we only handle session starts here)
  page_view.visitor_id || '-' || row_number() OVER(
    PARTITION BY page_view.visitor_id
    ORDER BY
      page_view.utc_time
  ) AS session_id,
  page_view.visitor_id,
  page_view.utc_time AS session_start_at,
  lead(utc_time) OVER(
    PARTITION BY page_view.visitor_id
    ORDER BY
      page_view.utc_time
  ) AS next_session_start_at
FROM (...) AS page_view
WHERE
  -- Filter for page views with an inactivity time greater 30 mins, these are session starts
  ABS(page_view.inactivity_time) > 30 * 60
  OR page_view.inactivity_time IS NULL;

考虑到这一点，您可能希望将结果存储在一个临时表中，以保持清晰。

假设结果存储在表"session“中，您可以通过将页面视图与其对应的会话连接起来，最终计算出一些有用的统计数据：

SELECT
  session_id,
  -- calculate the session duration
  ABS(
    MIN(page_view.utc_time) - MAX(page_view.utc_time)
  ) AS duration,
  -- show distinct paths per session
  COUNT(DISTINCT page_view.path)
FROM session
LEFT JOIN page_view ON page_view.visitor_id = session.visitor_id
  AND page_view.utc_time >= session.session_start_at
  AND (
    page_view.utc_time < session.next_session_start_at
    OR session.next_session_start_at IS NULL
  )
GROUP BY
  1

我建议从第一个问题开始，然后逐步向上，这有助于我理解发生了什么。

这里列出的大多数查询都来自这个blog post，我略微调整了它们以在SQLite中工作。