Visão geral dos principais recursos do Google BigQuery — pratique escrever solicitações para análise de marketing
Publicados: 2022-04-12Quanto mais informações uma empresa acumula, mais aguda é a questão de onde armazená-las. Se você não tiver a capacidade ou o desejo de manter seus próprios servidores, o Google BigQuery (GBQ) pode ajudar. O BigQuery oferece armazenamento rápido, econômico e escalável para trabalhar com big data e permite que você escreva consultas usando sintaxe semelhante a SQL, bem como funções padrão e definidas pelo usuário.
Neste artigo, analisamos as principais funções do BigQuery e mostramos suas possibilidades usando exemplos específicos. Você aprenderá a escrever consultas básicas e testá-las em dados de demonstração.
Crie relatórios sobre dados GBQ sem treinamento técnico ou conhecimento de SQL.
Você precisa regularmente de relatórios sobre campanhas publicitárias, mas não tem tempo para estudar SQL ou esperar uma resposta de seus analistas? Com o OWOX BI, você pode criar relatórios sem precisar entender como seus dados estão estruturados. Basta selecionar os parâmetros e métricas que você deseja ver em seu relatório de Smart Data. OWOX BI Smart Data visualizará instantaneamente seus dados de uma maneira que você possa entender.
Índice
- O que é SQL e quais dialetos são compatíveis com o BigQuery
- Onde começar
- Recursos do Google BigQuery
- Funções agregadas
- Funções de data
- Funções de string
- Funções da janela
- Conclusões
O que é SQL e quais dialetos são compatíveis com o BigQuery
A Linguagem de Consulta Estruturada (SQL) permite recuperar dados, adicionar dados e modificar dados em grandes arrays. O Google BigQuery oferece suporte a dois dialetos SQL: SQL padrão e o SQL legado desatualizado.
Qual dialeto escolher depende de suas preferências, mas o Google recomenda usar o SQL padrão para estes benefícios:
- Flexibilidade e funcionalidade para campos aninhados e repetitivos
- Suporte para as linguagens DML e DDL, permitindo alterar dados em tabelas, bem como gerenciar tabelas e visualizações no GBQ
- Processamento mais rápido de grandes quantidades de dados em comparação com o Legacy SQL
- Suporte para todas as atualizações futuras do BigQuery
Você pode saber mais sobre as diferenças de dialeto na documentação do BigQuery.
Veja também: Quais são as vantagens do novo dialeto SQL padrão do Google BigQuery sobre o SQL legado e quais tarefas de negócios você pode resolver com ele?
Por padrão, as consultas do Google BigQuery são executadas no SQL legado.
Você pode alternar para o SQL padrão de várias maneiras:
- Na interface do BigQuery, na janela de edição de consulta, selecione Mostrar opções e remova a marca de seleção ao lado de Usar SQL legado :
- Antes de consultar, adicione a linha #standardSQL e inicie sua consulta com uma nova linha:
Onde começar
Para que você possa praticar e executar consultas conosco, preparamos uma tabela com dados de demonstração. Preencha o formulário abaixo e enviaremos por e-mail para você.
Dados de demonstração para prática de consultas SQL
Baixe Para começar, faça o download da tabela de dados de demonstração e envie-a para seu projeto do Google BigQuery. A maneira mais fácil de fazer isso é com o complemento OWOX BI BigQuery Reports.
- Abra o Planilhas Google e instale o complemento OWOX BI BigQuery Reports.
- Abra a tabela que você baixou que contém dados de demonstração e selecione OWOX BI BigQuery Reports –> Upload Data to BigQuery :
- Na janela que se abre, escolha seu projeto do Google BigQuery, um conjunto de dados, e pense em um nome para a tabela na qual os dados carregados serão armazenados.
- Especifique um formato para os dados carregados (como mostrado na captura de tela):
Se você não tiver um projeto no Google BigQuery, crie um. Para fazer isso, você precisará de uma conta de faturamento ativa no Google Cloud Platform. Não se assuste com a necessidade de vincular um cartão bancário: você não será cobrado sem o seu conhecimento. Além disso, ao se registrar, você receberá US$ 300 por 12 meses que poderá gastar em armazenamento e processamento de dados.
OWOX BI ajuda você a combinar dados de diferentes sistemas no BigQuery: dados sobre ações do usuário em seu site, chamadas, pedidos de seu CRM, e-mails, custos de publicidade. Você pode usar o OWOX BI para personalizar análises avançadas e automatizar relatórios de qualquer complexidade.
Antes de falar sobre os recursos do Google BigQuery, vamos lembrar como são as consultas básicas nos dialetos SQL legado e SQL padrão:
| Inquerir | SQL legado | SQL padrão |
|---|---|---|
| Selecione os campos da tabela | SELECIONAR campo 1, campo2 | SELECIONAR campo 1, campo2 |
| Selecione uma tabela para escolher os campos | FROM [projectID:dataSet.tableName] | DE `projectID.dataSet.tableName` |
| Selecione o parâmetro pelo qual filtrar os valores | WHERE campo 1 = valor | WHERE campo 1 = valor |
| Selecione os campos pelos quais agrupar os resultados | GROUP BY campo 1, campo2 | GROUP BY campo 1, campo2 |
| Selecione como ordenar os resultados | ORDER BY campo 1 ASC (ascendente) ou DESC (descendente) | ORDER BY campo 1 ASC (ascendente) ou DESC (descendente) |
Recursos do Google BigQuery
Ao criar consultas, você usará funções de agregação, data, string e janela com mais frequência. Vamos dar uma olhada em cada um desses grupos de funções.
Veja também: Como começar a trabalhar com armazenamento em nuvem — crie um conjunto de dados e tabelas e configure a importação de dados para o Google BigQuery.
Funções agregadas
As funções agregadas fornecem valores de resumo para uma tabela inteira. Por exemplo, você pode usá-los para calcular o tamanho médio do cheque ou a receita total por mês ou pode usá-los para selecionar o segmento de usuários que fez o número máximo de compras.
Estas são as funções agregadas mais populares:
| SQL legado | SQL padrão | O que a função faz |
|---|---|---|
| AVG(campo) | AVG([DISTINTO] (campo)) | Retorna o valor médio da coluna do campo. No SQL padrão, quando você adiciona uma condição DISTINCT, a média é considerada apenas para linhas com valores exclusivos (não repetidos) na coluna do campo. |
| MAX(campo) | MAX(campo) | Retorna o valor máximo da coluna do campo. |
| MIN(campo) | MIN(campo) | Retorna o valor mínimo da coluna do campo. |
| SOMA(campo) | SOMA(campo) | Retorna a soma dos valores da coluna do campo. |
| COUNT(campo) | COUNT(campo) | Retorna o número de linhas na coluna do campo. |
| EXACT_COUNT_DISTINCT(campo) | COUNT([DISTINCT] (campo)) | Retorna o número de linhas exclusivas na coluna de campo. |
Para obter uma lista de todas as funções agregadas, consulte a documentação Legacy SQL and Standard SQL.
Vejamos os dados de demonstração para ver como essas funções funcionam. Podemos calcular a receita média das transações, compras para os valores mais altos e mais baixos, receita total, total de transações e o número de transações únicas (para verificar se as compras foram duplicadas). Para fazer isso, escreveremos uma consulta na qual especificamos o nome do nosso projeto do Google BigQuery, o conjunto de dados e a tabela.
#SQL legado
SELECT AVG(revenue) as average_revenue, MAX(revenue) as max_revenue, MIN(revenue) as min_revenue, SUM(revenue) as whole_revenue, COUNT(transactionId) as transactions, EXACT_COUNT_DISTINCT(transactionId) as unique_transactions FROM [owox-analytics:t_kravchenko.Demo_data]
SELECT AVG(revenue) as average_revenue, MAX(revenue) as max_revenue, MIN(revenue) as min_revenue, SUM(revenue) as whole_revenue, COUNT(transactionId) as transactions, EXACT_COUNT_DISTINCT(transactionId) as unique_transactions FROM [owox-analytics:t_kravchenko.Demo_data]#SQL padrão
SELECT AVG(revenue) as average_revenue, MAX(revenue) as max_revenue, MIN(revenue) as min_revenue, SUM(revenue) as whole_revenue, COUNT(transactionId) as transactions, COUNT(DISTINCT(transactionId)) as unique_transactions FROM `owox-analytics.t_kravchenko.Demo_data`
SELECT AVG(revenue) as average_revenue, MAX(revenue) as max_revenue, MIN(revenue) as min_revenue, SUM(revenue) as whole_revenue, COUNT(transactionId) as transactions, COUNT(DISTINCT(transactionId)) as unique_transactions FROM `owox-analytics.t_kravchenko.Demo_data`Como resultado, teremos o seguinte:
Você pode verificar os resultados desses cálculos na tabela original com dados de demonstração usando as funções padrão do Planilhas Google (SUM, AVG e outras) ou usando tabelas dinâmicas.
Como você pode ver na captura de tela acima, o número de transações e transações únicas é diferente. Isso sugere que há duas transações em nossa tabela com o mesmo transactionId:
Se você estiver interessado em transações exclusivas, use uma função que conte strings exclusivas. Como alternativa, você pode agrupar dados usando a função GROUP BY para se livrar de duplicatas antes de aplicar a função de agregação.
Dados de demonstração para prática de consultas SQL
BaixeFunções de data
Essas funções permitem processar datas: alterar seu formato, selecionar o campo necessário (dia, mês ou ano) ou deslocar a data por um determinado intervalo.
Eles podem ser úteis quando:
- converter datas e horas de diferentes fontes em um único formato para configurar análises avançadas
- criar relatórios atualizados automaticamente ou enviar correspondências (por exemplo, quando você precisar de dados das últimas duas horas, semana ou mês)
- criar relatórios de coorte nos quais é necessário obter dados por um período de dias, semanas ou meses
Estas são as funções de data mais usadas:
| SQL legado | SQL padrão | Descrição da função |
|---|---|---|
| DATA ATUAL() | DATA ATUAL() | Retorna a data atual no formato % YYYY -% MM-% DD. |
| DATA(timestamp) | DATA(timestamp) | Converte a data do formato % YYYY -% MM-% DD% H:% M:% C. para % YYYY -% MM-% DD. |
| DATE_ADD(timestamp, intervalo, interval_units) | DATE_ADD(timestamp, intervalo INTERVAL interval_units) | Retorna a data do carimbo de data/hora, aumentando-a pelo intervalo especificado interval.interval_units. No Legacy SQL, pode levar os valores YEAR, MONTH, DAY, HOUR, MINUTE e SECOND, e no SQL padrão pode levar YEAR, QUARTER, MONTH, SEMANA e DIA. |
| DATE_ADD(timestamp, - intervalo, interval_units) | DATE_SUB(timestamp, intervalo INTERVAL interval_units) | Retorna a data do carimbo de data/hora, diminuindo-a pelo intervalo especificado. |
| DATEDIFF(timestamp1, timestamp2) | DATE_DIFF(timestamp1, timestamp2, date_part) | Retorna a diferença entre as datas timestamp1 e timestamp2. Em Legacy SQL, retorna a diferença em dias e em Standard SQL, retorna a diferença dependendo do valor date_part especificado (dia, semana, mês, trimestre, ano). |
| DIA(timestamp) | EXTRACT(DAY FROM timestamp) | Retorna o dia a partir da data do carimbo de data/hora. Aceita valores de 1 a 31 inclusive. |
| MONTH(timestamp) | EXTRACT(MONTH FROM timestamp) | Retorna o número de sequência do mês a partir da data do carimbo de data/hora. Aceita valores de 1 a 12 inclusive. |
| ANO(timestamp) | EXTRACT(YEAR FROM timestamp) | Retorna o ano a partir da data do carimbo de data/hora. |
Para obter uma lista de todas as funções de data, consulte a documentação Legacy SQL and Standard SQL.
Vamos dar uma olhada em nossos dados de demonstração para ver como cada uma dessas funções funciona. Por exemplo, obteremos a data atual, transformaremos a data da tabela original no formato % YYYY -% MM-% DD, retiraremos e adicionaremos um dia a ela. Em seguida, calcularemos a diferença entre a data atual e a data da tabela de origem e dividiremos a data atual em campos separados de ano, mês e dia. Para fazer isso, você pode copiar as consultas de exemplo abaixo e substituir o nome do projeto, o conjunto de dados e a tabela de dados pelos seus próprios.
#SQL legado
SELECT CURRENT_DATE() AS today, DATE( date_UTC ) AS date_UTC_in_YYYYMMDD, DATE_ADD( date_UTC,1, 'DAY') AS date_UTC_plus_one_day, DATE_ADD( date_UTC,-1, 'DAY') AS date_UTC_minus_one_day, DATEDIFF(CURRENT_DATE(), date_UTC ) AS difference_between_date, DAY( CURRENT_DATE() ) AS the_day, MONTH( CURRENT_DATE()) AS the_month, YEAR( CURRENT_DATE()) AS the_year FROM [owox-analytics:t_kravchenko.Demo_data]
SELECT CURRENT_DATE() AS today, DATE( date_UTC ) AS date_UTC_in_YYYYMMDD, DATE_ADD( date_UTC,1, 'DAY') AS date_UTC_plus_one_day, DATE_ADD( date_UTC,-1, 'DAY') AS date_UTC_minus_one_day, DATEDIFF(CURRENT_DATE(), date_UTC ) AS difference_between_date, DAY( CURRENT_DATE() ) AS the_day, MONTH( CURRENT_DATE()) AS the_month, YEAR( CURRENT_DATE()) AS the_year FROM [owox-analytics:t_kravchenko.Demo_data]#SQL padrão
SELECT today, date_UTC_in_YYYYMMDD, DATE_ADD( date_UTC_in_YYYYMMDD, INTERVAL 1 DAY) AS date_UTC_plus_one_day, DATE_SUB( date_UTC_in_YYYYMMDD,INTERVAL 1 DAY) AS date_UTC_minus_one_day, DATE_DIFF(today, date_UTC_in_YYYYMMDD, DAY) AS difference_between_date, EXTRACT(DAY FROM today ) AS the_day, EXTRACT(MONTH FROM today ) AS the_month, EXTRACT(YEAR FROM today ) AS the_year FROM ( SELECT CURRENT_DATE() AS today, DATE( date_UTC ) AS date_UTC_in_YYYYMMDD FROM `owox-analytics.t_kravchenko.Demo_data`)
SELECT today, date_UTC_in_YYYYMMDD, DATE_ADD( date_UTC_in_YYYYMMDD, INTERVAL 1 DAY) AS date_UTC_plus_one_day, DATE_SUB( date_UTC_in_YYYYMMDD,INTERVAL 1 DAY) AS date_UTC_minus_one_day, DATE_DIFF(today, date_UTC_in_YYYYMMDD, DAY) AS difference_between_date, EXTRACT(DAY FROM today ) AS the_day, EXTRACT(MONTH FROM today ) AS the_month, EXTRACT(YEAR FROM today ) AS the_year FROM ( SELECT CURRENT_DATE() AS today, DATE( date_UTC ) AS date_UTC_in_YYYYMMDD FROM `owox-analytics.t_kravchenko.Demo_data`)Depois de executar a consulta, você receberá este relatório:
Veja também: Exemplos de relatórios que podem ser criados usando consultas SQL em dados no Google BigQuery e quais métricas exclusivas você pode complementar os dados do Google Analytics com OWOX BI.
Funções de string
As funções de string permitem que você gere uma string, selecione e substitua substrings e calcule o comprimento de uma string e a sequência de índice da substring na string original. Por exemplo, com funções de string, você pode:
- filtrar um relatório com tags UTM que são passadas para o URL da página
- trazer dados para um único formato se os nomes da fonte e da campanha forem escritos em registros diferentes
- substituir dados incorretos em um relatório (por exemplo, se o nome da campanha for impresso incorretamente)
Estas são as funções mais populares para trabalhar com strings:
| SQL legado | SQL padrão | Descrição da função |
|---|---|---|
| CONCAT('str1', 'str2') ou 'str1'+ 'str2' | CONCAT('str1', 'str2') | Concatena 'str1' e 'str2' em uma string. |
| 'str1' CONTÉM 'str2' | REGEXP_CONTAINS('str1', 'str2') ou 'str1' LIKE '%str2%' | Retorna true se a string 'str1' contiver a string 'str2.'No SQL padrão, a string 'str2' pode ser escrita como uma expressão regular usando a biblioteca re2 . |
| LENGTH('str') | CHAR_LENGTH('str') ou CHARACTER_LENGTH('str') | Retorna o comprimento da string 'str' (número de caracteres). |
| SUBSTR('str', índice [, max_len]) | SUBSTR('str', índice [, max_len]) | Retorna uma substring de comprimento max_len começando com um caractere de índice da string 'str'. |
| LOWER('str') | LOWER('str') | Converte todos os caracteres da string 'str para minúsculas. |
| SUPERIOR(str) | SUPERIOR(str) | Converte todos os caracteres na string 'str' para maiúsculas. |
| INSTR('str1', 'str2') | STRPOS('str1', 'str2') | Retorna o índice da primeira ocorrência da string 'str2' para a string 'str1'; caso contrário, retorna 0. |
| REPLACE('str1', 'str2', 'str3') | REPLACE('str1', 'str2', 'str3') | Substitui 'str1' por 'str2' por 'str3'. |
Você pode aprender mais sobre todas as funções de string na documentação Legacy SQL e Standard SQL.
Vejamos os dados de demonstração para ver como usar as funções descritas. Suponha que temos três colunas separadas que contêm valores de dia, mês e ano:
Trabalhar com uma data neste formato não é muito conveniente, então podemos combinar os valores em uma coluna. Para fazer isso, use as consultas SQL abaixo e lembre-se de substituir o nome do seu projeto, conjunto de dados e tabela no Google BigQuery.
#SQL legado
SELECT CONCAT(the_day,'-',the_month,'-',the_year) AS mix_string1 FROM ( SELECT 31 AS the_day, 12 AS the_month, 2018 AS the_year FROM [owox-analytics:t_kravchenko.Demo_data]) GROUP BY mix_string1
SELECT CONCAT(the_day,'-',the_month,'-',the_year) AS mix_string1 FROM ( SELECT 31 AS the_day, 12 AS the_month, 2018 AS the_year FROM [owox-analytics:t_kravchenko.Demo_data]) GROUP BY mix_string1#SQL padrão
SELECT CONCAT(the_day,'-',the_month,'-',the_year) AS mix_string1 FROM ( SELECT 31 AS the_day, 12 AS the_month, 2018 AS the_year FROM owox-analytics.t_kravchenko.Demo_data) GROUP BY mix_string1
SELECT CONCAT(the_day,'-',the_month,'-',the_year) AS mix_string1 FROM ( SELECT 31 AS the_day, 12 AS the_month, 2018 AS the_year FROM owox-analytics.t_kravchenko.Demo_data) GROUP BY mix_string1Após executar a consulta, recebemos a data em uma coluna:
Muitas vezes, quando você baixa uma página em um site, a URL registra os valores das variáveis que o usuário escolheu. Pode ser uma forma de pagamento ou entrega, número da transação, índice da loja física em que o comprador deseja retirar o item, etc. Usando uma consulta SQL, você pode selecionar esses parâmetros a partir do endereço da página. Considere dois exemplos de como e por que você pode fazer isso.
Exemplo 1 . Suponha que queremos saber o número de compras em que os usuários retiram mercadorias das lojas físicas. Para fazer isso, precisamos calcular o número de transações enviadas de páginas na URL que contêm uma substring shop_id (um índice para uma loja física). Podemos fazer isso com as seguintes consultas:
#SQL legado
SELECT COUNT(transactionId) AS transactions, check FROM ( SELECT transactionId, page CONTAINS 'shop_id' AS check FROM [owox-analytics:t_kravchenko.Demo_data]) GROUP BY check
SELECT COUNT(transactionId) AS transactions, check FROM ( SELECT transactionId, page CONTAINS 'shop_id' AS check FROM [owox-analytics:t_kravchenko.Demo_data]) GROUP BY check#SQL padrão
SELECT COUNT(transactionId) AS transactions, check1, check2 FROM ( SELECT transactionId, REGEXP_CONTAINS( page, 'shop_id') AS check1, page LIKE '%shop_id%' AS check2 FROM `owox-analytics.t_kravchenko.Demo_data`) GROUP BY check1, check2
SELECT COUNT(transactionId) AS transactions, check1, check2 FROM ( SELECT transactionId, REGEXP_CONTAINS( page, 'shop_id') AS check1, page LIKE '%shop_id%' AS check2 FROM `owox-analytics.t_kravchenko.Demo_data`) GROUP BY check1, check2Da tabela resultante, vemos que 5502 transações (check = true) foram enviadas de páginas contendo shop_id:
Exemplo 2 . Você atribuiu um delivery_id a cada método de entrega e especifica o valor desse parâmetro no URL da página. Para descobrir qual método de entrega o usuário escolheu, você precisa selecionar o delivery_id em uma coluna separada.
Podemos usar as seguintes consultas para isso:
#SQL legado
SELECT page_lower_case, page_length, index_of_delivery_id, selected_delivery_id, REPLACE(selected_delivery_id, 'selected_delivery_id=', '') as delivery_id FROM ( SELECT page_lower_case, page_length, index_of_delivery_id, SUBSTR(page_lower_case, index_of_delivery_id) AS selected_delivery_id FROM ( SELECT page_lower_case, LENGTH(page_lower_case) AS page_length, INSTR(page_lower_case, 'selected_delivery_id') AS index_of_delivery_id FROM ( SELECT LOWER( page) AS page_lower_case, UPPER( page) AS page_upper_case FROM [owox-analytics:t_kravchenko.Demo_data]))) ORDER BY page_lower_case ASC
SELECT page_lower_case, page_length, index_of_delivery_id, selected_delivery_id, REPLACE(selected_delivery_id, 'selected_delivery_id=', '') as delivery_id FROM ( SELECT page_lower_case, page_length, index_of_delivery_id, SUBSTR(page_lower_case, index_of_delivery_id) AS selected_delivery_id FROM ( SELECT page_lower_case, LENGTH(page_lower_case) AS page_length, INSTR(page_lower_case, 'selected_delivery_id') AS index_of_delivery_id FROM ( SELECT LOWER( page) AS page_lower_case, UPPER( page) AS page_upper_case FROM [owox-analytics:t_kravchenko.Demo_data]))) ORDER BY page_lower_case ASC#SQL padrão
SELECT page_lower_case, page_length, index_of_delivery_id, selected_delivery_id, REPLACE(selected_delivery_id, 'selected_delivery_id=', '') AS delivery_id FROM ( SELECT page_lower_case, page_length, index_of_delivery_id, SUBSTR(page_lower_case, index_of_delivery_id) AS selected_delivery_id FROM ( SELECT page_lower_case, CHAR_LENGTH(page_lower_case) AS page_length, STRPOS(page_lower_case, 'selected_delivery_id') AS index_of_delivery_id FROM ( SELECT LOWER( page) AS page_lower_case, UPPER( page) AS page_upper_case FROM `owox-analytics.t_kravchenko.Demo_data`))) ORDER BY page_lower_case ASC
SELECT page_lower_case, page_length, index_of_delivery_id, selected_delivery_id, REPLACE(selected_delivery_id, 'selected_delivery_id=', '') AS delivery_id FROM ( SELECT page_lower_case, page_length, index_of_delivery_id, SUBSTR(page_lower_case, index_of_delivery_id) AS selected_delivery_id FROM ( SELECT page_lower_case, CHAR_LENGTH(page_lower_case) AS page_length, STRPOS(page_lower_case, 'selected_delivery_id') AS index_of_delivery_id FROM ( SELECT LOWER( page) AS page_lower_case, UPPER( page) AS page_upper_case FROM `owox-analytics.t_kravchenko.Demo_data`))) ORDER BY page_lower_case ASCComo resultado, obtemos uma tabela como esta no Google BigQuery:
Dados de demonstração para prática de consultas SQL
BaixeFunções da janela
Essas funções são semelhantes às funções agregadas que discutimos acima. A principal diferença é que as funções de janela não realizam cálculos em todo o conjunto de dados selecionados usando a consulta, mas apenas em parte desses dados — um subconjunto ou janela .
Usando funções de janela, você pode agregar dados em uma seção de grupo sem usar a função JOIN para combinar várias consultas. Por exemplo, você pode calcular a receita média por campanha publicitária ou o número de transações por dispositivo. Ao adicionar outro campo ao relatório, você pode descobrir facilmente, por exemplo, a participação da receita de uma campanha publicitária na Black Friday ou a participação das transações feitas a partir de um aplicativo móvel.
Junto com cada função na consulta, você deve soletrar a expressão OVER que define os limites da janela. O OVER contém três componentes com os quais você pode trabalhar:
- PARTITION BY — Define a característica pela qual você divide os dados originais em subconjuntos, como clientId ou DayTime
- ORDER BY — Define a ordem das linhas em um subconjunto, como hora DESC
- WINDOW FRAME — Permite processar linhas dentro de um subconjunto de um recurso específico (por exemplo, apenas as cinco linhas antes da linha atual)
Nesta tabela, coletamos as funções de janela usadas com mais frequência:
| SQL legado | SQL padrão | Descrição da função |
|---|---|---|
| AVG(campo) COUNT(campo) COUNT(campo DISTINTO) MAX() MIN() SOMA() | AVG([DISTINTO] (campo)) COUNT(campo) COUNT([DISTINCT] (campo)) MAX(campo) MIN(campo) SOMA(campo) | Retorna o valor médio, numérico, máximo, mínimo e total da coluna de campo no subconjunto selecionado. DISTINCT é usado para calcular apenas valores exclusivos (não repetidos). |
| DENSE_RANK() | DENSE_RANK() | Retorna o número da linha em um subconjunto. |
| FIRST_VALUE(campo) | FIRST_VALUE (campo[{RESPECT | IGNORE} NULLS]) | Retorna o valor da primeira linha da coluna de campo em um subconjunto. Por padrão, as linhas com valores vazios da coluna do campo são incluídas no cálculo. RESPECT ou IGNORE NULLS especifica se as strings NULL devem ser incluídas ou ignoradas. |
| LAST_VALUE(campo) | LAST_VALUE (campo [{RESPECT | IGNORE} NULLS]) | Retorna o valor da última linha em um subconjunto da coluna do campo. Por padrão, as linhas com valores vazios na coluna do campo são incluídas no cálculo. RESPECT ou IGNORE NULLS especifica se as strings NULL devem ser incluídas ou ignoradas. |
| LAG(campo) | LAG (campo[, deslocamento [, expressão_padrão]]) | Retorna o valor da linha anterior em relação à coluna do campo atual dentro do subconjunto. Offset é um número inteiro que especifica o número de linhas a serem deslocadas da linha atual. Default_expression é o valor que a função retornará se não houver nenhum string dentro do subconjunto. |
| LEAD(campo) | LEAD (campo[, deslocamento [, expressão_padrão]]) | Retorna o valor da próxima linha em relação à coluna de campo atual dentro do subconjunto. Offset é um número inteiro que define o número de linhas que você deseja mover para cima em relação à linha atual. Default_expression é o valor que a função retornará se não houver nenhuma string obrigatória no subconjunto atual. |
Você pode ver uma lista de todas as funções analíticas agregadas e funções de navegação na documentação para Legacy SQL e Standard SQL.
Exemplo 1 . Digamos que queremos analisar a atividade dos clientes durante o horário comercial e não comercial. Para fazer isso, precisamos dividir as transações em dois grupos e calcular as métricas de interesse:
- Grupo 1 — Compras em horário comercial das 9h00 às 18h00
- Grupo 2 — Compras fora do expediente das 00:00 às 9:00 e das 18:00 às 23:59
Além das horas de trabalho e de folga, outra variável para formar uma janela é clientId. Ou seja, para cada usuário, teremos duas janelas:
| janela | ID do Cliente | Dia |
|---|---|---|
| janela 1 | ID do cliente 1 | jornada de trabalho |
| janela 2 | ID do cliente 2 | horas de folga |
| janela 3 | ID do cliente 3 | jornada de trabalho |
| janela 4 | ID do cliente 4 | horas de folga |
| janela N | ID do cliente N | jornada de trabalho |
| janela N+1 | clientId N+1 | horas de folga |
Vamos usar dados de demonstração para calcular a receita média, máxima, mínima e total, o número total de transações e o número de transações exclusivas por usuário durante o horário comercial e não comercial. As solicitações abaixo nos ajudarão a fazer isso.
#SQL legado
SELECT date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions FROM ( SELECT date, clientId, DayTime, AVG(revenue) OVER (PARTITION BY date, clientId, DayTime) AS avg_revenue, MAX(revenue) OVER (PARTITION BY date, clientId, DayTime) AS max_revenue, MIN(revenue) OVER (PARTITION BY date, clientId, DayTime) AS min_revenue, SUM(revenue) OVER (PARTITION BY date, clientId, DayTime) AS sum_revenue, COUNT(transactionId) OVER (PARTITION BY date, clientId, DayTime) AS transactions, COUNT(DISTINCT(transactionId)) OVER (PARTITION BY date, clientId, DayTime) AS unique_transactions FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM [owox-analytics:t_kravchenko.Demo_data])) GROUP BY date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions ORDER BY transactions DESC
SELECT date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions FROM ( SELECT date, clientId, DayTime, AVG(revenue) OVER (PARTITION BY date, clientId, DayTime) AS avg_revenue, MAX(revenue) OVER (PARTITION BY date, clientId, DayTime) AS max_revenue, MIN(revenue) OVER (PARTITION BY date, clientId, DayTime) AS min_revenue, SUM(revenue) OVER (PARTITION BY date, clientId, DayTime) AS sum_revenue, COUNT(transactionId) OVER (PARTITION BY date, clientId, DayTime) AS transactions, COUNT(DISTINCT(transactionId)) OVER (PARTITION BY date, clientId, DayTime) AS unique_transactions FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM [owox-analytics:t_kravchenko.Demo_data])) GROUP BY date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions ORDER BY transactions DESC#SQL padrão
#standardSQL SELECT date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions FROM ( SELECT date, clientId, DayTime, AVG(revenue) OVER (PARTITION BY date, clientId, DayTime) AS avg_revenue, MAX(revenue) OVER (PARTITION BY date, clientId, DayTime) AS max_revenue, MIN(revenue) OVER (PARTITION BY date, clientId, DayTime) AS min_revenue, SUM(revenue) OVER (PARTITION BY date, clientId, DayTime) AS sum_revenue, COUNT(transactionId) OVER (PARTITION BY date, clientId, DayTime) AS transactions, COUNT(DISTINCT(transactionId)) OVER (PARTITION BY date, clientId, DayTime) AS unique_transactions FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM `owox-analytics.t_kravchenko.Demo_data`)) GROUP BY date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions ORDER BY transactions DESC
#standardSQL SELECT date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions FROM ( SELECT date, clientId, DayTime, AVG(revenue) OVER (PARTITION BY date, clientId, DayTime) AS avg_revenue, MAX(revenue) OVER (PARTITION BY date, clientId, DayTime) AS max_revenue, MIN(revenue) OVER (PARTITION BY date, clientId, DayTime) AS min_revenue, SUM(revenue) OVER (PARTITION BY date, clientId, DayTime) AS sum_revenue, COUNT(transactionId) OVER (PARTITION BY date, clientId, DayTime) AS transactions, COUNT(DISTINCT(transactionId)) OVER (PARTITION BY date, clientId, DayTime) AS unique_transactions FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM `owox-analytics.t_kravchenko.Demo_data`)) GROUP BY date, clientId, DayTime, avg_revenue, max_revenue, min_revenue, sum_revenue, transactions, unique_transactions ORDER BY transactions DESCVamos ver o que acontece como resultado usando o exemplo do usuário com clientId 102041117.1428132012. Na tabela original deste usuário, temos os seguintes dados:
Ao executar a consulta, recebemos um relatório que contém a receita média, mínima, máxima e total desse usuário, bem como o número total de transações do usuário. Como você pode ver na captura de tela abaixo, ambas as transações foram feitas pelo usuário durante o horário de trabalho:
Exemplo 2 . Agora para uma tarefa mais complicada:
- Coloque números de sequência para todas as transações na janela, dependendo do momento de sua execução. Lembre-se de que definimos a janela por usuário e horários de trabalho/não trabalho.
- Relate a receita da transação seguinte/anterior (em relação à atual) dentro da janela.
- Exiba a receita da primeira e da última transação na janela.
Para fazer isso, usaremos as seguintes consultas:
#SQL legado
SELECT date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour FROM ( SELECT date, clientId, DayTime, hour, DENSE_RANK() OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS rank, revenue, LEAD( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lead_revenue, LAG( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lag_revenue, FIRST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS first_revenue_by_hour, LAST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS last_revenue_by_hour FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM [owox-analytics:t_kravchenko.Demo_data])) GROUP BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour ORDER BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour
SELECT date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour FROM ( SELECT date, clientId, DayTime, hour, DENSE_RANK() OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS rank, revenue, LEAD( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lead_revenue, LAG( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lag_revenue, FIRST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS first_revenue_by_hour, LAST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS last_revenue_by_hour FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM [owox-analytics:t_kravchenko.Demo_data])) GROUP BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour ORDER BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour#SQL padrão
SELECT date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour FROM ( SELECT date, clientId, DayTime, hour, DENSE_RANK() OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS rank, revenue, LEAD( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lead_revenue, LAG( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lag_revenue, FIRST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS first_revenue_by_hour, LAST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS last_revenue_by_hour FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM `owox-analytics.t_kravchenko.Demo_data`)) GROUP BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour ORDER BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour
SELECT date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour FROM ( SELECT date, clientId, DayTime, hour, DENSE_RANK() OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS rank, revenue, LEAD( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lead_revenue, LAG( revenue, 1) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS lag_revenue, FIRST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS first_revenue_by_hour, LAST_VALUE(revenue) OVER (PARTITION BY date, clientId, DayTime ORDER BY hour) AS last_revenue_by_hour FROM ( SELECT date, date_UTC, clientId, transactionId, revenue, page, hour, CASE WHEN hour>=9 AND hour<=18 THEN 'working hours' ELSE 'non-working hours' END AS DayTime FROM `owox-analytics.t_kravchenko.Demo_data`)) GROUP BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hour ORDER BY date, clientId, DayTime, hour, rank, revenue, lead_revenue, lag_revenue, first_revenue_by_hour, last_revenue_by_hourPodemos verificar os resultados dos cálculos usando o exemplo de um usuário que já conhecemos: clientId 102041117.1428132012:
Na captura de tela acima, podemos ver que:
- a primeira transação foi às 15:00 e a segunda transação foi às 16:00
- após a transação às 15h, houve uma transação às 16h com receita de 25.066 (coluna lead_revenue)
- antes da transação às 16:00, houve uma transação às 15:00 com receita de 3699 (coluna lag_revenue)
- a primeira transação na janela foi às 15:00 e a receita dessa transação foi de 3699 (coluna first_revenue_by_hour)
- a consulta processa os dados linha por linha, portanto, para a transação em questão, a última transação na janela será ela mesma e os valores nas colunas last_revenue_by_hour e Revenue serão os mesmos
Artigos úteis sobre o Google BigQuery:
- As 6 principais ferramentas de visualização do BigQuery
- Como fazer upload de dados para o Google BigQuery
- Como fazer upload de dados brutos do Google Ads para o Google BigQuery
- Conector do Google Planilhas Google BigQuery
- Automatize relatórios no Planilhas Google usando dados do Google BigQuery
- Automatize relatórios no Google Data Studio com base em dados do Google BigQuery
Se você deseja coletar dados sem amostragem do seu site no Google BigQuery, mas não sabe por onde começar, agende uma demonstração. Vamos falar sobre todas as possibilidades que você tem com BigQuery e OWOX BI.
Nossos clientes
crescer 22% mais rápido
Cresça mais rápido medindo o que funciona melhor em seu marketing
Analise sua eficiência de marketing, encontre as áreas de crescimento, aumente o ROI
Obter demonstraçãoConclusões
Neste artigo, examinamos os grupos de funções mais populares: agregação, data, string e janela. No entanto, o Google BigQuery tem muitas outras funções úteis, incluindo:
- funções de conversão que permitem converter dados para um formato específico
- funções curinga de tabela que permitem acessar várias tabelas em um conjunto de dados
- funções de expressão regular que permitem descrever o modelo de uma consulta de pesquisa e não seu valor exato
Definitivamente, escreveremos sobre essas funções em nosso blog. Enquanto isso, você pode experimentar todas as funções descritas neste artigo usando nossos dados de demonstração.
Dados de demonstração para prática de consultas SQL
Baixe