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

*Tercera entrega de la serie **[MariaDB desde cero a pro](/search?tag=mariadb-desde-cero)**. Tiempo de lectura estimado: 12 minutos.*

En la [entrega I](/post/mariadb-desde-cero-i-instalacion-y-primeros-pasos) instalamos MariaDB y en la [II](/post/mariadb-desde-cero-ii-storage-engines-tipos-y-restricciones) 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](/search?tag=postgresql-desde-cero), muchos patrones te resultarán familiares. Las diferencias están en los detalles.

## CTEs

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

```sql
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

```sql
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:

```sql
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:

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

### Ranking

```sql
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:

```sql
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

```sql
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

```sql
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:

```sql
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`:

```sql
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`:

```sql
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:

```sql
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:

```sql
-- 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:

```sql
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:

```sql
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:

```sql
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](/post/mariadb-desde-cero-ii-storage-engines-tipos-y-restricciones). Otras útiles:

```sql
-- 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`:

```sql
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:

```sql
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

```sql
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:

```sql
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](/post/mariadb-desde-cero-iv-indices-explain-y-tuning)** — rendimiento y diagnóstico.
- **[V: replicación, Galera y producción](/post/mariadb-desde-cero-v-replicacion-galera-y-produccion)** — cierre.

Si aterrizas ahora:

- [I: instalación y primeros pasos](/post/mariadb-desde-cero-i-instalacion-y-primeros-pasos)
- [II: storage engines, tipos y restricciones](/post/mariadb-desde-cero-ii-storage-engines-tipos-y-restricciones)