MariaDB desde cero (III): consultas, CTEs y window functions

Tercera entrega de la serie MariaDB desde cero a pro. Tiempo de lectura estimado: 12 minutos.

En la entrega I instalamos MariaDB y en la II diseñamos un esquema con tipos y restricciones serias. Ahora explotamos el lenguaje.

Un mito muy extendido es que MariaDB (y MySQL) son "SQL básico". No es cierto desde hace años. MariaDB 10.2+ tiene CTEs, window functions, CHECK, JSON. MariaDB 10.3+ añade secuencias estilo Oracle, análisis temporal. Si llevas años sin tocarlo, te vas a llevar sorpresas agradables.

Si vienes de la serie PostgreSQL, muchos patrones te resultarán familiares. Las diferencias están en los detalles.

CTEs

Since 10.2. Sintaxis estándar WITH ... AS:

WITH paid_invoices AS (
  SELECT * FROM invoices WHERE status = 'paid'
),
monthly AS (
  SELECT
    DATE_FORMAT(paid_at, '%Y-%m') AS month,
    SUM(amount) AS total
  FROM paid_invoices
  GROUP BY 1
)
SELECT
  month,
  total,
  total - LAG(total) OVER (ORDER BY month) AS diff
FROM monthly
ORDER BY month;

Ventajas idénticas a las de PostgreSQL: legibilidad, reutilización y separación de lógica.

CTEs recursivas

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 1 AS depth
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.parent_id, c.name, t.depth + 1
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY depth, name;

Misma forma canónica. Útil para árboles, grafos acíclicos, generación de secuencias, etc.

Ejemplo práctico: generar filas de 0 a 99 sin tabla auxiliar:

WITH RECURSIVE numbers AS (
  SELECT 0 AS n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 99
)
SELECT * FROM numbers;

Window functions

Desde MariaDB 10.2. Sintaxis estándar:

función() OVER (PARTITION BY ... ORDER BY ... ROWS/RANGE ...)

Ranking

SELECT
  department,
  employee,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rk,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS drk
FROM employees;

Diferencia entre las tres:

• ROW_NUMBER(): números únicos (1, 2, 3, 4).
• RANK(): empates comparten puesto y se saltan números (1, 2, 2, 4).
• DENSE_RANK(): empates comparten puesto sin saltar (1, 2, 2, 3).

Top N por grupo:

SELECT *
FROM (
  SELECT
    category_id, product_id, views,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY views DESC) AS rn
  FROM products
) ranked
WHERE rn <= 3;

Agregaciones como ventana

SELECT
  order_id,
  customer_id,
  total,
  SUM(total) OVER (PARTITION BY customer_id ORDER BY created_at
                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders;

lag, lead, first_value

SELECT
  day,
  value,
  LAG(value, 1)       OVER (ORDER BY day)         AS prev_day,
  LEAD(value, 1)      OVER (ORDER BY day)         AS next_day,
  FIRST_VALUE(value)  OVER (ORDER BY day)         AS first_val,
  value - LAG(value, 1) OVER (ORDER BY day)       AS diff
FROM metrics;

UPSERT con ON DUPLICATE KEY UPDATE

La forma histórica y específica de MariaDB/MySQL:

INSERT INTO page_views (path, day, views)
VALUES ('/home', CURDATE(), 1)
ON DUPLICATE KEY UPDATE
  views = views + VALUES(views);

Claves clave (valga la redundancia):

• La detección del conflicto depende de cualquier restricción única (PK o UNIQUE). No puedes nombrarla como en ON CONFLICT de PostgreSQL.
• VALUES(col) se refiere al valor que se intentaba insertar. Desde MariaDB 10.3.3 puedes usar alias más claros (INSERT ... AS new ON DUPLICATE KEY UPDATE x = new.x).

Desde MariaDB 10.5, también hay soporte limitado para INSERT ... RETURNING e INSERT IGNORE:

INSERT IGNORE INTO page_views (path, day, views)
VALUES ('/home', CURDATE(), 1);

IGNORE silencia errores de clave duplicada (y otros). Úsalo con conocimiento: esconde errores reales si no tienes cuidado.

REPLACE

MariaDB tiene un REPLACE INTO que borra la fila existente y la vuelve a insertar si hay conflicto de clave única. Evítalo: rompe foreign keys con ON DELETE CASCADE y genera WAL extra. Casi siempre ON DUPLICATE KEY UPDATE es lo que querías.

Agregaciones condicionales

No hay FILTER como en PostgreSQL. El patrón clásico es SUM/COUNT con CASE:

SELECT
  department,
  COUNT(*)                                        AS total,
  SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END)  AS active,
  AVG(CASE WHEN status = 'active' THEN salary END)    AS avg_active_salary
