{
 "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 soluciones 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",
    "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": [
    "## 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": [
    "q(\"\"\"\n",
    "SELECT SUM(CASE WHEN monto > 1000 THEN 1 ELSE 0 END) / COUNT(*) AS mal,\n",
    "       ROUND(SUM(CASE WHEN monto > 1000 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS bien\n",
    "FROM pedidos;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "mal  bien\n",
    "---  ----\n",
    "0    8.1\n",
    "```"
   ]
  },
  {
   "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": [
    "q(\"\"\"\n",
    "SELECT COUNT(*) AS filas,\n",
    "       COUNT(monto) AS con_valor,\n",
    "       COUNT(*) - COUNT(monto) AS nulos\n",
    "FROM pedidos;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "filas  con_valor  nulos\n",
    "-----  ---------  -----\n",
    "900    873        27\n",
    "```"
   ]
  },
  {
   "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": [
    "q(\"\"\"\n",
    "SELECT 500 + NULL AS sin_arreglar,\n",
    "       500 + COALESCE(NULL, 0) AS arreglado;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "sin_arreglar  arreglado\n",
    "------------  ---------\n",
    "              500\n",
    "```"
   ]
  },
  {
   "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": [
    "q(\"\"\"\n",
    "SELECT precio,\n",
    "       CAST(precio AS INTEGER) AS truncado,\n",
    "       ROUND(precio) AS redondeado\n",
    "FROM productos\n",
    "ORDER BY precio DESC\n",
    "LIMIT 1;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "precio  truncado  redondeado\n",
    "------  --------  ----------\n",
    "89.35   89        89.0\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Un céntimo por fila no suena a nada, y en un millón de filas es dinero."
   ]
  },
  {
   "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": [
    "q(\"\"\"\n",
    "SELECT typeof(id) AS tipo_id,\n",
    "       typeof(fecha) AS tipo_fecha,\n",
    "       typeof(monto) AS tipo_monto\n",
    "FROM pedidos LIMIT 1;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "tipo_id  tipo_fecha  tipo_monto\n",
    "-------  ----------  ----------\n",
    "integer  text        real\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "La fecha es `text`. En PostgreSQL, MySQL y SQL Server sería un tipo\n",
    "fecha de verdad, y ahí sí puedes restar dos fechas directamente."
   ]
  },
  {
   "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": [
    "q(\"\"\"\n",
    "SELECT ROUND(AVG(monto), 2) AS avg_ignora_nulos,\n",
    "       ROUND(SUM(COALESCE(monto, 0)) * 1.0 / COUNT(*), 2) AS nulos_como_cero\n",
    "FROM pedidos;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "avg_ignora_nulos  nulos_como_cero\n",
    "----------------  ---------------\n",
    "610.14            591.84\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "El primero divide entre las filas con valor; el segundo entre todas. Cuál es\n",
    "el correcto depende de qué significa el hueco, y eso lo sabe quien cargó los\n",
    "datos, no la consulta."
   ]
  },
  {
   "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": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "-- PostgreSQL\n",
    "SELECT ROUND(SUM(monto)::numeric / COUNT(*), 2) FROM pedidos;\n",
    "\n",
    "-- MySQL (el único que divide decimal por su cuenta)\n",
    "SELECT ROUND(SUM(monto) / COUNT(*), 2) FROM pedidos;\n",
    "\n",
    "-- SQL Server\n",
    "SELECT ROUND(CAST(SUM(monto) AS DECIMAL(12,2)) / COUNT(*), 2) FROM pedidos;\n",
    "\n",
    "-- SQLite\n",
    "SELECT ROUND(SUM(monto) * 1.0 / COUNT(*), 2) FROM pedidos;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y en los cuatro existe `AVG(monto)`, que hace lo mismo en una\n",
    "palabra. Cuando exista la función, úsala: es más corta y no tiene el problema de\n",
    "la división entera 🌟"
   ]
  },
  {
   "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": [
    "q(\"\"\"\n",
    "SELECT id, monto\n",
    "FROM pedidos\n",
    "ORDER BY monto IS NULL, monto\n",
    "LIMIT 5;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "id   monto\n",
    "---  -----\n",
    "852  5.05\n",
    "708  5.77\n",
    "52   16.48\n",
    "557  16.62\n",
    "351  42.04\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ahí está el truco entero: `monto IS NULL` devuelve 0 o 1, y\n",
    "ordenar por eso primero manda todos los huecos al final 🎯\n",
    "\n",
    "Sin esa línea, en SQLite esta misma consulta te devuelve cinco filas vacías y\n",
    "ni un solo monto, porque los nulos van primero. Y en PostgreSQL te habría\n",
    "funcionado sin ponerla, que es justo lo que hace que el error viaje: en un motor\n",
    "sale bien por casualidad y en el otro no.\n",
    "\n",
    "La versión declarada de esto es `ORDER BY monto NULLS LAST`, que\n",
    "existe en PostgreSQL y en Oracle pero no en SQLite ni en MySQL. El\n",
    "`IS NULL` funciona en los cuatro."
   ]
  },
  {
   "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": [
    "q(\"\"\"\n",
    "SELECT ROUND(SUM(monto), 2)               AS suma_ignorando,\n",
    "       ROUND(SUM(COALESCE(monto, 0)), 2)  AS suma_con_cero,\n",
    "       ROUND(AVG(monto), 2)               AS promedio_ignorando,\n",
    "       ROUND(AVG(COALESCE(monto, 0)), 2)  AS promedio_con_cero\n",
    "FROM pedidos;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "suma_ignorando  suma_con_cero  promedio_ignorando  promedio_con_cero\n",
    "--------------  -------------  ------------------  -----------------\n",
    "532653.85       532653.85      610.14              591.84\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Las dos sumas dan **exactamente lo mismo**, y los dos promedios\n",
    "no 🤨\n",
    "\n",
    "La suma no cambia porque sumar cero no suma nada, y `SUM` ya\n",
    "ignoraba esos huecos de todas formas. El promedio sí cambia porque el\n",
    "`COALESCE` convirtió 27 desconocidos en 27 pedidos de cero soles, y\n",
    "esos 27 ahora sí entran en el reparto.\n",
    "\n",
    "De ahí sale la regla que uso: **rellenar nulos con cero es una decisión\n",
    "sobre el denominador, no sobre el numerador**. Y hay que tomarla a\n",
    "sabiendas, no por costumbre de escribir `COALESCE` en todo."
   ]
  },
  {
   "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": [
    "q(\"\"\"\n",
    "SELECT COUNT(*)               AS todas,\n",
    "       COUNT(monto)           AS con_valor,\n",
    "       COUNT(DISTINCT canal)  AS canales\n",
    "FROM pedidos;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "todas  con_valor  canales\n",
    "-----  ---------  -------\n",
    "900    873        4\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Tres números que se parecen y contestan tres preguntas distintas 🔢\n",
    "\n",
    "- 🧾 `COUNT(*)` cuenta **filas**, mire lo que mire.\n",
    "\n",
    "- 💵 `COUNT(monto)` cuenta **valores que hay**. La\n",
    "diferencia con el primero son los huecos, y aquí son 27.\n",
    "\n",
    "- 🏷️ `COUNT(DISTINCT canal)` cuenta **valores\n",
    "diferentes**. Y también se salta los nulos, aunque aquí no haya.\n",
    "\n",
    "El truco de restar los dos primeros es la forma más corta que conozco de\n",
    "contar huecos sin escribir un `CASE`."
   ]
  },
  {
   "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": [
    "q(\"\"\"\n",
    "SELECT c.segmento,\n",
    "       COUNT(*)                                          AS pedidos,\n",
    "       SUM(CASE WHEN p.monto IS NULL THEN 1 ELSE 0 END)  AS sin_monto,\n",
    "       ROUND(AVG(p.monto), 2)                            AS ticket\n",
    "FROM pedidos p\n",
    "JOIN clientes c ON c.id = p.id_cliente\n",
    "GROUP BY c.segmento\n",
    "ORDER BY ticket DESC;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "segmento    pedidos  sin_monto  ticket\n",
    "----------  -------  ---------  ------\n",
    "Minimarket  174      8          630.43\n",
    "Horeca      257      8          610.05\n",
    "Mayorista   194      3          607.59\n",
    "Bodega      251      8          596.05\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ocho, ocho, ocho y tres. Repartidos, salvo Mayorista que tiene menos 🧐\n",
    "\n",
    "Y aquí hay que tener cuidado con lo que se concluye. Mayorista tiene 3 huecos\n",
    "sobre 194 pedidos y Bodega tiene 8 sobre 251: son 1,5% contra 3,2%. Parece el\n",
    "doble, pero estamos hablando de cinco pedidos de diferencia, y con números tan\n",
    "chicos eso se mueve solo.\n",
    "\n",
    "Lo honesto es decir que **no se ve un patrón**, no inventar uno.\n",
    "Cuando la diferencia son cinco filas, lo que hace falta no es una consulta más\n",
    "lista: son más datos."
   ]
  },
  {
   "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**\n",
    "\n",
    "La misma pregunta y dos respuestas distintas. La verdadera es 1 😬\n",
    "\n",
    "La tabla `pedidos` tiene 24 filas con el `id_cliente` en NULL. Y `NOT IN` con una lista que contiene un NULL **no devuelve nada nunca**. Por dentro es \"id distinto de 3 Y distinto de 7 Y distinto de NULL\", y esa última comparación es desconocida, así que la cadena entera deja de ser verdadera para todo el mundo.\n",
    "\n",
    "Y fíjate en la forma que tiene de fallar, que es la peor de todas: no da error, no da un número raro, da **cero**. Y cero se lee como \"qué bien, no hay ningún cliente sin comprar\" en vez de como \"esta consulta está rota\".\n",
    "\n",
    "La regla que uso: `NOT IN` solo contra una lista que yo escribí a mano. Contra una subconsulta, siempre `NOT EXISTS`, que trata los nulos como cualquiera esperaría."
   ]
  },
  {
   "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\n",
    "\n",
    "---\n",
    "\n",
    "**La correcta es la a.**\n",
    "\n",
    "*b)* Los dos significan lo mismo y los dos dan el mismo resultado aquí.\n",
    "\n",
    "*c)* Si contaran como cero, esas 500 filas habrían pasado el filtro.\n",
    "\n",
    "*d)* La consulta corre sin error. El problema es qué hace con los huecos.\n",
    "\n",
    "Con un NULL, cualquier comparación devuelve desconocido, y el WHERE solo deja pasar lo que es verdadero."
   ]
  },
  {
   "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
}