{"id":682,"date":"2018-12-16T21:01:38","date_gmt":"2018-12-16T20:01:38","guid":{"rendered":"http:\/\/35.180.88.53\/?p=682"},"modified":"2018-12-17T22:52:04","modified_gmt":"2018-12-17T21:52:04","slug":"advanced-queries-with-sql-that-will-save-your-time","status":"publish","type":"post","link":"https:\/\/www.sergilehkyi.com\/es\/2018\/12\/advanced-queries-with-sql-that-will-save-your-time\/","title":{"rendered":"Queries avanzadas con SQL que te ahorrar\u00e1n el tiempo"},"content":{"rendered":"\n

Durante los a\u00f1os del trabajo con datos de telecomunicaciones, mi carpeta con fragmentos de c\u00f3digo recopil\u00f3 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\u00e1s larga a la izquierda, ampliando los n\u00fameros por N d\u00edgitos y as\u00ed sucesivamente.<\/p>\n\n\n\n

Hoy me gustar\u00eda compartir esos fragmentos y explicar c\u00f3mo 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\u00e1cteme e intentaremos encontrarlo juntos \ud83d\ude42<\/p>\n\n\n\n

Para este art\u00edculo he preparado la tabla de llamadas falsas con n\u00fameros falsos y pa\u00edses reales, por lo que si intentas repetir la misma transacci\u00f3n no funcionar\u00e1 \ud83d\ude00 (ok, excepto para el Reino Unido, realmente tienen un c\u00f3digo de pa\u00eds de +44, pero No estoy seguro de que exista el n\u00famero que us\u00e9)<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Tratamiento de duplicados<\/h3>\n\n\n\n

La forma m\u00e1s r\u00e1pida y sencilla de seleccionar solo valores \u00fanicos de la tabla es usando la palabra clave DISTINCT<\/p>\n\n\n\n

SELECT DISTINCT *
FROM [test].[dbo].[12162018_FakeCalls]<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Al indicar las columnas que queremos recuperar, podemos seleccionar valores \u00fanicos basados solo en n\u00fameros y pa\u00edses.<\/p>\n\n\n\n
SELECT DISTINCT NumberFrom, CountryFrom, NumberTo, CountryTo
FROM [test].[dbo].[12162018_FakeCalls]<\/pre>\n\n\n\n
\"\"<\/figure><\/div>\n\n\n\n
Pero perdemos informaci\u00f3n de otras columnas.<\/p>\n\n\n\n
Tambi\u00e9n podemos encontrar duplicados con GROUP BY, HAVING y COUNT(*).<\/p>\n\n\n\n
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<\/pre>\n\n\n\n
\"\"<\/figure><\/div>\n\n\n\n
S\u00e9 que, en la base de datos SQL, los duplicados no deber\u00edan aparecer en absoluto, debe usar ID, restricciones e \u00edndices \u00fanicos. Pero a veces simplemente haces algunas pruebas o algunos c\u00e1lculos por primera vez, o experimentas y aparecen duplicados. O los registros tienen identificaciones distintas, aunque entiendes que uno de ellos debe ser eliminado. \u00bfQu\u00e9 har\u00edas en estos casos? Hace mucho tiempo encontr\u00e9 esta soluci\u00f3n en stackoverflow, la guard\u00e9 y desde entonces la uso al menos unas cuantas veces al d\u00eda (los duplicados son bastante comunes, espec\u00edficos del trabajo). WITH statement y ROW_NUMBER () son mis salvadores.<\/p>\n\n\n\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
)
SELECT * FROM CTE WHERE RN > 1<\/pre>\n\n\n\n
\"\"<\/figure><\/div>\n\n\n\n
He copiado los datos originales en la tabla temporal #t, porque “Deshacer” no funciona despu\u00e9s de eliminar, jej \ud83d\ude42 y vemos el mismo resultado que con GROUP BY, aunque con todas las columnas. Y ahora podemos realizar un DELETE aqu\u00ed y eliminar todos los valores de duplicaci\u00f3n seg\u00fan nuestra definici\u00f3n de duplicaci\u00f3n.<\/p>\n\n\n\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<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
PARTITION BY servir\u00e1 como la definici\u00f3n de duplicaci\u00f3n en este caso y con ORDER BY podemos, por ejemplo, eliminar llamadas que duraron menos o m\u00e1s tiempo o aquellas que comenzaron antes \/ despu\u00e9s o cualquier condici\u00f3n que pueda encontrar en otras columnas. Tenga en cuenta que la cl\u00e1usula ORDER BY es obligatoria, por lo que si no tiene una condici\u00f3n espec\u00edfica para realizar el ORDER BY, simplemente coloque cualquier campo all\u00ed.<\/p>\n\n\n\n
Seleccionando top N records dentro del grupo<\/h3>\n\n\n\n
Creo que ya sabes c\u00f3mo hacerlo: D. S\u00ed, con ROW_NUMBER () otra vez. Esta funci\u00f3n dividir\u00e1 nuestros datos y asignar\u00e1 un n\u00famero a cada registro dentro de un grupo, por lo tanto, solo tenemos que especificar qu\u00e9 rango de top N queremos seleccionar: \u00bftop 10? – RN <= 10, \u00bfdesde el top 3 al top 7? - f\u00e1cil, RN entre 3 y 7 o RN> = 3 y RN <= 7.<\/p>\n\n\n\n
Seleccionemos solo el segundo registro superior dentro de los grupos de pa\u00edses desde los cuales se realizaron las llamadas (“CountryFrom”) que tienen sus nombres que comienzan con la misma letra y clasifican los valores por duraci\u00f3n dentro de los grupos. (He insertado los mismos datos en la tabla temporal 3 veces para ver mejor las diferencias)<\/p>\n\n\n\n
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;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Mira c\u00f3mo 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:<\/p>\n\n\n\n
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;<\/pre>\n\n\n\n
\"\"<\/figure><\/div>\n\n\n\n
Mezclar registros dentro del grupo<\/h3>\n\n\n\n
En realidad, esta t\u00e9cnica 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\u00ed hacerlo un poco m\u00e1s complicado. No s\u00e9 si en alg\u00fan per\u00edodo de tu vida tendr\u00e1s que barajar los registros dentro de un grupo, pero si aparece esa ocasi\u00f3n, sabr\u00e1s c\u00f3mo hacerlo. De nuevo, esto es parte de mi trabajo diario y s\u00e9 que es una tarea rara :)) As\u00ed que volvamos a las consultas. Usaremos la misma condici\u00f3n de agrupaci\u00f3n y, para mezclar, ser\u00e1 una combinaci\u00f3n de funciones ABS() – funci\u00f3n que se usa para obtener el valor absoluto de un n\u00famero pasado como argumento, CHECKSUM() – funci\u00f3n que devuelve el valor de suma de comprobaci\u00f3n calculado sobre una fila de la tabla, o sobre una lista de expresiones, y NEWID() que crea un valor \u00fanico de tipo identificador \u00fanico. Mientras lees, puedes pensar: \u00bfqu\u00e9 demonios? Pero te lo digo, hace el trabajo. Mira:<\/p>\n\n\n\n
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<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Puede que no sea tan obvio con una peque\u00f1a cantidad de datos, pero lo m\u00e1s emocionante es que cada vez que ejecuta esta consulta, se mezcla de nuevo. As\u00ed que despu\u00e9s de una segunda carrera:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Creo que hayas notado que ahora Polonia es el primer registro y Espa\u00f1a es el segundo.<\/p>\n\n\n\n
Es todo por hoy. Algunos consejos con ROW_NUMBER () que me ayudan mucho en mi trabajo y espero que tambi\u00e9n ayuden a alguien m\u00e1s.<\/p>\n\n\n\n
\n\n\n\n
A pesar de que no incluye el aprendizaje autom\u00e1tico, la inteligencia artificial, el reconocimiento de im\u00e1genes, el ajuste de modelos, las redes neuronales profundas o cualquier otro t\u00e9rmino 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\u00eda hay muchos profesionales de datos que trabajan con este lenguaje y bases de datos relacionales. No olvidemos nuestras ra\u00edces, mis compa\u00f1eros.<\/p>\n\n\n\n
Gracias por leer, que seas cool, profesional y tengas un d\u00eda fant\u00e1stico.<\/p>\n\n\n\n
Foto de Kalen Emsley<\/a> en Unsplash<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Durante los a\u00f1os del trabajo con datos de telecomunicaciones, mi carpeta con fragmentos de c\u00f3digo recopil\u00f3 muchos ejemplos reutilizables. Y…<\/p>\n","protected":false},"author":1,"featured_media":694,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,5],"tags":[],"translation":{"provider":"WPGlobus","version":"3.0.0","language":"es","enabled_languages":["gb","es","uk"],"languages":{"gb":{"title":true,"content":true,"excerpt":false},"es":{"title":true,"content":true,"excerpt":false},"uk":{"title":true,"content":true,"excerpt":false}}},"_links":{"self":[{"href":"https:\/\/www.sergilehkyi.com\/es\/wp-json\/wp\/v2\/posts\/682"}],"collection":[{"href":"https:\/\/www.sergilehkyi.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sergilehkyi.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sergilehkyi.com\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sergilehkyi.com\/es\/wp-json\/wp\/v2\/comments?post=682"}],"version-history":[{"count":7,"href":"https:\/\/www.sergilehkyi.com\/es\/wp-json\/wp\/v2\/posts\/682\/revisions"}],"predecessor-version":[{"id":708,"href":"https:\/\/www.sergilehkyi.com\/es\/wp-json\/wp\/v2\/posts\/682\/revisions\/708"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sergilehkyi.com\/es\/wp-json\/wp\/v2\/media\/694"}],"wp:attachment":[{"href":"https:\/\/www.sergilehkyi.com\/es\/wp-json\/wp\/v2\/media?parent=682"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sergilehkyi.com\/es\/wp-json\/wp\/v2\/categories?post=682"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sergilehkyi.com\/es\/wp-json\/wp\/v2\/tags?post=682"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}