SQL - вычислить разницу во времени между ранжированными сессиями
У меня есть некоторые данные о продолжительности сеансов пользователей на веб-сайте (то есть время, когда они были активны с и до).
Это выглядит примерно так:
cust_id dt started_at stopped_at length rank
10046493 28/10/2015 27/10/2015 23:45 28/10/2015 00:31 00:46 1
10046493 28/10/2015 28/10/2015 00:31 28/10/2015 00:43 00:12 2
10046493 28/10/2015 28/10/2015 00:43 28/10/2015 00:51 00:08 3
10046493 28/10/2015 28/10/2015 17:35 28/10/2015 18:03 00:28 4
Может быть 1 сеанс в день, но у некоторых клиентов загружается больше (например, я нашел один с 85+).
Что мне нужно сделать, это проверить разницу во времени между окончанием одного сеанса и началом следующего. Если разница составляет менее 30 минут, мне нужно считать их как один сеанс и рассчитывать общую продолжительность, в противном случае они должны быть двумя отдельными сеансами.
Поэтому мне нужен код, который делает что-то вроде этого:
if rank2_start > (rank1_stop + 30) then DIFFERENT
if rank2_start < (rank1_stop + 30) then SAME >>>> in this case I also need rank2_stop - rank1_start (i.e. the difference between the two to get the total session length)
Поскольку в день может быть так много сеансов, это не вариант, когда нужно перечислять rank1, rank2, rank3 и т. Д.
Другая проблема заключается в том, что кто-то мог начать 6 сеансов в течение получаса, поэтому все они должны быть рассчитаны как единое целое.
У кого-нибудь есть идеи, как это сделать? Если это очень сложно или невозможно сделать с доступными данными, это тоже хорошо - это было бы хорошим плюсом для моего анализа, но не обязательно.
Спасибо