If I have a table that includes:
user_id | event_time
How can I calculate the average days between events? To get something like:
days_diff | count
1 | 100
2 | 90
3 | 20
A user may have 1 day between events, but may also have 3 days between to subsequent events. How can I count them in both buckets?
Sample data (note in this case the DAY DIFF is 0/1 but this is just a small subset of data)
user_id | event_time
82770 2015-05-04 02:34:53
1 2015-05-04 08:45:53
82770 2015-05-04 20:38:24
82770 2015-05-04 20:38:24
82770 2015-05-04 20:38:24
1 2015-05-05 09:31:42
82770 2015-05-05 13:33:36
82770 2015-05-05 13:33:53
1 2015-05-06 09:53:59
1 2015-05-06 23:31:18
1 2015-05-06 23:31:35
1 2015-05-07 12:31:41
82770 2015-05-07 16:01:16
Here's a solution without using a temporary table:
select daybetweenevents as days_diff,
count(daybetweenevents) as count
from (select t1.user_id,
t1.event_time,
datediff(day, t1.event_time, min(t2.event_time)) as daybetweenevents
from yourtable t1
inner join yourtable t2
on t1.user_id = t2.user_id
and t1.event_time < t2.event_time
group by t1.user_id, t1.event_time) temp
group by daybetweenevents
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다