sql - 如何使用 SQL 计算在 stackexchange 上发表评论所需的平均时间？

Question

我正在寻找一个 SQL 查询来计算评论的平均时间(每月测量一次)。

我能够编写一个查询来测量原始帖子日期时间和评论日期时间之间的平均时间，但这仍然不正确，因为应该在当前评论和前一个评论之间测量时间，因为它们最相关的时间。

select 
  dateadd(month, datediff(month, 0, Comments.creationdate),0) [Date],
  AVG(CAST(DATEDIFF(hour, Posts.CreationDate, Comments.creationdate ) AS BigInt)) [DelayHours]
  from comments
    INNER JOIN posts ON Comments.PostId = Posts.Id
  GROUP BY
    dateadd(month, datediff(month, 0, Comments.creationdate),0)
  ORDER BY Date

Answer 1

我认为这样的事情应该可行。抱歉，暂时无法测试；如果我打印有误，我深表歉意。

WITH cte1 AS
(
SELECT c.PostId, c.creationdate, 
ROW_NUMBER() OVER (PARTITION BY c.PostId ORDER BY c.creationdate) AS rn
FROM comments c
)
SELECT dateadd(month, datediff(month, 0, a.creationdate),0) [Date],
AVG(diff_hr) AS avg_diff
FROM 
(
  SELECT a1.PostId, a1.creationdate,
  CASE
   WHEN a1.rn = 1 THEN 
    CAST(DATEDIFF(hour,p.creationdate,a1.creationdate) AS BIGINT) 
   ELSE
    CAST(DATEDIFF(hour,a2.creationdate,a1.creationdate) AS BIGINT) 
  END AS diff_hr
  FROM cte1 a1
  INNER JOIN posts p ON (p.Id = a1.PostId)
  LEFT JOIN cte1 a2 ON (a2.PostId = a1.PostId AND a2.rn = a1.rn-1)
)a
GROUP BY dateadd(month, datediff(month, 0, a.creationdate),0)

更新对于 SQLServer 2012 LAG 将简化解决方案...我注意到有关版本太晚的评论。

更新 2 打印错误已修复(遗漏 FROM 子句和 p.PostId 更改为 p.Id 以匹配表定义)