sql - tablas - Encuentra valores perdidos

pandas dataframe español

Question

Tengo una tabla, con 2 columnas importantes DocEntry, WebId

Los datos de muestra son como

DocEntry  WebId
1         S001
2         S002
3         S003
4         S005

Ahora, como podemos observar aquí, en la columna WebId, falta S004. ¿Cómo podemos localizar esos números que faltan, con una consulta?

Explicación adicional:

La identificación web debe estar en orden creciente como, S001, S002, S003, S004, S005 si falta un número entre ellos, que ese es el número que falta. No tengo ninguna tabla separada para las posibles entradas, ya que no es práctico. Tengo que encontrar los números que faltan mes a mes, tomando el valor inicial y final de cada mes como los límites y que encontrar los números que faltan, si corresponde.

Answer 1

A menos que tenga un diseño de número específico ya definido (parece que lo es), tenga una tabla con todas las posibilidades (aunque no muy eficiente en el tiempo) y puede hacer algo como esto:

Obtenga una tabla con todas las posibilidades del nombre PossibleEntries y luego haga esto:

SELECCIONE pe.WebID desde PossibleEntries pe WHERE pe.WebID Not In (Seleccione WebID desde SampleData)

Creo que debería funcionar, pero no sé cuán eficiente es. Estoy de acuerdo con lo de arriba Si los números no son secuenciales, no podrás hacer esto.

Answer 2

Debes definir lo que quieres decir con "perdido". No puede esperar que su servidor de base de datos comprenda este concepto abstracto. Tal vez un procedimiento almacenado sea la mejor manera de hacerlo, ya que puede definir su lógica de manera más precisa.

Answer 3

Hay un truco estándar para generar enteros que requiere que cree una tabla de utilidad de 10 filas a saber:

create table Pivot (i int)

insert into Pivot values (0)
insert into Pivot values (1)
insert into Pivot values (2) 

/* ... down to */

insert into Pivot values (9)

Una vez que haya hecho esto, entonces, por ejemplo

select u.i + 10*t.i + 100*h.i from Pivot u, Pivot t, Pivot h

obtendrá todos los números del 0 al 999.

Agregue una cláusula where para restringirlo entre un rango, y algunas funciones de cadena lo llevarán a la tabla PossibleEntries en la respuesta Robs anterior.

Answer 4

Personalmente, haría esto en PHP o en cualquier lenguaje de programación que esté usando con SQL. Si no puede tener una tabla separada con todos los valores posibles (¿por qué no, por cierto?), Entonces el enfoque que tomaría sería hacer una consulta directa para obtener los valores que están en la tabla:

select WebID from table order by WebID;

y luego usa un bucle simple para encontrar cuáles faltan. Por ejemplo, en php:

$values = Array();
$query = "select WebID from table order by WebID;";
$dataset = mysql_query ($query) or die (mysql_error());
while ($data = mysql_fetch_assoc($dataset))
{
  $values[$data[''WebID''] = 1;
}

$last_line = $data[''WebID''];
$matches = Array();
ereg("S([0-9]+)", $last_line, $matches))

$max_value = $matches[0];
$missing = Array();

for ($count = 0; $count < $max_value; $count ++)
{
  if (!isset($values[$count])
  { 
    echo "value $count is missing/n";
    $missing[$count] = true;
  }
}

No lo he probado, pero si usa PHP, eso podría hacer lo que quiera.

Ben

Answer 5

Supongo que su base de datos tiene un defecto de diseño serio, ya que parece que su WebID es realmente al menos dos columnas que ha combinado. La parte numérica obviamente tiene algún tipo de significado, ya que quiere que sea secuencial, pero si ese es el caso, entonces, ¿qué significa la "S"? Como resultado de este defecto de diseño, la solución a su problema va a ser más compleja de lo que debe ser. Además, decir que no es "práctico" almacenar datos que son importantes para la base de datos es una gran señal de advertencia.

Dejando eso de lado, la siguiente consulta debería darle cualquier valor perdido:

SELECT
     (
          SELECT
               SUBSTRING(MAX(T4.WebID), 1, 1) +
               RIGHT(''000'' + CAST(CAST(SUBSTRING(MAX(T4.WebID), 2, 3) AS INT) + 1 AS VARCHAR), 3)
           FROM My_Table T4
           WHERE T4.WebID < T1.WebID
     ) AS min_range,
     SUBSTRING(T1.WebID, 1, 1) + RIGHT(''000'' + CAST(CAST(SUBSTRING(T1.WebID, 2, 3) AS INT) - 1 AS VARCHAR), 3) AS max_range
FROM
     My_Table T1
LEFT OUTER JOIN My_Table T2 ON
     T2.WebID = SUBSTRING(T1.WebID, 1, 1) +
                RIGHT(''000'' + CAST(CAST(SUBSTRING(T1.WebID, 2, 3) AS INT) - 1 AS VARCHAR), 3)

WHERE
     T2.WebID IS NULL AND
     T1.WebID <> (SELECT MIN(WebID) FROM My_Table)

Le da un inicio y un final para cada rango de valores perdidos, en lugar de una lista distinta de cada uno. Para obtener eso necesitarás una tabla de números que cindi y Rob han cubierto.

Answer 6

Un enfoque muy simple :)

