Queries avanzadas con SQL que te ahorrarán el tiempo

Durante los años del trabajo con datos de telecomunicaciones, mi carpeta con fragmentos de código recopiló muchos ejemplos reutilizables. Y no se trata de “SELECT * FROM Table1”, estoy hablando de buscar y manejar o eliminar valores duplicados, seleccionar top N valores de cada grupo de datos dentro de la misma tabla, barajar registros dentro de los grupos, pero mantener los grupos ordenados, encontrar la coincidencia más larga a la izquierda, ampliando los números por N dígitos y así sucesivamente.

Hoy me gustaría compartir esos fragmentos y explicar cómo funciona todo. Trabajo con Microsoft SQL Server y uso T-SQL, pero estoy seguro de que es posible encontrar alternativas para otros DBMS. Si no, contácteme e intentaremos encontrarlo juntos 🙂

Para este artículo he preparado la tabla de llamadas falsas con números falsos y países reales, por lo que si intentas repetir la misma transacción no funcionará 😀 (ok, excepto para el Reino Unido, realmente tienen un código de país de +44, pero No estoy seguro de que exista el número que usé)

Tratamiento de duplicados

La forma más rápida y sencilla de seleccionar solo valores únicos de la tabla es usando la palabra clave DISTINCT

SELECT DISTINCT *
FROM [test].[dbo].[12162018_FakeCalls]

Al indicar las columnas que queremos recuperar, podemos seleccionar valores únicos basados solo en números y países.

SELECT DISTINCT NumberFrom, CountryFrom, NumberTo, CountryTo
FROM [test].[dbo].[12162018_FakeCalls]

Pero perdemos información de otras columnas.

También podemos encontrar duplicados con GROUP BY, HAVING y COUNT(*).

SELECT NumberFrom, CountryFrom, NumberTo, CountryTo, count(*) as dups
FROM [test].[dbo].[12162018_FakeCalls]
GROUP BY NumberFrom, CountryFrom, NumberTo, CountryTo
HAVING COUNT(*) > 1
ORDER BY dups desc

Sé que, en la base de datos SQL, los duplicados no deberían aparecer en absoluto, debe usar ID, restricciones e índices únicos. Pero a veces simplemente haces algunas pruebas o algunos cálculos por primera vez, o experimentas y aparecen duplicados. O los registros tienen identificaciones distintas, aunque entiendes que uno de ellos debe ser eliminado. ¿Qué harías en estos casos? Hace mucho tiempo encontré esta solución en stackoverflow, la guardé y desde entonces la uso al menos unas cuantas veces al día (los duplicados son bastante comunes, específicos del trabajo). WITH statement y ROW_NUMBER () son mis salvadores.

WITH CTE AS(
   SELECT NumberFrom, CountryFrom, NumberTo, CountryTo, Duration, id,
       RN = ROW_NUMBER()OVER(PARTITION BY NumberFrom, CountryFrom, NumberTo, CountryTo ORDER BY CountryFrom)
   FROM #t
)
SELECT * FROM CTE WHERE RN > 1

He copiado los datos originales en la tabla temporal #t, porque “Deshacer” no funciona después de eliminar, jej 🙂 y vemos el mismo resultado que con GROUP BY, aunque con todas las columnas. Y ahora podemos realizar un DELETE aquí y eliminar todos los valores de duplicación según nuestra definición de duplicación.

WITH CTE AS(
   SELECT NumberFrom, CountryFrom, NumberTo, CountryTo, Duration, id,
       RN = ROW_NUMBER()OVER(PARTITION BY NumberFrom, CountryFrom, NumberTo, CountryTo ORDER BY CountryFrom)
   FROM #t
)
DELETE FROM CTE WHERE RN > 1;

SELECT *
FROM #t

PARTITION BY servirá como la definición de duplicación en este caso y con ORDER BY podemos, por ejemplo, eliminar llamadas que duraron menos o más tiempo o aquellas que comenzaron antes / después o cualquier condición que pueda encontrar en otras columnas. Tenga en cuenta que la cláusula ORDER BY es obligatoria, por lo que si no tiene una condición específica para realizar el ORDER BY, simplemente coloque cualquier campo allí.

