La lección anterior cerró el módulo 10 con una frase: "lo que falta no son más funciones, sino contexto". Este módulo es ese contexto, y empieza por lo más concreto que existe: los problemas que te van a pedir resolver. Porque la caja de herramientas ya está llena —JOIN, agregados, subconsultas, CTE, funciones de ventana, JSON— pero en un proyecto real nadie te pide "un LEFT JOIN": te piden "un listado de productos con filtros que el usuario elige", "el cuadro de mando de dirección", "los clientes que ya no compran" o "exportar esto para el gestor".

La buena noticia es que esos encargos se repiten. Cambia el negocio, cambian los nombres de las tablas, y el patrón es el mismo. Esta lección es el catálogo de esos patrones: once casos de uso, cada uno con su planteamiento de negocio, su solución sobre TiendaVerde y una nota de qué herramienta del curso lo resuelve. No hay sintaxis nueva. Hay reconocimiento de patrones, que es una cosa distinta y mucho más útil.

1. Listado paginado con filtros opcionales
2. Búsqueda: prefijo, contenido y full-text
3. Informe de KPI en una sola fila
4. Top N y ranking
5. Detección de huecos: el anti-join
6. Series temporales sin huecos
7. Cohortes de clientes
8. Detección y limpieza de duplicados
9. Auditoría: quién cambió qué y cuándo
10. Exportación a otro sistema
11. Carga desde fichero con tabla de staging
12. Tabla resumen: caso de uso → herramienta → lección
13. Errores Comunes y Consejos
14. Ejercicios
15. Conclusión

El encargo. "La pantalla de catálogo tiene cuatro filtros —categoría, precio máximo, texto y solo con stock—, y el usuario puede rellenar los que quiera. Además tiene paginación." Es el caso de uso número uno del mundo, y el que más veces se escribe mal. El problema no es el WHERE: es que no sabes cuál será el WHERE hasta que el usuario pulse "buscar". Hay dos enfoques, y conviene conocer los dos.

Una sola consulta, con un parámetro por filtro que se neutraliza cuando llega nulo:

Con los cuatro parámetros a NULL/false (primera página del catálogo, sin filtrar):

(5 primeras de 19 filas: los productos activos; el 20, descatalogado, no aparece.) Con :categoria_id = 2 y :precio_max = 15.00 la misma consulta devuelve 3 filas —el aceite corporal (14,25 €), el champú sólido (8,40 €) y el bálsamo labial (4,60 €)—, y con :solo_stock = true desaparecerían las velas de cera, las únicas con stock 0. Sus ventajas son reales: una sola consulta que mantener, cero riesgo de inyección (11-03) y un plan cacheable. Y su coste también: (:p IS NULL OR col = :p) es no sargable (08-04) —el motor no sabe de antemano si la columna estará filtrada—, así que el plan tiene que servir para los dieciséis casos posibles y no es óptimo para ninguno. Con 19 productos da igual; con 19 millones y un filtro muy selectivo, es un Seq Scan donde había un Index Scan.

Mitigación en PostgreSQL: una sentencia preparada pasa a plan genérico a partir de la sexta ejecución. Con SET plan_cache_mode = force_custom_plan el motor replanifica con los valores concretos y descarta las ramas neutralizadas. Es la salida cuando el patrón funciona bien salvo en un filtro concreto.

El otro enfoque es componer el WHERE en código, añadiendo solo las condiciones que el usuario ha rellenado:

La línea roja está en el último renglón: se construye el texto de las condiciones, nunca los valores, que siguen viajando como parámetros. Concatenar f"AND p.precio <= {precio_max}" es exactamente la vulnerabilidad de 11-03, y no deja de serlo porque el dato "parezca" un número.

El criterio del curso: empieza por el patrón de una sola consulta; pásate a la composición solo cuando midas (08-05) que el plan genérico te está costando. Y para la paginación, lo de 08-04: LIMIT/OFFSET para paginadores numerados pequeños, keyset para scroll infinito y APIs.

El encargo. "Que el buscador de la tienda encuentre el producto aunque el cliente escriba media palabra." Hay tres niveles, en orden de coste creciente, y elegir el más barato que resuelva el problema es la decisión:

Ahí está resumido el problema entero: buscar "oliva" por prefijo no encuentra el aceite de oliva, porque la palabra está en medio. Y buscar por contenido sí lo encuentra, pero un LIKE '%…%' no puede usar un índice B-tree (08-04): la única forma de acelerarlo es un índice GIN con pg_trgm, de 08-03. El nivel 3 aparece cuando el usuario escribe frases, quiere que "infusiones" encuentre "infusión", o espera resultados ordenados por relevancia: eso ya no es LIKE, es búsqueda de texto completo con to_tsvector/to_tsquery y un índice GIN sobre el vector.

