TecNoticias, tu portal de información

Transformaciones de SQL menos conocidas y por qué podrían ser útiles

Foto de akiragiulia en Pixabay En publicaciones anteriores, analicé datos del SQL Generator 5000 y describí las 5 transformaciones de SQL más populares. Esta vez, estoy haciendo lo contrario y mirando las 5 menos populares. Cuando inicialmente revisé las transformaciones menos populares, me sorprendió porque la mayoría de ellas son muy útiles. Por lo tanto, contacté a algunos usuarios que conozco personalmente para hablarles al respecto. Según esas conversaciones, aprendí que la razón principal por la que estas transformaciones rara vez se usan es doble: Algunas son muy situacional — mientras que el agregado y la combinación se usan en casi todas las consultas SQL, el promedio móvil solo se usa cuando se trabaja con datos de series temporales. Algunas de estas transformaciones son exclusivas del Generador SQL, y la falta de uso se debe simplemente a una falta de conciencia que existen. Por lo tanto, revisaré las 5 transformaciones menos populares y, junto con el código SQL, explicaré una situación en la que cada una podría ser útil.

¿Qué es?

Los escaladores son métodos que cambian el rango de valores. Los dos más comunes son: min_max_scaler y standard_scaler. En general, la escala se realiza de manera que el nuevo rango se encuentre entre 0 y 1. La estandarización, por otro lado, se realiza de manera que el valor promedio sea 0 y la desviación estándar sea 1. A veces, el término normalizar surge en una conversación sobre estas técnicas. Normalizar puede tener un significado muy específico, pero por lo general es un término genérico que podría significar escalar o estandarizar (¡o algo más!). Para evitar confusiones, no utilice el término normalizar.

¿Cómo se usa?

Hay varios escenarios comunes para escalar los datos: Comparar relativamente cosas que se miden con diferentes unidades Segmentación básica Preprocesamiento para aprendizaje automático Graficar métricas juntas en el mismo gráfico Imagine que tiene algunos datos sobre pacientes, donde cada una de las variables estaba en una escala diferente: Datos sin procesar Dado que la edad se mide en años y la glucosa se mide en mg/DL, es difícil para nosotros comparar pacientes. La fórmula para escalar estos datos requiere que conozcamos el MIN() y MAX() de los valores. De manera similar, standard_scaler requiere que conozcamos el AVG() y STDEV(). Tenga en cuenta cómo el SQL está escrito de una manera que calcula dinámicamente todo sobre la marcha. SQL para Min-Max ScalingScaled data

¿Qué es?

Latest es una transformación de SQL que devuelve el último valor en un conjunto ordenado de valores. Si bien la mayoría de las funciones de SQL solo “miran” una fila a la vez, esta usa lo que se llama funciones de ventana para mirar otras filas. Por esta razón, solo se utiliza cuando sus datos tienen un orden inherente.

¿Cómo se usa?

Hay muchas maneras en que se puede usar Latest, pero las más comunes son: analizar los registros del sistema o sistemas que registran valores solo cuando cambian analizar los datos de los sensores cuando los sensores a veces no informan (esto es común con los datos de los sensores) analizar datos de “instantáneas” — encontrar valores de fin de período
(Ventas de fin de mes, Llamadas de fin de día, etc.) preprocesamiento para series de tiempo o modelos de pronóstico Imagine que tiene datos de sensores, donde a veces faltan las lecturas de temperatura: Datos de sensores por hora (sin procesar) Aquí está el SQL personalizable que reemplaza cualquier valor en blanco con el último valor anterior. La función que funciona mejor para esta transformación se llama LAST_VALUE(). Funciona al declarar una ventana de búsqueda, que tiene diferentes comportamientos predeterminados según RDBMS. Por lo tanto, siempre recomiendo que declare explícitamente la definición ROWS BETWEEN para que su código sea más comprensible.

NOTA: Escribí un tutorial más largo sobre el uso de esta técnica para llenar los vacíos en los datos de series temporales.

¿Qué es?

La transformación del promedio móvil es otra función de ventana que requiere que los datos tengan un orden inherente. En lugar del monto de las ventas de hoy, el promedio móvil se puede usar para decirnos cuál fue el monto promedio de las ventas durante los últimos 7 días.

¿Cómo se usa?