Seleccionando top N records dentro del grupo

Creo que ya sabes cómo hacerlo: D. Sí, con ROW_NUMBER () otra vez. Esta función dividirá nuestros datos y asignará un número a cada registro dentro de un grupo, por lo tanto, solo tenemos que especificar qué rango de top N queremos seleccionar: ¿top 10? – RN <= 10, ¿desde el top 3 al top 7? - fácil, RN entre 3 y 7 o RN> = 3 y RN <= 7.

Seleccionemos solo el segundo registro superior dentro de los grupos de países desde los cuales se realizaron las llamadas (“CountryFrom”) que tienen sus nombres que comienzan con la misma letra y clasifican los valores por duración dentro de los grupos. (He insertado los mismos datos en la tabla temporal 3 veces para ver mejor las diferencias)

WITH CTE AS(
   SELECT NumberFrom, CountryFrom, NumberTo, CountryTo, Duration, id,
       RN = ROW_NUMBER()OVER(PARTITION BY LEFT(CountryFrom,1) ORDER BY Duration ASC)
   FROM #t
)
SELECT * 
FROM CTE;

Mira cómo Alemania y Grecia se ponen en un grupo e Irlanda con Italia en otro. Y para seleccionar solo el segundo registro dentro de cada grupo:

WITH CTE AS(
   SELECT NumberFrom, CountryFrom, NumberTo, CountryTo, Duration, id,
       RN = ROW_NUMBER()OVER(PARTITION BY LEFT(CountryFrom,1) ORDER BY Duration ASC)
   FROM #t
)
SELECT * 
FROM CTE 
WHERE RN = 2;

Mezclar registros dentro del grupo

En realidad, esta técnica se puede utilizar para mezclar registros en cualquier caso, no solo dentro del grupo, pero como ya hemos empezado con los grupos, ROW_NUMBER() y WITH, decidí hacerlo un poco más complicado. No sé si en algún período de tu vida tendrás que barajar los registros dentro de un grupo, pero si aparece esa ocasión, sabrás cómo hacerlo. De nuevo, esto es parte de mi trabajo diario y sé que es una tarea rara :)) Así que volvamos a las consultas. Usaremos la misma condición de agrupación y, para mezclar, será una combinación de funciones ABS() – función que se usa para obtener el valor absoluto de un número pasado como argumento, CHECKSUM() – función que devuelve el valor de suma de comprobación calculado sobre una fila de la tabla, o sobre una lista de expresiones, y NEWID() que crea un valor único de tipo identificador único. Mientras lees, puedes pensar: ¿qué demonios? Pero te lo digo, hace el trabajo. Mira:

WITH CTE AS(
   SELECT NumberFrom, CountryFrom, NumberTo, CountryTo, CallStarted, Duration, id,
       RN = ROW_NUMBER()OVER(PARTITION BY LEFT(CountryFrom,1) ORDER BY ABS(CHECKSUM(NewId())) % 1000000 DESC)
   FROM [test].[dbo].[12162018_FakeCalls]
)
SELECT * FROM CTE

Puede que no sea tan obvio con una pequeña cantidad de datos, pero lo más emocionante es que cada vez que ejecuta esta consulta, se mezcla de nuevo. Así que después de una segunda carrera:

Creo que hayas notado que ahora Polonia es el primer registro y España es el segundo.

Es todo por hoy. Algunos consejos con ROW_NUMBER () que me ayudan mucho en mi trabajo y espero que también ayuden a alguien más.

A pesar de que no incluye el aprendizaje automático, la inteligencia artificial, el reconocimiento de imágenes, el ajuste de modelos, las redes neuronales profundas o cualquier otro término sofisticado que aparezca en el campo de la ciencia de datos, trabajar con datos mediante SQL sigue siendo parte de la ciencia de datos. Y todavía hay muchos profesionales de datos que trabajan con este lenguaje y bases de datos relacionales. No olvidemos nuestras raíces, mis compañeros.

Gracias por leer, que seas cool, profesional y tengas un día fantástico.

Foto de Kalen Emsley en Unsplash