El criterio: no montes full-text para 19 productos, ni resuelvas un buscador de un millón de artículos con ILIKE '%…%'. Y cuando uses ILIKE, escapa siempre % y _ en la entrada del usuario: si alguien busca 100%, ese % es un comodín.

El encargo. "Dirección quiere una tira de números arriba del panel: ventas del mes, pedidos del mes, pedidos por enviar, facturación total y ticket medio." La tentación es lanzar cinco consultas; la solución es una, con la cláusula FILTER de 04-04, que aplica una condición distinta a cada agregado:

Una consulta, una fila, seis indicadores, y todos son cifras canónicas del curso: los 20 pedidos, los 727,95 € de facturación de producto, los 49,33 € de febrero de 2026 y el ticket medio de 36,40 €. Los 3 "por enviar" son los pedidos 18 y 19 (pagado) y el 20 (pendiente).

El COUNT(DISTINCT pe.id) es obligatorio: el JOIN con lineas_pedido multiplica cada pedido por sus líneas, y un COUNT(*) devolvería 47. Y FILTER es el estándar SQL para esto; el equivalente portable es SUM(CASE WHEN … THEN … END) de 06-05, más verboso y con la trampa de que COUNT(CASE …) cuenta también los NULL si no se escribe con cuidado.

El encargo. "Los cinco productos que más facturan, con su puesto." Es el patrón de 10-03 en su forma más simple, ROW_NUMBER calculado en una CTE y filtrado fuera:

Recuerda las tres decisiones de 10-03, porque en producción importan: ROW_NUMBER para cortar exactamente N filas, RANK si los empatados en el último puesto deben salir todos, y un desempate explícito en el ORDER BY de la ventana —aquí producto_id— para que el resultado sea reproducible. Si solo necesitas los 5 primeros y no la columna de puesto, un ORDER BY … LIMIT 5 sobre un índice es más barato (regla 4 de 08-04).

El encargo. "¿Qué clientes no han comprado nunca? ¿Qué productos no se venden? ¿Cuántos pedidos entran sin comercial?" Todas las preguntas de negocio que empiezan por "qué no" son el mismo patrón: un anti-join, que en PostgreSQL se escribe con NOT EXISTS (07-03). Los tres huecos deliberados de TiendaVerde, en una consulta:

Los nombres detrás de los números: Núria, Hugo e Inés nunca han comprado; las velas de cera (stock 0), el desodorante y las cápsulas de espirulina (descatalogadas) nunca se han vendido; y los cinco empleados sin pedidos son toda la dirección, logística, almacén y análisis. Una variante que se pide constantemente: el cliente inactivo, que no es el que nunca compró sino el que dejó de comprar — un anti-join con ventana temporal:

Dos clientes llevan más de seis meses sin comprar a fecha del 1 de marzo de 2026. Fíjate en la diferencia de negocio: Núria, Hugo e Inés (que nunca compraron) son un problema de activación; Marta y Tiago son un problema de retención. La misma tabla, dos campañas distintas.

Ojo con NOT IN. WHERE id NOT IN (SELECT empleado_id FROM pedidos) devuelve cero filas aquí, porque empleado_id tiene nulos y NOT IN con un NULL en la lista nunca es cierto (04-03, 07-03). Es el error clásico de esta familia de consultas: usa NOT EXISTS.

El encargo. "La gráfica de ventas mensuales se salta los meses sin pedidos y el eje sale torcido." Un GROUP BY solo produce filas para los datos que existen. Si un mes no tuvo pedidos, ese mes no aparece — y una gráfica que une directamente el punto anterior con el siguiente miente. La solución es generar el calendario y unir los datos contra él con generate_series (06-03) y un LEFT JOIN:

(6 de 14 filas; entre abril de 2025 y enero de 2026 la serie es la canónica del curso: 58,85 · 95,48 · 44,60 · 48,27 · 32,76 · 97,20 · 31,70 · 64,58.) Enero y febrero de 2025 aparecen con 0,00 € aunque no exista ni un pedido: la tienda ya tenía catálogo y clientes, pero su primer pedido es del 4 de marzo. Sin el calendario, la serie empezaría en marzo y nadie vería los dos meses sin ventas.

