Skip to main content

Parâmetros SQL

Parâmetros SQL

Você pode criar templates SQL adicionando variáveis às suas consultas SQL no editor nativo/SQL. Essas variáveis criarão widgets de filtro que você pode usar para alterar o valor da variável na consulta. Você também pode adicionar parâmetros à URL da sua pergunta para definir os valores dos filtros, de modo que, ao carregar a pergunta, esses valores sejam inseridos nas variáveis.

Variáveis

Definindo variáveis

Digitando {{variable_name}} em sua consulta nativa você cria uma variável chamada variable_name.

Field Filters (Filtros de Campo), um tipo especial de filtro, possuem uma sintaxe ligeiramente diferente.

Este exemplo define uma variável do tipo Texto chamada category:


SELECT
  count(*)
FROM
  products
WHERE
  category = {{category}}

O Analytics vai ler a variável e anexar um widget de filtro à consulta, que as pessoas podem usar para alterar o valor inserido na variável cat com aspas. Então, se alguém digitasse “Gizmo” no widget de filtro, a consulta que o Analytics executaria seria:


SELECT
  count(*)
FROM
  products
WHERE
  category = 'Gizmo'

Se você estiver escrevendo uma consulta nativa MongoDB, sua consulta seria mais parecida com esta, com a variável cat definida dentro da cláusula match.


[{ $match: { category: {{cat}} } }]

Definindo variáveis SQL

Para definir uma variável SQL com um valor, você pode:

  • Inserir um valor no widget de filtro e executar novamente a pergunta, ou
  • Adicionar um parâmetro na URL e carregar a página.

Para adicionar um valor na URL, siga esta sintaxe:

?variable_name=value

Por exemplo, para definir a variável {{cat}} em uma pergunta para o valor “Gizmo”, sua URL ficaria algo como:

https://analytics.example.com/question/42-eg-question?cat=Gizmo

Para definir múltiplas variáveis, separe os parâmetros com um e comercial (&):

https://analytics.example.com/question/42-eg-question?cat=Gizmo&maxprice=50

Tipos de variáveis SQL

Quando você define uma variável, o painel lateral Variáveis aparecerá. Você pode definir um tipo para a variável, o que altera o tipo de widget de filtro que o Analytics apresenta.

Existem quatro tipos de variáveis:

  • Texto: uma caixa de entrada simples.
  • Número: uma caixa de entrada simples.
  • Data: um seletor simples de data. Se você quiser um seletor de data mais expressivo, como especificar um intervalo, você deve usar um Field Filter.
  • Field Filter: widgets de filtro diferentes, dependendo do campo mapeado.

O último tipo de variável, Field Filter, é especial; ele permite criar widgets de filtro “inteligentes”, como uma caixa de pesquisa, um menu suspenso de valores ou um seletor dinâmico de datas que permite especificar um intervalo.

Você pode incluir múltiplas variáveis na consulta, e o Analytics irá adicionar múltiplos widgets de filtro à pergunta. Quando tiver múltiplos widgets de filtro, você pode clicar em um widget e arrastá-lo para reorganizar a ordem.

O tipo de variável Field Filter

Definir uma variável como tipo Field Filter permite mapear a variável para um campo em qualquer tabela no banco de dados atual. Field Filters permitem criar um widget de filtro “inteligente” que faz sentido para aquele campo.

Variáveis do tipo Field Filter devem ser usadas dentro de uma cláusula WHERE no SQL, ou uma cláusula $match no MongoDB.

Tipos compatíveis com Field Filter

Field Filters só funcionam com os seguintes tipos de campo:

  • Categoria
  • Nome da Entidade
  • Chave da Entidade
  • Chave Estrangeira
  • Cidade
  • Estado
  • CEP ou Código Postal

O campo também pode ser data ou timestamp, que podem ser deixados como “Sem tipo semântico” em Metadados da Tabela.

Quando você define o Tipo de variável para “Field Filter”, o Analytics apresentará uma opção para definir o Campo para mapear, bem como o Tipo do widget de filtro. As opções disponíveis dependem do tipo do campo. Por exemplo, se você mapear para um campo do tipo Categoria, verá opções para “Categoria” ou Nenhum. Se mapear para um campo de Data, verá opções como Nenhum, Mês e ano, Trimestre e ano, Data única, Intervalo de datas ou Filtro de datas.

Se você não estiver vendo a opção para mostrar um widget de filtro, certifique-se de que o campo mapeado está definido como um dos tipos acima e tente sincronizar manualmente seu banco de dados na seção “Bancos de Dados” do Painel de Administração para forçar o Analytics a escanear e armazenar em cache os valores do campo.

