Tutorial de Data Explorer - Parte 2 - Fundamentos de SQL en Discourse

Comunidad Datos e informes
, ,
1

:discourse: Esta guía es una continuación del Tutorial del Explorador de Datos - Parte 1 - Escribiendo tu primera consulta.

Ahora que has visto cómo funciona el Explorador de Datos en acción, hablemos de algunos conceptos básicos de SQL que te serán útiles mientras exploras tus aventuras con los datos.

Sentencias SQL

Todas las acciones que necesitarás realizar en la base de datos de Discourse se hacen con sentencias SQL.

:discourse: Las palabras clave de SQL NO distinguen entre mayúsculas y minúsculas: select es lo mismo que SELECT, pero en este tutorial, y como mejor práctica, escribiremos todas las palabras clave de SQL en mayúsculas.

Aquí tienes algunas de las sentencias SQL más comunes que podrías usar. Nota que usamos algunas de ellas en nuestra consulta de ejemplo anterior:

  • SELECT: Se utiliza para seleccionar datos de una base de datos. Los datos devueltos se almacenan en una tabla de resultados, llamada conjunto de resultados.
SELECT columna1, columna2, ...
FROM nombre_tabla;
  • WHERE: Se utiliza para filtrar registros.
SELECT columna1, columna2, ...
FROM nombre_tabla
WHERE condición;
  • ORDER BY: Se utiliza para ordenar el conjunto de resultados en orden ascendente o descendente.
SELECT columna1, columna2, ...
FROM nombre_tabla
ORDER BY columna1, columna2, ... ASC|DESC;
  • COUNT: Devuelve el número de filas que coinciden con un criterio especificado.
SELECT COUNT(nombre_columna)
FROM nombre_tabla
WHERE condición;
  • GROUP BY: A menudo se usa con funciones agregadas (COUNT, MAX, MIN, SUM, AVG) para agrupar el conjunto de resultados por una o más columnas.
SELECT nombre_columna, COUNT(*)
FROM nombre_tabla
GROUP BY nombre_columna;
  • HAVING: Se utiliza para filtrar los resultados de una operación GROUP BY. La cláusula HAVING se usa en SQL porque la palabra clave WHERE no puede usarse con funciones agregadas (Ej: COUNT(), MAX(), MIN(), SUM(), AVG()).
SELECT nombre_columna, COUNT(*)
FROM nombre_tabla
GROUP BY nombre_columna
HAVING COUNT(*) > valor;
  • IN: Se utiliza para verificar si un valor está presente en un conjunto de valores o devuelto por una subconsulta. Es una abreviatura para múltiples condiciones OR, lo que hace que tus consultas SQL sean más concisas y fáciles de leer.
SELECT nombre_columna(s)
FROM nombre_tabla
WHERE nombre_columna IN (valor1, valor2, ...);

:discourse: Los puntos y coma no son necesarios al escribir consultas SQL en el Explorador de Datos.

PostgreSQL

Discourse actualmente utiliza PostgreSQL versión 13 para su base de datos, un sistema de gestión de bases de datos relacionales (RDBMS) de código abierto que enfatiza la extensibilidad y el cumplimiento de SQL.

Para una comprensión detallada de PostgreSQL, es posible que quieras leer la Documentación Oficial de PostgreSQL. Específicamente, podrías encontrar particularmente relevantes las secciones sobre Consultas, Tipos de datos y Funciones al escribir consultas SQL.

Tablas de base de datos

La base de datos de Discourse está compuesta por tablas. Las tablas son la forma fundamental de almacenar datos en un sistema de gestión de bases de datos relacionales.

Cada tabla en Discourse se identifica por un nombre (por ejemplo, posts o topics), y cada tabla contiene registros (filas) con datos. Las columnas en las tablas se denominan comúnmente campos.

:discourse: ¡La base de datos de Discourse contiene más de 240 tablas!

El Explorador de Datos enumera las 9 tablas más importantes primero en el panel de la interfaz de usuario de edición de consultas, y puedes ver la estructura y los tipos de todas las columnas de las tablas con un clic:

Esquema de base de datos

En PostgreSQL, así como en otras bases de datos relacionales, los conceptos de clave primaria y clave foránea son fundamentales. Se utilizan para establecer relaciones entre tablas y garantizar la consistencia y la integridad de los datos.

Claves primarias

Una clave primaria es una columna o un conjunto de columnas en una tabla que identifica de manera única cada fila en esa tabla. Ninguna otra fila en una tabla puede tener el mismo valor de clave primaria. No puedes tener un valor de clave primaria nulo, y el valor debe ser único. La clave primaria se utiliza para indexar los datos en la tabla, lo que hace que la recuperación de datos sea mucho más rápida. En el explorador de esquemas de Discourse, la clave primaria de una tabla siempre se lista primero.

Ejemplo: La clave primaria de la tabla posts es el campo id.

Claves foráneas