Las dos piezas obligatorias son el LEFT JOIN en esa dirección (calendario a la izquierda, datos a la derecha) y el COALESCE, porque el LEFT JOIN produce NULL, no cero — y NULL en una gráfica es un agujero, no un valor bajo.

El encargo. "¿Los clientes que captamos en primavera compran más que los de otoño?" Una cohorte es un grupo de clientes que comparten el momento de entrada; el análisis consiste en seguir a cada grupo en el tiempo. La forma mínima —cuántos de cada mes de registro llegaron a comprar— es un LEFT JOIN agregado:

Ocho cohortes, 15 clientes, 20 pedidos y 727,95 €: las columnas cuadran con el total del curso, que es la primera comprobación que hay que hacer siempre. Y la lectura es la esperada: las cohortes antiguas tienen más pedidos por cliente (las de enero a marzo de 2025 llevan entre 2 y 2,5 pedidos por persona) y las recientes aún no han comprado — sencillamente porque han tenido menos tiempo. Ese sesgo es el motivo de que las cohortes se comparen siempre a la misma edad: "pedidos en los 90 días siguientes al alta", no "pedidos totales". En 11-04 se retoma con la advertencia honesta sobre el tamaño de muestra.

El encargo. "Marketing dice que hay clientes repetidos." En TiendaVerde no los hay, y la razón está en el CREATE TABLE de 01-06: clientes.email es UNIQUE. Esa es la primera lección del caso de uso — el duplicado se previene con una restricción, no se limpia con una consulta:

El duplicado aparece cuando no hay restricción: en una tabla de importación, en un formulario sin validar, o cuando la clave natural es "el mismo cliente" y no "el mismo email" —Lucía dada de alta dos veces con dos correos distintos. Para eso, dos técnicas: la clave normalizada, GROUP BY lower(unaccent(trim(nombre || ' ' || apellidos))) con su HAVING COUNT(*) > 1, y el parecido en lugar de la igualdad, con similarity() de pg_trgm (08-03) sobre un autojoin a.id < b.id y un umbral de partida de 0.6. Las dos devuelven 0 filas sobre los 15 clientes del curso, que es lo que debe pasar en una tabla sana.

Para la limpieza, el patrón canónico es ROW_NUMBER (10-03): numerar dentro de cada grupo de duplicados por un criterio de "cuál es el bueno" —el más antiguo, el que tiene pedidos— y borrar los demás. Ejecuta primero el SELECT, revisa las filas y solo entonces conviértelo en DELETE (05-04):

Antes de borrar hay que repuntar las referencias: si el cliente duplicado tiene pedidos, hay que moverlos al superviviente con un UPDATE, o la FK ON DELETE RESTRICT lo impedirá — y menos mal.

El encargo. "El precio del aceite cambió el martes y nadie sabe quién lo tocó." Una base de datos, por omisión, no recuerda: un UPDATE sustituye el valor anterior y no deja rastro. Registrar los cambios es una decisión de diseño explícita, y hay tres formas:

El de 10-05 es el primero, con auditoria_precios capturando el valor anterior, el nuevo, current_user y now(). Dos advertencias que solo se aprenden en producción. current_user es el usuario de la base de datos, no el de la aplicación: si la web se conecta con un único rol tv_app (11-03), todas las filas dirán tv_app; para saber qué persona fue hay que propagar el usuario con SET LOCAL app.usuario = '...' al abrir la transacción y leerlo con current_setting('app.usuario', true). Y una tabla de auditoría crece sin parar: planifica su purga o su archivado desde el primer día.

El encargo. "Mándame las ventas del año en un CSV para el gestor."

Los 16 pedidos de 2025. Y ahora las trampas, todas del mundo real y ninguna de SQL:

• Separador decimal y de campos. PostgreSQL escribe 109.53 con punto; un Excel en español espera coma y lo leerá como ciento nueve mil quinientos cincuenta y tres. Se arregla en destino, o en origen con replace(importe::text, '.', ',') — y entonces el DELIMITER no puede ser la coma. De ahí el ';', que es lo que Excel espera en el ámbito europeo.
• Codificación. UTF8 es lo correcto, pero Excel para Windows puede necesitar el BOM o incluso WIN1252 para no destrozar las tildes de "Alimentación". Pruébalo con una fila con acentos, nunca con datos limpios.
• Formato de fecha y nulos. 2025-03-04 es ISO 8601 y es lo correcto entre sistemas; si el destino exige 04/03/2025, conviértelo con to_char(fecha, 'DD/MM/YYYY') y no confíes en DateStyle. Un NULL sale como campo vacío: si el destino no distingue vacío de nulo, usa NULL 'NULO' o COALESCE.
• Datos personales. Un CSV con nombres y correos deja de estar protegido por los permisos de la base. Exporta lo mínimo y consulta 11-03 antes de mandar nada con datos de clientes.

