Heredé esta consulta MySQL como un código heredado:

       SELECT
        HardwareAddress, CONV(SUBSTRING(EventValue,3,2), 16, 10) AS 'Algorithm'
        FROM ( SELECT @prev := '') init
        JOIN
            ( SELECT HardwareAddress != @prev AS first,
                @prev := HardwareAddress,
                HardwareAddress, EventValue, ID
                FROM Events   
                    WHERE Time > {unixtime}
                    AND EventType = 104
                    AND HardwareAddress IN ({disps})
                    ORDER BY
                        HardwareAddress,
                        ID DESC
            ) x
       WHERE first;

{unixtime} y {disps} son variables que se completan con el método Python String.format ().

Me está costando crear nuevas funcionalidades a partir de esta consulta porque nadie más entiende cómo funciona, y no he podido encontrar la documentación adecuada. Todo lo que sé es que la consulta extrae una lista de valores llamada 'algoritmo' de una larga cadena hexadecimal enviada por dispositivos IoT.

Más o menos entiendo cómo funcionan las subselecciones y las variables de intervalo, pero hay muchas cosas que no entiendo. ¿Cómo funciona la línea FROM (SELECT @prev := '') init? Las dos últimas líneas de la consulta también me confunden. ¿Por qué la subconsulta tiene un alias de x cuando nada hace referencia a ella, y qué significa WHERE first?

Si alguien puede guiarme a través de lo que está haciendo este fragmento de código, estaría muy agradecido.

0
OnlyDean 28 feb. 2018 a las 00:47

3 respuestas

La mejor respuesta

Dividamos el SQL en partes. Las entrañas del todo es la subconsulta UNIDA:

SELECT
  HardwareAddress != @prev AS first,
  @prev := HardwareAddress,
  HardwareAddress,
  EventValue,
  ID
FROM Events
WHERE
    Time > {unixtime}
AND EventType = 104
AND HardwareAddress IN ({disps})
ORDER BY HardwareAddress, ID DESC
  1. Columna 1: Sin saber (todavía) qué es @prev, vemos que el operador es !=. Eso significa que cualesquiera que sean sus operandos, la columna 1 será un valor binario. En MySQL, un 1 o un 0.

  2. Columna 2: establece una variable SQL, @prev, en el valor de la fila coincidente actualmente. Discusión a continuación, pero el resultado en lo que respecta a la consulta siempre será NULL.

  3. Columnas 3, 4, 5: supongo que se explica por sí mismo.

  4. Restricciones 1, 2, 3: supongo que se explica por sí mismo.

  5. ORDER BY: una nota es que la consulta ordena los resultados por la tercera columna HardwareAddress, y luego ID descendente .

La primera columna es entonces un booleano, que dice si la columna HardwareAddress de esta fila es la misma que la de la fila anterior. En contexto, 1 significa que esta es la primera fila de ese HardwareAddress, dado el ORDER BY.

Entonces, la consulta devolverá un resultado como:

+-------+--------------------------+-------------------+------------+-----+
| first | @prev := HardwareAddress | HardwareAddress   | EventValue | ID  |
+-------+--------------------------+-------------------+------------+-----+
|     1 |                     NULL | ff:ff:9d:5f:f5:01 | ...        |  10 |
|     0 |                     NULL | ff:ff:9d:5f:f5:01 | ...        |   9 |
|     0 |                     NULL | ff:ff:9d:5f:f5:01 | ...        |   8 |
|     0 |                     NULL | ff:ff:9d:5f:f5:01 | ...        |   7 |
|     1 |                     NULL | ff:ff:9d:5f:f5:02 | ...        | 200 |
|     0 |                     NULL | ff:ff:9d:5f:f5:02 | ...        |  37 |
|     0 |                     NULL | ff:ff:9d:5f:f5:02 | ...        |  24 |
|     0 |                     NULL | ff:ff:9d:5f:f5:02 | ...        |  23 |
|     0 |                     NULL | ff:ff:9d:5f:f5:02 | ...        |  22 |
|     1 |                     NULL | ff:ff:9d:5f:f5:03 | ...        | 152 |
|   ... |                     NULL | ff:ff:9d:..:..:.. | ...        | ... |
|   ... |                     NULL | ff:ff:9d:..:..:.. | ...        | ... |
|   ... |                     NULL | ff:ff:9d:..:..:.. | ...        | ... |
+-----+----------------------------+-------------------+------------+-----+

Poniéndolo junto con la restricción de la consulta externa, WHERE first, y el resultado final sería:

+-------+--------------------------+-------------------+------------+-----+
| first | @prev := HardwareAddress | HardwareAddress   | EventValue | ID  |
+-------+--------------------------+-------------------+------------+-----+
|     1 |                     NULL | ff:ff:9d:5f:f5:01 | ...        |  10 |
|     1 |                     NULL | ff:ff:9d:5f:f5:02 | ...        | 200 |
|     1 |                     NULL | ff:ff:9d:5f:f5:03 | ...        | 152 |
+-----+----------------------------+-------------------+------------+-----+

