{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Texto y fechas, donde los cuatro se separan\n", "\n", "Limpiar nombres, buscar sin que importen las mayúsculas y hacerle cuentas a una fecha en PostgreSQL, MySQL, SQL Server y SQLite.\n", "\n", "Cuaderno de soluciones del capítulo 5 de **SQL desde cero**, de Miss Yera.\n", "\n", "Corre de arriba abajo. Si lo abres en Google Colab no necesitas instalar nada.\n", "\n", "Capítulo completo: https://missyera.com/guias/sql-desde-cero/texto-y-fechas/\n", "\n", "Este es el cuaderno de **soluciones**. Trae el código de cada ejercicio, la\n", "explicación de la trampa y la respuesta del quiz. Si vienes del cuaderno de\n", "práctica sin haberlo intentado, vuelve 🙂" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Antes de empezar\n", "\n", "Se baja la base y se deja lista una función `q()` que corre las consultas." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import sqlite3\n", "import urllib.request\n", "\n", "import pandas as pd\n", "\n", "urllib.request.urlretrieve(\"https://missyera.com/static/datasets/tienda.db\", \"tienda.db\")\n", "con = sqlite3.connect(\"tienda.db\")\n", "\n", "def q(sql):\n", " \"\"\"Corre las sentencias del bloque y devuelve la ultima como tabla.\n", "\n", " Parte por sentencias igual que la consola de SQLite, porque un bloque puede\n", " traer varias y un CREATE TRIGGER lleva punto y coma dentro de su cuerpo.\n", " \"\"\"\n", " resultado = None\n", " trozo = \"\"\n", " for linea in sql.splitlines(keepends=True):\n", " trozo += linea\n", " if sqlite3.complete_statement(trozo):\n", " if trozo.strip():\n", " cur = con.execute(trozo.strip())\n", " resultado = (pd.DataFrame(cur.fetchall(),\n", " columns=[d[0] for d in cur.description])\n", " if cur.description else None)\n", " trozo = \"\"\n", " if trozo.strip():\n", " cur = con.execute(trozo.strip())\n", " resultado = (pd.DataFrame(cur.fetchall(),\n", " columns=[d[0] for d in cur.description])\n", " if cur.description else None)\n", " con.commit()\n", " return resultado\n", "\n", "q(\"SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Un cliente me pidió el ranking de sus marcas. Doce locales, doce marcas, un\n", "Excel de media hora. Corrí la consulta y me salieron **veintitrés**.\n", "\n", "No había veintitrés marcas. Había doce, y once estaban escritas con\n", "mayúsculas y con dos espacios delante porque alguien las cargó copiando de otro\n", "sistema. Para la base, `Market Central` y\n", "` MARKET CENTRAL ` son dos cosas distintas. Y no te avisa. Te\n", "entrega el reporte con cara de que todo está bien 🙃\n", "\n", "Este capítulo va de eso: de texto sucio y de fechas que no son fechas. Son\n", "los dos sitios donde los cuatro motores más se separan, así que aquí la tabla de\n", "dialectos vale doble.\n", "\n", "Antes de seguir, una pregunta: **¿cuántos clientes distintos crees que tiene de verdad tu base?** Yo aprendí a no contestar eso de memoria nunca, porque la respuesta cambia según cómo esté escrito el nombre 🧼" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## La tienda que aparecía dos veces" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Vamos a ver la suciedad con nuestros ojos. El truco es meter el texto entre\n", "corchetes: los espacios de los bordes son invisibles hasta que pones algo al\n", "lado." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT id, '[' || nombre || ']' AS entre_corchetes\n", "FROM clientes\n", "WHERE nombre <> TRIM(nombre)\n", "ORDER BY id\n", "LIMIT 5;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Dieciocho clientes de ciento veinte vienen así. Y fíjate que además están en\n", "mayúsculas, que es la otra mitad del problema.\n", "\n", "Ahora la consulta que me dio veintitrés. La idea es quitarle los tres dígitos\n", "del final al nombre para quedarme con la marca." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT COUNT(DISTINCT SUBSTR(nombre, 1, LENGTH(nombre) - 4)) AS marcas\n", "FROM clientes;\n", "\"\"\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT COUNT(DISTINCT UPPER(TRIM(SUBSTR(TRIM(nombre), 1, LENGTH(TRIM(nombre)) - 4)))) AS marcas\n", "FROM clientes;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Veintitrés contra doce. La misma pregunta, la misma base, el mismo día. Lo\n", "único que cambió es que la segunda limpia antes de contar.\n", "\n", "Esa consulta se lee de adentro hacia afuera, como las muñecas rusas:\n", "`TRIM` quita los espacios, `SUBSTR` corta el código,\n", "`TRIM` otra vez limpia el espacio que quedó, `UPPER` pone\n", "todo en mayúsculas y recién ahí `COUNT(DISTINCT ...)` cuenta. Cinco\n", "funciones que vamos a ver una por una 🧼\n", "\n", "Y guárdate la regla, que es la que de verdad importa:\n", "**antes de agrupar por un texto, límpialo**. Siempre. No importa\n", "qué tan confiable te digan que es la base." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Juntar texto" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT nombre || ' - ' || ciudad AS ficha\n", "FROM clientes\n", "ORDER BY id\n", "LIMIT 4;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Esas dos barras verticales son el \"pega esto con esto\". Y son la primera\n", "cosa que cambia de motor a motor." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Pegar dos textos\n", "\n", "| PostgreSQL | `nombre || ' - ' || ciudad` |\n", "|---|---|\n", "| MySQL | `CONCAT(nombre, ' - ', ciudad)` |\n", "| SQL Server | `nombre + ' - ' + ciudad` |\n", "| SQLite | `nombre || ' - ' || ciudad` |\n", "\n", "MySQL usa || para el OR lógico, así que ahí las barras no pegan nada: te devuelven 0 o 1 y encima sin error. En SQL Server el + hace de suma y de pegamento según los tipos, que es cómo aparece el \"conversion failed\" cuando una de las dos columnas es un número." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Si quieres una sola forma que funcione en los cuatro, es\n", "`CONCAT()`: existe en PostgreSQL, en MySQL, en SQL Server desde 2012\n", "y en SQLite desde la versión 3.44. Pero ojo, porque con nulos no se portan\n", "igual." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT 'Lima' || NULL AS con_pipes, COALESCE('Lima' || NULL, 'se perdio') AS rescatado;\n", "\"\"\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT CONCAT('Lima', NULL, 'Peru') AS con_concat;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Con `||` un solo nulo se come toda la frase, igual que en el\n", "capítulo 4. Con `CONCAT()` en SQLite el nulo se ignora y sale\n", "`LimaPeru`." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Qué hace CONCAT cuando uno de los textos es NULL\n", "\n", "| PostgreSQL | `ignora el NULL` |\n", "|---|---|\n", "| MySQL | `devuelve NULL entero` |\n", "| SQL Server | `trata el NULL como texto vacío` |\n", "| SQLite | `ignora el NULL` |\n", "\n", "Tres lo ignoran y MySQL no, así que la misma consulta que en Postgres te arma la ficha del cliente, en MySQL te deja la columna vacía en cuanto falte un dato. Si hay nulos posibles, envuelve cada trozo en COALESCE y deja de depender del motor." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Mayúsculas, minúsculas y espacios que no ves" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`UPPER` y `LOWER` hacen lo que suena y se escriben\n", "igual en los cuatro. `TRIM` quita los espacios de los dos bordes, y\n", "también en los cuatro." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT id,\n", " LENGTH(nombre) AS largo,\n", " LENGTH(TRIM(nombre)) AS largo_limpio,\n", " '[' || TRIM(nombre) || ']' AS limpio\n", "FROM clientes\n", "WHERE id IN (1, 26)\n", "ORDER BY id;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "El cliente 26 mide 25 caracteres pero su nombre real mide 21. Los cuatro\n", "sobrantes son aire, y ese aire es lo que hacía que apareciera como una marca\n", "aparte." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Contar cuántos caracteres tiene un texto\n", "\n", "| PostgreSQL | `LENGTH(nombre)` |\n", "|---|---|\n", "| MySQL | `CHAR_LENGTH(nombre)` |\n", "| SQL Server | `LEN(nombre)` |\n", "| SQLite | `LENGTH(nombre)` |\n", "\n", "Aquí hay dos trampas. En MySQL, LENGTH cuenta BYTES, así que \"Ñaña\" mide 6 y no 4: para caracteres es CHAR_LENGTH. Y el LEN de SQL Server ignora los espacios del final, así que el cliente 26 mediría 23 y no 25, y la suciedad se te esconde justo cuando la estás buscando." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Quitar los espacios de los bordes\n", "\n", "| PostgreSQL | `TRIM(nombre)` |\n", "|---|---|\n", "| MySQL | `TRIM(nombre)` |\n", "| SQL Server | `TRIM(nombre) -- desde 2017; antes LTRIM(RTRIM(nombre))` |\n", "| SQLite | `TRIM(nombre)` |\n", "\n", "De las pocas que se escriben igual en los cuatro. Si trabajas contra un SQL Server viejito, el LTRIM(RTRIM(...)) sigue funcionando en todas las versiones, así que es el que no falla nunca." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Cortar y reemplazar" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`SUBSTR(texto, desde, cuántos)` corta un pedazo. Y ojo con algo\n", "que confunde el primer día: **en SQL se empieza a contar en 1, no en\n", "0**. Si vienes de Python, es al revés de lo que tienes en el dedo." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT id,\n", " '[' || SUBSTR(nombre, -3) || ']' AS ultimos_tres,\n", " UPPER(TRIM(SUBSTR(TRIM(nombre), 1, LENGTH(TRIM(nombre)) - 4))) AS marca\n", "FROM clientes\n", "WHERE id IN (1, 26)\n", "ORDER BY id;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Mira el cliente 26: pedí los últimos tres caracteres y me trajo\n", "`6` y dos espacios, porque los últimos tres caracteres de verdad son\n", "espacios. La suciedad no se arregla sola en ningún paso, hay que limpiarla\n", "antes de cortar. Por eso la columna `marca` lleva el\n", "`TRIM` por dentro." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Cortar un pedazo de texto\n", "\n", "| PostgreSQL | `SUBSTRING(nombre FROM 1 FOR 10) -- también SUBSTR(nombre, 1, 10)` |\n", "|---|---|\n", "| MySQL | `SUBSTRING(nombre, 1, 10)` |\n", "| SQL Server | `SUBSTRING(nombre, 1, 10) -- los tres argumentos son obligatorios` |\n", "| SQLite | `SUBSTR(nombre, 1, 10)` |\n", "\n", "SUBSTRING(texto, desde, cuántos) con esos tres argumentos funciona en los cuatro y es la forma que yo escribo. Lo que no viaja es el número negativo para contar desde el final: en SQL Server no existe y hay que usar RIGHT(nombre, 3)." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`REPLACE` cambia un texto por otro, y esta sí se escribe igual en\n", "todos." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT id, direccion, REPLACE(direccion, 'nro', 'N.') AS normalizada\n", "FROM direcciones\n", "ORDER BY id\n", "LIMIT 3;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Reemplazar un texto por otro\n", "\n", "| PostgreSQL, MySQL, SQL Server y SQLite | `REPLACE(direccion, 'nro', 'N.')` |\n", "|---|---|\n", "\n", "Una de las poquísimas que se escribe idéntica en los cuatro. Reemplaza TODAS las apariciones, no solo la primera." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Buscar, y la trampa de las mayúsculas" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "En el capítulo 3 vimos `LIKE` y quedó dicho que se comporta\n", "distinto en cada motor. Ahora lo vamos a medir, que es otra cosa." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT COUNT(*) AS con_like\n", "FROM clientes\n", "WHERE nombre LIKE '%Sol%';\n", "\"\"\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT COUNT(*) AS con_glob\n", "FROM clientes\n", "WHERE nombre GLOB '*Sol*';\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Once y diez. La diferencia es el cliente 26, el de\n", "`MINIMARKET EL SOL 026`: el `LIKE` de SQLite no distingue\n", "mayúsculas y lo encuentra, y el `GLOB` sí distingue y lo deja\n", "fuera.\n", "\n", "Y aquí está lo que quiero que se te quede: **esa misma consulta con\n", "`LIKE` te devuelve 10 en PostgreSQL**, porque ahí el\n", "`LIKE` sí distingue mayúsculas. Mismo SQL, misma data, distinto\n", "número. Sin error, sin aviso, sin nada 😳" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT COUNT(*) AS a_prueba_de_motor\n", "FROM clientes\n", "WHERE UPPER(nombre) LIKE '%SOL%';\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Buscar sin que importen las mayúsculas\n", "\n", "| PostgreSQL | `nombre ILIKE '%sol%' -- el LIKE normal SÍ distingue` |\n", "|---|---|\n", "| MySQL | `nombre LIKE '%sol%' -- el collation por defecto no distingue` |\n", "| SQL Server | `nombre LIKE '%sol%' -- según el collation de la base` |\n", "| SQLite | `nombre LIKE '%sol%' -- no distingue, pero solo en letras sin tilde` |\n", "\n", "La única que da el mismo número en los cuatro es UPPER(columna) LIKE '%TEXTO%'. Cuesta seis letras más y te ahorra la conversación de por qué el reporte de la nube y el de tu laptop no coinciden." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Un detalle peruano que muerde: el `LIKE` de SQLite ignora\n", "mayúsculas solo en el alfabeto inglés. Con `Ñ` o con vocales con\n", "tilde vuelve a distinguir, así que `'%ÑAÑA%'` no encuentra\n", "`Ñaña`. Nuestros nombres de negocio están llenos de tildes, o sea que\n", "esto no es un caso raro de manual, es el martes 🇵🇪" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Las fechas no son fechas" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Esta es la parte que hay que leer despacio, porque de aquí salen los errores\n", "más caros y ninguno da error." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT id, fecha, TYPEOF(fecha) AS tipo, monto, TYPEOF(monto) AS tipo_monto\n", "FROM pedidos\n", "ORDER BY id\n", "LIMIT 3;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`text`. La columna `fecha` no guarda fechas, guarda\n", "texto que a nosotras nos parece una fecha. Y no es que la base esté mal hecha:\n", "**SQLite no tiene tipo fecha**, punto. Los otros tres sí." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Cómo se guarda una fecha\n", "\n", "| PostgreSQL | `DATE, TIMESTAMP` |\n", "|---|---|\n", "| MySQL | `DATE, DATETIME, TIMESTAMP` |\n", "| SQL Server | `DATE, DATETIME2` |\n", "| SQLite | `no existe: se guarda como TEXT en formato AAAA-MM-DD` |\n", "\n", "Que SQLite no tenga tipo fecha suena a defecto y en la práctica funciona, porque el formato AAAA-MM-DD ordena y compara bien como texto: el 2025 va antes que el 2026 tanto en el calendario como en el diccionario. Lo que no funciona es cualquier otro formato." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Y ahí está la trampa. Mira lo que pasa si escribes la fecha como la\n", "escribimos en Perú." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT COUNT(*) AS pedidos_de_2026\n", "FROM pedidos\n", "WHERE fecha >= '01/01/2026';\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Novecientos. O sea todos los pedidos de la tabla, incluidos los de 2025.\n", "\n", "¿Por qué? Porque está comparando texto letra por letra, como el diccionario.\n", "El `0` de `01/01/2026` va antes que el `2` de\n", "`2025-05-30`, así que *todas* las fechas le parecen mayores.\n", "Cero errores, cero avisos, un número que está mal." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT COUNT(*) AS pedidos_de_2026\n", "FROM pedidos\n", "WHERE fecha >= '2026-01-01';\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Doscientos noventa y dos. Ese es el bueno.\n", "\n", "Y si intentas convertir la fecha peruana a fecha de verdad, tampoco te\n", "avisa." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT DATE('24/06/2026') AS a_la_peruana,\n", " DATE('2026-06-24') AS en_iso;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "La primera sale vacía, que como vimos en el capítulo 4 es `NULL`.\n", "SQLite no entendió la fecha y en vez de reclamar, se encogió de hombros.\n", "\n", "La regla, y va en mayúsculas porque me ha costado caro:\n", "**dentro de la base, las fechas se escriben AAAA-MM-DD.** El\n", "formato bonito se pone al final, cuando el número ya está calculado." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## El error que sí es error" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Ahora prueba lo que hace todo el mundo que viene de MySQL o de SQL\n", "Server." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Esto revienta a propósito.** Se ejecuta dentro de un `try` para que puedas seguir con \"ejecutar todo\" y aun así ver la queja." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "try:\n", " q(\"\"\"\n", " SELECT YEAR(fecha) AS anio\n", " FROM pedidos\n", " LIMIT 3;\n", " \"\"\")\n", "except Exception as e:\n", " print(f'{type(e).__name__}: {e}')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Y la queja que tiene que salir es esta:\n", "\n", "```\n", "OperationalError: no such function: YEAR\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "*no such function: YEAR*. Y este es de los buenos, en serio 🙌 Porque\n", "te lo dice de frente en vez de devolverte un número equivocado.\n", "\n", "`YEAR()` existe en MySQL y en SQL Server, no existe en SQLite, y\n", "en PostgreSQL tampoco: ahí es `EXTRACT`. En SQLite todo lo de fechas\n", "pasa por una sola función, `STRFTIME`." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT COUNT(*) AS pedidos_de_junio_2026\n", "FROM pedidos\n", "WHERE STRFTIME('%Y-%m', fecha) = '2026-06';\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Sacar el año de una fecha\n", "\n", "| PostgreSQL | `EXTRACT(YEAR FROM fecha)` |\n", "|---|---|\n", "| MySQL | `YEAR(fecha) -- también EXTRACT(YEAR FROM fecha)` |\n", "| SQL Server | `YEAR(fecha) -- también DATEPART(year, fecha)` |\n", "| SQLite | `CAST(STRFTIME('%Y', fecha) AS INTEGER)` |\n", "\n", "EXTRACT es la forma del estándar y funciona en PostgreSQL y en MySQL. SQL Server no la tiene. Para el mes es igual: MONTH() en dos, EXTRACT en dos, STRFTIME('%m', ...) en SQLite." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "El `CAST` del final no es decoración: `STRFTIME`\n", "devuelve texto, así que sin él te llevas `'2026'` entre comillas y no\n", "puedes sumarlo ni restarlo." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Hoy, hace 90 días y el primero del mes" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Casi todo reporte de negocio empieza igual: \"lo de los últimos 90 días\". Para\n", "eso hay que saber hacerle cuentas a una fecha." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT DATE('2026-06-24', '-90 days') AS hace_90_dias,\n", " DATE('2026-06-24', '+1 month') AS en_un_mes,\n", " DATE('2026-06-24', 'start of month') AS inicio_de_mes;\n", "\"\"\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT COUNT(*) AS ultimos_90_dias\n", "FROM pedidos\n", "WHERE fecha >= DATE('2026-06-24', '-90 days');\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Ciento sesenta y seis pedidos en los últimos 90 días de la base. Puse la\n", "fecha a mano porque el último pedido de esta tienda es del 24 de junio de 2026 y\n", "quiero que a ti te salga el mismo número que a mí. En tu trabajo, en cambio, vas\n", "a querer \"hoy\", y ahí es donde los cuatro se ponen creativos." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "La fecha de hoy\n", "\n", "| PostgreSQL | `CURRENT_DATE` |\n", "|---|---|\n", "| MySQL | `CURDATE() -- también CURRENT_DATE` |\n", "| SQL Server | `CAST(GETDATE() AS DATE)` |\n", "| SQLite | `DATE('now') -- también CURRENT_DATE` |\n", "\n", "CURRENT_DATE funciona en tres de los cuatro; el que se queda fuera es SQL Server, que tiene CURRENT_TIMESTAMP pero no CURRENT_DATE. Y en SQLite el DATE('now') te da la hora UTC, o sea cinco horas adelantada de Lima: si te importa el borde del día, es DATE('now', 'localtime')." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Restarle 90 días a una fecha\n", "\n", "| PostgreSQL | `fecha - INTERVAL '90 days'` |\n", "|---|---|\n", "| MySQL | `DATE_SUB(fecha, INTERVAL 90 DAY)` |\n", "| SQL Server | `DATEADD(day, -90, fecha)` |\n", "| SQLite | `DATE(fecha, '-90 days')` |\n", "\n", "Cuatro sintaxis distintas para la misma resta, y ninguna se parece a otra. Esta es la fila que yo tengo pegada al monitor." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Ponerle formato al final" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT id, fecha, STRFTIME('%d/%m/%Y', fecha) AS a_la_peruana\n", "FROM pedidos\n", "ORDER BY id\n", "LIMIT 3;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Mostrar una fecha como 24/06/2026\n", "\n", "| PostgreSQL | `TO_CHAR(fecha, 'DD/MM/YYYY')` |\n", "|---|---|\n", "| MySQL | `DATE_FORMAT(fecha, '%d/%m/%Y')` |\n", "| SQL Server | `FORMAT(fecha, 'dd/MM/yyyy') -- también CONVERT(varchar, fecha, 103)` |\n", "| SQLite | `STRFTIME('%d/%m/%Y', fecha)` |\n", "\n", "Cuatro nombres distintos y encima dos idiomas de máscara: MySQL y SQLite usan %d/%m/%Y, PostgreSQL usa DD/MM/YYYY y SQL Server usa dd/MM/yyyy con la M en mayúscula para el mes, porque la m minúscula ahí son los minutos." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Y una cosa de orden que vale para los cuatro: **el formato se pone en\n", "el SELECT, nunca en el WHERE ni en el ORDER BY**. Si ordenas por\n", "`'24/06/2026'` estás ordenando por día, y diciembre de 2025 te va a\n", "quedar antes que enero del mismo año.\n", "\n", "El día de la semana también sale de ahí, y sirve para preguntas de\n", "negocio de verdad." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT COUNT(*) AS pedidos_en_domingo\n", "FROM pedidos\n", "WHERE STRFTIME('%w', fecha) = '0';\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "En SQLite el domingo es el 0. En MySQL `DAYOFWEEK()` también\n", "arranca el domingo pero en 1, y en SQL Server `DATEPART(weekday, ...)`\n", "depende de una configuración de la sesión. Si vas a usar el día de la semana,\n", "compruébalo con una fecha que sepas antes de creerle." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Cuánto tiempo pasó" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT nombre,\n", " fecha_alta,\n", " CAST(JULIANDAY('2026-06-24') - JULIANDAY(fecha_alta) AS INTEGER) AS dias_con_nosotros\n", "FROM clientes\n", "ORDER BY dias_con_nosotros DESC\n", "LIMIT 3;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "`JULIANDAY` convierte una fecha en un número de días corridos, así\n", "que restar dos julianday da días. Y mira el tercero de la lista, que sigue\n", "teniendo su nombre sucio: la limpieza que hicimos arriba no cambió la base, solo\n", "la consulta 🧽" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Días entre dos fechas\n", "\n", "| PostgreSQL | `fecha_fin - fecha_ini -- con columnas DATE devuelve el número de días` |\n", "|---|---|\n", "| MySQL | `DATEDIFF(fecha_fin, fecha_ini)` |\n", "| SQL Server | `DATEDIFF(day, fecha_ini, fecha_fin)` |\n", "| SQLite | `JULIANDAY(fecha_fin) - JULIANDAY(fecha_ini)` |\n", "\n", "Fíjate bien en las dos del medio: se llaman igual, DATEDIFF, y reciben los argumentos al revés. La de MySQL va (fin, inicio) y la de SQL Server va (unidad, inicio, fin). Copiar una consulta de un motor al otro te cambia el signo del resultado sin un solo error." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Ejercicios" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Los siete corren sobre `tienda.db`. Intenta antes de abrir 💛" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 1. La ficha del cliente\n", "\n", "Arma una sola columna que diga\n", "`NOMBRE LIMPIO (segmento, ciudad)` para los clientes 1, 12 y 44. El\n", "12 es uno de los sucios." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT UPPER(TRIM(nombre)) || ' (' || segmento || ', ' || ciudad || ')' AS ficha\n", "FROM clientes\n", "WHERE id IN (1, 12, 44)\n", "ORDER BY id;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "ficha\n", "-----------------------------------------\n", "MINIMARKET EL SOL 001 (Horeca, Piura)\n", "MARKET CENTRAL 012 (Horeca, Arequipa)\n", "BODEGA LA ESQUINA 044 (Minimarket, Piura)\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "El `TRIM` va por dentro del `UPPER`, aunque en este\n", "caso da igual el orden. Lo que no da igual es que esté." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 2. Cuánta suciedad hay\n", "\n", "Cuenta cuántos clientes tienen espacios en los bordes. Sin\n", "subconsultas todavía, que eso es el capítulo 8." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT COUNT(*) AS con_espacios\n", "FROM clientes\n", "WHERE nombre <> TRIM(nombre);\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "con_espacios\n", "------------\n", "18\n", "```" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT COUNT(*) AS en_mayuscula\n", "FROM clientes\n", "WHERE nombre = UPPER(nombre);\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "en_mayuscula\n", "------------\n", "18\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Dieciocho y dieciocho, y no es casualidad: son los mismos dieciocho\n", "registros, que entraron por la misma carga mal hecha. Un 15% de la tabla de\n", "clientes 🫠" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 3. El código de tres dígitos, como número\n", "\n", "Saca los tres dígitos del final del nombre y conviértelos\n", "en entero, para los clientes 1, 26 y 120. El 26 es sucio, así que ahí está la\n", "gracia." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT id, CAST(SUBSTR(TRIM(nombre), -3) AS INTEGER) AS codigo\n", "FROM clientes\n", "WHERE id IN (1, 26, 120)\n", "ORDER BY id;\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "id codigo\n", "--- ------\n", "1 1\n", "26 26\n", "120 120\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "El `TRIM` tiene que ir *dentro* del `SUBSTR`. Si\n", "lo pones fuera, cortas primero y limpias después, o sea que te llevas\n", "`'6 '` y el `CAST` te devuelve 6 en vez de 26. Sin\n", "error.\n", "\n", "Y de paso: el `CAST` se come los ceros de la izquierda, por eso el\n", "001 sale como 1. Si el código es un identificador y no una cantidad, déjalo\n", "como texto." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 4. Todas las bodegas\n", "\n", "Cuenta los clientes cuyo nombre empieza por \"Bodega\", de\n", "forma que dé el mismo número en los cuatro motores." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT COUNT(*) AS bodegas\n", "FROM clientes\n", "WHERE UPPER(TRIM(nombre)) LIKE 'BODEGA%';\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "bodegas\n", "-------\n", "17\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Diecisiete. El `UPPER` lo hace igual en los cuatro y el\n", "`TRIM` rescata a las que tenían espacios delante, que con\n", "`LIKE 'BODEGA%'` se habrían quedado fuera porque el\n", "`%` está al final y no al principio." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 5. El primer trimestre de 2026\n", "\n", "Cuántos pedidos y cuántos soles entre el 1 de enero y el 31\n", "de marzo de 2026." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT COUNT(*) AS primer_trimestre_2026, ROUND(SUM(monto), 2) AS soles\n", "FROM pedidos\n", "WHERE fecha BETWEEN '2026-01-01' AND '2026-03-31';\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "primer_trimestre_2026 soles\n", "--------------------- --------\n", "139 81510.72\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Funciona porque la columna es texto en formato ISO y no lleva hora. El día\n", "que esa columna tenga hora, el 31 de marzo a las 3 de la tarde se queda fuera y\n", "el `BETWEEN` te miente. Por eso en producción se escribe\n", "`>= '2026-01-01' AND fecha < '2026-04-01'`, que es correcto\n", "lleve hora o no." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 6. Fin de semana por WhatsApp\n", "\n", "Cuántos pedidos de WhatsApp cayeron en sábado o domingo." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "q(\"\"\"\n", "SELECT COUNT(*) AS whatsapp_fin_de_semana\n", "FROM pedidos\n", "WHERE canal = 'WhatsApp' AND STRFTIME('%w', fecha) IN ('0', '6');\n", "\"\"\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "whatsapp_fin_de_semana\n", "----------------------\n", "70\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Setenta de los 231 pedidos de WhatsApp, o sea un 30%, que es casi\n", "exactamente lo que pesan dos días de siete. Un dato que suena a hallazgo y no lo\n", "es: antes de contárselo a nadie, compara siempre contra lo que saldría por puro\n", "azar 📊" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 7. Escríbelo para los cuatro\n", "\n", "Sin ejecutar: los pedidos de los últimos 90 días contados\n", "desde hoy, en los cuatro motores." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "-- PostgreSQL\n", "SELECT COUNT(*) FROM pedidos WHERE fecha >= CURRENT_DATE - INTERVAL '90 days';\n", "\n", "-- MySQL\n", "SELECT COUNT(*) FROM pedidos WHERE fecha >= DATE_SUB(CURDATE(), INTERVAL 90 DAY);\n", "\n", "-- SQL Server\n", "SELECT COUNT(*) FROM pedidos WHERE fecha >= DATEADD(day, -90, CAST(GETDATE() AS DATE));\n", "\n", "-- SQLite\n", "SELECT COUNT(*) FROM pedidos WHERE fecha >= DATE('now', '-90 days');\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Cuatro formas de escribir exactamente lo mismo. No hay salida publicada\n", "porque el resultado cambia cada día que la corras, y en este libro no se publica\n", "una salida que no se pueda comprobar.\n", "\n", "Sobre nuestra base te va a dar un número distinto al mío según el día que la\n", "corras, porque \"hoy\" se mueve y los pedidos no. Y en algún momento va a dar\n", "cero, porque el último pedido de la tienda es del 24 de junio de 2026. Los datos\n", "de práctica también envejecen 🐣" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Cuando septiembre es mayor que octubre" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Ya viste que aquí las fechas son texto. Esto es lo que pasa cuando se te olvida por un segundo, y pasa en una sola línea 🫠" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### La trampa\n", "\n", "Filtras por fecha. Alguien del equipo te pasa el rango escrito a mano y lo pegas tal cual en la consulta.\n", "\n", "```\n", "SELECT '2025-9-1' > '2025-10-01';\n", "-- 1\n", "```\n", "\n", "**Qué está mal**\n", "\n", "Septiembre es mayor que octubre 🫠 Y tiene todo el sentido del mundo, porque eso no son fechas: son textos. Y comparando letra a letra, el `9` va después del `1`.\n", "\n", "En SQLite las fechas se guardan como texto, así que el orden solo funciona si están escritas **siempre con el mismo formato y con el cero delante**: `2025-09-01`. Eso es lo que hace que ISO 8601 sea el formato correcto y no una manía: es el único que se ordena bien siendo texto.\n", "\n", "Un rango escrito sin el cero no falla, filtra mal. Te devuelve filas de más o de menos y el reporte sale con un número que nadie puede cuadrar.\n", "\n", "Antes de fiarte de un filtro de fechas, míralas: `SELECT MIN(fecha), MAX(fecha) FROM pedidos`. Si el máximo no es la fecha más reciente que esperabas, ahí tienes el formato." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Comprueba que lo tienes\n", "\n", "En SQLite guardaste las fechas como texto y filtras con WHERE fecha >= '2026-1-5'. No sale ninguna venta de enero.\n", "\n", "a) Sin el cero delante, el texto no ordena como ordena el calendario\n", "\n", "b) SQLite no sabe comparar fechas\n", "\n", "c) Hay que convertirlas con CAST antes de comparar\n", "\n", "d) Falta escribirlo como DATE '2026-1-5'\n", "\n", "---\n", "\n", "**La correcta es la a.**\n", "\n", "*b)* Las compara perfectamente, siempre que estén escritas en el formato que ordena bien.\n", "\n", "*c)* El CAST no arregla un texto que ya está mal escrito.\n", "\n", "*d)* El problema no es el envoltorio, es el cero que falta.\n", "\n", "En texto," ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Lo que te llevas" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "- 🧼 Antes de agrupar por un texto: `UPPER(TRIM(columna))`. Doce\n", "marcas se convirtieron en veintitrés por saltarse esto.\n", "\n", "- 🔗 Pegar texto es `||` en PostgreSQL y SQLite,\n", "`CONCAT()` en MySQL y `+` en SQL Server.\n", "`CONCAT()` es la que funciona en los cuatro.\n", "\n", "- 🔍 `LIKE` distingue mayúsculas en PostgreSQL y no en los otros\n", "tres. `UPPER(columna) LIKE '%TEXTO%'` da el mismo número\n", "siempre.\n", "\n", "- 📅 SQLite no tiene tipo fecha: son textos en formato AAAA-MM-DD, y ese\n", "formato es obligatorio o las comparaciones mienten sin avisar.\n", "\n", "- 🚫 `YEAR()` no existe en SQLite ni en PostgreSQL. En SQLite todo\n", "sale de `STRFTIME`, y hay que envolverlo en `CAST` para\n", "tener un número.\n", "\n", "- ⚠️ `DATEDIFF` se llama igual en MySQL y en SQL Server y recibe\n", "los argumentos al revés.\n", "\n", "- 🎀 El formato bonito va en el `SELECT`, nunca en el\n", "`WHERE` ni en el `ORDER BY`.\n", "\n", "Y si de todo el capítulo te llevas una sola frase, que sea esta:" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Para la base, `Market Central` y `MARKET CENTRAL` son\n", "dos clientes distintos. Y no te avisa." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Esa limpieza de texto también se hace del otro lado, cuando los datos ya\n", "salieron de la base, y ahí los métodos `.str` hacen lo mismo que\n", "el `TRIM` y el `UPPER` de acá:\n", "[libro de Python](https://missyera.com/guias/python-desde-cero/) 🧼\n", "\n", "En el capítulo 6 llega `GROUP BY`, que es donde el SQL deja de\n", "listar filas y empieza a responder preguntas. Y donde PostgreSQL te va a exigir\n", "una cosa que MySQL te deja pasar, con consecuencias.\n", "\n", "Que tengas lindo día! 🌸" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "---\n", "\n", "Ese era el capítulo 5 de **SQL desde cero**. El texto completo, con las salidas de cada bloque, está en https://missyera.com/guias/sql-desde-cero/texto-y-fechas/\n", "\n", "Que tengas lindo día! 🌸" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "name": "python", "version": "3.11" } }, "nbformat": 4, "nbformat_minor": 5 }