FROM employees
GROUP BY department;

Alternativa idiomática cuando solo cuentas condiciones booleanas:

SELECT
  department,
  COUNT(*)                                 AS total,
  SUM(status = 'active')                   AS active,     -- booleano = 0/1
  SUM(hired_at > NOW() - INTERVAL 1 YEAR)  AS new
FROM employees
GROUP BY department;

MariaDB evalúa comparaciones como 0 o 1, lo que permite sumarlas directamente. Es conciso y rápido.

UPDATE y DELETE con JOIN

Una feature útil que no está en SQL estándar:

-- Update basado en un JOIN
UPDATE posts p
JOIN authors a ON p.author_id = a.id
SET p.title = UPPER(p.title)
WHERE a.status = 'premium';

-- Delete con JOIN
DELETE p
FROM posts p
JOIN expired_users u ON p.author_id = u.id;

Perfectamente soportado y a menudo más claro que una subquery correlacionada.

Multi-table INSERT SELECT

Insert masivo desde otra tabla, con transformación:

INSERT INTO posts_archive (id, title, archived_at)
SELECT id, title, NOW()
FROM posts
WHERE status = 'archived'
  AND archived_at IS NULL;

Se ejecuta en una transacción y es mucho más rápido que un bucle de inserts individuales.

GROUP_CONCAT

Una función muy útil para convertir filas en strings agrupados:

SELECT
  author_id,
  GROUP_CONCAT(title ORDER BY published_at DESC SEPARATOR ' | ') AS titles
FROM posts
GROUP BY author_id;

GROUP_CONCAT tiene un límite en group_concat_max_len (1024 bytes por defecto). Si manipulas mucho texto, súbelo:

SET SESSION group_concat_max_len = 1000000;

Para "arrays" verdaderos en una columna, usa JSON: JSON_ARRAYAGG(title) devuelve un JSON array.

Funciones JSON

Ya vimos JSON_VALID, JSON_VALUE, JSON_EXTRACT en la entrega II. Otras útiles:

-- Construir objetos/arrays
SELECT JSON_OBJECT('name', name, 'email', email) FROM authors;
SELECT JSON_ARRAY('a', 'b', 'c');

-- Agregar en consulta
SELECT author_id, JSON_ARRAYAGG(id) AS post_ids
FROM posts
GROUP BY author_id;

-- Modificar
UPDATE events
SET data = JSON_SET(data, '$.processed', TRUE)
WHERE id = 42;

UPDATE events
SET data = JSON_REMOVE(data, '$.tmp')
WHERE id = 42;

-- Merge
SELECT JSON_MERGE_PATCH(data1, data2) FROM ...;

Ruta JSON: '$.user.email', '$.items[0]', '$.items[*].price'.

JSON_MERGE_PATCH (RFC 7396) es el comportamiento moderno de merge: recursivo, sustituye arrays.

Sequences (estilo Oracle/PostgreSQL)

Desde 10.3. Alternativa a AUTO_INCREMENT:

