sql - por - Encontrar rangos continuos en un conjunto de números

like en sql

Question

Tengo un conjunto razonablemente grande de números de teléfono (aproximadamente 2 millones) en una tabla de base de datos. Estos números se han insertado en bloques, por lo que hay muchos rangos continuos de números, desde 10 hasta 10 mil en un rango. Algunos de estos números están en uso y, por lo tanto, están marcados como no disponibles, el resto están disponibles. Dado un número en particular, necesito una forma de encontrar rangos continuos de números, tanto por encima como por debajo de ese número. El rango debe continuar hasta que encuentre un número no disponible o encuentre el límite de dos rangos.

Por ejemplo, dado el siguiente conjunto:

1000
1001
1002
1010
1011
1012
1013
1020
1021
1022

Hacer una búsqueda usando 1012 como parámetro debería devolver 1010, 1011, 1012, 1013.

¿Cuál es una buena forma de formar una consulta para encontrar estos rangos? Usamos NHibernate en la parte superior del servidor SQL, una solución con cualquiera de ellas está bien.

Answer 1

SQL no puede hacer esto en una sola consulta (excepto que hay mejoras de SQL nativo que desconozco), porque SQL no puede acceder a la fila ''antes'' o ''después''.

Necesitas pasar por la secuencia en un bucle.

Puede probar NHibernates Enumerable , que no carga las entidades en la memoria, sino que solo crea proxies de ellas. En realidad, no creo que sea una buena idea, ya que creará proxies para los 2 millones de números.

Plan B, usa paginación. A grandes rasgos, se ve así:

List<PhoneNumber> result = new List<PhoneNumber>();

int input = 1012;
int pageSize = 100;
int currentPage = 0;
int expectedNumber = input;

bool carryOn = true;

while(carryOn)
{
  var numbers = session
    .CreateQuery("from PhoneNumber pn where pn.Number > :input")
    .SetInt("input", input)
    .SetFirstResult(currentPage * pageSize)
    .SetMaxResult(pageSize)
    .List<PhoneNumbers>();

  foreach(var number in numbers)
  {
    expectNumber++;
    if (number.Number != expectedNumber) 
    {
      carryOn = false;
      break;
    }
    result.Add(number);
  }

  currentPage++;
}

Y lo mismo para el rango anterior en la otra dirección.

Answer 2

Si usa SQL server, debería poder hacer una consulta recursiva que se unirá en root.number = leaf.number + 1

Si selecciona el número desde la raíz y desde la última recursión, y el nivel de la recursión, debe tener una consulta activa.

Primero probaría el rendimiento de eso, y luego, si no es satisfactorio, cambiaré al enfoque basado en cursor / fila (que en este caso haría un trabajo con un único escaneo completo, donde la recursión puede fallar al alcanzar la profundidad máxima de recursión).

De lo contrario, sus opciones son almacenar datos de manera diferente y mantener una lista de números mínimos y máximos asociados con una tabla.

Esto podría implementarse realmente en desencadenantes que no tengan una penalización tan alta en las actualizaciones de fila única (las actualizaciones en la fila única de la tabla base actualizarían, borrarían o dividirían una fila en la tabla min-max; esto se puede determinar consultando el fila ''anterior'' y ''siguiente'' solamente).

Answer 3

Teóricamente, los elementos de un conjunto no tienen un valor particular, así que supongo que también tiene una columna de ID continua que define el orden de los números. Algo como esto:

ID  Number
1   1000
2   1001
3   1002
4   1010
5   1011
6   1012
7   1013
8   1020
9   1021
10  1022

Puede crear una columna adicional que contenga el resultado del Number - ID :

ID  Number  Diff
1   1000    999
2   1001    999
3   1002    999
4   1010    1006
5   1011    1006
6   1012    1006
7   1013    1006
8   1020    1012
9   1021    1012
10  1022    1012

Los números en el mismo rango tendrán el mismo resultado en la columna Diff.

Answer 4

Use una tabla auxiliar de todos los valores secuenciales posibles o materialice una en un CTE, por ej.

WITH
-- materialize a table of sequential integers
l0 AS (SELECT 0 AS c UNION ALL SELECT 0),
l1 AS (SELECT 0 AS c FROM l0 AS a, l0 AS b),
l2 AS (SELECT 0 AS c FROM l1 AS a, l1 AS b),
l3 AS (SELECT 0 AS c FROM l2 AS a, l2 AS b),
l4 AS (SELECT 0 AS c FROM l2 AS a, l3 AS b),
l5 AS (SELECT 0 AS c FROM l2 AS a, l4 AS b),
nums AS (SELECT row_number() OVER(ORDER BY c) AS n FROM l5), 
-- materialize sample table
MyTable (ID) AS 
(
 SELECT 1000
 UNION ALL 
 SELECT 1001
 UNION ALL 
 SELECT 1002
 UNION ALL 
 SELECT 1010
 UNION ALL 
 SELECT 1011
 UNION ALL 
 SELECT 1012
 UNION ALL 
 SELECT 1013
 UNION ALL 
 SELECT 1020
 UNION ALL 
 SELECT 1021
 UNION ALL 
 SELECT 1022
), 
-- materialize parameter table
params (param) AS (SELECT 1012)
SELECT MIN(N1.n) - 1 AS last_in_sequence
  FROM nums AS N1 
       CROSS JOIN params AS P1
 WHERE N1.n > P1.param
       AND NOT EXISTS 
       (
        SELECT * 
          FROM MyTable AS T1
         WHERE N1.n = T1.ID
       );