COPY ... TO (sin barra) se ejecuta en el servidor y requiere el rol pg_write_server_files; \copy es el de psql, el que usarás casi siempre. Y para consumo por otro programa, devolver JSON desde la base (10-06) suele ser mejor que un CSV.

El encargo. "Nos han pasado un CSV con 600 clientes de una feria. Cárgalo." Nunca cargues directamente sobre la tabla buena: carga en una tabla de staging sin restricciones, valida allí y mueve solo lo que pasa.

Sobre un fichero de prueba de seis filas —dos de ellas la misma Lucía escrita de dos maneras, un cliente que ya existe, un correo sin @, un país fuera de catálogo y una alta buena—:

De seis filas se carga una. Ese informe es el entregable: quien te mandó el fichero necesita saber qué no ha entrado y por qué, y una carga que falla con ERROR: duplicate key value en la fila 400 no se lo dice a nadie. La inserción final es un INSERT ... SELECT con WHERE motivo_rechazo IS NULL, o un INSERT ... ON CONFLICT DO NOTHING de 05-05 si prefieres que el UNIQUE haga de última red — todo dentro de una transacción (09-03) para que la carga sea todo o nada.

• Usar NOT IN con una subconsulta que puede devolver NULL. Devuelve cero filas y parece que "no hay huecos": usa NOT EXISTS. Y olvidar COUNT(DISTINCT …) en un informe con JOIN a las líneas: el JOIN multiplica, 20 pedidos se convierten en 47 filas y el ticket medio queda dividido por 2,35.
• Pintar una serie temporal sin calendario. Los meses sin datos no se saltan: valen cero, y la gráfica que los omite dibuja una tendencia que no existe. Y comparar cohortes de distinta edad: la de enero lleva un año comprando y la de diciembre, un mes.
• Cargar un CSV directamente sobre la tabla de producción. Una fila mala aborta la carga entera o, peor, entra a medias. Staging, validación e informe de rechazos. Y concatenar valores del usuario al construir un WHERE dinámico: compón condiciones si hace falta, los valores siempre son parámetros (11-03).
• Consejo: comprueba los totales de cada informe contra una cifra que ya conozcas. Las ocho cohortes de este texto suman 727,95 € porque tienen que sumarlo; si no cuadra, el error está en el JOIN, no en los datos. Y guarda cada consulta de informe en el repositorio con un comentario que diga qué pregunta responde. Es el germen de la capa semántica de 11-04. Y cuando un encargo suene nuevo, búscalo en la tabla del apartado 12: casi siempre es uno de estos doce con otro nombre.

La pantalla de administración de pedidos necesita un listado con tres filtros opcionales —estado, país del cliente y rango de fechas— ordenado por fecha descendente y paginado de 10 en 10. (1) Escríbelo con el patrón de una sola consulta. (2) ¿Cuántas filas devuelve sin ningún filtro y cuántas con estado = 'entregado'? (3) Reescribe la paginación en modo keyset y explica qué columna necesitas para que sea estable.

Dirección quiere una alerta de catálogo: los productos activos que no se han vendido nunca o que no tienen ninguna reseña, con una columna que diga cuál de los dos problemas tiene (o los dos). (1) Escríbela. (2) ¿Cuántas filas salen? (3) ¿Por qué el resultado cambia si usas INNER JOIN en lugar de NOT EXISTS?

Te pasan un fichero de altas de proveedores con estas columnas: nombre, pais, email. (1) Diseña la tabla de staging y di por qué no debe tener restricciones. (2) Escribe las cuatro validaciones que aplicarías antes de insertar en proveedores. (3) ¿Qué harías con una fila cuyo nombre ya existe pero con el email distinto?

Solución 1

2. Sin filtros, 10 filas (la primera página de los 20 pedidos); con estado = 'entregado', también 10, porque hay 14 entregados. El total sin LIMIT sería 20 y 14. Fíjate en < :hasta y no <=: el patrón de rango de 08-04. 3. Keyset: WHERE (pe.fecha_pedido, pe.id) < (:ultima_fecha, :ultimo_id) ORDER BY pe.fecha_pedido DESC, pe.id DESC LIMIT 10. Hace falta pe.id como desempate, porque fecha_pedido no es única: sin él, dos pedidos del mismo día podrían repetirse o perderse entre páginas. Por eso el ORDER BY de un keyset debe terminar siempre en una columna única.

