Tutorial do Data Explorer - Parte 2 - Fundamentos do SQL no Discourse

Comunidade Dados e relatórios
, ,
1

:discourse: Este guia é uma continuação do Tutorial do Data Explorer - Parte 1 - Escrevendo Sua Primeira Consulta.

Agora que você viu como o Data Explorer funciona na prática, vamos falar sobre alguns conceitos básicos de SQL que serão úteis durante suas aventuras de exploração de dados.

Instruções SQL

Todas as ações que você precisará realizar no banco de dados do Discourse são feitas por meio de instruções SQL.

:discourse: As palavras-chave do SQL NÃO são sensíveis a maiúsculas e minúsculas: select é o mesmo que SELECT, mas neste tutorial e como melhor prática, escreveremos todas as palavras-chave do SQL em maiúsculas.

Aqui estão algumas das instruções SQL mais comuns que você pode usar. Note que usamos algumas delas em nossa consulta de exemplo anterior:

  • SELECT: Usada para selecionar dados de um banco de dados. Os dados retornados são armazenados em uma tabela de resultados, chamada de conjunto de resultados.
SELECT coluna1, coluna2, ...
FROM nome_da_tabela;
  • WHERE: Usada para filtrar registros.
SELECT coluna1, coluna2, ...
FROM nome_da_tabela
WHERE condição;
  • ORDER BY: Usada para ordenar o conjunto de resultados em ordem crescente ou decrescente.
SELECT coluna1, coluna2, ...
FROM nome_da_tabela
ORDER BY coluna1, coluna2, ... ASC|DESC;
  • COUNT: Retorna o número de linhas que atendem a um critério especificado.
SELECT COUNT(nome_da_coluna)
FROM nome_da_tabela
WHERE condição;
  • GROUP BY: Frequentemente usado com funções de agregação (COUNT, MAX, MIN, SUM, AVG) para agrupar o conjunto de resultados por uma ou mais colunas.
SELECT nome_da_coluna, COUNT(*)
FROM nome_da_tabela
GROUP BY nome_da_coluna;
  • HAVING: Usada para filtrar os resultados de uma operação GROUP BY. A cláusula HAVING é usada em SQL porque a palavra-chave WHERE não pode ser usada com funções de agregação (Ex: COUNT(), MAX(), MIN(), SUM(), AVG()).
SELECT nome_da_coluna, COUNT(*)
FROM nome_da_tabela
GROUP BY nome_da_coluna
HAVING COUNT(*) > valor;
  • IN: Usada para verificar se um valor está presente em um conjunto de valores ou retornado por uma subconsulta. É uma abreviação para múltiplas condições OR, tornando suas consultas SQL mais concisas e fáceis de ler.
SELECT nome_das_colunas
FROM nome_da_tabela
WHERE nome_da_coluna IN (valor1, valor2, ...);

:discourse: Ponto e vírgula não são necessários ao escrever consultas SQL no Data Explorer.

PostgreSQL

O Discourse atualmente usa o PostgreSQL versão 13 para seu banco de dados, um sistema de gerenciamento de banco de dados relacional (RDBMS) de código aberto que enfatiza extensibilidade e conformidade com SQL.

Para um entendimento detalhado do PostgreSQL, você pode querer ler a Documentação Oficial do PostgreSQL. Especificamente, as seções sobre Consultas, Tipos de Dados e Funções podem ser particularmente relevantes ao escrever consultas SQL.

Tabelas de Banco de Dados

O banco de dados do Discourse é composto por tabelas. Tabelas são a forma fundamental de armazenar dados em um sistema de gerenciamento de banco de dados relacional.

Cada tabela no Discourse é identificada por um nome (por exemplo, posts ou topics), e cada tabela contém registros (linhas) com dados. Colunas em tabelas são comumente chamadas de campos.

:discourse: O banco de dados do Discourse contém mais de 240 tabelas!

O Data Explorer lista as 9 tabelas mais importantes primeiro no painel de edição de consulta da interface, e você pode ver a estrutura e os tipos de todas as colunas das tabelas com um clique:

Esquema de Banco de Dados

No PostgreSQL, assim como em outros bancos de dados relacionais, os conceitos de Chave Primária e Chave Estrangeira são fundamentais. Eles são usados para estabelecer relacionamentos entre tabelas e garantir a consistência e integridade dos dados.

Chaves Primárias

Uma chave primária é uma coluna ou um conjunto de colunas em uma tabela que identifica unicamente cada linha nessa tabela. Nenhuma duas linhas em uma tabela podem ter o mesmo valor de chave primária. Você não pode ter um valor nulo de chave primária, e o valor deve ser único. A chave primária é usada para indexar os dados na tabela, tornando a recuperação de dados muito mais rápida. No explorador de esquema do Discourse, a chave primária de uma tabela é sempre listada primeiro.

Exemplo: A chave primária da tabela posts é o campo id.

Chaves Estrangeiras

Uma chave estrangeira é uma coluna ou um conjunto de colunas em uma tabela que é usada para estabelecer um vínculo entre os dados de duas tabelas. Ela atua como uma referência cruzada entre tabelas porque referencia a chave primária de outra tabela, estabelecendo assim um vínculo entre elas. A tabela com a chave estrangeira é chamada de tabela filha, e a tabela com a chave primária é chamada de tabela referenciada ou pai. No explorador de esquema do Discourse, uma chave estrangeira é indicada pelo texto fkey seguido pelo nome da tabela pai.

