none
Obtener la agrupacion (o foco) de datos deseada, en una tabla de numeros aleatorios. RRS feed

  • Pregunta

  • Un saludo a tod@s

    Tengo una duda respecto a la posibilidad de si existe una manera de encontrar una cantidad DETERMINADA de DATOS en una tabla de "N" columnas, agrupados en grupos acotados por ID (autoincremental) de "X" registros que se pueden hallar en cualquier campo de las columnas, en cualquier orden, cualquier posición y resulten estar(sin importar)repetidos o no repetidos.

    De alguna manera sería "visualmente" como hallar el "foco" más concentrado o más "caliente" del grupo de datos que coincide o que más se ajusta a la consulta que se desea realizar en una tabla de números aleatorios. El escenario al que me refiero como ejemplo sería el siguiente:

    CREATE TABLE LISTADOALEATORIO (ID INT IDENTITY(1,1), COLUMNA1 INT, COLUMNA2 INT, COLUMNA3 INT, COLUMNA4 INT);
    GO
    ;WITH R AS (
    		 SELECT 1 AS C UNION ALL SELECT 1),
        R1 AS (SELECT 1 AS C FROM R  AS A CROSS JOIN R  AS B),
        R2 AS (SELECT 1 AS C FROM R1 AS A CROSS JOIN R1 AS B),
        R3 AS (SELECT 1 AS C FROM R2 AS A CROSS JOIN R2 AS B),
        R4 AS (SELECT 1 AS C FROM R3 AS A CROSS JOIN R3 AS B),
    	R5 AS (SELECT 1 AS C FROM R4 AS A CROSS JOIN R4 AS B),
        R6 AS (
    	   SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS FILA FROM R5),
        Filas As(
    	   SELECT  TOP (2000) R6.FILA FROM R6
    )
    INSERT INTO LISTADOALEATORIO 
    SELECT 
        1.0 + floor(29 * RAND(convert(varbinary, newid()))) COLUMNA1,
        1.0 + floor(29 * RAND(convert(varbinary, newid()))) COLUMNA2,
    	1.0 + floor(29 * RAND(convert(varbinary, newid()))) COLUMNA3,
        1.0 + floor(29 * RAND(convert(varbinary, newid()))) COLUMNA4
    FROM Filas;

    Con este código creamos una tabla llamada "LISTADOALEATORIO" con esas columnas y con números ALEATORIOS que van del 1 al 29:

    Cómo se puede ver en la imagen, existe un ID autoincremental y 4 columnas cuyos campos son: COLUMNA1, COLUMNA2, COLUMNA3 y COLUMNA4. Con un total de 2000 registros de números aleatorios como decía.

    Ahora voy a realizar la consulta con el siguiente código:

    select *
        from LISTADOALEATORIO T1 
         where COLUMNA1 = '1' OR COLUMNA2 = '1' OR COLUMNA3 = '1' or COLUMNA4 = '1'  AND COLUMNA1 = '6' OR COLUMNA2 = '6' OR COLUMNA3 = '6' or COLUMNA4 = '6'  AND COLUMNA1 = '9' OR COLUMNA2 = '9' OR COLUMNA3 = '9' or COLUMNA4 = '9'  AND COLUMNA1 = '24' OR COLUMNA2 = '24' OR COLUMNA3 = '24' or COLUMNA4 = '24'  AND COLUMNA1 = '17' OR COLUMNA2 = '17' OR COLUMNA3 = '17' or COLUMNA4 = '17' AND COLUMNA1 = '2' OR COLUMNA2 = '2' OR COLUMNA3 = '2' or COLUMNA4 = '2' AND COLUMNA1 = '3' OR COLUMNA2 = '3' OR COLUMNA3 = '3' or COLUMNA4 = '3' AND COLUMNA1 = '4' OR COLUMNA2 = '4' OR COLUMNA3 = '4' or COLUMNA4 = '4'  AND COLUMNA1 = '5' OR COLUMNA2 = '5' OR COLUMNA3 = '5' or COLUMNA4 = '5' AND COLUMNA1 = '7' OR COLUMNA2 = '7' OR COLUMNA3 = '7' or COLUMNA4 = '7'  AND COLUMNA1 = '10' OR COLUMNA2 = '10' OR COLUMNA3 = '10' or COLUMNA4 = '10' AND COLUMNA1 = '12' OR COLUMNA2 = '12' OR COLUMNA3 = '12' or COLUMNA4 = '12'   AND exists 
           (select id
           from LISTADOALEATORIO T2
           where
         T2.COLUMNA1 = '1' OR COLUMNA2 = '1' OR COLUMNA3 = '1' or COLUMNA4 = '1'  AND COLUMNA1 = '6' OR COLUMNA2 = '6' OR COLUMNA3 = '6' or COLUMNA4 = '6'  AND COLUMNA1 = '9' OR COLUMNA2 = '9' OR COLUMNA3 = '9' or COLUMNA4 = '9'  AND COLUMNA1 = '24' OR COLUMNA2 = '24' OR COLUMNA3 = '24' or COLUMNA4 = '24'  AND COLUMNA1 = '17' OR COLUMNA2 = '17' OR COLUMNA3 = '17' or COLUMNA4 = '17' AND COLUMNA1 = '2' OR COLUMNA2 = '2' OR COLUMNA3 = '2' or COLUMNA4 = '2' AND COLUMNA1 = '3' OR COLUMNA2 = '3' OR COLUMNA3 = '3' or COLUMNA4 = '3' AND COLUMNA1 = '4' OR COLUMNA2 = '4' OR COLUMNA3 = '4' or COLUMNA4 = '4'  AND COLUMNA1 = '5' OR COLUMNA2 = '5' OR COLUMNA3 = '5' or COLUMNA4 = '5' AND COLUMNA1 = '7' OR COLUMNA2 = '7' OR COLUMNA3 = '7' or COLUMNA4 = '7'  AND COLUMNA1 = '10' OR COLUMNA2 = '10' OR COLUMNA3 = '10' or COLUMNA4 = '10' AND COLUMNA1 = '12' OR COLUMNA2 = '12' OR COLUMNA3 = '12' or COLUMNA4 = '12' 
    	 
      AND
    
        T2.ID >= T1.ID - 10
    AND T2.ID <= T1.ID + 10
    AND T1.ID <> T2.ID
    )



    Aquí lo que hago es simplemente decirle que me busque de entre todos los 2000 datos de la tabla los que coincidan con los números: 1/6/9/24/17/2/3/4/5/7/10/12 en cualquier columna y en cualquier orden, estén repetidos o no lo estén.

    El resultado de esta consulta es:


    Me ha devuelto 1368 filas que ha encontrado que coinciden con los parámetros de la búsqueda que le he realizado.

    Al haber indicado esto en el código:

     AND
    
        T2.ID >= T1.ID - 10
    AND T2.ID <= T1.ID + 10
    AND T1.ID <> T2.ID

    Lo que me ha devuelto cómo indicaba al principio del post, es que los resultados estén agrupados por ID=+-10, es decir, que entre las fila a devolver del resultado no exista una distancia de ID mayor a 10. Esto lo hago porque la idea es que me agrupe los resultados en conjuntos de 10.

    Hasta aquí sería el ejemplo del escenario en el que me encuentros para poder situarnos. Y el problema en cuestión es que de todos esos resultados que me ha devuelto (las 1368 filas) yo quiero que SÓLO me devuelva las agrupaciones de 10 que contengan una MAYOR CONCENTRACIÓN de esos datos que yo he introducido en la consulta: (1/6/9/24/17/2/3/4/5/7/10/12).

    He probado con COUNT, GROUP BY, HAVING...ect y no consigo la sintaxis correcta para lograrlo. Voy a poner una imagen de lo que intento obtener para que se pueda ver gráficamente:

    En la TABLA A se puede ver que la concentración de números a buscar es menor que en la TABLA B donde aunque hay registros que no coinciden con los introducidos en la consulta, la mayoría si que forman parte de ella. Por lo tanto La TABLA B es la tabla que contiene un grupo de 10 filas con mayor cantidad concentrada de los números seleccionados.

    Gracias de antemano por vuestra atención.

     

    domingo, 14 de abril de 2019 15:13

