subconsultas - subquery sql server ejemplos

SQL: conteo y numeración de duplicados: optimización de la subconsulta correlacionada

Question

En una base de datos SQLite tengo una tabla donde necesito contar los duplicados en ciertas columnas (es decir, filas donde 3 columnas particulares son las mismas) y luego también numere cada uno de estos casos (es decir, si hay 2 ocurrencias de un duplicado particular, necesita ser numerado como 1 y 2). Me resulta un poco difícil de explicar con palabras, así que usaré un ejemplo simplificado a continuación.

Los datos que tengo son similares a los siguientes (la primera línea es la fila del encabezado, a la tabla se hace referencia en los siguientes como "idcountdata"):

id match1 match2 match3 data
1 AbCde BC 0 data01
2 AbCde BC 0 data02
3 AbCde BC 1 data03
4 AbCde AB 0 data04
5 FGhiJ BC 0 data05
6 FGhiJ AB 0 data06
7 FGhiJ BC 1 data07
8 FGhiJ BC 1 data08
9 FGhiJ BC 2 data09
10 HkLMop BC 1 data10
11 HkLMop BC 1 data11
12 HkLMop BC 1 data12
13 HkLMop DE 1 data13
14 HkLMop DE 2 data14
15 HkLMop DE 2 data15
16 HkLMop DE 2 data16
17 HkLMop DE 2 data17

Y la salida que necesito generar para lo anterior sería:

id match1 match2 match3 data matchid matchcount
1 AbCde BC 0 data01 1 2
2 AbCde BC 0 data02 2 2
3 AbCde BC 1 data03 1 1
4 AbCde AB 0 data04 1 1
5 FGhiJ BC 0 data05 1 1
6 FGhiJ AB 0 data06 1 1
7 FGhiJ BC 1 data07 1 2
8 FGhiJ BC 1 data08 2 2
9 FGhiJ BC 2 data09 1 1
10 HkLMop BC 1 data10 1 3
11 HkLMop BC 1 data11 2 3
12 HkLMop BC 1 data12 3 3
13 HkLMop DE 1 data13 1 1
14 HkLMop DE 2 data14 1 4
15 HkLMop DE 2 data15 2 4
16 HkLMop DE 2 data16 3 4
17 HkLMop DE 2 data17 4 4

Anteriormente estaba usando un par de subconsultas correlacionadas para lograr esto de la siguiente manera:

SELECT id, match1, match2, match3, data,
(SELECT count(*) FROM idcountdata d2
WHERE d1.match1=d2.match1 AND d1.match2=d2.match2 AND d1.match3=d2.match3
AND d2.id<=d1.id)
AS matchid,
(SELECT count(*) FROM idcountdata d2
WHERE d1.match1=d2.match1 AND d1.match2=d2.match2 AND d1.match3=d2.match3)
AS matchcount
FROM idcountdata d1;

Pero la tabla tiene más de 200,000 filas (y los datos pueden ser variables en longitud / contenido) y, por lo tanto, esto toma horas para ejecutarse. (Extrañamente, cuando utilicé por primera vez la misma consulta sobre los mismos datos en el período de mediados a finales de 2013, tardé minutos en lugar de horas, pero eso no viene al caso, incluso en aquel momento pensé que era poco elegante e ineficiente).

Ya he convertido la subconsulta correlacionada para "recuento de coincidencias" en el anterior a una subconsulta no correlacionada con un JOIN de la siguiente manera:

SELECT d1.id, d1.match1, d1.match2, d1.match3, d1.data,
matchcount
FROM idcountdata d1
JOIN
(SELECT id,match1,match2,match3,count(*) matchcount
FROM idcountdata
GROUP BY match1,match2,match3) d2
ON (d1.match1=d2.match1 and d1.match2=d2.match2 and d1.match3=d2.match3);

Por lo tanto, es solo la subconsulta de "coincidencia" que me gustaría obtener ayuda para optimizar.
En resumen, la siguiente consulta se ejecuta muy lentamente para conjuntos de datos más grandes:

SELECT id, match1, match2, match3, data,
(SELECT count(*) FROM idcountdata d2
WHERE d1.match1=d2.match1 AND d1.match2=d2.match2 AND d1.match3=d2.match3
AND d2.id<=d1.id)
matchid
FROM idcountdata d1;

¿Cómo puedo mejorar el rendimiento de la consulta anterior?
No tiene que ejecutarse en segundos, pero debe ser de minutos en lugar de horas (para alrededor de 200,000 filas).

Answer 1

Una auto unión puede ser más rápida que una subconsulta correlacionada

SELECT d1.id, d1.match1, d1.match2, d1.match3, d1.data, count(*) matchid
FROM idcountdata d1
JOIN idcountdata d2 on d1.match1 = d2.match1
and d1.match2 = d2.match2
and d1.match3 = d2.match3
and d1.id >= d2.id
GROUP BY d1.id, d1.match1, d1.match2, d1.match3, d1.data

Esta consulta puede aprovechar un índice compuesto en (match1,match2,match3,id)