Se quiser mapear um Field Filter para um campo que não está na lista de tipos compatíveis, será necessário que um Admin altere o tipo do campo para aquela coluna. Veja edição de metadados.

Sintaxe do Field Filter

Suponha que você queira criar um Field Filter que filtre a tabela People pelo estado, e que as pessoas possam selecionar múltiplos estados ao mesmo tempo. Segue a consulta:

A sintaxe para Field Filters difere das variáveis do tipo Texto, Número ou Data.


SELECT
  *
FROM
  PEOPLE
WHERE
  {{state}}

Então, no painel lateral, selecione o tipo da variável “Field Filter” e escolha qual campo mapear sua variável (neste caso, State).

Note a ausência da coluna e do operador (como =). A razão pela qual você deve estruturar os Field Filters desta forma é para lidar com casos onde o Analytics gera o código para você. Por exemplo, para tratar casos onde alguém seleciona múltiplos valores no widget de filtro, ou um intervalo de datas. Com Field Filters, você não pode controlar o SQL gerado, então se precisar de maior controle, deve usar uma ou mais variáveis do tipo Texto, Número ou Data.

Um exemplo de consulta nativa MongoDB pode ser assim:


[ {$match: {{date_var}} } ]

Para um guia mais detalhado, confira Field Filters: criar widgets inteligentes para perguntas SQL.

Field filters no BigQuery e Oracle

Certifique-se de que o dialeto SQL corresponde ao banco de dados selecionado. Problemas comuns envolvem a forma como tabelas são citadas na consulta:

Banco de Dados Particularidade do dialeto Exemplo
BigQuery Esquemas e tabelas devem ser citados com backticks. FROM `dataset.table`
Oracle Esquemas e tabelas devem ser citados com aspas duplas. FROM schema.table

Para mais ajuda, veja Resolução de erros em SQL.

Como criar diferentes tipos de widgets de filtro

O tipo de widget de filtro que o Analytics mostra quando você cria um widget Field Filter depende da configuração para esse campo no Analytics chamada Filtragem neste campo. Admins podem definir esta opção para:

  • Caixa de entrada simples
  • Caixa de pesquisa
  • Uma lista de todos os valores (também conhecido como menu suspenso)

Campos de data terão ou um filtro de data simples (para variáveis do tipo Data) ou um seletor dinâmico de datas (para Field Filters mapeados para um campo de data).

Se quiser alterar o widget de filtro para um campo específico, será necessário pedir a um Admin para atualizar esse campo em Metadados da Tabela definindo a opção “Filtragem neste campo” desejada.

Widget de filtro com caixa de entrada simples

Crie uma variável simples do tipo Texto ou Número. Além disso, você pode usar um Field Filter com um campo que tem a opção “Filtragem neste campo” definida como “Caixa de entrada simples”.

Nota: para evitar ataques de injeção SQL, o Analytics converte tudo o que estiver na caixa de busca para uma string. Se quiser usar curingas, veja nosso artigo Learn.

Widget de filtro com caixa de pesquisa

  • Inclua uma variável SQL em sua consulta.
  • Defina o tipo da variável para Field Filter.
  • Defina o Campo para mapear para um campo do tipo “Categoria” que tenha a opção “Filtragem neste campo” configurada como “Caixa de pesquisa”.

Widget de filtro com menu suspenso e pesquisa

Para criar um menu suspenso com pesquisa e uma lista de todos os valores, você precisa:

  • Incluir uma variável SQL em sua consulta.
  • Definir o tipo da variável para Field Filter.
  • Definir o Campo para mapear para um campo do tipo “Categoria” que tenha a opção “Filtragem neste campo” configurada como “Uma lista de todos os valores”.
  • Definir o tipo do widget de filtro para “Categoria”.

Se o campo no qual deseja criar um menu suspenso não estiver configurado como tipo “Categoria” com a opção “Filtragem neste campo” em “Uma lista de todos os valores”, um Admin precisará atualizar essas configurações para aquele campo. Por exemplo, para criar um menu suspenso para um campo incompatível como Email, um Admin deverá mudar o tipo do campo para “Categoria”, definir a opção “Filtragem neste campo” como “Uma lista de todos os valores” e reescansar os valores daquele campo.

No entanto, se houver muitos valores diferentes naquela coluna para mostrar em um menu suspenso, o Analytics simplesmente exibirá uma caixa de pesquisa. Então, se você tem muitos endereços de email, provavelmente o filtro irá apenas exibir uma caixa de busca. Widgets de menu suspenso funcionam melhor quando há um conjunto reduzido de valores para escolher (como os 50 estados dos EUA).

