Vuelva a escribir la consulta SQL para aceptar la condición de diferencia de date en la cláusula where

A continuación están los datos en TestingTable1

BUYER_ID | ITEM_ID | CREATED_TIME -----------+-------------------+------------------------ 1345653 110909316904 2012-07-09 21:29:06 1345653 151851771618 2012-07-09 19:57:33 1345653 221065796761 2012-07-09 19:31:48 1345653 400307563710 2012-07-09 18:57:33 1345653 310411560125 2012-07-09 16:09:49 1345653 120945302103 2012-07-09 13:40:23 1345653 261060982989 2012-07-09 09:02:21 

A continuación están los datos en TestingTable2

 USER_ID | PRODUCT_ID | LAST_TIME -----------+-------------------+------------------- 1345653 110909316904 2012-07-09 21:30:06 1345653 152851771618 2012-07-09 19:57:33 1345653 221065796761 2012-07-09 19:31:48 1345653 400307563710 2012-07-09 18:57:33 

Necesito comparar TestingTable2 con TestingTable1 en BUYER_ID y USER_ID . Y necesito encontrar todas (básicamente el recuento) las inputs faltantes y no coincidentes en TestingTable2 después de comparar de TestingTable1 . Creé el violín de SQL para esto-

http://sqlfiddle.com/#!3/d87b2/1

Si ejecuta mi consulta en el SQL Fiddle, obtendrá resultados como-

 BUYER_ID ERROR 1345653 5 

que es correcto, ya que las últimas three filas de TestingTable1 faltan en TestingTable2 y las restantes two no coinciden después de la comparación de TestingTable1 en BUYER_ID y USER_ID .

Ahora lo complicado está comenzando.

Planteamiento del problema-

En mi salida actual, estoy obteniendo el conteo de ERROR como 5 . Por lo tanto, si ve la primera fila en ambas tablas, ITEM_ID y PRODUCT_ID son las mismas, pero CREATED_TIME y LAST_TIME no son iguales, y la diferencia entre esas dos veces es de solo 1 minute . Por lo tanto, actualmente estoy informando que no coinciden, pero lo que necesito es que si la diferencia entre ellos está dentro 15 minutes range , entonces no quiero informar como un error. Entonces, después de implementar esta function en mi consulta actual, obtendré el error como 4 porque la diferencia está dentro 15 minutes range de 15 minutes range para la primera fila.

Entonces, después de tomar la ayuda de Stack Overflow, encontré la solución para esto, y abajo está la consulta sql que funciona bien en el server SQL (que dará el error count como 4) pero no en Hive porque Hive supports only equality JOINS y no puedo ejecutar la consulta a continuación en Hive. Entonces necesito otra forma de resolver este problema. ¿Es posible hacer la condición de diferencia de date en cláusula where de alguna manera? Basically how I can rewrite the below SQL query de alguna otra manera, de modo que cumpla todos mis requisitos anteriores.

 SELECT TT.BUYER_ID, COUNT(*) FROM ( SELECT testingtable1.buyer_id, testingtable1.item_id, testingtable1.created_time FROM testingtable2 RIGHT JOIN testingtable1 ON ( testingtable1.item_id = testingtable2.product_id AND testingtable1.BUYER_ID = testingtable2.USER_ID AND ABS(DATEDIFF(mi, testingtable1.created_time, testingtable2.last_time)) <= 15 ) WHERE testingtable2.product_id IS NULL ) TT GROUP BY TT.BUYER_ID; 

Resultado esperado que necesito después de implementar la característica anterior-

 BUYER_ID ERROR 1345653 4 

ACTUALIZAR:-

AS Según el comentario WEST inferior, la salida solo muestra el recuento de ERROR como 1, pero debería mostrarse como 4. Y también después de eliminar la última fila que agregó en su violín SQL, no funciona y obtengo cero error, que no es ya que ya hay un error en la diferencia de time.

¿Qué pasa si haces una equijoin y pones tu lógica de comparación de time dentro de una expresión CASE con SUM, en lugar de COUNT?

 SELECT TT1.BUYER_ID, SUM(CASE WHEN ABS(DATEDIFF(mi, TT1.created_time, TT2.last_time)) <= 15 THEN 0 ELSE 1 END) AS ERROR FROM testingtable1 TT1 LEFT JOIN testingtable2 TT2 ON ( TT1.item_id = TT2.product_id AND TT1.BUYER_ID = TT2.USER_ID ) GROUP BY TT1.BUYER_ID; 

Deberá convertir la aritmética de la date a cualquier hive que use …

Aquí hay un server MS Sql SQLFiddle que devuelve 4 errores.