Respuestas

  • Hola marcosmrc:

    A lo mejor es un poco demasiado complicado, pero puedes hacer algo así

    Lo primero es que voy a reconvertir tu select en un select into

    select * into listadoFiltrado
        from LISTADOALEATORIO T1 
         where COLUMNA1 = '1' OR COLUMNA2 = '1' OR COLUMNA3 = '1' or COLUMNA4 = '1'  AND COLUMNA1 = '6' OR COLUMNA2 = '6' OR COLUMNA3 = '6' or COLUMNA4 = '6'  AND COLUMNA1 = '9' OR COLUMNA2 = '9' OR COLUMNA3 = '9' or COLUMNA4 = '9'  AND COLUMNA1 = '24' OR COLUMNA2 = '24' OR COLUMNA3 = '24' or COLUMNA4 = '24'  AND COLUMNA1 = '17' OR COLUMNA2 = '17' OR COLUMNA3 = '17' or COLUMNA4 = '17' AND COLUMNA1 = '2' OR COLUMNA2 = '2' OR COLUMNA3 = '2' or COLUMNA4 = '2' AND COLUMNA1 = '3' OR COLUMNA2 = '3' OR COLUMNA3 = '3' or COLUMNA4 = '3' AND COLUMNA1 = '4' OR COLUMNA2 = '4' OR COLUMNA3 = '4' or COLUMNA4 = '4'  AND COLUMNA1 = '5' OR COLUMNA2 = '5' OR COLUMNA3 = '5' or COLUMNA4 = '5' AND COLUMNA1 = '7' OR COLUMNA2 = '7' OR COLUMNA3 = '7' or COLUMNA4 = '7'  AND COLUMNA1 = '10' OR COLUMNA2 = '10' OR COLUMNA3 = '10' or COLUMNA4 = '10' AND COLUMNA1 = '12' OR COLUMNA2 = '12' OR COLUMNA3 = '12' or COLUMNA4 = '12'   AND exists 
           (select id
           from LISTADOALEATORIO T2
           where
         T2.COLUMNA1 = '1' OR COLUMNA2 = '1' OR COLUMNA3 = '1' or COLUMNA4 = '1'  AND COLUMNA1 = '6' OR COLUMNA2 = '6' OR COLUMNA3 = '6' or COLUMNA4 = '6'  AND COLUMNA1 = '9' OR COLUMNA2 = '9' OR COLUMNA3 = '9' or COLUMNA4 = '9'  AND COLUMNA1 = '24' OR COLUMNA2 = '24' OR COLUMNA3 = '24' or COLUMNA4 = '24'  AND COLUMNA1 = '17' OR COLUMNA2 = '17' OR COLUMNA3 = '17' or COLUMNA4 = '17' AND COLUMNA1 = '2' OR COLUMNA2 = '2' OR COLUMNA3 = '2' or COLUMNA4 = '2' AND COLUMNA1 = '3' OR COLUMNA2 = '3' OR COLUMNA3 = '3' or COLUMNA4 = '3' AND COLUMNA1 = '4' OR COLUMNA2 = '4' OR COLUMNA3 = '4' or COLUMNA4 = '4'  AND COLUMNA1 = '5' OR COLUMNA2 = '5' OR COLUMNA3 = '5' or COLUMNA4 = '5' AND COLUMNA1 = '7' OR COLUMNA2 = '7' OR COLUMNA3 = '7' or COLUMNA4 = '7'  AND COLUMNA1 = '10' OR COLUMNA2 = '10' OR COLUMNA3 = '10' or COLUMNA4 = '10' AND COLUMNA1 = '12' OR COLUMNA2 = '12' OR COLUMNA3 = '12' or COLUMNA4 = '12' 
    	 
      AND
    
        T2.ID >= T1.ID - 10
    AND T2.ID <= T1.ID + 10
    AND T1.ID <> T2.ID
    )

    Ya tengo tu select en una tabla llamada listadoFiltrado.

    ;WITH R AS (
    		 SELECT 1 AS C UNION ALL SELECT 1),
        R1 AS (SELECT 1 AS C FROM R  AS A CROSS JOIN R  AS B),
        R2 AS (SELECT 1 AS C FROM R1 AS A CROSS JOIN R1 AS B),
        R3 AS (SELECT 1 AS C FROM R2 AS A CROSS JOIN R2 AS B),
        R4 AS (SELECT 1 AS C FROM R3 AS A CROSS JOIN R3 AS B),
    	R5 AS (SELECT 1 AS C FROM R4 AS A CROSS JOIN R4 AS B),
        R6 AS (
    	   SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS FILA FROM R5),
        Filas As(
    	   SELECT  TOP (2000) R6.FILA FROM R6
    ), EXISTENTES AS (
        Select Filas.FILA, l.ID
    	   from Filas 
    		  left join listadofiltrado l on Filas.FILA = L.ID
    ),BL1 AS (
        SELECT E.FILA, E.ID, LEAD(E.ID) OVER(ORDER BY FILA) AS SIG FROM EXISTENTES E
    ),BL2 AS (
       SELECT E.FILA, E.ID, E.SIG, LEAD(E.SIG) OVER(ORDER BY FILA) AS SIG2 FROM BL1 E
    ),BL3 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, LEAD(E.SIG2) OVER(ORDER BY FILA) AS SIG3 FROM BL2 E
    ),BL4 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, LEAD(E.SIG3) OVER(ORDER BY FILA) AS SIG4 FROM BL3 E
    ),BL5 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, LEAD(E.SIG4) OVER(ORDER BY FILA) AS SIG5  FROM BL4 E
    ),BL6 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, E.SIG5, LEAD(E.SIG5) OVER(ORDER BY FILA) AS SIG6 FROM BL5 E
    ),BL7 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, E.SIG5, E.SIG6, LEAD(E.SIG6) OVER(ORDER BY FILA) AS SIG7 FROM BL6 E
    ),BL8 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, E.SIG5, E.SIG6, E.SIG7, LEAD(E.SIG7) OVER(ORDER BY FILA) AS SIG8 FROM BL7 E
    ),BL9 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, E.SIG5, E.SIG6, E.SIG7, E.SIG8, LEAD(E.SIG8) OVER(ORDER BY FILA) AS SIG9 FROM BL8 E
    ), GRUPOS AS (
    SELECT FILA, ID, 
    CASE WHEN SIG  IS NULL THEN 0 ELSE 1 END AS SIG1,
    CASE WHEN SIG2 IS NULL THEN 0 ELSE 1 END AS SIG2,
    CASE WHEN SIG3 IS NULL THEN 0 ELSE 1 END AS SIG3,
    CASE WHEN SIG4 IS NULL THEN 0 ELSE 1 END AS SIG4,
    CASE WHEN SIG5 IS NULL THEN 0 ELSE 1 END AS SIG5,
    CASE WHEN SIG6 IS NULL THEN 0 ELSE 1 END AS SIG6,
    CASE WHEN SIG7 IS NULL THEN 0 ELSE 1 END AS SIG7,
    CASE WHEN SIG8 IS NULL THEN 0 ELSE 1 END AS SIG8,
    CASE WHEN SIG9 IS NULL THEN 0 ELSE 1 END AS SIG9
    
     FROM BL9
     ), RESUMEN AS (
    SELECT FILA, ID, SIG1+SIG2+SIG3+SIG4+SIG5+SIG6+SIG7+SIG8+SIG9 AS GRUPO1
        FROM GRUPOS 
    where id is not null
    )
    SELECT TOP(10) L.*
    FROM RESUMEN R INNER JOIN LISTADOFILTRADO L ON R.FILA = L.ID
    ORDER BY R.GRUPO1 DESC

    Sobre la misma me genero las 2000 filas  y las relaciono con listado filtrado

    Luego cojo de cada uno su valor siguiente, para poder tener 10 filas, y si el siguiente, y sucesivos no son nulos, le pongo un 1 sino un 0, y así se si por las 2000 filas se repiten y cuantas veces, y por ultimo sumo todos y ordeno por mayores repeticiones.


    Espero te ayude

    • Propuesto como respuesta Carlos_Ruiz_M miércoles, 17 de abril de 2019 14:14
    • Marcado como respuesta marcosmrc miércoles, 17 de abril de 2019 15:13
    miércoles, 17 de abril de 2019 5:23
  • Hola marcosmrc:

    Ejemplo con AdventureWorks2017

    WITH origenDatos
    	AS (
    	    SELECT    o.fila as ID
    			  , o.SalesPersonID AS COLUMNA1
    			  , o.Mes AS COLUMNA2
    			  , o.TerritoryID AS COLUMNA3
    			  , o.TerritoryID AS COLUMNA4
    	    FROM          
    	    (
    /* tabla que contiene los datos realmente*/
    		   SELECT ROW_NUMBER() OVER(
    				ORDER BY salesPersonId
    					  , ShipDate
    					  , territoryId) AS fila 
    /* numeramos los registros del primero al último */
    			   , SalesPersonID
    			   , MONTH(SHIPdATE) AS Mes
    			   , TerritoryID
    		   FROM   
    			   sales.SalesOrderHeader
    		   WHERE  SalesPersonID IS NOT NULL
    	    ) AS o /* la salida del origen de la tabla fuente la llamamos o */
        where (o.Mes = 1 and TerritoryID >4)
    	   or 
    		  ((o.Mes between 3 and 5) and (TerritoryID between 1 and 3))	  
    	   or 
    		  ((o.Mes between 7 and 12) and (TerritoryID between 4 and 9))  
    /*Quitamos filas, porque no encajan en nuestra lógica de negocio, ya que 
    la idea de esta consulta, era saber que en una determinada cantidad de datos
    cuales son los que contienen más, Luego jugaremos con las columnas, para saber
    si hay mas usuarios, o mas meses o mas territorios*/
    ), ListadoDatos AS(
    select ID, COLUMNA1, COLUMNA2, COLUMNA3, COLUMNA4  
        from origenDatos T1 
         where 
    		  (COLUMNA1 = '274' OR COLUMNA2 = '8' OR COLUMNA3 = '6' OR COLUMNA4 = '1') 
    	   OR (COLUMNA1 = '6' OR COLUMNA2 = '6' OR COLUMNA3 = '6' OR COLUMNA4 = '6')  
    	   OR (COLUMNA1 = '9' OR COLUMNA2 = '9' OR COLUMNA3 = '9' OR COLUMNA4 = '9')  
    	   OR (COLUMNA1 = '24' OR COLUMNA2 = '24' OR COLUMNA3 = '24' OR COLUMNA4 = '24')  
    	   OR (COLUMNA1 = '17' OR COLUMNA2 = '17' OR COLUMNA3 = '17' OR COLUMNA4 = '17') 
    	   OR (COLUMNA1 = '2' OR COLUMNA2 = '2' OR COLUMNA3 = '2' OR COLUMNA4 = '2') 
    	   OR (COLUMNA1 = '3' OR COLUMNA2 = '3' OR COLUMNA3 = '3' OR COLUMNA4 = '3') 
    	   OR (COLUMNA1 = '4' OR COLUMNA2 = '4' OR COLUMNA3 = '4' OR COLUMNA4 = '4')  
    	   OR (COLUMNA1 = '5' OR COLUMNA2 = '5' OR COLUMNA3 = '5' OR COLUMNA4 = '5') 
    	   OR (COLUMNA1 = '7' OR COLUMNA2 = '7' OR COLUMNA3 = '7' OR COLUMNA4 = '7')  
    	   OR (COLUMNA1 = '10' OR COLUMNA2 = '10' OR COLUMNA3 = '10' OR COLUMNA4 = '10') 
    	   OR (COLUMNA1 = '12' OR COLUMNA2 = '12' OR COLUMNA3 = '12' OR COLUMNA4 = '12')  
    	   AND exists 
    		  (
    		  select id
    			 from origenDatos T2
    		  where
    				(T2.COLUMNA1 = '1' OR COLUMNA2 = '1' OR COLUMNA3 = '1' OR COLUMNA4 = '1')  
    			 OR (COLUMNA1 = '6' OR COLUMNA2 = '6' OR COLUMNA3 = '6' OR COLUMNA4 = '6')  
    			 OR (COLUMNA1 = '9' OR COLUMNA2 = '9' OR COLUMNA3 = '9' OR COLUMNA4 = '9')  
    			 OR (COLUMNA1 = '24' OR COLUMNA2 = '24' OR COLUMNA3 = '24' OR COLUMNA4 = '24')  
    			 OR (COLUMNA1 = '17' OR COLUMNA2 = '17' OR COLUMNA3 = '17' OR COLUMNA4 = '17') 
    			 OR (COLUMNA1 = '2' OR COLUMNA2 = '2' OR COLUMNA3 = '2' OR COLUMNA4 = '2') 
    			 OR (COLUMNA1 = '3' OR COLUMNA2 = '3' OR COLUMNA3 = '3' OR COLUMNA4 = '3') 
    			 OR (COLUMNA1 = '4' OR COLUMNA2 = '4' OR COLUMNA3 = '4' OR COLUMNA4 = '4')  
    			 OR (COLUMNA1 = '5' OR COLUMNA2 = '5' OR COLUMNA3 = '5' OR COLUMNA4 = '5') 
    			 OR (COLUMNA1 = '7' OR COLUMNA2 = '7' OR COLUMNA3 = '7' OR COLUMNA4 = '7')  
    			 OR (COLUMNA1 = '10' OR COLUMNA2 = '10' OR COLUMNA3 = '10' OR COLUMNA4 = '10') 
    			 OR (COLUMNA1 = '12' OR COLUMNA2 = '12' OR COLUMNA3 = '12' OR COLUMNA4 = '12' )
    			 AND T2.ID >= T1.ID - 10
    			 AND T2.ID <= T1.ID + 10
    			 AND T1.ID <> T2.ID)
    	    /* LA SALIDA DE ESTA QUERY, REALMENTE, ME HA DEVUELTO UN CONJUNTO DE DATOS, CUYA LÓGICA DE NEGOCIO DICE QUE CUMPLEN UNAS CONDICIONES, QUE NO SON EL OBJETO
    	   DE ESTA CONSULTA, SINO EL HECHO DE QUE FALTAN FILAS, Y DE QUE LAS FILAS ESTAN EN POSIBLES GRUPOS DE 10 CUANDO ENCAJAN. */
    ), R AS ( SELECT 1 AS C UNION ALL SELECT 1),
        R1 AS (SELECT 1 AS C FROM R  AS A CROSS JOIN R  AS B),
        R2 AS (SELECT 1 AS C FROM R1 AS A CROSS JOIN R1 AS B),
        R3 AS (SELECT 1 AS C FROM R2 AS A CROSS JOIN R2 AS B),
        R4 AS (SELECT 1 AS C FROM R3 AS A CROSS JOIN R3 AS B),
    	R5 AS (SELECT 1 AS C FROM R4 AS A CROSS JOIN R4 AS B),
        R6 AS (
    	   SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS FILA FROM R5),
        Filas As(
    	   SELECT  TOP (2000) R6.FILA FROM R6
    ), EXISTENTES AS (
        Select Filas.FILA, l.ID
    	   from Filas LEFT JOIN ListadoDatos L ON Filas.FILA = L.ID
    ),BL1 AS (
        SELECT E.FILA, E.ID, LEAD(E.ID) OVER(ORDER BY FILA) AS SIG FROM EXISTENTES E
    ),BL2 AS (
       SELECT E.FILA, E.ID, E.SIG, LEAD(E.SIG) OVER(ORDER BY FILA) AS SIG2 FROM BL1 E
    ),BL3 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, LEAD(E.SIG2) OVER(ORDER BY FILA) AS SIG3 FROM BL2 E
    ),BL4 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, LEAD(E.SIG3) OVER(ORDER BY FILA) AS SIG4 FROM BL3 E
    ),BL5 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, LEAD(E.SIG4) OVER(ORDER BY FILA) AS SIG5  FROM BL4 E
    ),BL6 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, E.SIG5, LEAD(E.SIG5) OVER(ORDER BY FILA) AS SIG6 FROM BL5 E
    ),BL7 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, E.SIG5, E.SIG6, LEAD(E.SIG6) OVER(ORDER BY FILA) AS SIG7 FROM BL6 E
    ),BL8 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, E.SIG5, E.SIG6, E.SIG7, LEAD(E.SIG7) OVER(ORDER BY FILA) AS SIG8 FROM BL7 E
    ),BL9 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, E.SIG5, E.SIG6, E.SIG7, E.SIG8, LEAD(E.SIG8) OVER(ORDER BY FILA) AS SIG9 FROM BL8 E
    ), GRUPOS AS (
    SELECT FILA, ID, 
    CASE WHEN SIG  IS NULL THEN 0 ELSE 1 END AS SIG1,
    CASE WHEN SIG2 IS NULL THEN 0 ELSE 1 END AS SIG2,
    CASE WHEN SIG3 IS NULL THEN 0 ELSE 1 END AS SIG3,
    CASE WHEN SIG4 IS NULL THEN 0 ELSE 1 END AS SIG4,
    CASE WHEN SIG5 IS NULL THEN 0 ELSE 1 END AS SIG5,
    CASE WHEN SIG6 IS NULL THEN 0 ELSE 1 END AS SIG6,
    CASE WHEN SIG7 IS NULL THEN 0 ELSE 1 END AS SIG7,
    CASE WHEN SIG8 IS NULL THEN 0 ELSE 1 END AS SIG8,
    CASE WHEN SIG9 IS NULL THEN 0 ELSE 1 END AS SIG9
    
     FROM BL9
     ), RESUMEN AS (
    SELECT FILA, ID, SIG1+SIG2+SIG3+SIG4+SIG5+SIG6+SIG7+SIG8+SIG9 AS GRUPO1
        FROM GRUPOS 
    where id is not null
    )
    SELECT TOP(10) L.*
    FROM RESUMEN R INNER JOIN ListadoDatos L ON R.FILA = L.ID
    ORDER BY R.GRUPO1 DESC
    

    Es evidente que la consulta es muy mejorable, que se podría generalizar, que la lógica de negocio es un poco "rara", que por ejemplo la manera de obtener las dos mil filas, se puede realizar con una función. Pero es un punto de partida. 

    Espero te ayude.

    • Marcado como respuesta marcosmrc domingo, 21 de abril de 2019 9:33
    domingo, 21 de abril de 2019 6:53