Limitações do Field Filter

Algumas situações que podem causar problemas ao configurar uma variável Field Filter.

Field Filters não funcionam com alias de tabela

Você não poderá selecionar valores de Field Filters em consultas que usam alias de tabelas para joins ou CTEs.

O motivo é que Field Filters geram SQL baseado no campo mapeado; o Analytics não analisa o SQL, então não consegue identificar a que o alias se refere. Você tem três opções de solução, dependendo da complexidade da sua consulta:

  1. Use nomes completos das tabelas.
  2. Substitua CTEs por subconsultas.
  3. Crie uma view no seu banco de dados e use a view como base da sua consulta.

Field Filters devem estar conectados a campos incluídos na consulta

Sua consulta principal deve estar ciente de todas as tabelas que seu Field Filter está apontando, caso contrário você terá um erro de sintaxe SQL. Por exemplo, suponha que sua consulta principal tenha um field filter como este:


SELECT
  *
FROM
  ORDERS
WHERE
  {{ product_category }}

Suponha que a variável {{ product_category }} se refere a outra pergunta que usa a tabela Products. Para que o field filter funcione, você precisará incluir um join para Products na sua consulta principal.


SELECT
  *
FROM
  ORDERS
  JOIN PRODUCTS ON ORDERS.product_id = PRODUCTS.id
WHERE
  {{ product_category }}

Personalizando listas de dropdown e valores da caixa de pesquisa

Com as variáveis do tipo Texto e Field Filter, você pode indicar ao Analytics quais valores as pessoas podem escolher ao usar um filtro com lista suspensa ou caixa de pesquisa.

  1. No editor nativo, adicione {{variable}} entre chaves duplas.
  2. Se o painel lateral não abrir, clique no ícone {x} à direita para abrir o painel Variáveis.
  3. Na aba Configurações, defina o Tipo da variável para “Texto” ou “Field Filter”.
  4. No painel lateral, vá para Como os usuários devem filtrar essa variável? e escolha “Lista suspensa” ou “Caixa de pesquisa”.
  5. Ao lado da opção escolhida, clique em Editar.
  6. O Analytics abrirá uma janela onde você pode selecionar De onde os valores devem vir.

Você pode escolher:

  • De campos conectados Se você selecionou o tipo Field Filter, também terá a opção de usar o campo conectado.
  • De outro modelo ou pergunta. Se você selecionar esta opção, precisará escolher um modelo ou pergunta, depois um campo desse modelo ou pergunta que o Analytics usará para fornecer os valores para o dropdown ou caixa de pesquisa. Por exemplo, para listar os diferentes planos que uma conta pode ter, você poderia escolher um modelo “Conta” e selecionar o campo “Plano” para alimentar a lista suspensa. A lista então mostraria todas as opções distintas que aparecem na coluna “Plano” do modelo Conta.
  • Lista personalizada. Insira cada item em uma linha. Você pode digitar qualquer valor string que desejar.

Você também pode alterar os valores selecionáveis de um filtro de painel.

Definindo um valor padrão no widget de filtro

No painel lateral de variáveis, você pode definir um valor padrão para sua variável. Esse valor será inserido no widget de filtro correspondente por padrão (mesmo que o widget de filtro esteja vazio). Você precisará inserir um novo valor no widget para substituir o padrão.

Definindo valores padrão complexos na consulta

Você também pode definir valores padrão diretamente em sua consulta, incluindo sintaxe de comentário dentro dos colchetes finais de um parâmetro opcional.


WHERE column = [[ {{ your_parameter }} --]] your_default_value

O comentário “ativará” sempre que um valor for passado para your_parameter.

Isso é útil ao definir valores padrão complexos (por exemplo, se seu valor padrão for uma função como CURRENT_DATE). Aqui está um exemplo em PostgreSQL que define o valor padrão de um filtro de Data para a data atual usando CURRENT_DATE:


SELECT
  *
FROM
  orders
WHERE
  DATE(created_at) = [[ {{dateOfCreation}} --]] CURRENT_DATE

Se você passar um valor para a variável, a cláusula WHERE executará, incluindo a sintaxe de comentário que ignora a função CURRENT_DATE padrão.

Note que o hash (--) usado para comentar o texto pode precisar ser substituído pela sintaxe de comentário específica do banco de dados que você estiver usando.

Exigindo um valor para o widget de filtro

