     
 Em uma escala de 1 a 10, quão boas são suas habilidades de armazenamento de dados?  Quer ir acima de 7/10? Este artigo é para você então.  Quão bom é o seu SQL? Quer se preparar para uma entrevista de emprego o mais rápido possível?  Esta postagem de blog explica detalhadamente as técnicas SQL de data warehouse mais intrincadas. Usarei o dialeto SQL padrão do BigQuery para rabiscar algumas ideias sobre esse tópico.  1. Tabelas incrementais e MERGE  A atualização da tabela é importante. É importante mesmo. A situação ideal é quando você tem transações que são uma chave PRIMÁRIA, números inteiros únicos e incremento automático. A atualização da tabela neste caso é simples:   https://gist.github.com/mshakhomirov/18775cbbe8288af864ad79247c0de63d?embedable=true#file-1-1-sql  Esse nem sempre é o caso ao trabalhar com conjuntos de dados de esquema em estrela desnormalizados em armazéns de dados modernos. você pode ser encarregado de criar   com SQL e/ou atualizar conjuntos de dados de forma incremental com apenas uma parte dos dados.   pode não existir, mas em vez disso você terá que lidar com o modelo de dados onde a chave única depende do último   (ou carimbo de data/hora) conhecido. Por exemplo,   no conjunto de dados   depende do carimbo de data/hora da conexão mais recente. Nesse caso, você deseja   os usuários existentes e   os novos. sessões transaction_id transaction_id user_id last_online update insert  MERGE e atualizações incrementais  Você pode usar   ou pode dividir a operação em duas ações. Um para atualizar registros existentes com novos e outro para inserir registros completamente novos que não saem (situação LEFT JOIN). MERGE    é uma instrução geralmente usada em bancos de dados relacionais. O comando MERGE do Google BigQuery é uma das instruções da linguagem de manipulação de dados (DML). Muitas vezes, é usado para executar três funções principais atomicamente em uma única instrução. Essas funções são UPDATE, INSERT e DELETE. MERGE  A cláusula UPDATE ou DELETE pode ser usada quando dois ou mais dados correspondem.  A cláusula INSERT pode ser usada quando dois ou mais dados são diferentes e não correspondem.  A cláusula UPDATE ou DELETE também pode ser usada quando os dados fornecidos não correspondem à fonte.  Isso significa que o comando Google BigQuery MERGE permite mesclar dados do Google BigQuery atualizando, inserindo e excluindo dados de suas tabelas do Google BigQuery.  Considere este SQL:   https://gist.github.com/mshakhomirov/5ad1a7518c54bc030d1c78b56fe3cf82?embedable=true#file-1-2-sql  2. Contando palavras  Fazer UNNEST() e verificar se a palavra que você precisa está na lista que você precisa pode ser útil em muitas situações, por exemplo, análise de sentimento do data warehouse:   https://gist.github.com/mshakhomirov/694e040539b0d1b556f8e053d315a3bf?embedable=true#file-2-sql  3. Usando a instrução IF() fora da instrução SELECT  Isso nos dá a oportunidade de economizar algumas linhas de código e ser mais eloquente em termos de código. Normalmente, você gostaria de colocar isso em uma subconsulta e adicionar um filtro na cláusula   , mas você pode fazer   : where isso   https://gist.github.com/mshakhomirov/933e6a358e49dcccd4e547a5509c8fda?embedable=true#file-3-sql  Outro exemplo de como   usá-lo com tabelas   .   . Este é um exemplo ruim porque, como os sufixos da tabela correspondente provavelmente são determinados dinamicamente (com base em algo em sua tabela), você  NÃO particionadas Não faça isso será cobrado por uma verificação completa da tabela.   https://gist.github.com/mshakhomirov/1c62d79cd9690140c569cd047b9d491f?embedable=true#file-3-2-sql  Você também pode usá-lo na cláusula   e nas funções   . HAVING AGGREGATE  4. Usando GROUP BY ROLLUP  A função ROLLUP é usada para realizar a agregação em vários níveis. Isso é útil quando você precisa trabalhar com gráficos de dimensão.   A consulta a seguir retorna o gasto total de crédito por dia pelo tipo de transação (is_gift) especificado na cláusula   e também mostra o gasto total para cada dia e o gasto total em todas as datas disponíveis. where   https://gist.github.com/mshakhomirov/4cf738aaad967fe92c4fb7192874fadf?embedable=true#file-4-sql  5. Converta a tabela para JSON  Imagine que você precise converter sua tabela em um objeto JSON, onde cada registro é um elemento do array aninhado. É aqui que a função   se torna útil: to_json_string()   https://gist.github.com/mshakhomirov/aac1f93312ae305ba80c915fe4a2a386?embedable=true#file-5-sql  Então você pode usá-lo em qualquer lugar: datas, funis de marketing, índices, gráficos de histograma, etc.  6. Usando PARTITION BY  Dadas as colunas   ,   e   . Para CADA data, como você mostra o valor total da receita para CADA cliente enquanto mantém todas as linhas? Você pode conseguir isso assim: user_id date total_cost   https://gist.github.com/mshakhomirov/e4f11721eb5a3182150df08f25b70d64?embedable=true#file-6-sql  7. Média móvel  Muitas vezes, os desenvolvedores de BI têm a tarefa de adicionar uma média móvel a seus relatórios e painéis fantásticos. Isso pode ser um gráfico de linha MA de 7, 14, 30 dias/mês ou mesmo ano. Então, como fazemos isso?   https://gist.github.com/mshakhomirov/ebf5488d0036bc9b84ae05889346d986?embedable=true#file-7-sql  8. Matrizes de data  Torna-se muito útil quando você trabalha com   ou deseja verificar algum conjunto de dados em busca de valores ausentes, ou seja, datas. O BigQuery tem uma função chamada   : retenção de usuários GENERATE_DATE_ARRAY   https://gist.github.com/mshakhomirov/2ba5a67053f85794462dab98e56ad74d?embedable=true#file-8-sql  9. Row_number ()  Isso é útil para obter algo mais recente de seus dados, ou seja, registro atualizado mais recente, etc. ou até mesmo para remover duplicatas:   https://gist.github.com/mshakhomirov/05d0c04c5975207d98552ffd436add8b?embedable=true#file-9-sql  10. NTIL()  Outra função de numeração. Realmente útil para monitorar coisas como   se você tiver um aplicativo móvel. Por exemplo, tenho meu aplicativo conectado ao Firebase e, quando os usuários   , posso ver quanto tempo demorou para eles.  Login duration in seconds login  Essa função divide as linhas em depósitos   com base na ordem das linhas e retorna o número do depósito baseado em 1 atribuído a cada linha. O número de linhas nos intervalos pode diferir em no máximo 1. Os valores restantes (o restante do número de linhas dividido por intervalos) são distribuídos um para cada intervalo, começando com o intervalo 1. Se   for avaliado como NULL, 0 ou negativo, um erro é fornecido. constant_integer_expression constant_integer_expression   https://gist.github.com/mshakhomirov/16fe941aa8c4ed79e4aad8b7049b307a?embedable=true#file-10-sql  11. Classificação / classificação densa  Eles também são chamados de funções   . Costumo usar     , pois não pula a próxima classificação disponível, enquanto   o faria. Ele retorna valores de classificação consecutivos. Você pode usá-lo com uma partição que divide os resultados em baldes distintos. As linhas em cada partição recebem as mesmas classificações se tiverem os mesmos valores.  de numeração DENSE_RANK como função de classificação padrão RANK Exemplo:   https://gist.github.com/mshakhomirov/459b68c5f3d1e8284c01e516db1d8dcb?embedable=true#file-11-1-sql   Outro exemplo com preços de produtos:   https://gist.github.com/mshakhomirov/4c90a6fc8516d8264e172676a83a1048?embedable=true#file-11-2-sql  12. Pivot / não pivot  Pivot transforma linhas em colunas. É tudo o que faz. Unpivot faz o   . oposto   https://gist.github.com/mshakhomirov/f90b035ba259e672d4d51a669e0cd1fc?embedable=true#file-12-sql  13. Primeiro_valor / último_valor  Essa é outra função útil que ajuda a obter um delta para cada linha em relação ao primeiro/último valor nessa partição específica.   https://gist.github.com/mshakhomirov/ea4de9144b97bf8c196cab07609c309e?embedable=true#file-13-sql  14. Converter uma tabela em Array de structs e passá-los para UDF  Isso é útil quando você precisa aplicar uma função definida pelo usuário (UDF) com alguma lógica complexa para cada linha ou tabela. Você sempre pode considerar sua tabela como um array de objetos TYPE STRUCT e então passar cada um deles para UDF. Depende da sua lógica. Por exemplo, eu o uso para calcular os tempos de expiração da compra:   https://gist.github.com/mshakhomirov/35d956fa9db86b12b44ab62c00f42a40?embedable=true#file-14-sql  De maneira semelhante, você pode criar tabelas sem a necessidade de usar   . Por exemplo, eu o uso para zombar de alguns dados de teste para testes de unidade. Desta forma, você pode fazer isso muito rápido apenas usando   +   +   em seu editor. UNION ALL Alt Shift Down   https://gist.github.com/mshakhomirov/6ea226c1b5b789d4a31691ce065c20d7?embedable=true#file-14-2-sql  15. Criando funis de eventos usando FOLLOWING E UNBOUNDED FOLLOWING  Um bom exemplo pode ser funis de marketing. Seu conjunto de dados pode conter eventos repetidos continuamente do mesmo tipo, mas, idealmente, você deseja encadear cada evento com o próximo de um tipo diferente. Isso pode ser útil quando você precisa obter uma lista de algo, por exemplo, eventos, compras etc. para criar um conjunto de dados de funis. Trabalhar com PARTITION BY dá a você a oportunidade de agrupar todos os eventos a seguir, não importa quantos deles existam em cada partição.   https://gist.github.com/mshakhomirov/05fd7d79d8acf3b173181a5d950ab6e7?embedable=true#file-15-sql  16. Regexp  Você deve usá-lo se precisar extrair algo de dados não estruturados, ou seja, taxas de câmbio, agrupamentos personalizados, etc.  Trabalhando com taxas de câmbio usando regexp  Considere este exemplo com dados de taxas de câmbio:   https://gist.github.com/mshakhomirov/9ca6e153da19c491034bd57995875308?embedable=true#file-16-1-sql  Trabalhando com versões de aplicativos usando regexp  Às vezes, você pode querer usar   para obter versões   ,   ou   para seu aplicativo e criar um relatório personalizado: regexp principais de lançamento mod   https://gist.github.com/mshakhomirov/b1f442a296ffef52c7baa1245e1dc316?embedable=true#file-16-2-sql  Conclusão  SQL é uma ferramenta poderosa que ajuda a manipular dados. Espero que esses casos de uso de SQL do marketing digital sejam úteis para você. É realmente uma habilidade útil e pode ajudá-lo em muitos projetos. Esses trechos de SQL facilitaram muito minha vida e eu uso no trabalho quase todos os dias. Além disso, o SQL e os data warehouses modernos são ferramentas essenciais para a ciência de dados. Seus recursos robustos de dialeto permitem modelar e visualizar dados com facilidade. Como o SQL é a linguagem que os data warehouses e os profissionais de inteligência de negócios usam, é uma excelente opção se você deseja compartilhar dados com eles. É a maneira mais comum de se comunicar com quase todas as soluções de data warehouse/lake do mercado.  Originalmente publicado em   por  mydataschool.com datamike  Mike é um indivíduo apaixonado e focado digitalmente com uma abundância de motivação e entusiasmo, amando os desafios que toda a combinação de marketing digital apresenta. Mora no Reino Unido, concluiu MBA pela Newcastle University em 2015.

Read me on Medium.com

Read My Stories

Este áudio é produzido no idioma original da história!

Muito longo; Para ler

Make resilience your competitive advantage

16 técnicas de SQL que todo iniciante precisa saber

About Author

COMENTARIOS

Rótulos

ESTE ARTIGO FOI APRESENTADO EM

Related Stories

Modelo Bitcoin UTXO, alimentando um ecossistema único

Crescimento de Criptomoedas: Criando Personas de Usuários Eficazes

Guia do arquiteto para construir arquitetura de referência para um Datalake de IA/ML

Telegram: a ponte da Crypto Island para o continente

Modelo Bitcoin UTXO, alimentando um ecossistema único

Crescimento de Criptomoedas: Criando Personas de Usuários Eficazes

Guia do arquiteto para construir arquitetura de referência para um Datalake de IA/ML

Telegram: a ponte da Crypto Island para o continente

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps