uso over operador nativas función funciones ejemplos datos clausula agrupar agregación agregacion sql-server tsql common-table-expression gaps-and-islands

sql-server - over - operador like sql server



Cómo agrupar valores ranged utilizando SQL Server (2)

Del artículo que Josh publicó, aquí está mi opinión (probado y en funcionamiento):

SELECT MAX(t1.gapID) as gapID, t2.gapID-MAX(t1.gapID)+t2.gapSize as gapSize -- max(t1) is the specific lower bound of t2 because of the group by. FROM ( -- t1 is the lower boundary of an island. SELECT gapID FROM gaps tbl1 WHERE NOT EXISTS( SELECT * FROM gaps tbl2 WHERE tbl1.gapID = tbl2.gapID + tbl2.gapSize + 1 ) ) t1 INNER JOIN ( -- t2 is the upper boundary of an island. SELECT gapID, gapSize FROM gaps tbl1 WHERE NOT EXISTS( SELECT * FROM gaps tbl2 WHERE tbl2.gapID = tbl1.gapID + tbl1.gapSize + 1 ) ) t2 ON t1.gapID <= t2.gapID -- For all t1, we get all bigger t2 and opposite. GROUP BY t2.gapID, t2.gapSize

Tengo una tabla de valores como esta

978412, 400 978813, 20 978834, 50 981001, 20

Como puede ver, el segundo número cuando se agrega al primero es 1 número antes del siguiente en la secuencia. El último número no está en el rango (no sigue una secuencia directa, como en el siguiente valor). Lo que necesito es un CTE (sí, idealmente) que genere este

978412, 472 981001, 20

La primera fila contiene el número de inicio del rango y luego la suma de los nodos dentro. La siguiente fila es el siguiente rango, que en este ejemplo es el mismo que el de los datos originales.


Echa un vistazo a este artículo de MSDN . Le brinda una solución a su problema, si le sirve dependerá de la cantidad de datos que tenga y sus requisitos de rendimiento para la consulta.

Editar:

Bien usando el ejemplo en la consulta, y yendo con su última solución, la segunda forma de obtener islas (la primera forma resultó en un error en SQL 2005).

SELECT MIN(start) AS startGroup, endGroup, (endgroup-min(start) +1) as NumNodes FROM (SELECT g1.gapID AS start, (SELECT min(g2.gapID) FROM #gaps g2 WHERE g2.gapID >= g1.gapID and NOT EXISTS (SELECT * FROM #gaps g3 WHERE g3.gapID - g2.gapID = 1)) as endGroup FROM #gaps g1) T1 GROUP BY endGroup

Lo que agregué es (endgroup-min(start) +1) as NumNodes . Esto te dará los conteos.