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

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

Muito longo; Para ler

Making security fun one episode at a time

16 técnicas de SQL que todo iniciante precisa saber

Muito longo; Para ler

Mike Shakhomirov

About Author

Rótulos

Languages

ESTE ARTIGO FOI APRESENTADO EM...

16 técnicas de SQL que todo iniciante precisa saber

Muito longo; Para ler

Mike Shakhomirov

About Author

Rótulos

Languages

ESTE ARTIGO FOI APRESENTADO EM...

HISTÓRIAS RELACIONADAS