16 técnicas de SQL que todo principiante necesita saber

En una escala del 1 al 10, ¿qué tan buenas son sus habilidades de almacenamiento de datos?

¿Quieres ir por encima de 7/10? Este artículo es para ti entonces.

¿Qué tan bueno es tu SQL? ¿Quieres prepararte para una entrevista de trabajo lo antes posible?

Esta publicación de blog explica en detalle las técnicas de almacenamiento de datos SQL más complejas. Usaré el dialecto SQL estándar de BigQuery para escribir algunas ideas sobre este tema.

1. Tablas incrementales y MERGE

Es importante actualizar la tabla. Es importante de hecho. La situación ideal es cuando tiene transacciones que son una clave PRIMARIA, enteros únicos e incremento automático. La actualización de la tabla en este caso es simple:

Ese no siempre es el caso cuando se trabaja con conjuntos de datos de esquema en estrella desnormalizados en almacenes de datos modernos. es posible que tenga la tarea de crear sesiones con SQL y/o actualizar conjuntos de datos de forma incremental con solo una parte de los datos. Es posible que transaction_id no exista, pero en su lugar tendrá que lidiar con el modelo de datos donde la clave única depende de la última transaction_id (o marca de tiempo) conocida. Por ejemplo, user_id en el conjunto de datos last_online depende de la última marca de tiempo de conexión conocida. En este caso, querrá update los usuarios existentes e insert los nuevos.

MERGE y actualizaciones incrementales

Puede usar MERGE o puede dividir la operación en dos acciones. Uno para actualizar registros existentes con nuevos y otro para insertar registros completamente nuevos que no existen (situación LEFT JOIN).

MERGE es una declaración que generalmente se usa en bases de datos relacionales. El comando MERGE de Google BigQuery es una de las declaraciones del lenguaje de manipulación de datos (DML). A menudo se usa para realizar tres funciones principales de forma atómica en una sola declaración. Estas funciones son ACTUALIZAR, INSERTAR y ELIMINAR.

• La cláusula UPDATE o DELETE se puede utilizar cuando dos o más datos coinciden.
• La cláusula INSERT se puede utilizar cuando dos o más datos son diferentes y no coinciden.
• La cláusula UPDATE o DELETE también se puede usar cuando los datos proporcionados no coinciden con la fuente.

Esto significa que el comando MERGE de Google BigQuery le permite combinar datos de Google BigQuery actualizando, insertando y eliminando datos de sus tablas de Google BigQuery.

Considere este SQL:

2. Contar palabras

Hacer UNNEST() y verificar si la palabra que necesita está en la lista que necesita podría ser útil en muchas situaciones, es decir, el análisis de sentimientos del almacén de datos:

3. Usando la instrucción IF() fuera de la instrucción SELECT

Esto nos da la oportunidad de guardar algunas líneas de código y ser más elocuentes en cuanto al código. Normalmente, querrá poner esto en una subconsulta y agregar un filtro en la cláusula where , pero puede hacer esto en su lugar:

Otro ejemplo de cómo NO usarlo con tablas particionadas . No hagas esto . Este es un mal ejemplo porque, dado que los sufijos de la tabla coincidente probablemente se determinen dinámicamente (en función de algo en su tabla), se le cobrará por un escaneo completo de la tabla.

También puede usarlo en la cláusula HAVING y las funciones AGGREGATE .

4. Usando GROUP BY ROLLUP

La función ROLLUP se utiliza para realizar la agregación en varios niveles. Esto es útil cuando tiene que trabajar con gráficos de dimensiones.

La siguiente consulta devuelve el crédito total gastado por día por el tipo de transacción (is_gift) especificado en la cláusula where , y también muestra el gasto total de cada día y el gasto total en todas las fechas disponibles.

5. Convertir tabla a JSON

Imagine que debe convertir su tabla en un objeto JSON donde cada registro es un elemento de una matriz anidada. Aquí es donde la función to_json_string() se vuelve útil:

Luego, puede usarlo en cualquier lugar: fechas, embudos de marketing, índices, gráficos de histograma, etc.

6. Usando PARTICIÓN POR

Dadas las columnas user_id , date y total_cost . Para CADA fecha, ¿cómo muestra el valor de ingresos totales para CADA cliente manteniendo todas las filas? Puedes lograr esto así:

7. Media móvil

Muy a menudo, los desarrolladores de BI tienen la tarea de agregar un promedio móvil a sus informes y tableros fantásticos. Esto podría ser un gráfico de líneas MA de 7, 14, 30 días/mes o incluso un año. Entonces, ¿Cómo lo hacemos?

8. Matrices de fechas

