SQL Server LAG() function to calculate differences

问题:

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:

---

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

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

回答1:

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

回答2:

You don't need lag() at all. The average is the maximum minus the minimum divided by one less than the count:

SELECT userid,
       DATEDIFF(day, MIN(dateid), MAX(dateid)) * 1.0 / NULLIF(COUNT(*), 1) as avg_diff
FROM table1
WHERE wager<> 0
GROUP BY userid;