Exemplo: Na tabela posts, o user_id é uma chave estrangeira da tabela users.

Usando Chaves Primárias e Estrangeiras

Quando você estiver escrevendo consultas no Data Explorer, pode usar essas chaves primárias e estrangeiras para JOIN (juntar) tabelas e obter dados mais complexos. Por exemplo, se você quiser encontrar todas as postagens feitas por um usuário específico, pode escrever uma consulta assim:

SELECT p.id, p.created_at, p.raw
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE u.username = 'nome_de_usuário_aqui'

Nesta consulta, estamos usando a chave estrangeira user_id na tabela posts para juntar com a chave primária id na tabela users. Isso nos permite encontrar todas as postagens feitas por um usuário específico.

SQL JOINS

Como mencionado anteriormente, a instrução JOIN é usada para combinar linhas de duas ou mais tabelas, com base em uma coluna relacionada entre elas.

É importante notar que existem vários tipos de instruções SQL JOIN, cada um servindo a um propósito diferente:

  1. INNER JOIN: A palavra-chave INNER JOIN seleciona registros que têm valores correspondentes em ambas as tabelas. Retorna as linhas de ambas as tabelas onde há uma correspondência. Use INNER JOIN quando quiser retornar apenas os registros onde há uma correspondência em ambas as tabelas. Quando JOIN é usado sem especificar o tipo de junção (como LEFT, RIGHT ou FULL), ele padrão para INNER JOIN.

  2. LEFT (OUTER) JOIN: A palavra-chave LEFT JOIN retorna todos os registros da tabela da esquerda (tabela1) e os registros correspondentes da tabela da direita (tabela2). O resultado é NULL do lado direito, se não houver correspondência. Use LEFT JOIN quando quiser retornar todos os registros da tabela da esquerda e os registros correspondentes da tabela da direita. Se não houver correspondência, o resultado será NULL no lado direito.

  3. RIGHT (OUTER) JOIN: A palavra-chave RIGHT JOIN retorna todos os registros da tabela da direita (tabela2) e os registros correspondentes da tabela da esquerda (tabela1). O resultado é NULL do lado esquerdo, quando não há correspondência. Use RIGHT JOIN quando quiser retornar todos os registros da tabela da direita e os registros correspondentes da tabela da esquerda. Se não houver correspondência, o resultado será NULL no lado esquerdo.

  4. FULL (OUTER) JOIN: A palavra-chave FULL JOIN retorna todos os registros quando há uma correspondência em qualquer uma das tabelas da esquerda (tabela1) ou da direita (tabela2). Use FULL JOIN quando quiser retornar todos os registros quando houver uma correspondência em uma das tabelas.

Você também pode combinar múltiplas instruções JOIN para juntar várias tabelas. Por exemplo, podemos juntar a tabela topic_tags com a tabela topics e, em seguida, juntar a tabela tags com a tabela topic_tags para acessar os nomes das tags associadas a um tópico.

SELECT 
    t.id AS topic_id, 
    tg.name as tag_name
FROM 
    topics t
JOIN topic_tags tt ON t.id = tt.topic_id
JOIN tags tg ON tg.id = tt.tag_id

Instruções WITH e Subconsultas

A instrução SQL WITH, também conhecida como Expressão de Tabela Comum (CTE), é usada para criar um conjunto de resultados temporário que pode ser referenciado dentro de outra instrução SELECT. Isso pode ser particularmente útil ao trabalhar com consultas SQL complexas, pois pode ajudar a simplificá-las, tornando-as mais fáceis de ler e manter.

Aqui está um exemplo básico de uma instrução WITH:

WITH post_counts AS (
  SELECT user_id, COUNT(*) as post_count
  FROM posts
  GROUP BY user_id
)

SELECT u.username, post_counts.post_count
FROM users u
JOIN post_counts ON u.id = post_counts.user_id
ORDER BY post_counts.post_count DESC;

Nesta consulta, a instrução WITH cria uma tabela temporária post_counts que contém o ID de cada usuário e o total de postagens dele. A consulta principal então junta essa tabela com a tabela users para obter os nomes de usuário associados a cada ID e ordena o resultado pelo número de postagens em ordem decrescente.

Esta consulta retornaria o número de postagens que cada usuário fez.

Subconsultas são consultas aninhadas dentro de outra consulta. Uma subconsulta pode ser usada dentro de instruções SELECT ou dentro de outra subconsulta. Uma subconsulta pode retornar um conjunto de registros, um único registro ou um único valor.

Aqui está um exemplo de uma subconsulta:

SELECT u.username 
FROM users u
WHERE u.id IN (
  SELECT p.user_id 
  FROM posts p
  GROUP BY p.user_id 
  HAVING COUNT(p.id) > 100
)

Neste exemplo, a subconsulta (SELECT p.user_id FROM posts p GROUP BY p.user_id HAVING COUNT(p.id) > 100) seleciona o user_id da tabela posts para usuários que fizeram mais de 100 postagens. A consulta principal então obtém o username da tabela users para esses user_ids.

Esta consulta retornaria uma lista de nomes de usuário para usuários que fizeram mais de 100 postagens.

Mais Tópicos nesta Série

9 curtidas
Discourse Data Explorer
Using Parameters in Data Explorer Queries
Dashboard Report - Consolidated Pageviews