Se vuelve realmente útil cuando trabaja con la retención de usuarios o desea verificar algún conjunto de datos en busca de valores faltantes, es decir, fechas. BigQuery tiene una función llamada GENERATE_DATE_ARRAY :

9. Número_fila()

Esto es útil para obtener algo más reciente de sus datos, es decir, el último registro actualizado, etc. o incluso para eliminar duplicados:

10. NTIL()

Otra función de numeración. Realmente útil para monitorear cosas como Login duration in seconds si tiene una aplicación móvil. Por ejemplo, tengo mi aplicación conectada a Firebase y cuando los usuarios login , puedo ver cuánto tiempo les tomó.

Esta función divide las filas en depósitos constant_integer_expression según el orden de las filas y devuelve el número de depósito basado en 1 que se asigna a cada fila. El número de filas en los cubos puede diferir en 1 como máximo. Los valores restantes (el resto del número de filas dividido por cubos) se distribuyen uno para cada cubo, comenzando con el cubo 1. Si constant_integer_expression se evalúa como NULL, 0 o negativo, se proporciona un error.

11. Rango / dense_rank

También se les llama funciones de numeración . Tiendo a usar DENSE_RANK como función de clasificación predeterminada , ya que no omite la siguiente clasificación disponible, mientras que RANK sí lo haría. Devuelve valores de rango consecutivos. Puede usarlo con una partición que divide los resultados en cubos distintos. Las filas de cada partición reciben los mismos rangos si tienen los mismos valores. Ejemplo:

Otro ejemplo con precios de productos:

12. Pivotar/despivotar

Pivot cambia filas a columnas. Es todo lo que hace. Unpivot hace lo contrario .

13. Primer_valor / último_valor

Esa es otra función útil que ayuda a obtener un delta para cada fila contra el primer/último valor en esa partición en particular.

14. Convierta una tabla en una matriz de estructuras y páselas a UDF

Esto es útil cuando necesita aplicar una función definida por el usuario (UDF) con alguna lógica compleja a cada fila o tabla. Siempre puede considerar su tabla como una matriz de objetos TYPE STRUCT y luego pasar cada uno de ellos a UDF. Depende de tu lógica. Por ejemplo, lo uso para calcular los tiempos de vencimiento de la compra:

De manera similar, puede crear tablas sin necesidad de usar UNION ALL . Por ejemplo, lo uso para simular algunos datos de prueba para pruebas unitarias. De esta manera, puede hacerlo muy rápido simplemente usando Alt + Shift + Down en su editor.

15. Creación de embudos de eventos usando SEGUIMIENTO Y SEGUIMIENTO ILIMITADO

Un buen ejemplo podrían ser los embudos de marketing. Su conjunto de datos puede contener eventos que se repiten continuamente del mismo tipo, pero lo ideal sería encadenar cada evento con el siguiente de un tipo diferente. Esto puede ser útil cuando necesita obtener una lista de algo, por ejemplo, eventos, compras, etc. para crear un conjunto de datos de embudos. Trabajar con PARTITION BY le brinda la oportunidad de agrupar todos los siguientes eventos sin importar cuántos de ellos existan en cada partición.

16. expresión regular

Lo usaría si necesita extraer algo de datos no estructurados, es decir, tipos de cambio, agrupaciones personalizadas, etc.

Trabajando con tasas de cambio de moneda usando regexp

Considere este ejemplo con datos de tipos de cambio:

Trabajar con versiones de la aplicación usando expresiones regulares

A veces, es posible que desee usar regexp para obtener versiones principales , de lanzamiento o de modificación para su aplicación y crear un informe personalizado:

Conclusión

SQL es una poderosa herramienta que ayuda a manipular datos. Con suerte, estos casos de uso de SQL del marketing digital le serán útiles. Es una habilidad muy útil y puede ayudarte con muchos proyectos. Estos fragmentos de SQL me hicieron la vida mucho más fácil y los uso en el trabajo casi todos los días. Además, SQL y los almacenes de datos modernos son herramientas esenciales para la ciencia de datos. Sus sólidas características de dialecto permiten modelar y visualizar datos con facilidad. Dado que SQL es el lenguaje que utilizan los profesionales de los almacenes de datos y la inteligencia empresarial, es una excelente selección si desea compartir datos con ellos. Es la forma más común de comunicarse con casi todas las soluciones de almacén/lago de datos del mercado.

Publicado originalmente en mydataschool.com por datamike

Mike es una persona apasionada y enfocada digitalmente con una gran cantidad de impulso y entusiasmo, que adora los desafíos que presenta la combinación completa de marketing digital. Vive en el Reino Unido, completó un MBA de la Universidad de Newcastle en 2015.