Nivel 1 · Una clave por ubicación

El capítulo 6 limpió una columna: UPPER(TRIM(state)). Los informes reales suelen agrupar por varias columnas desordenadas a la vez, así que necesitas una clave: un valor estandarizado que identifica lo que estás contando, construido a partir de partes limpias.

Construir una clave

Una clave tiene tres ingredientes: limpiar cada parte, elegir una sola capitalización y unir las partes con un separador que no pueda aparecer dentro de ellas.

CONCAT(UPPER(TRIM(state)), '-', LOWER(TRIM(city)))   -- 'NY-new york'

TRIM quita los espacios al principio y al final, UPPER y LOWER arreglan las mayúsculas, y CONCAT pega las piezas. Minúsculas es una buena elección para la parte de la ciudad: es una clave, no una etiqueta para mostrar. DuckDB no tiene INITCAP, así que poner bonito el título es trabajo de la capa de informes.

¿Por qué incluir el estado? Porque Harbor & Pine tiene clientes tanto en Portland, Oregon como en Portland, Maine. Con la ciudad sola como clave se fusionarían en una ubicación.

Medir el desorden

Cuenta los valores en bruto dentro de cada grupo limpio para ver cuánto hizo la limpieza:

SELECT
  CONCAT(UPPER(TRIM(state)), '-', LOWER(TRIM(city))) AS location_key,
  COUNT(*) AS customers,
  COUNT(DISTINCT city || '|' || state) AS raw_spellings
FROM customers
WHERE city IS NOT NULL
GROUP BY 1;

Con estos datos, los 2,004 clientes con ciudad tienen 168 pares distintos de ciudad y estado en bruto, que se reúnen en 62 ubicaciones. NY-new york tiene 181 clientes repartidos en 5 grafías, y TX-houston tiene 8. Los 14 clientes con ciudad NULL se dejan fuera a propósito: un NULL es un dato que falta, no una grafía, y recibe su propio tratamiento en el nivel 3.

Errores comunes

• Sin separador. COUNT(DISTINCT city || state) devuelve 163 en lugar de 168, porque 'Houston' || ' TX' y 'Houston ' || 'TX' producen la misma cadena. Pon un carácter como | entre las partes, o cuenta el par directamente con COUNT(DISTINCT (city, state)).
• Limpiar en el SELECT, agrupar en bruto. Si haces GROUP BY city, state y solo limpias en el SELECT, obtienes varias filas que muestran la misma clave, cada una con un conteo parcial.
• Limpiar de más. Quitar todos los espacios convertiría “San Jose” en “sanjose”. Recorta los extremos, conserva el interior.

En el trabajo

Guarda una lista de las grafías en bruto que reuniste y envíasela a quien sea dueño del formulario del CRM. Arreglar la entrada (un menú desplegable en lugar de texto libre) es mejor que limpiar el mismo desorden en cada consulta para siempre. En una entrevista, “¿cómo contarías los clientes por ciudad?” es una prueba de si revisas primero el texto.

En SQL Server

TRIM, UPPER, LOWER y CONCAT existen con los mismos nombres (TRIM desde SQL Server 2017; el código más antiguo usa LTRIM(RTRIM(city))). Usa + o CONCAT para unir cadenas. Ojo con LEN: ignora los espacios finales, así que LEN('Boston ') es 6, mientras que DATALENGTH cuenta bytes.

SELECT CONCAT(UPPER(TRIM(state)), '-', LOWER(TRIM(city))) AS location_key, COUNT(*) AS customers
FROM customers WHERE city IS NOT NULL
GROUP BY CONCAT(UPPER(TRIM(state)), '-', LOWER(TRIM(city)));