sql - 如何计算 SQL 中特定值之间的差异的平均值？

Question

嘿伙计们，抱歉，这是一个有点长的问题......

我有一个包含以下列的表格:

[ChatID] [用户] [LogID] [创建时间] [文本]

我需要找到的是给定用户 ID 到另一个特定用户 ID 的平均响应时间。因此，如果我的数据如下所示:

[1] [john] [20] [1/1/11 3:00:00] [Hello]
[1] [john] [21] [1/1/11 3:00:23] [Anyone there?]
[1] [susan] [22] [1/1/11 3:00:43] [Hello!]
[1] [susan] [23] [1/1/11 3:00:53] [What's up?]
[1] [john] [24] [1/1/11 3:01:02] [Not much]
[1] [susan] [25] [1/1/11 3:01:08] [Cool]

...然后我需要看到 Susan 对 John 的平均响应时间为 (20 + 6)/2 => 13 秒，John 对 Susan 的平均响应时间为 (9/1) => 9 秒.

我什至不确定这可以在基于集合的逻辑中完成，但如果有人有任何想法，他们将不胜感激!

Answer 1

我没有电脑来验证语法或任何东西，但我认为这应该给你一个起点:

WITH ChatWithRownum AS (
    SELECT ChatID, User, LogID, CreatedOn, ROW_NUMBER() OVER(ORDER BY ChatID, CreatedOn) AS rownum
    FROM ChatLog
)
SELECT First.ChatID, Second.User,
    AVG(DATEDIFF(seconds, First.CreatedOn, Second.CreatedOn)) AS AvgElapsedTime
FROM ChatWithRownum First
    JOIN ChatWithRownum Second ON First.ChatID = Second.ChatID
        AND First.rownum = Second.rownum - 1
WHERE First.User != Second.User
GROUP BY First.ChatID, Second.User

本质上，这个想法是将行号添加到数据中，以便您可以将一行连接到下一行(这样您就有一个语句，后跟其立即响应)。连接行后，您可以获得两个条目之间耗时，然后按 ChatID(我假设单独聊天之间的时间不相关)和两个用户对数据进行分组。正如我所说，这只是一个起点，因为我确信我的查询中可能存在一些其他条件和/或错误:)