En otras palabras, toda esta consulta intenta obtener el primero de cada HardwareAddress en el pedido dado. ¿La magia FROM (SELECT @prev := '') init? Eso solo inicializa la variable SQL @prev para usar dentro de la subconsulta resultante. La parte de cola ... ID DESC) x alias la consulta interna a x. Uno podría utilizar estos alias para nuevas uniones en consultas más complicadas, pero en este caso, están ahí por razones de sintaxis de MySQL. Puedes ignorarlos.

En general, es un método muy ineficiente para obtener la ID máxima asociada con cada HardwareAddress. Si la consulta necesita el máximo de cada columna, simplemente pregunte directamente con MAX. Considerar:

SELECT
  HardwareAddress,
  CONV(SUBSTRING(EventValue,3,2), 16, 10) AS 'Algorithm',
  MAX(ID) AS ID
FROM Events
WHERE
    Time > {unixtime}
AND EventType = 104
AND HardwareAddress IN ({disps})
GROUP BY 1, 2;

Tendrá una identificación adicional en su salida; Si el código es demasiado frágil para manejar la nueva columna, puede enmascararlo de manera similar a como lo hizo la consulta original con una subselección:

SELECT HardwareAddress, CONV(...) AS 'Algorithm'
FROM (SELECT ...) x

A pesar de lo selectivos que son AND EventType y AND HardwareAddress, esta debería ser una consulta mucho más eficiente. Aún mejor si la columna ID tiene un índice.

3
hunteke 4 mar. 2018 a las 07:19

Todas las subconsultas deben tener un alias. Todo lo que hace la init subconsulta es inicializar la sesión / @ variable (es equivalente a ejecutar SET @prev := ''; antes de ejecutar la consulta).

1
Uueerdo 27 feb. 2018 a las 21:58
FROM ( SELECT ... ) init
JOIN ( SELECT ... ) x

Podría ser escrito

FROM ( SELECT ... ) AS init
JOIN ( SELECT ... ) AS x

Esta última sintaxis ayuda a implicar que init y x son "alias" para las subconsultas. Esos alias son normalmente necesarios en otra parte de la consulta, pero no en este caso, especialmente cuando hay una cláusula ON. (Aún así, son obligatorios). Los nombres reales utilizados no son importantes.

SELECT @prev := ''

Devuelve solo una fila; No se utiliza realmente. Tiene el efecto secundario de asignar la "variable de usuario" @prev a la cadena vacía. La consulta externa depende de que esta subconsulta se realice antes que la otra subconsulta.

Por cierto, las dos subconsultas se denominan tablas "derivadas", ya que son posteriores a FROM o JOIN.

SELECT HardwareAddress != @prev AS first,  -- Outputs 1 or 0
       @prev := HardwareAddress,           -- Outputs HA, and sets @prev
    ...
    ORDER BY
        HardwareAddress,                   -- to go thru in order
        ID DESC

Mostrará 1 como first siempre que haya un "cambio" en HardwareAddress. La intención es que 1 esté en la primera línea, luego un montón de líneas con {{ X4}}, luego de vuelta a 1 para una dirección diferente. Estas dos expresiones hacen un 'patrón' para lograr ese objetivo.

HardwareAddress, EventValue, ID

Finalmente, puedes ver HardwareAddress (y un par de otras cosas).

Realmente sería mejor formatear la salida en el código de su aplicación, no intentar hacer el juego @prev en SQL .

WHERE first

Tenga en cuenta que 0 significa FALSO y cualquier otra cosa significa VERDADERO. first es un alias para la columna discutida anteriormente. Entonces, cuando es 1, se muestra; De lo contrario, se omite.

El efecto es mostrar el primero de cada grupo, agrupando por HardwareAddress. Por cierto, esto no es posible con un simple GROUP BY; Se necesita alguna forma de engaño. (Vea mi discusión sobre groupwise max .)

SELECT
    HardwareAddress,

La segunda tabla derivada necesariamente tiene algunas columnas adicionales. Tener esta consulta externa le permite tirar esos extras y centrarse en la salida deseada, es decir, HardwareAddress, no first, no la copia adicional de HardwareAddress.

SELECT ...
    CONV(SUBSTRING(EventValue,3,2), 16, 10) AS 'Algorithm'

Esto convierte una porción de EventValue de hexadecimal a decimal. La expresión podría haberse hecho en la tabla derivada; No hace mucha diferencia.

, ID

Esto parece ser información espuria que luego se descarta.

              WHERE Time > {unixtime}
                AND EventType = 104
                AND HardwareAddress IN ({disps})

(Supongo que comprende la cláusula WHERE). Recomiendo lo siguiente para ayudar al rendimiento, especialmente si la tabla es grande:

INDEX(EventType, Time),
INDEX(EventType, HardwareAddress)
1
Rick James 2 mar. 2018 a las 16:57