No painel de configurações da Variável, você pode ativar a opção Sempre exigir um valor. Se ativar:

  • Você deve definir um valor padrão.
  • O valor padrão irá substituir qualquer sintaxe opcional no seu código (como uma cláusula WHERE opcional). Se nenhum valor for passado para o filtro, o Analytics executará a consulta usando o valor padrão. Clique no ícone Olho no editor para visualizar o SQL que o Analytics executará.

Tornando variáveis opcionais

Você pode tornar uma cláusula opcional em uma consulta. Por exemplo, pode criar uma cláusula WHERE opcional que contenha uma variável SQL, para que, caso nenhum valor seja fornecido para a variável (no filtro ou via URL), a consulta ainda execute como se aquela cláusula WHERE não existisse.

Para tornar uma variável opcional em sua consulta nativa, coloque colchetes duplos [[ .. ]] ao redor de toda a cláusula contendo a variável {{variable}}. Se alguém inserir um valor no widget de filtro para essa variável, o Analytics colocará a cláusula no template; caso contrário, vai ignorar a cláusula e executar a consulta como se ela não existisse.

Neste exemplo, se nenhum valor for dado para cat, a consulta apenas selecionará todas as linhas da tabela products. Mas, se cat tiver um valor, como “Widget”, a consulta trará apenas os produtos com categoria “Widget”:


SELECT
  count(*)
FROM
  products
[[WHERE category = {{cat}}]]

Seu SQL também deve poder rodar sem a cláusula opcional em [[ ]]

Você precisa garantir que seu SQL será válido quando nenhum valor for passado para a variável na cláusula entre colchetes.

Por exemplo, excluir a palavra-chave WHERE da cláusula entre colchetes causará um erro se nenhum valor for fornecido para cat:


-- isso causará erro:

SELECT
  count(*)
FROM
  products
WHERE [[category = {{cat}}]]

Isso porque, quando nenhum valor for fornecido para cat, o Analytics tentará executar o SQL como se a cláusula em [[ ]] não existisse:


SELECT
  count(*)
FROM
  products
WHERE

o que não é uma consulta SQL válida.

Em vez disso, coloque a cláusula WHERE inteira dentro de [[ ]]:


SELECT
  count(*)
FROM
  products
[[WHERE category = {{cat}}]]

Quando nenhum valor for fornecido para cat, o Analytics executará:


SELECT
  count(*)
FROM
  products

que é uma consulta válida.

Você precisa de pelo menos um WHERE ao usar múltiplas cláusulas opcionais

Para usar várias cláusulas opcionais, você deve incluir ao menos uma cláusula WHERE regular seguida de cláusulas opcionais, cada uma começando com AND:


SELECT
  count(*)
FROM
  products
WHERE
  TRUE
  [[AND id = {{id}}]]
  [[AND {{category}}]]

A última cláusula usa um Field Filter (note a ausência da coluna na cláusula AND). Ao usar um Field Filter, você deve excluir a coluna na consulta; é necessário mapear a variável no painel lateral.

Variáveis opcionais no MongoDB

Se estiver usando MongoDB, você pode tornar uma cláusula opcional assim:


[
    [[{
        $match: {category: {{cat}}}
    },]]
    {
        $count: "Total"
    }
]

Ou com múltiplos filtros opcionais:


[
    [[{ $match: {{cat}} },]]
    [[{ $match: { price: { "$gt": {{minprice}} } } },]]
    {
        $count: "Total"
    }
]

Conectando uma pergunta SQL a um filtro de painel

Para que uma pergunta SQL/nativa salva possa ser usada com um filtro de painel, a pergunta deve conter pelo menos uma variável.

O tipo de filtro de painel que pode ser usado com a pergunta SQL depende do campo. Por exemplo, se você tem um field filter chamado {{var}} e o mapeou para um campo State, você pode mapear um filtro de Localização do painel à sua pergunta SQL. Neste exemplo, você criaria um painel novo (ou usaria um existente), clicaria no ícone de lápis para entrar no modo de edição do painel, adicionaria a pergunta SQL que contém seu field filter State, adicionaria um filtro de painel novo (ou editasse um filtro de Localização existente) e então clicaria no dropdown do cartão da pergunta SQL para ver o field filter de State.

Se você adicionar uma variável Data à pergunta, só será possível usar a opção de filtro de painel Data única. Portanto, se estiver tentando usar outras opções de tempo no painel, precisará alterar a variável para uma variável Field Filter e mapeá-la para um campo de data.

Field filter

Mais sobre Filtros de painel.

Leitura adicional

Leia a documentação para outras versões do Analytics.

Back to top