{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tipos de dato y nulos\n",
    "\n",
    "Donde están los errores más caros y más silenciosos: la división que da cero y el nulo que se come una suma.\n",
    "\n",
    "Cuaderno de práctica del capítulo 4 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/tipos-y-nulos/\n",
    "\n",
    "Los ejercicios están al final y traen una celda vacía debajo de cada uno. Las\n",
    "respuestas viven en el cuaderno de soluciones, y merece la pena pelearse un\n",
    "rato antes de abrirlo 💛"
   ]
  },
  {
   "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": [
    "## Antes de empezar\n",
    "\n",
    "Esta celda baja el ayudante que corrige tus ejercicios. Después, en cada\n",
    "ejercicio que se pueda corregir solo, vas a ver `%%revisa` arriba de la celda:\n",
    "escribe tu respuesta debajo, ejecuta, y te digo si te salió 💛"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import urllib.request\n",
    "\n",
    "# El ayudante de los cuadernos. Trae la corrección de los ejercicios y, en los\n",
    "# capítulos de consola, la celda mágica que ejecuta los comandos. Se baja en\n",
    "# vez de venir pegado aquí para que siempre sea el último.\n",
    "urllib.request.urlretrieve(\n",
    "    \"https://missyera.com/static/cuadernos/revisa.py\", \"revisa.py\")\n",
    "import revisa\n",
    "revisa.carga({\n",
    "    1: \"bWFsICBiaWVuCi0tLSAgLS0tLQowICAgIDguMQ==\",\n",
    "    2: \"ZmlsYXMgIGNvbl92YWxvciAgbnVsb3MKLS0tLS0gIC0tLS0tLS0tLSAgLS0tLS0KOTAwICAgIDg3MyAgICAgICAgMjc=\",\n",
    "    3: \"c2luX2FycmVnbGFyICBhcnJlZ2xhZG8KLS0tLS0tLS0tLS0tICAtLS0tLS0tLS0KICAgICAgICAgICAgICA1MDA=\",\n",
    "    4: \"cHJlY2lvICB0cnVuY2FkbyAgcmVkb25kZWFkbwotLS0tLS0gIC0tLS0tLS0tICAtLS0tLS0tLS0tCjg5LjM1ICAgODkgICAgICAgIDg5LjA=\",\n",
    "    5: \"dGlwb19pZCAgdGlwb19mZWNoYSAgdGlwb19tb250bwotLS0tLS0tICAtLS0tLS0tLS0tICAtLS0tLS0tLS0tCmludGVnZXIgIHRleHQgICAgICAgIHJlYWw=\",\n",
    "    6: \"YXZnX2lnbm9yYV9udWxvcyAgbnVsb3NfY29tb19jZXJvCi0tLS0tLS0tLS0tLS0tLS0gIC0tLS0tLS0tLS0tLS0tLQo2MTAuMTQgICAgICAgICAgICA1OTEuODQ=\",\n",
    "    8: \"aWQgICBtb250bwotLS0gIC0tLS0tCjg1MiAgNS4wNQo3MDggIDUuNzcKNTIgICAxNi40OAo1NTcgIDE2LjYyCjM1MSAgNDIuMDQ=\",\n",
    "    9: \"c3VtYV9pZ25vcmFuZG8gIHN1bWFfY29uX2Nlcm8gIHByb21lZGlvX2lnbm9yYW5kbyAgcHJvbWVkaW9fY29uX2Nlcm8KLS0tLS0tLS0tLS0tLS0gIC0tLS0tLS0tLS0tLS0gIC0tLS0tLS0tLS0tLS0tLS0tLSAgLS0tLS0tLS0tLS0tLS0tLS0KNTMyNjUzLjg1ICAgICAgIDUzMjY1My44NSAgICAgIDYxMC4xNCAgICAgICAgICAgICAgNTkxLjg0\",\n",
    "    10: \"dG9kYXMgIGNvbl92YWxvciAgY2FuYWxlcwotLS0tLSAgLS0tLS0tLS0tICAtLS0tLS0tCjkwMCAgICA4NzMgICAgICAgIDQ=\",\n",
    "    11: \"c2VnbWVudG8gICAgcGVkaWRvcyAgc2luX21vbnRvICB0aWNrZXQKLS0tLS0tLS0tLSAgLS0tLS0tLSAgLS0tLS0tLS0tICAtLS0tLS0KTWluaW1hcmtldCAgMTc0ICAgICAgOCAgICAgICAgICA2MzAuNDMKSG9yZWNhICAgICAgMjU3ICAgICAgOCAgICAgICAgICA2MTAuMDUKTWF5b3Jpc3RhICAgMTk0ICAgICAgMyAgICAgICAgICA2MDcuNTkKQm9kZWdhICAgICAgMjUxICAgICAgOCAgICAgICAgICA1OTYuMDU=\",\n",
    "}, lenguaje=\"sql\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Hola! Bienvenida al capítulo de los errores silenciosos"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Todo lo de este capítulo tiene algo en común: **no da error**. Te\n",
    "devuelve un número, tú lo pones en un reporte, y está mal 😬\n",
    "\n",
    "Los que dan error se arreglan solos porque los ves. Estos hay que\n",
    "conocerlos.\n",
    "\n",
    "Y dime si te suena: **¿alguna vez entregaste un reporte y el total no cuadraba por poquito?** Ese poquito casi siempre está en este capítulo, y casi siempre tiene forma de NULL 🕳️"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Los tipos que vas a usar"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Un tipo dice qué se puede guardar en una columna y cómo se opera con ella.\n",
    "Son muchos y en la práctica se reducen a cinco familias."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Texto de largo variable\n",
    "\n",
    "| PostgreSQL | `VARCHAR(100) o TEXT   -- TEXT no tiene penalización` |\n",
    "|---|---|\n",
    "| MySQL | `VARCHAR(100) o TEXT   -- TEXT no se puede indexar entero` |\n",
    "| SQL Server | `NVARCHAR(100) o NVARCHAR(MAX)   -- la N es para Unicode` |\n",
    "| SQLite | `TEXT   -- solo hay uno y acepta cualquier largo` |\n",
    "\n",
    "La N de SQL Server importa en Perú: sin ella, VARCHAR puede no guardar bien las tildes y las eñes según la configuración. Usa siempre NVARCHAR."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Dinero y decimales exactos\n",
    "\n",
    "| PostgreSQL | `NUMERIC(12, 2)` |\n",
    "|---|---|\n",
    "| MySQL | `DECIMAL(12, 2)` |\n",
    "| SQL Server | `DECIMAL(12, 2) o MONEY` |\n",
    "| SQLite | `REAL   -- no tiene decimal exacto, y eso importa` |\n",
    "\n",
    "Para plata NUNCA uses FLOAT ni REAL: son aproximados y los centavos se van perdiendo. DECIMAL y NUMERIC son exactos. SQLite no tiene ninguno de los dos, así que ahí el dinero se suele guardar en céntimos como entero."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fecha con hora\n",
    "\n",
    "| PostgreSQL | `TIMESTAMP o TIMESTAMPTZ   -- el TZ guarda la zona horaria` |\n",
    "|---|---|\n",
    "| MySQL | `DATETIME o TIMESTAMP` |\n",
    "| SQL Server | `DATETIME2   -- DATETIME es el viejo y tiene menos precisión` |\n",
    "| SQLite | `TEXT en formato ISO   -- no hay tipo fecha` |\n",
    "\n",
    "SQLite guarda las fechas como texto, y funciona porque el formato AAAA-MM-DD se ordena igual como texto que como fecha. Por eso el formato internacional no es una manía: es lo que hace que ordene bien."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y una diferencia de fondo que explica muchas cosas raras de SQLite:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT typeof(id), typeof(nombre), typeof(precio)\n",
    "FROM productos LIMIT 1;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SQLite tiene **tipado dinámico**: el tipo lo lleva cada valor, no\n",
    "la columna. Puedes meter un texto en una columna declarada como entero y lo\n",
    "acepta. Los otros tres te lo rechazan de plano.\n",
    "\n",
    "Eso es cómodo mientras aprendes y es una fuente de desastres en producción,\n",
    "porque un dato mal cargado entra sin que nadie se entere 🙃"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## La división que devuelve cero"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Este es el error más caro del capítulo y no da ningún aviso."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT 1 / 2 AS mitad;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cero. Porque los dos son enteros, y en SQL **entero dividido entero da\n",
    "entero**. Se corta la parte decimal y ya."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT 1 * 1.0 / 2 AS mitad,\n",
    "       CAST(1 AS REAL) / 2 AS tambien_mitad;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se arregla haciendo que uno de los dos sea decimal, y hay dos maneras:\n",
    "multiplicar por 1.0 o convertir con `CAST`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Dividir sin perder los decimales\n",
    "\n",
    "| PostgreSQL | `SELECT monto::numeric / cantidad   -- o CAST(monto AS numeric)` |\n",
    "|---|---|\n",
    "| MySQL | `SELECT monto / cantidad   -- MySQL ya devuelve decimal, es el raro` |\n",
    "| SQL Server | `SELECT CAST(monto AS DECIMAL(12,2)) / cantidad` |\n",
    "| SQLite | `SELECT monto * 1.0 / cantidad` |\n",
    "\n",
    "MySQL es el único que hace la división decimal por su cuenta. Si aprendes ahí y pasas a otro motor, tus porcentajes se convierten en ceros de golpe."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Dónde muerde de verdad, y lo he visto en informes de empresa:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT canal,\n",
    "       COUNT(*) AS pedidos,\n",
    "       SUM(CASE WHEN monto > 1000 THEN 1 ELSE 0 END) / COUNT(*) AS mal,\n",
    "       SUM(CASE WHEN monto > 1000 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS bien\n",
    "FROM pedidos\n",
    "GROUP BY canal;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "La columna `mal` da cero en todos los canales. Un porcentaje entero\n",
    "que sale cero siempre es esto 💡"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## NULL, que no es cero ni vacío"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "NULL significa **\"no se sabe\"**. No es el número cero, no es el\n",
    "texto vacío, no es un espacio. Es la ausencia del dato.\n",
    "\n",
    "Y de ahí sale su comportamiento, que al principio parece caprichoso y en\n",
    "realidad es coherente."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT 100 + NULL AS suma,\n",
    "       'Lima' || NULL AS texto,\n",
    "       NULL = NULL AS son_iguales;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Todo NULL. Y tiene lógica: si no sé cuánto es una cosa, tampoco sé cuánto es\n",
    "esa cosa más cien. Y no puedo afirmar que dos cosas que no conozco sean\n",
    "iguales 🤷\n",
    "\n",
    "### Dónde te muerde en serio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT COUNT(*) AS todas,\n",
    "       COUNT(monto) AS con_monto,\n",
    "       COUNT(id_cliente) AS con_cliente\n",
    "FROM pedidos;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**`COUNT(*)` cuenta filas y `COUNT(columna)`\n",
    "cuenta valores no nulos.** Esa diferencia, si no la sabes, te cambia\n",
    "cualquier conteo.\n",
    "\n",
    "Lo mismo con los promedios:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT ROUND(AVG(monto), 2) AS promedio_sql,\n",
    "       ROUND(SUM(monto) * 1.0 / COUNT(*), 2) AS sobre_todas_las_filas\n",
    "FROM pedidos;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Dan distinto porque `AVG` ignora los nulos: divide entre los que\n",
    "tienen valor, no entre todas las filas. Ninguno de los dos está mal; lo que está\n",
    "mal es no saber cuál te dio tu reporte 🔍"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## COALESCE, que es el estándar"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT id, monto, COALESCE(monto, 0) AS con_repuesto\n",
    "FROM pedidos\n",
    "WHERE monto IS NULL\n",
    "LIMIT 3;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Si es nulo, ponme otro valor\n",
    "\n",
    "| PostgreSQL | `COALESCE(monto, 0)` |\n",
    "|---|---|\n",
    "| MySQL | `COALESCE(monto, 0) o IFNULL(monto, 0)` |\n",
    "| SQL Server | `COALESCE(monto, 0) o ISNULL(monto, 0)` |\n",
    "| SQLite | `COALESCE(monto, 0) o IFNULL(monto, 0)` |\n",
    "\n",
    "COALESCE está en los cuatro y además acepta varios repuestos en cadena: COALESCE(a, b, c, 0) devuelve el primero que no sea nulo. Los atajos de cada motor solo aceptan dos."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y el aviso importante: **rellenar con cero no siempre está bien**.\n",
    "Si el monto es nulo porque nadie lo cargó, ponerle cero convierte \"no sé\" en \"no\n",
    "vendió\", y eso te baja el promedio con datos inventados. A veces la respuesta\n",
    "correcta es dejarlo nulo y decir cuántos había 💜"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Convertir tipos con CAST"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT CAST('2026-01-15' AS TEXT) AS fecha_texto,\n",
    "       CAST('123' AS INTEGER) + 1 AS numero,\n",
    "       CAST(89.7 AS INTEGER) AS trunca;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fíjate en el último: `CAST` a entero **trunca, no\n",
    "redondea**. 89,7 se convierte en 89. Si querías redondear, es\n",
    "`ROUND`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT ROUND(89.7) AS redondea, CAST(89.7 AS INTEGER) AS trunca;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Convertir a número\n",
    "\n",
    "| PostgreSQL | `CAST('123' AS INTEGER) o '123'::integer` |\n",
    "|---|---|\n",
    "| MySQL | `CAST('123' AS SIGNED)   -- ojo, no acepta INTEGER` |\n",
    "| SQL Server | `CAST('123' AS INT) o CONVERT(INT, '123')` |\n",
    "| SQLite | `CAST('123' AS INTEGER)` |\n",
    "\n",
    "CAST es estándar y está en los cuatro; lo que cambia es el nombre del tipo de destino. MySQL pide SIGNED donde los otros piden INTEGER o INT."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ese `::integer` de PostgreSQL es cortito y engancha, así que se\n",
    "copia muchísimo. Mira lo que hace fuera de su casa."
   ]
  },
  {
   "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 SUM(monto)::numeric FROM pedidos;\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: unrecognized token: \":\"\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*unrecognized token* quiere decir \"no sé ni qué es ese símbolo\". Los\n",
    "dos puntos dobles son de PostgreSQL y de nadie más: no están en MySQL, ni en SQL\n",
    "Server, ni en SQLite. `CAST(algo AS tipo)` es más largo de escribir y\n",
    "te sirve en los cuatro 💛"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Verdadero, falso y una tercera cosa"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Aquí está la idea que hace que todo lo anterior encaje, y casi nadie la\n",
    "cuenta: en SQL una comparación no devuelve dos valores, devuelve tres 🎲"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT COUNT(*) AS total,\n",
    "       SUM(CASE WHEN monto > 500 THEN 1 ELSE 0 END) AS mayores,\n",
    "       SUM(CASE WHEN NOT (monto > 500) THEN 1 ELSE 0 END) AS no_mayores\n",
    "FROM pedidos;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Mira la suma: 558 más 315 dan 873, y la tabla tiene 900 pedidos.\n",
    "**Faltan 27** 😳\n",
    "\n",
    "Son los 27 pedidos sin monto. No están en \"mayores que 500\" y tampoco en \"no\n",
    "mayores que 500\", porque para ellos la comparación no es ni verdadera ni falsa:\n",
    "es **desconocida**. Y el `NOT` de un desconocido sigue\n",
    "siendo desconocido.\n",
    "\n",
    "Eso es lo que explica todos los sustos de este capítulo de una vez:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT NULL = NULL       AS son_iguales,\n",
    "       NULL <> NULL      AS son_distintos,\n",
    "       NULL IS NULL      AS es_nulo;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Las dos primeras salen vacías, que es como se ve un desconocido. La tercera\n",
    "sale 1 🎯\n",
    "\n",
    "Por eso `IS NULL` no es una manía de la sintaxis: es el único\n",
    "operador que contesta verdadero o falso cuando hay un hueco. Todos los demás se\n",
    "encogen de hombros.\n",
    "\n",
    "Y la regla práctica, que vale para el resto del libro: **si una cuenta\n",
    "tiene que cuadrar con el total de filas, cuenta los tres casos, no dos**.\n",
    "El tercero es el que se escapa."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## El promedio que no es el promedio"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Esta es la consecuencia cara, y la vas a ver en un informe algún día 💸"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT ROUND(SUM(monto), 2)               AS suma,\n",
    "       COUNT(*)                            AS filas,\n",
    "       ROUND(SUM(monto) / COUNT(*), 2)     AS a_mano,\n",
    "       ROUND(AVG(monto), 2)                AS con_avg\n",
    "FROM pedidos;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Dieciocho soles de diferencia por pedido, con los mismos datos y en la misma\n",
    "consulta 😖\n",
    "\n",
    "Los dos están bien calculados y contestan preguntas distintas.\n",
    "`AVG` divide entre los pedidos **que tienen monto**, o\n",
    "sea 873. El de la izquierda divide entre **todas las filas**, 900,\n",
    "repartiendo la suma también entre los 27 que no aportaron nada.\n",
    "\n",
    "Cuál está bien depende de lo que preguntaste 🤔\n",
    "\n",
    "- 💰 **\"¿Cuánto vale un pedido típico?\"** Ahí van los 873, porque\n",
    "un pedido sin monto no es un pedido de cero soles: es un pedido del que no\n",
    "sabemos el monto.\n",
    "\n",
    "- 📉 **\"¿Cuánto facturamos por pedido registrado?\"** Ahí van los\n",
    "900, y estás midiendo también lo mal que se registra.\n",
    "\n",
    "Lo que no vale es no saber cuál de las dos estás calculando, que es lo que\n",
    "pasa cuando uno escribe `AVG` sin mirar los huecos."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## NULLIF, que es COALESCE al revés"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`COALESCE` cambia un nulo por un valor. `NULLIF` hace lo\n",
    "contrario: cambia un valor por un nulo. Y suena inútil hasta que ves para qué 🔧"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT 100 / 0                AS entre_cero,\n",
    "       100 / NULLIF(0, 0)     AS con_nullif;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Las dos salen vacías, y aquí hay una trampa que solo se ve sabiendo dónde\n",
    "estás parada 🪤\n",
    "\n",
    "En SQLite, **dividir entre cero devuelve NULL** y la consulta\n",
    "sigue como si nada. En PostgreSQL, MySQL en modo estricto y SQL Server, esa\n",
    "misma línea **revienta** con un error de división por cero y te tira\n",
    "el reporte entero.\n",
    "\n",
    "O sea que si desarrollas en SQLite y despliegas en Postgres, este es\n",
    "exactamente el tipo de cosa que funciona en tu máquina y falla el día de la\n",
    "presentación 😬\n",
    "\n",
    "`NULLIF(divisor, 0)` convierte el cero en nulo antes de dividir, y\n",
    "un nulo no revienta en ningún motor: devuelve nulo. Es la forma portátil de\n",
    "protegerse.\n",
    "\n",
    "Juntando los dos, que es como se usan de verdad:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT canal,\n",
    "       COUNT(*)                                                        AS pedidos,\n",
    "       SUM(CASE WHEN monto IS NULL THEN 1 ELSE 0 END)                  AS sin_monto,\n",
    "       ROUND(COALESCE(100.0 * SUM(CASE WHEN monto IS NULL THEN 1 ELSE 0 END)\n",
    "                      / NULLIF(COUNT(*), 0), 0), 1)                    AS pct\n",
    "FROM pedidos\n",
    "GROUP BY canal\n",
    "ORDER BY pct DESC;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y la respuesta es tranquilizadora: entre el 2,6% y el 3,9% en los cuatro\n",
    "canales. **No hay un canal culpable**, el registro falla parejo en\n",
    "todos 🤷‍♀️\n",
    "\n",
    "Eso también es un hallazgo y hay que decirlo. Si un canal tuviera el 20%, la\n",
    "conversación sería con esa gente; como están todos igual, la conversación es\n",
    "sobre el proceso."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dónde se ponen los nulos al ordenar"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT id, monto FROM pedidos ORDER BY monto LIMIT 5;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Los nulos salen **primero**. Y ahora al revés:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT id, monto FROM pedidos ORDER BY monto DESC LIMIT 5;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Al final, así que aquí no se ven 👀\n",
    "\n",
    "Esto importa más de lo que parece. Si pides \"los cinco pedidos más pequeños\"\n",
    "y ordenas ascendente, en SQLite te salen cinco filas vacías y ni un monto. El\n",
    "top se lo comen los huecos.\n",
    "\n",
    "Y no es igual en todos los motores, que es lo peor que puede pasar:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Dónde caen los nulos al ordenar\n",
    "\n",
    "| PostgreSQL | `ORDER BY monto            -- nulos al final` |\n",
    "|---|---|\n",
    "| MySQL | `ORDER BY monto            -- nulos al principio` |\n",
    "| SQL Server | `ORDER BY monto            -- nulos al principio` |\n",
    "| SQLite | `ORDER BY monto            -- nulos al principio` |\n",
    "\n",
    "Y la forma que funciona igual en los cuatro: ORDER BY monto IS NULL, monto. Ese IS NULL devuelve 0 o 1, así que ordenar por él primero manda los huecos al final digas lo que digas después."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "PostgreSQL y Oracle los tratan como el valor más grande, así que en orden\n",
    "ascendente salen al final; SQLite, MySQL y SQL Server los tratan como el más\n",
    "pequeño y salen al principio. La misma consulta, dos resultados 🙃\n",
    "\n",
    "La única forma de no depender de eso es decirlo tú, y por eso el\n",
    "`IS NULL` del final aparece tanto en el código de verdad: ordena\n",
    "primero por si es nulo, y solo después por el monto."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ejercicios"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. La división que engaña\n",
    "\n",
    "Calcula qué porcentaje de pedidos pasan de S/1000, primero\n",
    "mal y después bien."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 1\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. COUNT(*) contra COUNT(columna)\n",
    "\n",
    "Sobre pedidos, muestra las dos cuentas y cuántos nulos hay."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 2\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. El nulo que se come la suma\n",
    "\n",
    "Comprueba que sumar algo a NULL devuelve NULL, y arréglalo\n",
    "con COALESCE."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 3\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Truncar contra redondear\n",
    "\n",
    "Con el precio del producto más caro, muestra el valor\n",
    "original, truncado y redondeado."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 4\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. El tipo de cada valor\n",
    "\n",
    "Comprueba el tipado dinámico de SQLite: mira el tipo de tres\n",
    "columnas distintas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 5\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6. Promedio con nulos\n",
    "\n",
    "Calcula el monto promedio de las dos formas y explica por\n",
    "qué difieren."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 6\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7. Escríbelo para los cuatro\n",
    "\n",
    "Sin ejecutar: el ticket promedio por pedido con dos\n",
    "decimales, en los cuatro motores."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7. Los nulos donde tú digas\n",
    "\n",
    "Pide los cinco pedidos más baratos, de verdad."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 8\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 8. Rellenar con cero, ¿cambia algo?\n",
    "\n",
    "Calcula la suma y el promedio de dos formas: ignorando los\n",
    "nulos y tratándolos como cero."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 9\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 9. Las tres formas de contar, juntas\n",
    "\n",
    "Cuenta filas, valores y valores distintos en una sola\n",
    "consulta."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 10\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 10. Dónde se pierden los montos\n",
    "\n",
    "Mira si los pedidos sin monto se concentran en algún tipo\n",
    "de cliente."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%revisa 11\n",
    "-- tu turno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## La consulta que contesta cero y debería contestar uno"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Con todo lo del capítulo en la cabeza, mira esta. Es la trampa más cara de los nulos y la contesta mal casi todo el mundo la primera vez 😬"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### La trampa\n",
    "\n",
    "Quieres los clientes que nunca compraron. Lo escribes como se dice en español: los que no están en la lista de los que hicieron pedidos.\n",
    "\n",
    "```\n",
    "SELECT COUNT(*) FROM clientes\n",
    "WHERE id NOT IN (SELECT id_cliente FROM pedidos);\n",
    "-- 0\n",
    "\n",
    "SELECT COUNT(*) FROM clientes c\n",
    "WHERE NOT EXISTS (\n",
    "    SELECT 1 FROM pedidos p WHERE p.id_cliente = c.id);\n",
    "-- 1\n",
    "```\n",
    "\n",
    "**¿Qué está mal?** La respuesta está en el cuaderno de soluciones. Míralo tú primero."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Comprueba que lo tienes\n",
    "\n",
    "Filtras con WHERE descuento != 15 y esperabas 2.400 filas, pero salen 1.900. Compruebas y hay 500 filas con el descuento vacío.\n",
    "\n",
    "a) Los NULL no son distintos de 15 ni iguales: se caen del filtro\n",
    "\n",
    "b) El != no funciona en SQL, hay que usar <>\n",
    "\n",
    "c) Los nulos cuentan como cero y cero sí es distinto de 15\n",
    "\n",
    "d) Falta un paréntesis en la condición"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lo que te llevas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 💸 Para dinero, `DECIMAL` o `NUMERIC`, nunca\n",
    "`FLOAT`.\n",
    "\n",
    "- ➗ Entero entre entero da entero: multiplica por 1.0 o usa\n",
    "`CAST`. Un porcentaje que sale cero siempre es esto.\n",
    "\n",
    "- 🕳️ `NULL` es \"no se sabe\": cualquier operación con él da\n",
    "`NULL`.\n",
    "\n",
    "- 🔢 `COUNT(*)` cuenta filas, `COUNT(columna)` cuenta\n",
    "valores. `AVG` ignora los nulos.\n",
    "\n",
    "- 🧰 `COALESCE` funciona en los cuatro y acepta varios repuestos.\n",
    "\n",
    "- ✂️ `CAST` a entero trunca, no redondea.\n",
    "\n",
    "Y si de todo el capítulo te llevas una sola frase, que sea esta:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Un NULL no es cero ni vacío. Es un \"no se sabe\", y se contagia a todo lo\n",
    "que toca."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y qué hacer con los nulos no es una pregunta de SQL, es una pregunta de\n",
    "estadística: si los borras cambias el promedio y si los rellenas también.\n",
    "Está contado en el [libro de\n",
    "estadística](https://missyera.com/guias/estadistica-desde-cero/) 📐\n",
    "\n",
    "En el capítulo 5 vamos a texto y fechas, que es donde los cuatro motores más\n",
    "se separan y donde vas a agradecer tener la tabla al lado.\n",
    "\n",
    "Que tengas lindo día! 🌸"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "Ese era el capítulo 4 de **SQL desde cero**. El texto completo, con las salidas de cada bloque, está en https://missyera.com/guias/sql-desde-cero/tipos-y-nulos/\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
}