La media móvil se utiliza principalmente para identificar una tendencia. También pueden “suavizar” las fluctuaciones si sus valores saltan mucho. Es uno de los indicadores técnicos más antiguos y más utilizados. ¡Incluso está disponible en Excel! Si traza una serie de tiempo en Excel, puede agregar un promedio móvil haciendo clic con el botón derecho y “Agregar línea de tendencia”. patrón para ello:Datos de ejemplo con patrón estacionalHice el gráfico para que pueda ver cómo hay una tendencia ligeramente creciente. Sin embargo, cuantificar esa tendencia puede ser difícil ya que los números están por todas partes. Usando la transformación de la media móvil, podemos crear SQL que agregue la media móvil para nosotros. Cuando graficamos el resultado, podemos ver una tendencia agradable, constante y creciente. .

¿Qué es?

La entropía es una forma de agregar datos categóricos y representar cuán dispersos están. Proviene del ámbito de la teoría de la información y, a menudo, se usa detrás de escena en algoritmos de aprendizaje automático.

¿Cómo se usa?

Nuestro análisis actual del Generador SQL es un gran ejemplo de dónde es útil la entropía. Imagine que el Usuario A y el Usuario B usaron el Generador de SQL 100 veces cada uno, y que solo usaron las 2 transformaciones más populares agregadas y dinámicas. Usuario A: agregadas 50 tiempos, pivote 50 veces
Usuario B: agregado 99 tiempos, pivote 1 timeSi solo agregamos COUNT() y COUNT(DISTINCT), no podríamos diferenciar el comportamiento de estos 2 usuarios, pero su comportamiento es bastante diferente. Aquí es donde ayuda medir la entropía. Los usuarios A y B son claramente diferentes debido a sus distribuciones de uso. La entropía nos mostrará que el usuario A tiene una entropía de 1 porque la distribución entre el agregado y el pivote está tan dispersa como es posible. Por otro lado, el Usuario B tendrá una entropía muy baja porque utilizan principalmente una única transformación. No existe una función SQL directa para calcular la entropía, pero el Generador SQL escribe la lógica para realizar esta agregación por usted. ¡Inténtalo tú mismo!

¿Qué es?

Un histograma es un tipo de gráfico, por lo que no sorprende que los usuarios de una herramienta SQL no encuentren esta transformación muy popular. De manera similar a la entropía, un histograma también mide la dispersión, dividiendo una columna numérica en contenedores y luego trazando la frecuencia con la que ocurren. Sin embargo, un histograma es un gráfico visual, y SQL no puede hacer gráficos, entonces, ¿para qué sirve esta transformación de SQL?

¿Cómo se usa?

Los histogramas en sí mismos son muy útiles. Permiten que un usuario responda rápidamente preguntas como: ¿Con qué frecuencia el tiempo de envío supera las 48 horas? ¿Cuál es la distribución del monto total del pedido? Los analistas generalmente usan una herramienta de BI o un paquete de python para agregar automáticamente los datos y crear el gráfico. . Esta transformación de SQL está destinada a agregar solo los datos. Esto se debe a que los datos subyacentes pueden ser muy grandes en comparación con el agregado que realmente se traza. Por ejemplo, si tuviera 500 millones de pedidos y quisiera trazar un histograma del total_order_amount, sería un desperdicio computacional mover los 500 millones filas en su computadora portátil, solo para agregarlas en 100 filas para un gráfico de histograma. Este SQL producirá las filas agregadas directamente desde el almacén, por lo que trazar el histograma se vuelve rápido y fácil. Aquí hay un ejemplo: al configurar el Generador de SQL con histograma, podemos elegir cuántos contenedores usar (100, en este ejemplo). SQL se genera como: Para recapitular, la semana pasada analizamos las 5 transformaciones más populares y seguimos con las 5 menos populares.

Más popular — Correlación, Limpiar, Unir, Pivotar, AgregarMenos populares — MinMax Scaler, Latest, Moving Promedio, Entropía, Histograma

Como puede ver, las transformaciones populares parecen ser las simples y tediosas, mientras que las menos populares parecen ser las más especializadas y complicadas. Según mis conversaciones con los usuarios, también es evidente que muchos no son conscientes de lo que algunos de estas transformaciones especializadas están destinadas a hacer. Entonces, con suerte, he compartido un contexto útil sobre los que se usan con poca frecuencia, de modo que si se encuentra en una de estas situaciones raras, ya tendrá el código escrito para usted.