Una clave foránea es una columna o un conjunto de columnas en una tabla que se utiliza para establecer un vínculo entre los datos de dos tablas. Actúa como una referencia cruzada entre tablas porque hace referencia a la clave primaria de otra tabla, estableciendo así un vínculo entre ellas. La tabla con la clave foránea se llama tabla hija, y la tabla con la clave primaria se llama tabla referenciada o padre. En el explorador de esquemas de Discourse, una clave foránea se indica con el texto fkey seguido del nombre de la tabla padre.

Ejemplo: En la tabla posts, el user_id es una clave foránea de la tabla users.

Uso de claves primarias y foráneas

Cuando escribes consultas en el Explorador de Datos, puedes usar estas claves primarias y foráneas para JOIN (unir) tablas y obtener datos más complejos. Por ejemplo, si quisieras encontrar todas las publicaciones hechas por un usuario en particular, podrías escribir una consulta como esta:

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

En esta consulta, estamos usando la clave foránea user_id en la tabla posts para unirla con la clave primaria id en la tabla users. Esto nos permite encontrar todas las publicaciones hechas por un usuario en particular.

JOINS en SQL

Como se mencionó anteriormente, la sentencia JOIN se utiliza para combinar filas de dos o más tablas, basándose en una columna relacionada entre ellas.

Es importante tener en cuenta que existen varios tipos de sentencias JOIN en SQL, cada una con un propósito diferente:

  1. INNER JOIN: La palabra clave INNER JOIN selecciona registros que tienen valores coincidentes en ambas tablas. Devuelve las filas de ambas tablas donde hay una coincidencia. Usa INNER JOIN cuando solo quieras devolver los registros donde hay una coincidencia en ambas tablas. Cuando se usa JOIN sin especificar el tipo de unión (como LEFT, RIGHT o FULL), por defecto es INNER JOIN.

  2. LEFT (OUTER) JOIN: La palabra clave LEFT JOIN devuelve todos los registros de la tabla izquierda (tabla1) y los registros coincidentes de la tabla derecha (tabla2). El resultado es NULL del lado derecho si no hay coincidencia. Usa LEFT JOIN cuando quieras devolver todos los registros de la tabla izquierda y los registros coincidentes de la tabla derecha. Si no hay coincidencia, el resultado es NULL en el lado derecho.

  3. RIGHT (OUTER) JOIN: La palabra clave RIGHT JOIN devuelve todos los registros de la tabla derecha (tabla2) y los registros coincidentes de la tabla izquierda (tabla1). El resultado es NULL del lado izquierdo cuando no hay coincidencia. Usa RIGHT JOIN cuando quieras devolver todos los registros de la tabla derecha y los registros coincidentes de la tabla izquierda. Si no hay coincidencia, el resultado es NULL en el lado izquierdo.

  4. FULL (OUTER) JOIN: La palabra clave FULL JOIN devuelve todos los registros cuando hay una coincidencia en la tabla izquierda (tabla1) o en la tabla derecha (tabla2). Usa FULL JOIN cuando quieras devolver todos los registros cuando haya una coincidencia en una de las tablas.

También puedes combinar múltiples sentencias JOIN para unir más de dos tablas. Por ejemplo, podemos unir la tabla topic_tags con la tabla topics, y luego unir la tabla tags con la tabla topic_tags para acceder a los nombres de las etiquetas asociadas a un tema.

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

Sentencias WITH y subconsultas

La sentencia WITH de SQL, también conocida como Expresión de Tabla Común (CTE), se utiliza para crear un conjunto de resultados temporal que puede ser referenciado dentro de otra sentencia SELECT. Esto puede ser particularmente útil al trabajar con consultas SQL complejas, ya que ayuda a simplificarlas, haciéndolas más fáciles de leer y mantener.

Aquí tienes un ejemplo básico de una sentencia 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;

En esta consulta, la sentencia WITH crea una tabla temporal post_counts que contiene el ID de cada usuario y su recuento total de publicaciones. La consulta principal luego une esta tabla con la tabla users para obtener los nombres de usuario asociados con cada ID, y ordena el resultado por el recuento de publicaciones en orden descendente.

Esta consulta devolvería el número de publicaciones que ha realizado cada usuario.

Las subconsultas son consultas anidadas dentro de otra consulta. Una subconsulta puede usarse dentro de sentencias SELECT o dentro de otra subconsulta. Una subconsulta puede devolver un conjunto de registros, un solo registro o un solo valor.

Aquí tienes un ejemplo de una 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
)

En este ejemplo, la subconsulta (SELECT p.user_id FROM posts p GROUP BY p.user_id HAVING COUNT(p.id) > 100) selecciona el user_id de la tabla posts para los usuarios que han realizado más de 100 publicaciones. La consulta principal luego obtiene el username de la tabla users para esos user_ids.

Esta consulta devolvería una lista de nombres de usuario de usuarios que han realizado más de 100 publicaciones.

Más temas en esta serie

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