Solución 2

2. Diez filas. Los productos activos sin reseña son 10 (los 11 sin reseña menos las cápsulas de espirulina, que están descatalogadas y quedan fuera por WHERE p.activo), y los dos activos nunca vendidos —las velas de cera (id 13) y el desodorante (id 19)— están dentro de esos diez, porque tampoco tienen reseña. De ahí que la respuesta no sea 12: la unión de los dos conjuntos, no la suma. 3. Un INNER JOIN con lineas_pedido o con resenas responde a la pregunta contraria: devuelve los productos que sí tienen ventas o reseñas. Y un LEFT JOIN … WHERE lp.id IS NULL sí funcionaría, pero produce filas intermedias que luego hay que descartar y obliga a un DISTINCT; NOT EXISTS expresa la pregunta directamente y el motor la resuelve como anti-join (07-03, 07-05).

Solución 3

1. CREATE TEMP TABLE stg_proveedores (nombre TEXT, pais TEXT, email TEXT); — todo texto y sin restricciones, ni NOT NULL, ni UNIQUE, ni CHECK. El motivo: si el staging rechaza filas, la carga falla y pierdes precisamente la información que necesitas (qué filas venían mal y por qué). El staging acepta la basura para poder inventariarla; la tabla buena es la que la rechaza. 2. (a) Obligatorios: nombre y pais no nulos ni vacíos tras trim. (b) Formato: email LIKE '%_@_%.%' o NULL —en proveedores el email es opcional—. (c) Catálogo: pais IN ('España','Portugal','Francia','Alemania'), o mejor contra una tabla de países. (d) Duplicados: dentro del fichero con COUNT(*) OVER (PARTITION BY lower(trim(nombre))), y contra la tabla real con EXISTS. 3. Es una decisión de negocio, no técnica: puede ser el mismo proveedor que cambió de correo (→ upsert de 05-05) o dos empresas distintas con nombre parecido (→ INSERT). Lo que no debe hacer el programa es elegir en silencio: marca la fila como "revisión manual" y que decida una persona. Y si el nombre debe ser único de verdad, esa regla va en un UNIQUE sobre proveedores, no en el guion de carga.

Este era el catálogo de encargos, y ya lo tienes completo:

• El listado con filtros opcionales se resuelve con (:param IS NULL OR columna = :param) a cambio de un plan genérico, o construyendo el SQL en la aplicación componiendo condiciones, nunca valores. La búsqueda tiene tres niveles: prefijo con B-tree, contenido con pg_trgm y GIN, y full-text cuando hay frases y relevancia — buscar "oliva" por prefijo no encuentra el aceite de oliva.
• El cuadro de mando cabe en una fila y una consulta con FILTER: 20 pedidos, 727,95 €, 36,40 € de ticket medio y 3 pedidos por enviar; el top N es ROW_NUMBER en una CTE filtrado fuera. Los huecos son siempre un NOT EXISTS —3 clientes sin comprar, 3 productos sin vender, 11 sin reseña, 10 pedidos web—, y nunca un NOT IN si puede haber nulos. Las series temporales se generan con generate_series y se unen con LEFT JOIN + COALESCE, para que enero de 2025 aparezca con 0,00 € en lugar de desaparecer. Las cohortes agrupan por mes de alta y solo se comparan a la misma edad.
• Los duplicados se previenen con UNIQUE y se detectan con GROUP BY … HAVING; la auditoría vive en un trigger, con la advertencia de que current_user no es el usuario de tu aplicación. La exportación falla por el separador decimal, la codificación y el formato de fecha, no por el SQL; y la carga pasa siempre por una tabla de staging que acepta la basura para poder inventariarla: de seis filas de ejemplo, solo una era cargable.

Todas estas consultas funcionan. La pregunta que viene ahora es distinta: ¿podrá otra persona mantenerlas dentro de un año? En la lección siguiente, Mejores prácticas, se trata el oficio: la nomenclatura de tablas, columnas, claves e índices, y por qué lo importante es ser coherente; el formato que hace legible una consulta de cuarenta líneas; las decisiones de diseño —normalizar, tipos restrictivos, NOT NULL por defecto, restricciones nombradas— que separan una base con la que se puede trabajar de una que da miedo tocar; el proceso, con migraciones, revisiones de código y copias de seguridad probadas; y el catálogo de antipatrones, del SELECT * en producción al "lo arreglo directamente en producción".