16 técnicas de SQL que todo iniciante precisa saber

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:

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 sessões com SQL e/ou atualizar conjuntos de dados de forma incremental com apenas uma parte dos dados. transaction_id pode não existir, mas em vez disso você terá que lidar com o modelo de dados onde a chave única depende do último transaction_id (ou carimbo de data/hora) conhecido. Por exemplo, user_id no conjunto de dados last_online depende do carimbo de data/hora da conexão mais recente. Nesse caso, você deseja update os usuários existentes e insert os novos.

MERGE e atualizações incrementais

Você pode usar MERGE 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.

• 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:

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:

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 where , mas você pode fazer isso :

Outro exemplo de como NÃO usá-lo com tabelas particionadas . Não faça isso . Este é um exemplo ruim porque, como os sufixos da tabela correspondente provavelmente são determinados dinamicamente (com base em algo em sua tabela), você será cobrado por uma verificação completa da tabela.

Você também pode usá-lo na cláusula HAVING e nas funções 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 where e também mostra o gasto total para cada dia e o gasto total em todas as datas disponíveis.

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 to_json_string() se torna útil:

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 user_id , date e total_cost . 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:

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?

8. Matrizes de data

Torna-se muito útil quando você trabalha com retenção de usuários ou deseja verificar algum conjunto de dados em busca de valores ausentes, ou seja, datas. O BigQuery tem uma função chamada GENERATE_DATE_ARRAY :

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:

10. NTIL()

Outra função de numeração. Realmente útil para monitorar coisas como Login duration in seconds se você tiver um aplicativo móvel. Por exemplo, tenho meu aplicativo conectado ao Firebase e, quando os usuários login , posso ver quanto tempo demorou para eles.

Essa função divide as linhas em depósitos constant_integer_expression 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 constant_integer_expression for avaliado como NULL, 0 ou negativo, um erro é fornecido.

11. Classificação / classificação densa

Eles também são chamados de funções de numeração . Costumo usar DENSE_RANK como função de classificação padrão , pois não pula a próxima classificação disponível, enquanto RANK 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. Exemplo:

Outro exemplo com preços de produtos:

12. Pivot / não pivot

Pivot transforma linhas em colunas. É tudo o que faz. Unpivot faz o oposto .

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.

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:

De maneira semelhante, você pode criar tabelas sem a necessidade de usar UNION ALL . 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 Alt + Shift + Down em seu editor.

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.

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:

Trabalhando com versões de aplicativos usando regexp

Às vezes, você pode querer usar regexp para obter versões principais , de lançamento ou mod para seu aplicativo e criar um relatório personalizado:

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 mydataschool.com por 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.