CREATE SEQUENCE seq_posts_id
  START WITH 1
  INCREMENT BY 1
  NOCACHE;

CREATE TABLE posts (
  id BIGINT NOT NULL DEFAULT NEXTVAL(seq_posts_id),
  ...
);

SELECT NEXTVAL(seq_posts_id);
SELECT LASTVAL(seq_posts_id);
SELECT PREVIOUS VALUE FOR seq_posts_id;

Ventajas sobre AUTO_INCREMENT:

• Puedes obtener el siguiente valor antes de insertar.
• Soportan MINVALUE, MAXVALUE, CACHE, CYCLE.
• Encajan mejor con código SQL portable.

Desventaja: menos conocido en el ecosistema MariaDB, los ORMs a menudo no los soportan de serie.

System-versioned tables (temporal)

Desde 10.3. Mantén histórico automáticamente:

CREATE TABLE products (
  id    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(200) NOT NULL,
  price DECIMAL(12,2) NOT NULL
) WITH SYSTEM VERSIONING;

-- Consultar "tal y como era el 1 de enero"
SELECT * FROM products FOR SYSTEM_TIME AS OF TIMESTAMP '2026-01-01 00:00:00';

-- Rango de tiempo
SELECT * FROM products FOR SYSTEM_TIME BETWEEN
  TIMESTAMP '2026-01-01' AND TIMESTAMP '2026-02-01';

Cada UPDATE y DELETE guarda la versión anterior automáticamente. Útil para auditoría sin montar triggers custom.

Ten en cuenta el tamaño: todas las versiones se acumulan. Puedes particionar por ROW_END para poder borrar histórico antiguo.

Funciones de fecha útiles

NOW(), CURRENT_TIMESTAMP()
CURDATE(), CURRENT_DATE()
CURTIME()

DATE_ADD(ts, INTERVAL 7 DAY)
DATE_SUB(ts, INTERVAL 30 MINUTE)
DATEDIFF(d1, d2)
TIMESTAMPDIFF(HOUR, t1, t2)

DATE_FORMAT(ts, '%Y-%m-%d %H:%i:%s')
EXTRACT(YEAR FROM ts)
YEAR(ts), MONTH(ts), DAY(ts), HOUR(ts)

LAST_DAY(ts)            -- último día del mes
WEEK(ts, 1)             -- modo ISO (lunes)
DAYOFWEEK(ts)           -- 1 = domingo

Un ejemplo realista

Funnel de conversión, versión MariaDB:

WITH sessions AS (
  SELECT
    session_id,
    user_id,
    MIN(CASE WHEN event = 'visit'    THEN created_at END) AS visited_at,
    MIN(CASE WHEN event = 'signup'   THEN created_at END) AS signed_up_at,
    MIN(CASE WHEN event = 'purchase' THEN created_at END) AS purchased_at
  FROM events
  WHERE created_at >= NOW() - INTERVAL 30 DAY
  GROUP BY session_id, user_id
)
SELECT
  COUNT(*)                                     AS visits,
  SUM(signed_up_at IS NOT NULL)                AS signups,
  SUM(purchased_at IS NOT NULL)                AS purchases,
  ROUND(100 * SUM(signed_up_at IS NOT NULL) / NULLIF(COUNT(*), 0), 2)     AS signup_rate,
  ROUND(100 * SUM(purchased_at IS NOT NULL) / NULLIF(SUM(signed_up_at IS NOT NULL), 0), 2) AS conversion_rate
FROM sessions
WHERE visited_at IS NOT NULL;

CTE + booleanos como enteros + NULLIF para evitar divisiones por cero. Todo lo que haría falta en la vida real.

Por dónde seguir

• IV: índices, EXPLAIN y tuning — rendimiento y diagnóstico.
• V: replicación, Galera y producción — cierre.

Si aterrizas ahora:

• I: instalación y primeros pasos
• II: storage engines, tipos y restricciones