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: https://gist.github.com/mshakhomirov/18775cbbe8288af864ad79247c0de63d?embedable=true#file-1-1-sql 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 con SQL y/o actualizar conjuntos de datos de forma incremental con solo una parte de los datos. Es posible que no exista, pero en su lugar tendrá que lidiar con el modelo de datos donde la clave única depende de la última (o marca de tiempo) conocida. Por ejemplo, en el conjunto de datos depende de la última marca de tiempo de conexión conocida. En este caso, querrá los usuarios existentes e los nuevos. sesiones transaction_id transaction_id user_id last_online update insert MERGE y actualizaciones incrementales Puede usar 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. MERGE 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: https://gist.github.com/mshakhomirov/5ad1a7518c54bc030d1c78b56fe3cf82?embedable=true#file-1-2-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: https://gist.github.com/mshakhomirov/694e040539b0d1b556f8e053d315a3bf?embedable=true#file-2-sql 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 , pero puede hacer en su lugar: where esto https://gist.github.com/mshakhomirov/933e6a358e49dcccd4e547a5509c8fda?embedable=true#file-3-sql Otro ejemplo de cómo usarlo con tablas . . 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 NO particionadas No hagas esto cobrará por un escaneo completo de la tabla. https://gist.github.com/mshakhomirov/1c62d79cd9690140c569cd047b9d491f?embedable=true#file-3-2-sql También puede usarlo en la cláusula y las funciones . HAVING 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 , y también muestra el gasto total de cada día y el gasto total en todas las fechas disponibles. where https://gist.github.com/mshakhomirov/4cf738aaad967fe92c4fb7192874fadf?embedable=true#file-4-sql 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 se vuelve útil: to_json_string() https://gist.github.com/mshakhomirov/aac1f93312ae305ba80c915fe4a2a386?embedable=true#file-5-sql Luego, puede usarlo en cualquier lugar: fechas, embudos de marketing, índices, gráficos de histograma, etc. 6. Usando PARTICIÓN POR Dadas las columnas , y . Para CADA fecha, ¿cómo muestra el valor de ingresos totales para CADA cliente manteniendo todas las filas? Puedes lograr esto así: user_id date total_cost https://gist.github.com/mshakhomirov/e4f11721eb5a3182150df08f25b70d64?embedable=true#file-6-sql 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? https://gist.github.com/mshakhomirov/ebf5488d0036bc9b84ae05889346d986?embedable=true#file-7-sql 8. Matrices de fechas Se vuelve realmente útil cuando trabaja con o desea verificar algún conjunto de datos en busca de valores faltantes, es decir, fechas. BigQuery tiene una función llamada : la retención de usuarios GENERATE_DATE_ARRAY https://gist.github.com/mshakhomirov/2ba5a67053f85794462dab98e56ad74d?embedable=true#file-8-sql 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: https://gist.github.com/mshakhomirov/05d0c04c5975207d98552ffd436add8b?embedable=true#file-9-sql 10. NTIL() Otra función de numeración. Realmente útil para monitorear cosas como si tiene una aplicación móvil. Por ejemplo, tengo mi aplicación conectada a Firebase y cuando los usuarios , puedo ver cuánto tiempo les tomó. Login duration in seconds login Esta función divide las filas en depósitos 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 se evalúa como NULL, 0 o negativo, se proporciona un error. constant_integer_expression constant_integer_expression https://gist.github.com/mshakhomirov/16fe941aa8c4ed79e4aad8b7049b307a?embedable=true#file-10-sql 11. Rango / dense_rank También se les llama funciones . Tiendo a usar , ya que no omite la siguiente clasificación disponible, mientras que 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. de numeración DENSE_RANK como función de clasificación predeterminada RANK Ejemplo: https://gist.github.com/mshakhomirov/459b68c5f3d1e8284c01e516db1d8dcb?embedable=true#file-11-1-sql Otro ejemplo con precios de productos: https://gist.github.com/mshakhomirov/4c90a6fc8516d8264e172676a83a1048?embedable=true#file-11-2-sql 12. Pivotar/despivotar Pivot cambia filas a columnas. Es todo lo que hace. Unpivot hace lo . contrario https://gist.github.com/mshakhomirov/f90b035ba259e672d4d51a669e0cd1fc?embedable=true#file-12-sql 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. https://gist.github.com/mshakhomirov/ea4de9144b97bf8c196cab07609c309e?embedable=true#file-13-sql 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: https://gist.github.com/mshakhomirov/35d956fa9db86b12b44ab62c00f42a40?embedable=true#file-14-sql De manera similar, puede crear tablas sin necesidad de usar . Por ejemplo, lo uso para simular algunos datos de prueba para pruebas unitarias. De esta manera, puede hacerlo muy rápido simplemente usando + + en su editor. UNION ALL Alt Shift Down https://gist.github.com/mshakhomirov/6ea226c1b5b789d4a31691ce065c20d7?embedable=true#file-14-2-sql 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. https://gist.github.com/mshakhomirov/05fd7d79d8acf3b173181a5d950ab6e7?embedable=true#file-15-sql 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: https://gist.github.com/mshakhomirov/9ca6e153da19c491034bd57995875308?embedable=true#file-16-1-sql Trabajar con versiones de la aplicación usando expresiones regulares A veces, es posible que desee usar para obtener versiones , o para su aplicación y crear un informe personalizado: regexp principales de lanzamiento de modificación https://gist.github.com/mshakhomirov/b1f442a296ffef52c7baa1245e1dc316?embedable=true#file-16-2-sql 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 por mydataschool.com 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.

Read me on Medium.com

Este audio es producido en el idioma original de la historia!

Demasiado Largo; Para Leer

Making security fun one episode at a time

16 técnicas de SQL que todo principiante necesita saber

Demasiado Largo; Para Leer

Mike Shakhomirov

About Author

ETIQUETAS

Languages

ESTE ARTÍCULO FUE PRESENTADO EN...

16 técnicas de SQL que todo principiante necesita saber

Demasiado Largo; Para Leer

Mike Shakhomirov

About Author

ETIQUETAS

Languages

ESTE ARTÍCULO FUE PRESENTADO EN...

HISTORIAS RELACIONADAS