SQL：查找列值匹配的行之间的差异

Question

抱歉如果我的标题没有恰当地描述我要执行的任务。

为了一项大学项目，我收到了一个网站的访问日志，我已经丢弃了不必要的列，并将其压缩成了以下内容：

╔══════════╦══════════════════════╦═════════════════╦═════════════╦════════════════╗
║ accessid ║ date_time_in_seconds ║ yg_requester_id ║ referent_id ║ referent_docid ║
╠══════════╬══════════════════════╬═════════════════╬═════════════╬════════════════╣
║     2449 ║        2009011621830 ║           32276 ║       12648 ║              1 ║
║     2776 ║        2009011622726 ║           76360 ║       11070 ║              1 ║
║     2804 ║        2009011622783 ║           32276 ║       13845 ║              1 ║
║     2894 ║        2009011623025 ║           32276 ║        7222 ║              1 ║
║     2895 ║        2009011623037 ║           32276 ║        1530 ║              1 ║
║     3000 ║        2009011623406 ║           32276 ║        3728 ║              1 ║
║     3019 ║        2009011623497 ║          520060 ║       10356 ║              1 ║
║     3245 ║        2009011625780 ║          300841 ║        4607 ║              1 ║
║     3274 ║        2009011628309 ║          532664 ║       14377 ║              1 ║
║     3275 ║        2009011628420 ║          532664 ║        9097 ║              1 ║
╚══════════╩══════════════════════╩═════════════════╩═════════════╩════════════════╝

最初时间戳和日期是按单位（年、月、日、小时、分钟、秒）分别列在不同的列中，为了更方便地计算，我已将它们合并成 date_time_in_seconds 格式。

[0000][00][00][00000]
[YEAR][MONTH][DAY][Number of Seconds since 00:00]

accessid是表格条目ID，yg_requester_id是网站访问者的唯一ID，referent_id是他们阅读的网站文章的ID，referent_docid表示文章类型，但在此任务中不需要。

基本上，我想能够找到自上次相同的yg_requester_id访问了不同的referent_id以来的时间差。

例如，查看上表中的这一行：

╔══════════╦══════════════════════╦═════════════════╦═════════════╦════════════════╗
║ accessid ║ date_time_in_seconds ║ yg_requester_id ║ referent_id ║ referent_docid ║
╠══════════╬══════════════════════╬═════════════════╬═════════════╬════════════════╣
║     2449 ║        2009011621830 ║           32276 ║       12648 ║              1 ║
║     2776 ║        2009011622726 ║           76360 ║       11070 ║              1 ║
║     2804 ║        2009011622783 ║           32276 ║       13845 ║              1 ║
╚══════════╩══════════════════════╩═════════════════╩═════════════╩════════════════╝

yg_requester_id 32276在2009年1月16日06:03:50（午夜后的21830秒）访问了id为12648的文章。然后在同一天06:19:43（午夜后的22783秒）访问了id为13845的文章。因此可以推断用户阅读第一篇文章12648大约花费了15分钟50秒。

我想要找到同一用户访问两篇文章之间的时间差。由同一用户阅读的连续文章可能没有连续的访问ID（尽管它将始终递增）。另外，为了过滤掉阅读时间小于一定分钟数（如15分钟）的记录，我还想限制阅读时间在一小时左右。

提前感谢，如果需要更多信息，请让我知道。

- Edward

---

首先，不要仅仅为了这个而创造一个新的时间存储约定。你可以使用从1970年开始的Unix秒数，或者将其存储为适当的日期时间字段；超出原始组件字段。 - RichardTheKiwi

谢谢，我刚刚以那种方式合并了日期/时间测量值，因为我认为这样计算会更容易。我仍然拥有原始数据，并将将其转换为适当的日期时间数据类型。 - Edward

您对于最后访问时间的处理是什么？您会将其与 GetDate() 进行比较以获取时间间隔吗？ - RichardTheKiwi

如果时间差很大（比如一个小时或更多），那么差异将被限制为一个小时。我试图隔离过去15分钟内访问了以下文章的条目。 - Edward

Answer 1

我会使用ROW_NUMBER将结果集按yg_requester_id分区，并按accessid或datetime排序（假设您要将date_time_in_seconds列更改为常规datetime列，如评论中建议的一样）。 然后，我会通过请求者将结果集与自身连接，并获取差异。

让我尝试在没有正确数据的情况下编写查询：

SELECT X1.yg_requester_id, DATEDIFF(SECOND, X1.NewDateTimeField, X2.NewDateTimeField) AS TimeDifferenceInSeconds, X1.referent_id AS NewArticle, X2.referent_id AS FormerArticle
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY yg_requester_id ORDER BY NewDateTimeField DESC) AS Position, NewDateTimeField, yg_requester_id, referent_id
FROM YourTable

) X1
INNER JOIN 
(
SELECT ROW_NUMBER() OVER(PARTITION BY yg_requester_id ORDER BY NewDateTimeField DESC) AS Position, NewDateTimeField, yg_requester_id, referent_id
FROM YourTable  
) X2 ON X2.yg_requester_id = X1.yg_requester_id AND X2.Position = X1.Position - 1

- Jaime

---

@Edward 您好，不用谢。您能否将答案标记为有效答案？非常感谢。 - Jaime

Answer 2

这个查询应该检索请求者、被引用者和请求者在被引用者上所花费的时间差（以秒为单位）：

select abc.A_requestor as requestor_id,abc.B_refer as referent_id,abc.A_datetime-abc.B_datetime as time_difference   from 
(select a.accessid as A_accessid ,b.accessid as B_accessid,
a.yg_requestor_id as A_requestor,a.date_time_in_seconds as A_datetime,a.referent_id as A_refer,
b.yg_requestor_id as B_requestor,b.date_time_in_seconds as B_datetime,b.referent_id as B_refer
from weblog a
inner join weblog b
on a.yg_requestor_id = b.yg_requestor_id
and a.date_time_in_seconds > b.date_time_in_seconds
and a.referent_id != b.referent_id) abc

inner join 

(select cte.B_accessid,min(cte.A_accessid) as C_accessid from
(select a.accessid as A_accessid ,b.accessid as B_accessid,
a.yg_requestor_id as A_requestor,a.date_time_in_seconds as A_datetime,a.referent_id as A_refer,
b.yg_requestor_id as B_requestor,b.date_time_in_seconds as B_datetime,b.referent_id as B_refer
from weblog a
inner join weblog b
on a.yg_requestor_id = b.yg_requestor_id
and a.date_time_in_seconds > b.date_time_in_seconds
and a.referent_id != b.referent_id) cte 
group by cte.B_accessid ) xyz

on xyz.B_accessid = abc.B_accessid and xyz.C_accessid = abc.A_accessid

- Seasoned