Todas las respuestas

  • Hola  

    Gracias por levantar tu consulta en los foros de MSDN. Con respecto a la misma, te comento que estaremos realizando una revisión a profundidad acerca del inconveniente que se te está presentando.

    Proporcionaremos una respuesta lo más pronto posible.

    Gracias por usar los foros de MSDN.

    Carlos Ruiz
     ____

    Por favor recuerde "Marcar como respuesta" las respuestas que hayan resuelto su problema, es una forma común de reconocer a aquellos que han ayudado, y hace que sea más fácil para los otros visitantes encontrar la solución más tarde. 

    Microsoft ofrece este servicio de forma gratuita, con la finalidad de ayudar a los usuarios y la ampliación de la base de datos de conocimientos relacionados con los productos y tecnologías de Microsoft.  

    Este contenido es proporcionado "tal cual" y no implica ninguna responsabilidad de parte de Microsoft.
    lunes, 15 de abril de 2019 14:21
  • Gracias Carlos_Ruiz_M, cualquier dato que necesitéis y que pueda ayudar a resolver mi duda no dudéis en indicármelo.

    No sé si en la exposición de mi pregunta he podido liarme un poco... En esencia es poder encontrar la mayor concentración de una determinada agrupación de datos en una tabla extensa.

    La consulta actual me devuelve una gran cantidad de filas como resultado pero el objetivo no es obtener todas esas filas, si no sólo las que concentran juntas como grupo, una mayor cantidad de esos datos introducidos en la consulta. Cómo si fuesen "focos" de mayor intensidad. 

    Gracias nuevamente por vuestra atención.

    lunes, 15 de abril de 2019 14:43
  • Hola  marcosmrc

    Gracias por levantar tu consulta en los foros de MSDN. Con respecto a la misma, te hago la recomendación de ingresar al siguiente enlace en donde puedes encontrar una posible solución para tu problema.

    Gracias por usar los foros de MSDN.

    El proceso adecuado para realizar dicho análisis y ejecución en las tablas y columnas de su base de datos.

    Consiste armar 2 cuestiones más sencillas en primer momento es necesario crear procedimientos almacenados

    https://docs.microsoft.com/es-es/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-2017

    O conectar los resultados de la tabla a otro elemento que cuente con mayor cantidad de elementos lógicos para realizar el análisis de las zonas que indica.

    Le recomiendo el siguiente recurso para conocer las limitantes de SQL en lo que a elementos lógicos se refiere

    https://www.w3schools.com/sql/default.asp

    En primer momento dicho desarrollo requiere de ciertos elementos de teoría de conjuntos que van a integrar el análisis sobre cómo se analiza la información para obtener el resultado deseado

    https://lasoperacionesconjuntos.blogspot.com/2015/08/matrices.html que es la forma en la cuál su espacio ha tomado forma al estar integrado en tablas. Esto principalmente para conocer los determinantes de las variables que se están integrando.

    Piense como que su espacio A= {a,b,c,...,},{a,b,c,...,},{a,b,c,...,} necesita desarrollar los métodos que encuentren los determinantes de cada conjunto por separado y de esa manera poder arrojar la salida de su análisis dando como resultado los 2 nuevos espacios donde existen las 2 condiciones principales que usted requiere.

    Incluso poder lograr las condiciones necesarias con un lenguaje como C# o otro lenguaje que cuente con ciclos de repetición para evitar las repeticiones con and y or.

    Pablo Sanchez

     ____
    Por favor recuerde "Marcar como respuesta" las respuestas que hayan resuelto su problema, es una forma común de reconocer a aquellos que han ayudado, y hace que sea más fácil para los otros visitantes encontrar la solución más tarde.
    Microsoft ofrece este servicio de forma gratuita, con la finalidad de ayudar a los usuarios y la ampliación de la base de datos de conocimientos relacionados con los productos y tecnologías de Microsoft. 
    Este contenido es proporcionado "tal cual" y no implica ninguna responsabilidad de parte de Microsoft.

    martes, 16 de abril de 2019 20:19
    Moderador
  • Gracias por tu respuesta  Pablo Rubio,

    veo que el asunto es más complejo de lo que yo imaginaba... Siendo así agradezco los enlaces para poder revisar toda esa información y mirar de encontrar una posible solución al problema a través de esas vías.

    Gracias por vuestro tiempo y atención.

    Un saludo

    • Marcado como respuesta marcosmrc miércoles, 17 de abril de 2019 15:13
    • Desmarcado como respuesta marcosmrc miércoles, 17 de abril de 2019 15:13
    martes, 16 de abril de 2019 22:30
  • Hola marcosmrc:

    A lo mejor es un poco demasiado complicado, pero puedes hacer algo así

    Lo primero es que voy a reconvertir tu select en un select into

    select * into listadoFiltrado
        from LISTADOALEATORIO T1 
         where COLUMNA1 = '1' OR COLUMNA2 = '1' OR COLUMNA3 = '1' or COLUMNA4 = '1'  AND COLUMNA1 = '6' OR COLUMNA2 = '6' OR COLUMNA3 = '6' or COLUMNA4 = '6'  AND COLUMNA1 = '9' OR COLUMNA2 = '9' OR COLUMNA3 = '9' or COLUMNA4 = '9'  AND COLUMNA1 = '24' OR COLUMNA2 = '24' OR COLUMNA3 = '24' or COLUMNA4 = '24'  AND COLUMNA1 = '17' OR COLUMNA2 = '17' OR COLUMNA3 = '17' or COLUMNA4 = '17' AND COLUMNA1 = '2' OR COLUMNA2 = '2' OR COLUMNA3 = '2' or COLUMNA4 = '2' AND COLUMNA1 = '3' OR COLUMNA2 = '3' OR COLUMNA3 = '3' or COLUMNA4 = '3' AND COLUMNA1 = '4' OR COLUMNA2 = '4' OR COLUMNA3 = '4' or COLUMNA4 = '4'  AND COLUMNA1 = '5' OR COLUMNA2 = '5' OR COLUMNA3 = '5' or COLUMNA4 = '5' AND COLUMNA1 = '7' OR COLUMNA2 = '7' OR COLUMNA3 = '7' or COLUMNA4 = '7'  AND COLUMNA1 = '10' OR COLUMNA2 = '10' OR COLUMNA3 = '10' or COLUMNA4 = '10' AND COLUMNA1 = '12' OR COLUMNA2 = '12' OR COLUMNA3 = '12' or COLUMNA4 = '12'   AND exists 
           (select id
           from LISTADOALEATORIO T2
           where
         T2.COLUMNA1 = '1' OR COLUMNA2 = '1' OR COLUMNA3 = '1' or COLUMNA4 = '1'  AND COLUMNA1 = '6' OR COLUMNA2 = '6' OR COLUMNA3 = '6' or COLUMNA4 = '6'  AND COLUMNA1 = '9' OR COLUMNA2 = '9' OR COLUMNA3 = '9' or COLUMNA4 = '9'  AND COLUMNA1 = '24' OR COLUMNA2 = '24' OR COLUMNA3 = '24' or COLUMNA4 = '24'  AND COLUMNA1 = '17' OR COLUMNA2 = '17' OR COLUMNA3 = '17' or COLUMNA4 = '17' AND COLUMNA1 = '2' OR COLUMNA2 = '2' OR COLUMNA3 = '2' or COLUMNA4 = '2' AND COLUMNA1 = '3' OR COLUMNA2 = '3' OR COLUMNA3 = '3' or COLUMNA4 = '3' AND COLUMNA1 = '4' OR COLUMNA2 = '4' OR COLUMNA3 = '4' or COLUMNA4 = '4'  AND COLUMNA1 = '5' OR COLUMNA2 = '5' OR COLUMNA3 = '5' or COLUMNA4 = '5' AND COLUMNA1 = '7' OR COLUMNA2 = '7' OR COLUMNA3 = '7' or COLUMNA4 = '7'  AND COLUMNA1 = '10' OR COLUMNA2 = '10' OR COLUMNA3 = '10' or COLUMNA4 = '10' AND COLUMNA1 = '12' OR COLUMNA2 = '12' OR COLUMNA3 = '12' or COLUMNA4 = '12' 
    	 
      AND
    
        T2.ID >= T1.ID - 10
    AND T2.ID <= T1.ID + 10
    AND T1.ID <> T2.ID
    )

    Ya tengo tu select en una tabla llamada listadoFiltrado.

    ;WITH R AS (
    		 SELECT 1 AS C UNION ALL SELECT 1),
        R1 AS (SELECT 1 AS C FROM R  AS A CROSS JOIN R  AS B),
        R2 AS (SELECT 1 AS C FROM R1 AS A CROSS JOIN R1 AS B),
        R3 AS (SELECT 1 AS C FROM R2 AS A CROSS JOIN R2 AS B),
        R4 AS (SELECT 1 AS C FROM R3 AS A CROSS JOIN R3 AS B),
    	R5 AS (SELECT 1 AS C FROM R4 AS A CROSS JOIN R4 AS B),
        R6 AS (
    	   SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS FILA FROM R5),
        Filas As(
    	   SELECT  TOP (2000) R6.FILA FROM R6
    ), EXISTENTES AS (
        Select Filas.FILA, l.ID
    	   from Filas 
    		  left join listadofiltrado l on Filas.FILA = L.ID
    ),BL1 AS (
        SELECT E.FILA, E.ID, LEAD(E.ID) OVER(ORDER BY FILA) AS SIG FROM EXISTENTES E
    ),BL2 AS (
       SELECT E.FILA, E.ID, E.SIG, LEAD(E.SIG) OVER(ORDER BY FILA) AS SIG2 FROM BL1 E
    ),BL3 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, LEAD(E.SIG2) OVER(ORDER BY FILA) AS SIG3 FROM BL2 E
    ),BL4 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, LEAD(E.SIG3) OVER(ORDER BY FILA) AS SIG4 FROM BL3 E
    ),BL5 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, LEAD(E.SIG4) OVER(ORDER BY FILA) AS SIG5  FROM BL4 E
    ),BL6 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, E.SIG5, LEAD(E.SIG5) OVER(ORDER BY FILA) AS SIG6 FROM BL5 E
    ),BL7 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, E.SIG5, E.SIG6, LEAD(E.SIG6) OVER(ORDER BY FILA) AS SIG7 FROM BL6 E
    ),BL8 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, E.SIG5, E.SIG6, E.SIG7, LEAD(E.SIG7) OVER(ORDER BY FILA) AS SIG8 FROM BL7 E
    ),BL9 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, E.SIG5, E.SIG6, E.SIG7, E.SIG8, LEAD(E.SIG8) OVER(ORDER BY FILA) AS SIG9 FROM BL8 E
    ), GRUPOS AS (
    SELECT FILA, ID, 
    CASE WHEN SIG  IS NULL THEN 0 ELSE 1 END AS SIG1,
    CASE WHEN SIG2 IS NULL THEN 0 ELSE 1 END AS SIG2,
    CASE WHEN SIG3 IS NULL THEN 0 ELSE 1 END AS SIG3,
    CASE WHEN SIG4 IS NULL THEN 0 ELSE 1 END AS SIG4,
    CASE WHEN SIG5 IS NULL THEN 0 ELSE 1 END AS SIG5,
    CASE WHEN SIG6 IS NULL THEN 0 ELSE 1 END AS SIG6,
    CASE WHEN SIG7 IS NULL THEN 0 ELSE 1 END AS SIG7,
    CASE WHEN SIG8 IS NULL THEN 0 ELSE 1 END AS SIG8,
    CASE WHEN SIG9 IS NULL THEN 0 ELSE 1 END AS SIG9
    
     FROM BL9
     ), RESUMEN AS (
    SELECT FILA, ID, SIG1+SIG2+SIG3+SIG4+SIG5+SIG6+SIG7+SIG8+SIG9 AS GRUPO1
        FROM GRUPOS 
    where id is not null
    )
    SELECT TOP(10) L.*
    FROM RESUMEN R INNER JOIN LISTADOFILTRADO L ON R.FILA = L.ID
    ORDER BY R.GRUPO1 DESC

    Sobre la misma me genero las 2000 filas  y las relaciono con listado filtrado

    Luego cojo de cada uno su valor siguiente, para poder tener 10 filas, y si el siguiente, y sucesivos no son nulos, le pongo un 1 sino un 0, y así se si por las 2000 filas se repiten y cuantas veces, y por ultimo sumo todos y ordeno por mayores repeticiones.


    Espero te ayude

    • Propuesto como respuesta Carlos_Ruiz_M miércoles, 17 de abril de 2019 14:14
    • Marcado como respuesta marcosmrc miércoles, 17 de abril de 2019 15:13
    miércoles, 17 de abril de 2019 5:23
  • Impresionante Javi!! muchísimas gracias por tu ayuda!!

    A raíz del anterior POST que me ayudaste instalé el SQL-server y me resulto el día y la noche respecto a las aplicaciones que yo usaba antes para manejar las BASES DE DATOS y te lo agradezco de verdad. Ahora estoy repasando tu código para poder entenderlo bien y usarlo correctamente, pero por lo de ahora, acabo de leerlo todo y parece que es justo lo que necesitaba para poder hallar la mayor concentración de datos en una tabla extensa. Con este método tuyo veo que se contabilizan las repeticiones y luego se agrupan los más repetidos!! impresionante de nuevo!! estaba totalmente atascado con esto... GRACIAS. Espero que mi consulta pueda ayudar también a más gente del foro.

    Un abrazo!

    miércoles, 17 de abril de 2019 15:13
  • De nada, siempre es un placer aportar un granito de arena.



    miércoles, 17 de abril de 2019 15:16
  • Buenas Javi, 

    Simplemente indicar que el código funciona a la perfección y con él, obtengo en efecto, el conjunto de datos deseado en el SELECT en un grupo de "acotación id=10" más concentrado en una tabla extensa de datos aleatorios, justo como se necesitaba.

    Imagino que también se podría utilizar exactamente este mismo código sin variar nada en otras tablas de datos no aleatorias que tengamos. Únicamente modificandolo en la primera parte del código donde generas el conjunto de números del listado aleatorio con la tabla de expresión común "R" y el producto cartesiano "CROSS JOIN". Substituyendo todo el contenido del paréntesis de dicha CTE con un simple SELECT a la tabla y campos que se quieran.

    Pudiendo así hacer lo mismo con otras tablas existentes de datos.

    viernes, 19 de abril de 2019 9:53
  • Me alegro que funcione bien.

    Si así es. Lo has visto perfecto.

    viernes, 19 de abril de 2019 10:13
  • Genial! :)

    Así resulta ser un código sumamente aprovechable para cualquier tabla! 

    Gracias por la ayuda excepcional con este código nuevamente! imposible me hubiera sido el sacarlo yo sólo...

    Un abrazo y feliz semana santa jejeje 


    • Editado marcosmrc viernes, 19 de abril de 2019 10:57
    viernes, 19 de abril de 2019 10:57
  • Buenas Javi,

    perdona que te consulte de nuevo sobre la misma cuestión, pero me estoy volviendo loco intentando adaptar el código a una tabla de datos idéntica a la del ejemplo (mismas columnas, mismos campos...todo igual), excepto, que los datos no sean aleatorios para así como te comentaba poder aplicar el mismo código que me proporcionaste a cualquier tabla ya que me ha resultado una maravilla para lo que yo necesito.

    Resulta que como te comenté estoy realizando un SELECT seleccionando todos los campos: ID, COLUMNA1, COLUMNA2, COLUMNA3 y COLUMNA4 dentro de la cte (conservando "R") indicando en el FROM a que "dbo.XXX" de datos no aleatorios me quiero dirigir. Pero al ser un código complejo de adaptar para mí, en el intento de conservar todo el código TAL CUAL está pero quitando todo la primera parte que contiene la cte en la que se generan las 2000 lineas aleatorias en sustitución de mi SELECT no paro de tener errores por todas partes y de los 5000 intentos de modificar esa parte y adaptarla a cualquier tabla de idénticas características para aprovechar el código de forma versátil no he tenido ningún éxito... Intento realizar el SELECT conservando "FILA" o "Fila" en la adaptación para NO TOCAR el resto de líneas que le siguen y mantenerlas intactas pero no hay manera... Saber adaptar está genialidad a cualquier tabla de datos es clave para mí...

    Disculpa las molestias Javi y gracias.

    viernes, 19 de abril de 2019 19:38
  • Hola marcosmrc:

    La misma aproximación con la base de datos AdventureWorks2017 sobre la tabla de empleados.

    WITH R AS (
    SELECT 
           ROW_NUMBER() over (order by BusinessEntityID) as fila
    	, Employee.BusinessEntityID as ID
    	, Employee.JobTitle
    	, Employee.Gender
    	
    
    FROM   
    	HumanResources.Employee
    	where BusinessEntityID > 50
    
    ),
        Filas As(
    	   SELECT  TOP (2000) R6.FILA FROM R as R6
    ), EXISTENTES AS (
        Select Filas.FILA, l.ID
    	   from Filas 
    		  left join R l on Filas.FILA = L.ID
    ),BL1 AS (
        SELECT E.FILA, E.ID, LEAD(E.ID) OVER(ORDER BY FILA) AS SIG FROM EXISTENTES E
    ),BL2 AS (
       SELECT E.FILA, E.ID, E.SIG, LEAD(E.SIG) OVER(ORDER BY FILA) AS SIG2 FROM BL1 E
    ),BL3 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, LEAD(E.SIG2) OVER(ORDER BY FILA) AS SIG3 FROM BL2 E
    ),BL4 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, LEAD(E.SIG3) OVER(ORDER BY FILA) AS SIG4 FROM BL3 E
    ),BL5 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, LEAD(E.SIG4) OVER(ORDER BY FILA) AS SIG5  FROM BL4 E
    ),BL6 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, E.SIG5, LEAD(E.SIG5) OVER(ORDER BY FILA) AS SIG6 FROM BL5 E
    ),BL7 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, E.SIG5, E.SIG6, LEAD(E.SIG6) OVER(ORDER BY FILA) AS SIG7 FROM BL6 E
    ),BL8 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, E.SIG5, E.SIG6, E.SIG7, LEAD(E.SIG7) OVER(ORDER BY FILA) AS SIG8 FROM BL7 E
    ),BL9 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, E.SIG5, E.SIG6, E.SIG7, E.SIG8, LEAD(E.SIG8) OVER(ORDER BY FILA) AS SIG9 FROM BL8 E
    ), GRUPOS AS (
    SELECT FILA, ID, 
    CASE WHEN SIG  IS NULL THEN 0 ELSE 1 END AS SIG1,
    CASE WHEN SIG2 IS NULL THEN 0 ELSE 1 END AS SIG2,
    CASE WHEN SIG3 IS NULL THEN 0 ELSE 1 END AS SIG3,
    CASE WHEN SIG4 IS NULL THEN 0 ELSE 1 END AS SIG4,
    CASE WHEN SIG5 IS NULL THEN 0 ELSE 1 END AS SIG5,
    CASE WHEN SIG6 IS NULL THEN 0 ELSE 1 END AS SIG6,
    CASE WHEN SIG7 IS NULL THEN 0 ELSE 1 END AS SIG7,
    CASE WHEN SIG8 IS NULL THEN 0 ELSE 1 END AS SIG8,
    CASE WHEN SIG9 IS NULL THEN 0 ELSE 1 END AS SIG9
    
     FROM BL9
     ), RESUMEN AS (
    SELECT FILA, ID, SIG1+SIG2+SIG3+SIG4+SIG5+SIG6+SIG7+SIG8+SIG9 AS GRUPO1
        FROM GRUPOS 
    where id is not null
    )
    SELECT TOP(10) L.*
    FROM RESUMEN R INNER JOIN R L ON R.FILA = L.ID
    ORDER BY R.GRUPO1 DESC

    Supongo que tantos alias de tabla son un poco lio.

    En R el origen de los datos, en este caso, utilizo la función row_number directamente en el conjunto para que me de la numeración de filas. Filas solo hace un top, algo que podría hacer también en R pero así no toco la estructura como estaba montada.

    Row_number

    https://javifer2.blogspot.com/2019/01/numerar-filas-funciones-de-ventana.html

    Espero te ayude

    sábado, 20 de abril de 2019 7:30
  • Hola Javi, 

    Gracias por tu respuesta, en ese caso voy a instalar AdventureWorks2017 en SQL-server y voy a hacer las pruebas con lo que me has enseñado.

    Impresionante Blog por cierto! ya lo había consultado antes pero no tengo el nivel aún para entender muchas de las cuestiones de SQL que en él se plantean... poco a poco jejeje

    Gracias!

    sábado, 20 de abril de 2019 8:26
  • De nada.No obstante como ejemplo no tiene sentido, porque realmente, tú idea es obtener grupos acotados de registros y de estos un top.

    Y en el ejemplo lo único que doy es una tira de registros. Pero lo que te quería mostrar es como formar, por un lado las filas, por ejemplo 2000 secuenciales, y por otro lado el origen de los datos, con las faltas numeradas de manera que igualas filas, con filasdedatos. Con las filas y filas de datos, obtienes para cada una sus 10 siguientes, con la salida de esta la sumas 1 si no falla y 0 si lo hace y así obtienes un computo de la fila.

    Luego solo recoger las mayores.

    Un saludo

    sábado, 20 de abril de 2019 9:12
  • Buenas,

    Justamente acabo de instalar el archivo: "AdventureWorks2017.bak" y estaba haciendo pruebas con la tabla employee y...EN EFECTO! no me salía nada... jejeje ni cuenta me había dado madre mía... y yo adaptando el código a una tabla de pruebas que tengo que se llama "listado" (con datos no aleatorios) realizando previamente el SELECT * INTO "listadofiltrado" del primer paso con los números seleccionados...etc. Y sobre la tabla creada "listadofiltrado" le estaba cargando el código en cuestión "adaptado" con el FROM a "listado" y las columnas: ID, COLUMNA1, COLUMNA2, COLUMNA3, COLUMNA4 y el único resultado que estaba obteniendo eran en efecto las 10 primeras líneas de la tabla... que desastre... jajaja. Si no me lo recalcas aún seguía toda la mañana intentando ver que hacía mal... jajaja

    He estado leyendo el Blog y entiendo los conceptos porque está muy bien explicado pero a la hora de armar correctamente la consulta me hago un lío con los alías+estructura... Y eso que la tabla "listado" que te comento tiene la misma estructura y campos que la del ejemplo inicial del post con la salvedad de que los datos no son aleatorios y aún así no doy hecho... que desastre... 



    • Editado marcosmrc sábado, 20 de abril de 2019 10:00
    sábado, 20 de abril de 2019 9:59
  • Te dejo que te pegues un poco con ello, si ves, que no le das solución, pegas tu origen de datos, y cuando tenga un ratillo, intento darte la solución. (A tí como a todos nosotros, las cosas, nos entran mejor pegándonos contra el muro, y luego a base de perseverar, los conceptos se quedan grabados a fuego)
    sábado, 20 de abril de 2019 11:32
  • Mi tabla de datos no aleatorios:

    ID, COLUMNA1, COLUMNA2, COLUMNA3 y COLUMNA4 (id autincremental y todo INT).

    Código del ejemplo "adaptado" y EJECUTADO en la tabla "listadofiltrado" creada a partir del INSERT * INTO del inicio con los numeros respectivos a buscar en el WHERE:

    WITH R AS (
    SELECT 
           ROW_NUMBER() over (order by ID) as fila
    	, listado.ID as ID
    	, listado.COLUMNA1
    	, listado.COLUMNA2
    	, listado.COLUMNA3
    	, listado.COLUMNA4
    	
    	
    
    FROM   
    	listado
    	
    
    ),
        Filas As(
    	   SELECT  TOP (1000) R6.FILA FROM R as R6
    ), EXISTENTES AS (
        Select Filas.FILA, l.ID
    	   from Filas 
    		  left join R l on Filas.FILA = L.ID
    ),BL1 AS (
        SELECT E.FILA, E.ID, LEAD(E.ID) OVER(ORDER BY FILA) AS SIG FROM EXISTENTES E
    ),BL2 AS (
       SELECT E.FILA, E.ID, E.SIG, LEAD(E.SIG) OVER(ORDER BY FILA) AS SIG2 FROM BL1 E
    ),BL3 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, LEAD(E.SIG2) OVER(ORDER BY FILA) AS SIG3 FROM BL2 E
    ),BL4 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, LEAD(E.SIG3) OVER(ORDER BY FILA) AS SIG4 FROM BL3 E
    ),BL5 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, LEAD(E.SIG4) OVER(ORDER BY FILA) AS SIG5  FROM BL4 E
    ),BL6 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, E.SIG5, LEAD(E.SIG5) OVER(ORDER BY FILA) AS SIG6 FROM BL5 E
    ),BL7 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, E.SIG5, E.SIG6, LEAD(E.SIG6) OVER(ORDER BY FILA) AS SIG7 FROM BL6 E
    ),BL8 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, E.SIG5, E.SIG6, E.SIG7, LEAD(E.SIG7) OVER(ORDER BY FILA) AS SIG8 FROM BL7 E
    ),BL9 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, E.SIG5, E.SIG6, E.SIG7, E.SIG8, LEAD(E.SIG8) OVER(ORDER BY FILA) AS SIG9 FROM BL8 E
    ), GRUPOS AS (
    SELECT FILA, ID, 
    CASE WHEN SIG  IS NULL THEN 0 ELSE 1 END AS SIG1,
    CASE WHEN SIG2 IS NULL THEN 0 ELSE 1 END AS SIG2,
    CASE WHEN SIG3 IS NULL THEN 0 ELSE 1 END AS SIG3,
    CASE WHEN SIG4 IS NULL THEN 0 ELSE 1 END AS SIG4,
    CASE WHEN SIG5 IS NULL THEN 0 ELSE 1 END AS SIG5,
    CASE WHEN SIG6 IS NULL THEN 0 ELSE 1 END AS SIG6,
    CASE WHEN SIG7 IS NULL THEN 0 ELSE 1 END AS SIG7,
    CASE WHEN SIG8 IS NULL THEN 0 ELSE 1 END AS SIG8,
    CASE WHEN SIG9 IS NULL THEN 0 ELSE 1 END AS SIG9
    
     FROM BL9
     ), RESUMEN AS (
    SELECT FILA, ID, SIG1+SIG2+SIG3+SIG4+SIG5+SIG6+SIG7+SIG8+SIG9 AS GRUPO1
        FROM GRUPOS 
    where id is not null
    )
    SELECT TOP(10) L.*
    FROM RESUMEN R INNER JOIN R L ON R.FILA = L.ID
    ORDER BY R.GRUPO1 DESC

    Esta consulta sólo me devuelve las 10 primeras filas de forma descendente... sé que lo estoy haciendo mal porque en efecto es lo que me comentabas pero soy incapaz de darme cuenta de como acomodar correctamente el código a dicha tabla... no lo veo de ninguna manera y sé que estoy insistiendo mucho en esta cuestión pero soy incapaz de resolverla por mí mismo y es frustrante...


    • Editado marcosmrc sábado, 20 de abril de 2019 11:47
    sábado, 20 de abril de 2019 11:36
  • Te agradezco muchísimo tu paciencia Javi... de verdad no tiene precio esta ayuda.

    Gracias

    sábado, 20 de abril de 2019 11:37
  • Mañana te doy respuesta, que ahora me tengo que ir de casa y quedo desconectado todo el dia

    :)

    sábado, 20 de abril de 2019 11:46
  • Como si es el mes que viene, faltaría más Javi! 

    GRACIAS!!!

    sábado, 20 de abril de 2019 12:18
  • Hola marcosmrc:

    Ejemplo con AdventureWorks2017

    WITH origenDatos
    	AS (
    	    SELECT    o.fila as ID
    			  , o.SalesPersonID AS COLUMNA1
    			  , o.Mes AS COLUMNA2
    			  , o.TerritoryID AS COLUMNA3
    			  , o.TerritoryID AS COLUMNA4
    	    FROM          
    	    (
    /* tabla que contiene los datos realmente*/
    		   SELECT ROW_NUMBER() OVER(
    				ORDER BY salesPersonId
    					  , ShipDate
    					  , territoryId) AS fila 
    /* numeramos los registros del primero al último */
    			   , SalesPersonID
    			   , MONTH(SHIPdATE) AS Mes
    			   , TerritoryID
    		   FROM   
    			   sales.SalesOrderHeader
    		   WHERE  SalesPersonID IS NOT NULL
    	    ) AS o /* la salida del origen de la tabla fuente la llamamos o */
        where (o.Mes = 1 and TerritoryID >4)
    	   or 
    		  ((o.Mes between 3 and 5) and (TerritoryID between 1 and 3))	  
    	   or 
    		  ((o.Mes between 7 and 12) and (TerritoryID between 4 and 9))  
    /*Quitamos filas, porque no encajan en nuestra lógica de negocio, ya que 
    la idea de esta consulta, era saber que en una determinada cantidad de datos
    cuales son los que contienen más, Luego jugaremos con las columnas, para saber
    si hay mas usuarios, o mas meses o mas territorios*/
    ), ListadoDatos AS(
    select ID, COLUMNA1, COLUMNA2, COLUMNA3, COLUMNA4  
        from origenDatos T1 
         where 
    		  (COLUMNA1 = '274' OR COLUMNA2 = '8' OR COLUMNA3 = '6' OR COLUMNA4 = '1') 
    	   OR (COLUMNA1 = '6' OR COLUMNA2 = '6' OR COLUMNA3 = '6' OR COLUMNA4 = '6')  
    	   OR (COLUMNA1 = '9' OR COLUMNA2 = '9' OR COLUMNA3 = '9' OR COLUMNA4 = '9')  
    	   OR (COLUMNA1 = '24' OR COLUMNA2 = '24' OR COLUMNA3 = '24' OR COLUMNA4 = '24')  
    	   OR (COLUMNA1 = '17' OR COLUMNA2 = '17' OR COLUMNA3 = '17' OR COLUMNA4 = '17') 
    	   OR (COLUMNA1 = '2' OR COLUMNA2 = '2' OR COLUMNA3 = '2' OR COLUMNA4 = '2') 
    	   OR (COLUMNA1 = '3' OR COLUMNA2 = '3' OR COLUMNA3 = '3' OR COLUMNA4 = '3') 
    	   OR (COLUMNA1 = '4' OR COLUMNA2 = '4' OR COLUMNA3 = '4' OR COLUMNA4 = '4')  
    	   OR (COLUMNA1 = '5' OR COLUMNA2 = '5' OR COLUMNA3 = '5' OR COLUMNA4 = '5') 
    	   OR (COLUMNA1 = '7' OR COLUMNA2 = '7' OR COLUMNA3 = '7' OR COLUMNA4 = '7')  
    	   OR (COLUMNA1 = '10' OR COLUMNA2 = '10' OR COLUMNA3 = '10' OR COLUMNA4 = '10') 
    	   OR (COLUMNA1 = '12' OR COLUMNA2 = '12' OR COLUMNA3 = '12' OR COLUMNA4 = '12')  
    	   AND exists 
    		  (
    		  select id
    			 from origenDatos T2
    		  where
    				(T2.COLUMNA1 = '1' OR COLUMNA2 = '1' OR COLUMNA3 = '1' OR COLUMNA4 = '1')  
    			 OR (COLUMNA1 = '6' OR COLUMNA2 = '6' OR COLUMNA3 = '6' OR COLUMNA4 = '6')  
    			 OR (COLUMNA1 = '9' OR COLUMNA2 = '9' OR COLUMNA3 = '9' OR COLUMNA4 = '9')  
    			 OR (COLUMNA1 = '24' OR COLUMNA2 = '24' OR COLUMNA3 = '24' OR COLUMNA4 = '24')  
    			 OR (COLUMNA1 = '17' OR COLUMNA2 = '17' OR COLUMNA3 = '17' OR COLUMNA4 = '17') 
    			 OR (COLUMNA1 = '2' OR COLUMNA2 = '2' OR COLUMNA3 = '2' OR COLUMNA4 = '2') 
    			 OR (COLUMNA1 = '3' OR COLUMNA2 = '3' OR COLUMNA3 = '3' OR COLUMNA4 = '3') 
    			 OR (COLUMNA1 = '4' OR COLUMNA2 = '4' OR COLUMNA3 = '4' OR COLUMNA4 = '4')  
    			 OR (COLUMNA1 = '5' OR COLUMNA2 = '5' OR COLUMNA3 = '5' OR COLUMNA4 = '5') 
    			 OR (COLUMNA1 = '7' OR COLUMNA2 = '7' OR COLUMNA3 = '7' OR COLUMNA4 = '7')  
    			 OR (COLUMNA1 = '10' OR COLUMNA2 = '10' OR COLUMNA3 = '10' OR COLUMNA4 = '10') 
    			 OR (COLUMNA1 = '12' OR COLUMNA2 = '12' OR COLUMNA3 = '12' OR COLUMNA4 = '12' )
    			 AND T2.ID >= T1.ID - 10
    			 AND T2.ID <= T1.ID + 10
    			 AND T1.ID <> T2.ID)
    	    /* LA SALIDA DE ESTA QUERY, REALMENTE, ME HA DEVUELTO UN CONJUNTO DE DATOS, CUYA LÓGICA DE NEGOCIO DICE QUE CUMPLEN UNAS CONDICIONES, QUE NO SON EL OBJETO
    	   DE ESTA CONSULTA, SINO EL HECHO DE QUE FALTAN FILAS, Y DE QUE LAS FILAS ESTAN EN POSIBLES GRUPOS DE 10 CUANDO ENCAJAN. */
    ), R AS ( SELECT 1 AS C UNION ALL SELECT 1),
        R1 AS (SELECT 1 AS C FROM R  AS A CROSS JOIN R  AS B),
        R2 AS (SELECT 1 AS C FROM R1 AS A CROSS JOIN R1 AS B),
        R3 AS (SELECT 1 AS C FROM R2 AS A CROSS JOIN R2 AS B),
        R4 AS (SELECT 1 AS C FROM R3 AS A CROSS JOIN R3 AS B),
    	R5 AS (SELECT 1 AS C FROM R4 AS A CROSS JOIN R4 AS B),
        R6 AS (
    	   SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS FILA FROM R5),
        Filas As(
    	   SELECT  TOP (2000) R6.FILA FROM R6
    ), EXISTENTES AS (
        Select Filas.FILA, l.ID
    	   from Filas LEFT JOIN ListadoDatos L ON Filas.FILA = L.ID
    ),BL1 AS (
        SELECT E.FILA, E.ID, LEAD(E.ID) OVER(ORDER BY FILA) AS SIG FROM EXISTENTES E
    ),BL2 AS (
       SELECT E.FILA, E.ID, E.SIG, LEAD(E.SIG) OVER(ORDER BY FILA) AS SIG2 FROM BL1 E
    ),BL3 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, LEAD(E.SIG2) OVER(ORDER BY FILA) AS SIG3 FROM BL2 E
    ),BL4 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, LEAD(E.SIG3) OVER(ORDER BY FILA) AS SIG4 FROM BL3 E
    ),BL5 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, LEAD(E.SIG4) OVER(ORDER BY FILA) AS SIG5  FROM BL4 E
    ),BL6 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, E.SIG5, LEAD(E.SIG5) OVER(ORDER BY FILA) AS SIG6 FROM BL5 E
    ),BL7 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, E.SIG5, E.SIG6, LEAD(E.SIG6) OVER(ORDER BY FILA) AS SIG7 FROM BL6 E
    ),BL8 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, E.SIG5, E.SIG6, E.SIG7, LEAD(E.SIG7) OVER(ORDER BY FILA) AS SIG8 FROM BL7 E
    ),BL9 AS (
       SELECT E.FILA, E.ID, E.SIG, E.SIG2, E.SIG3, E.SIG4, E.SIG5, E.SIG6, E.SIG7, E.SIG8, LEAD(E.SIG8) OVER(ORDER BY FILA) AS SIG9 FROM BL8 E
    ), GRUPOS AS (
    SELECT FILA, ID, 
    CASE WHEN SIG  IS NULL THEN 0 ELSE 1 END AS SIG1,
    CASE WHEN SIG2 IS NULL THEN 0 ELSE 1 END AS SIG2,
    CASE WHEN SIG3 IS NULL THEN 0 ELSE 1 END AS SIG3,
    CASE WHEN SIG4 IS NULL THEN 0 ELSE 1 END AS SIG4,
    CASE WHEN SIG5 IS NULL THEN 0 ELSE 1 END AS SIG5,
    CASE WHEN SIG6 IS NULL THEN 0 ELSE 1 END AS SIG6,
    CASE WHEN SIG7 IS NULL THEN 0 ELSE 1 END AS SIG7,
    CASE WHEN SIG8 IS NULL THEN 0 ELSE 1 END AS SIG8,
    CASE WHEN SIG9 IS NULL THEN 0 ELSE 1 END AS SIG9
    
     FROM BL9
     ), RESUMEN AS (
    SELECT FILA, ID, SIG1+SIG2+SIG3+SIG4+SIG5+SIG6+SIG7+SIG8+SIG9 AS GRUPO1
        FROM GRUPOS 
    where id is not null
    )
    SELECT TOP(10) L.*
    FROM RESUMEN R INNER JOIN ListadoDatos L ON R.FILA = L.ID
    ORDER BY R.GRUPO1 DESC
    

    Es evidente que la consulta es muy mejorable, que se podría generalizar, que la lógica de negocio es un poco "rara", que por ejemplo la manera de obtener las dos mil filas, se puede realizar con una función. Pero es un punto de partida. 

    Espero te ayude.

    • Marcado como respuesta marcosmrc domingo, 21 de abril de 2019 9:33
    domingo, 21 de abril de 2019 6:53
  • IMPRESIONANTE Javi!!!

    Viendo el código alcanzo a comprender lo lejos que hubiese estado yo de realizar esta tarea actualmente...

    En efecto lo acabo de ejecutar en AdventureWorks2017 y funciona a la perfección te agradezco infinitamente las aclaraciones que me haces en varios de los tramos del código para que pueda entenderlo mejor y siendo así me voy a poner a estudiarlo todo ahora mismo para llegar a entenderlo todo a la perfección. Algo que será un importante paso adelante para mí y para mí trabajo!!

    De nuevo agradecerte el tiempo y la paciencia Javi y sin más esperar que le sirva a alguien del foro esta consulta que estoy seguro de que los usuarios de SQL van a agradecer como yo infinitamente estos conocimientos compartidos.

    UN ABRAZO!!

    domingo, 21 de abril de 2019 9:33
  • Bueno eso mismo me pasaba a mi con ese problema hace un par de dias.....o tres. Y me sigue pasando a diario porque cuanto mas aprendo, mas me doy cuenta de que no tengo ni idea. Pero conforme avanzo voy saliendo al paso.... Y es que hay algo y solo eso que si se. Programar es algo magico ya que significa crear, la mitad de las veces sin tener los conocimientos suficientes, y la otra mitad de carambola. Resultado. El camino es genial.

    Me encanta que te haya podido servir para algo el "pequeño esfuerzo" que a mi me ha costado resolver el enigma. Un placer ademas tratar con personas que como tu son bien agradecidas, ya que para un albañil como yo suelen ser mas por esfuerzo que por talento.

    Otro abrazo para ti

    domingo, 21 de abril de 2019 11:06
  • El placer es mío Javi,

    Y no se puede definir el mundo de la programación mejor...CREAR! tener la posibilidad de hacer realidad lo que está en nuestras cabezas es sin lugar a duda ASOMBROSO! :)

    pd: indicar a los propietarios del foro que hay que instalar emoticonos ya que he querído poner unos aplausos que me han faltado... jajaja

                                                                              ;)

    domingo, 21 de abril de 2019 11:28