SQL Server LAG()函数来计算行之间的差异 [英] SQL Server LAG() function to calculate differences between rows

问题描述

我是SQL Server的新手，并且对lag()函数有一些疑问. 我必须计算两个用户活动之间的平均距离(以天为单位).然后，我必须对所有用户进行GROUP BY，为每个用户计算行之间的所有日期差，最后选择组的平均值.

I'm new in SQL Server and I've got some doubts about the lag() function. I have to calculate the average distance (in days) between two user's activities. Then, I have to GROUP BY all the users, calculate all the date differences between rows for each user, and finally select the average of the group.

请明确说明，我有这种类型的桌子:

Just to be clear, I've got this kind of table:

首先，我必须使用活动(活动！= 0)来过滤天数.然后，我必须创建一个:

First I have to filter days with activities (activities!=0). Then I have to create this:

最后，预期结果是这个:

And finally, the expected outcome is this one:

我认为这可能是一种"代码:

I thought this could be a "kind of" code:

select userid, avg(diff)
  (SELECT *,DATEDIFF(day, Lag(dateid, 1) OVER(ORDER BY [Userid]), 
   dateid) as diff
   FROM table1
   where activities!=0
   group by userid) t
group by userid

当然不行.我认为我还必须进行一次while循环，因为每个用户的行号都会更改.

Of course it doesn't work. I think I also have to do a while loop since rownumber changes for each users.

希望您能帮到我！非常感谢

I hope you can help meeee! thank you very much

推荐答案

您快到了.只需添加partition by userid，即可计算每个用户ID和order by dateid的差异.

You are almost there. Just add partition by userid so the difference is calculated for each userid and order by dateid.

select userid, avg(diff)
  (SELECT t.*
         ,DATEDIFF(day, Lag(dateid, 1) OVER(PARTITION BY [Userid] ORDER BY [dateid]),dateid) as diff
   FROM table1 t
   where wager!=0
  ) t
group by userid

这篇关于SQL Server LAG()函数来计算行之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！