mysql> select * from test;
+----------+-------+
| DocEntry | WebId |
+----------+-------+
| 1        | S001  |
| 2        | S002  |
| 3        | S003  |
| 4        | S005  |
| 5        | S006  |
| 6        | S007  |
| 7        | S008  |
| 8        | S010  |
+----------+-------+
8 rows in set (0,00 sec)

mysql> SELECT right(t1.webid,3) +1 as missing_WebId FROM test t1 left join test t2 on right(t1.webid,3)+1 = right(t2.webid,3) where t2.webid is null;
+---------------+
| missing_WebId |
+---------------+
| 4             |
| 9             |
| 11            |
+---------------+
3 rows in set (0,01 sec)

buena suerte, Maurice

Answer 7

( Aparte: ¿Por qué la gente en general (Rahul no es el único, por ningún lado de la imaginación) omite el nombre de su mesa de la pregunta? )

Es muy difícil de hacer de una manera relacional porque se basa intrínsecamente en el orden de datos y el álgebra relacional funciona en conjuntos (no ordenados). Supongo que debemos suponer que no hay ningún significado para la columna de DocID y que no se puede usar para ayudar a resolver el problema.

En el ejemplo, tiene S003 y S005 y le falta S004. ¿Cómo decimos que hay un valor perdido? Presumiblemente, porque hay una operación de comparación que nos dice ''menos que'', ''igual'', ''mayor que'', y también porque hay una función de diferencia que nos dice que la brecha entre S003 y S005 es 2. Supongamos que '' > ''y amigos hacen la comparación (funciona aquí para cadenas de caracteres), y que puede producir un procedimiento almacenado webid_diff () que toma dos valores de WebID y devuelve la diferencia.

Luego, podría escribir una consulta como:

SELECT a.webid, MIN(b.webid) AS min_next
    FROM AnonymousTable AS a, AnonymousTable AS b
    WHERE a.webid < b.webid
    GROUP BY a.webid;

Esto utiliza una no equijoin entre la tabla y ella misma para encontrar el valor mínimo de WebID sucesor para cada elemento.

Con eso como núcleo, podemos filtrar el resultado para seleccionar solo aquellas filas para las cuales el espacio entre WebID y Min_Next es más de uno. Entonces, creo que tenemos ( primer intento) :

SELECT x.webid, y.min_next, webid_diff(x.webid, y.min_next) AS gap
    FROM AnonymousTable AS x,
         (SELECT a.webid, MIN(b.webid) AS min_next
             FROM AnonymousTable AS a, AnonymousTable AS b
             WHERE a.webid < b.webid
             GROUP BY a.webid
         ) AS y
    WHERE x.webid = y.webid
      AND webid_diff(x.webid, y.min_next) > 1;

¿La unión en el nivel externo realmente nos proporciona algo útil? No lo creo, así que podemos eliminarlo, lo que lleva a ( segundo intento) :

SELECT y.webid, y.min_next, webid_diff(y.webid, y.min_next) AS gap
    FROM (SELECT a.webid, MIN(b.webid) AS min_next
             FROM AnonymousTable AS a, AnonymousTable AS b
             WHERE a.webid < b.webid
             GROUP BY a.webid
         ) AS y
    WHERE webid_diff(y.webid, y.min_next) > 1;

Esto funciona Tratar de poner la función webid_diff () en la consulta interna me da problemas, al menos la expresión GAP debería incluirse en la cláusula GROUP BY, pero eso dará la respuesta incorrecta.

La cláusula HAVING se usa para aplicar condiciones de filtro a los agregados, por lo que parece un poco como si la consulta pudiera reducirse a:

SELECT a.webid, MIN(b.webid) AS min_next, webid_diff(a.webid, b.webid) AS gap
    FROM AnonymousTable AS a, AnonymousTable AS b
    WHERE a.webid < b.webid
    GROUP BY a.webid
    HAVING webid_diff(a.webid, b.webid) > 1;

Sin embargo, esto no funciona (para mí, con mi DBMS - IBM Informix Dynamic Server) porque webid_diff () no es un agregado.

Aquí está el código que utilicé para la función webid_diff () (tendría que ajustar para adaptarse a la sintaxis de su DBMS), y la función auxiliar webid_num ():

CREATE FUNCTION webid_num(a CHAR(4)) RETURNING INTEGER;
    DEFINE i INTEGER;
    LET i = substr(a, 2, 3);
    RETURN i;
END FUNCTION;

CREATE FUNCTION webid_diff(a CHAR(4), b CHAR(4)) RETURNING INTEGER;
    DEFINE i, j INTEGER;
    LET i = webid_num(a);
    LET j = webid_num(b);
    RETURN (j - i);
END FUNCTION;