Necesito calcular el promedio semanal de duración del uso de la aplicación (cada uso tiene su propio registro con la duración). El problema es que me gustaría comparar los promedios antes de que ocurriera un determinado evento y después (fecha diferente para cada usuario). Entonces, si el evento tuvo lugar el martes hace un mes, me gustaría calcular los promedios semanales a partir de los martes antes de ese martes específico y después.
Los datos relevantes son los siguientes: EVENTOS
User ID || date of event 3fin2d..|| 19/03/17 2f4j34..|| 20/03/17USO
UID || timestamp start || timestamp end || Duration 3fin2d.. || 11/03/17 11:20:00 || 11/03/17 12:00:00 || 00:40 3fin2d.. || 18/03/17 11:20:00 || 18/03/17 12:00:00 || 00:40 2f4j34.. || 19/03/17 18:20:00 || 19/03/17 18:40:00 || 00:20 2f4j34.. || 19/03/17 19:20:00 || 19/03/17 20:00:00 || 00:40 3fin2d.. || 19/03/17 19:30:00 || 19/03/17 20:00:00 || 00:30 2f4j34.. || 20/03/17 19:20:00 || 20/03/17 20:00:00 || 00:40editar: USUARIOS
UID || Created On 3fin2d.. || 11/03/17 11:00:00 2f4j34.. || 18/03/17 13:00:00Resultado Esperado:
UID ||Average Duration before even||Average Duration After event 3fin2d.. || 00:40:00 || 00:30:00 2f4j34.. || 01:00:00 || 00:40:00por supuesto, se deben considerar semanas con 0 uso. En el ejemplo anterior, asumo que la fecha actual es el 20/03/17 (de lo contrario, se deben contar muchos más ceros).
Gracias
http://rextester.com/USBJ20378
select uid, avg(u.duration) filter (where usage_start < e.event_date) as before, avg(U.duration) filter (where usage_start >= e.event_date) as after from usage u inner join events e using (uid) where usage_start between e.event_date - 7 and e.event_date + 7 group by uid ; uid | before | after --------+----------+---------- 2f4j34 | 00:30:00 | 00:40:00 3fin2d | 00:40:00 | 00:30:00