Neodani,
vamos por partes:
1) Creo que hay una gran diferencia entre contar clics dentro de períodos fijos de 30 minutos, establecidos como tales, de media en media hora (es decir, de hora a hora y media), o contar solo como válidos los que se separan 30 minutos entre sí cuando hablamos del mismo producto, sin rangos de referencia horaria, sino como diferencia temporal entre ellos. En el primer caso un clic de un cliente para el producto a las y 12:29:00 y otro a las 13:01:00 sobre el mismo producto se contarían como dos clics, y en el segundo como solo uno, porque se separan entre sí dos minutos.
2) Yo lo haría con programación (es posible que con procedimientos pudieras hacerlo en la base), pero yo lo haría con mi programa porque no manejo mucho los procedimientos. El truco es usar bien el ORDER BY de la consulta. Tienes que ordenar la salida de la consulta así:
... ORDER BY user_id, producto_id, visit_date
Tan sencillo como eso en lo que se refiere a la base de datos. Y no es poco, como verás.
3) Para resolver el problema de la función TO_SECONDS, puedes usar TIMESTAMPDIFF (SECOND, unafechareferencia, visit_date), y cuando digo unafechareferencia me refiero a cualquier fecha con horas minutos y segundos anterior a todas tus fechas registradas; podrías usar por ejemplo el primer día del primer mes del año en su primera hora minuto y segundo, pero del año en que tienes ya resultados, aunque te valdría cualquier otra siempre que sea anterior a la más antigua que tienes registrada. Imaginemos que tus resultados parten de 2011, pues pones '2011-01-01 00:00:01' como fecha de referencia. No importará mucho la fecha que sea, pues lo que a ti te sirve es la diferencia entre el momento de un clic y del siguiente sobre un mismo producto. Veamos un ejemplo:
SELECT TIMESTAMPDIFF(SECOND,'2011-01-01 00:00:01', '2011-02-04 23:14:12')
esto da por ejemplo 3021251, que como valor absoluto no dice nada, pero que comparado con el de la siguiente pulsación sobre el mismo producto sí puede ser comparado para saber si es o no 1800 segundos anterior.
Bien, me dices que no puedes agrupar. Imagino que lo dices porque quieres sacar los resultados de cada cliente, es decir, de todos los clientes, de una vez, algo así como
user_id|clics|mes
1|52|1
2|25|1
entendiendo que en el mes de enero el usuario 1 ha pulsado 52 veces, pero esos clics solo se cuentan si es la primera vez que se hace clic sobre ese producto o han pasado al menos 1800 segundos respecto del anterior clic sobre dicho producto;
Hacer la consulta sobre el mismo cliente es fácil, con un WHERE user_id = 1, aunque puedes usar el mismo ORDER BY si bien quitando la parte del order referida a user_id. Pero el problema para ti parece ser cómo hacerlo sobre todos los clientes de una vez. Pues lanza la siguiente consulta para que veas el resultado de la consulta (he puesto el año 2010 como comienzo):
Código MySQL:
Ver original user_id,
producto_id,
user_id,
producto_id,
visit_date
Esta consulta es solo la preparación. Lo demás lo resuelves con programación recorriendo los datos tal y como están y creando un array sobre el que cargas los valores que te interesa mostrar. Eso lo devuelves y lo muestras. Esto es de programación, Neodani, y allí podrán ayudarte si es que tienes alguna dificultad, pero te hablaré de la lógica, sin escribir nada de código (
pido perdón por las aclaraciones y si nuestro moderador considera que debe quitarlas, comprenderé que lo haga).
Recorres esos datos habiendo creado una variable de control para user, otra para producto, otra para segundos, otra para clics, y un array en el que se cargará dentro del ciclo cada user_id y la suma de sus clics.
Bien, comparas así:
si user de la variable distinto de user_id que pasas, cargas el valor de user_id en user y sumas un clic a la variable clics y guardas el producto_id en producto y los segundos en la variable segundos; pasas al siguiente dato en el ciclo: si es el mismo user_id que el guardado en user, comparas el producto_id que te traes con el que tienes en producto y si es distinto, directamente sumas uno a clics y vuelves a actualizar los datos de las tres variables con los últimos recogidos; pero si tanto el user como el producto coincide, comparas los segundos y si son igual o mayor que 1800 añades 1 a clics. Cuando pasas a los datos del segundo user_id, al comparar y ver que se trata de un nuevo user, cargas los datos del user anterior y sus clics acumulados en el array, y actualizas los datos de las variables con los del nuevo user_id, producto_id, segundos, y actualizas los clics a 0, claro. Así hasta el último usuario (tendrás que ver el modo de que los datos del último también se carguen en el array con un control sobre el número de registros pasados, tal vez una variable, pero para todo esto pide ayuda en el foro de tu programa). Luego muestras el array. Es decir, el ciclo es para hacer la carga de ese array, y luego muestras el array. Pero esto tienes que programarlo.
4) Respecto a lo que preguntas sobre subconsultas, claro que podremos hacer algo, pero ahora interesa resolver primero cuál de las opciones del punto uno prefieres. Si es la segunda, la consulta que te propuse en mi primer post del hilo no te servirá, porque no te estará devolviendo los datos que buscas.
Es lo que se me ocurre.
