Tengo la tabla mencionada a continuación en MySQL/Dataframe (versión de MySQL - 5.7.18):
Tabla 1:
ID Date uid I-1 2020-01-01 10:12:15 K-1 I-2 2020-01-02 10:12:15 K-1 I-3 2020-02-01 10:12:15 K-2 I-4 2020-02-02 10:12:15 K-3 I-5 2020-02-04 10:12:15 K-4 I-6 2019-11-01 10:12:15 K-4 I-7 2019-11-01 10:12:15 K-3 I-8 2018-12-13 10:12:15 K-5 I-9 2019-05-17 10:12:15 K-4 I-19 2020-03-11 10:12:15 K-7Tabla 2:
ID city code I-1 New York 123 I-2 Washington 122 I-3 Tokyo 123 I-4 London 144 I-5 Dubai 101 I-6 Dubai 101 I-7 London 144 I-8 Tokyo 143 I-9 Dubai 101 I-19 Dubai 150 Usando la tabla mencionada anteriormente, quiero obtener registros entre el 1 de enero de 2020 y el 29 de febrero de 2002 y comparar esos ID en toda la base de datos para verificar si tanto la city como code coinciden con otro ID y categorizarlo aún más para verificar cuántos tienen el mismo uid y cuántos tienen diferentes.
Donde,
city y coincidencia de code con otra ID en la base de datosMatch para identificar cuántos ID tienen un uid similarMatch para identificar cuántos ID no tienen un uid similarID en particular en toda la base de datosSalida requerida
ID Date city code uid Match Same_uid different_uid uid_count I-1 2020-01-01 10:12:15 New York 123 K-1 No 0 0 2 I-2 2020-01-02 10:12:15 Washington 122 K-1 No 0 0 2 I-3 2020-02-01 10:12:15 Tokyo 123 K-2 No 0 0 1 I-4 2020-02-02 10:12:15 London 144 K-3 Yes 1 0 2 I-5 2020-02-04 10:12:15 Dubai 101 K-4 Yes 2 0 3Parece (después de todas las correcciones) que necesita
SELECT t1.ID, t1.`Date`, t1. city, t1.code, t1.uid, CASE WHEN SUM((t1.city = t2.city) * (t1.code = t2.code)) - 1 THEN 'Yes' ELSE 'No' END `Match`, SUM((t1.city = t2.city) * (t1.code = t2.code) * (t1.uid = t2.uid)) - 1 same_uid, SUM((t1.city = t2.city) * (t1.code = t2.code) * (t1.uid != t2.uid)) different_uid, SUM(t1.uid = t2.uid) uid_count FROM cities t1 CROSS JOIN cities t2 WHERE t1.`Date` >= '2020-01-01' AND t1.`Date` < '2020-03-01' GROUP BY t1.ID, t1.`Date`, t1. city, t1.code, t1.uid ORDER BY t1.ID PD. Las multiplicaciones en SUM() s pueden reemplazarse con AND .
¿Qué pasa si tengo esta información en dos tablas diferentes?
SELECT t11.ID, t11.`Date`, t21.city, t21.code, t11.uid, CASE WHEN SUM((t21.city = t22.city) * (t21.code = t22.code)) - 1 THEN 'Yes' ELSE 'No' END `Match`, SUM((t21.city = t22.city) * (t21.code = t22.code) * (t11.uid = t12.uid)) - 1 same_uid, SUM((t21.city = t22.city) * (t21.code = t22.code) * (t11.uid != t12.uid)) different_uid, SUM(t11.uid = t12.uid) uid_count FROM /* cities t1 */ (Table_1 t11 NATURAL JOIN Table_2 t21) CROSS JOIN /* cities t2 */ (Table_1 t12 NATURAL JOIN Table_2 t22) WHERE t11.`Date` >= '2020-01-01' AND t11.`Date` < '2020-03-01' GROUP BY t11.ID, t11.`Date`, t21.city, t21.code, t11.uid ORDER BY t11.ID