Historia de la pregunta. La necesidad de contar valores distintos en tiempo de ejecución surgió de cargas de trabajo analíticas que rastrean métricas como adquisiciones acumulativas de clientes únicos o introducciones de SKU distintos a lo largo del tiempo. Antes de las extensiones de funciones de ventana ANSI SQL:2003, los analistas dependían de autocombinaciones o subconsultas correlacionadas, lo que resultaba en una complejidad de tiempo cuadrático inaceptable para los volúmenes de datos modernos. La estandarización de las funciones de ventana proporcionó un mecanismo basado en conjuntos de tiempo lineal para mantener la cardinalidad sin bucles procedimentales.
El problema. ANSI SQL prohíbe explícitamente la palabra clave DISTINCT dentro de las funciones agregadas de ventana (por ejemplo, COUNT(DISTINCT col) OVER (...)). Esta restricción impide el cálculo directo de valores distintos dentro de un marco acumulativo o deslizante. El desafío principal reside en identificar la aparición inaugural de cada entidad dentro del orden de clasificación de la partición y sumar estas banderas binarias (primera aparición = 1, de lo contrario = 0) progresivamente.
La solución. El enfoque canónico combina ROW_NUMBER() para marcar las primeras apariciones con una función de ventana SUM() condicional. Al particionar ROW_NUMBER() por el identificador de la entidad, la primera aparición cronológicamente recibe el valor 1; las apariciones posteriores reciben enteros incrementales. Una consulta externa luego suma una expresión de caso que emite 1 solo cuando el número de fila es igual a 1, evaluada sobre un marco sin límite anterior.
SELECT event_date, region_id, user_id, SUM(CASE WHEN rn = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY region_id ORDER BY event_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_unique_users FROM ( SELECT event_date, region_id, user_id, ROW_NUMBER() OVER ( PARTITION BY region_id, user_id ORDER BY event_date, event_id -- event_id como desempate ) AS rn FROM user_activity ) flagged;
Descripción del problema. Una startup fintech necesitaba monitorear el cumplimiento regulatorio rastreando el número acumulativo de comerciantes únicos incorporados por región de ventas a lo largo del año fiscal. Su tabla merchant_signups contenía 120 millones de filas con region_code, merchant_id y signup_timestamp. Los trabajos por lotes existentes en Python tardaban 35 minutos en calcular estas métricas por la noche, causando retrasos en los informes y datos de panel obsoletos. El requisito era producir conteos acumulativos en tiempo real dentro de una estricta SQL ANSI para la portabilidad entre almacenes de datos en la nube.
Solución A: El enfoque de autocombinación. Este método une la tabla consigo misma en función de la coincidencia de la región y las marcas de tiempo anteriores, contando comerciantes distintos por cada fila externa. Pros: No requiere soporte para funciones de ventana y funciona en motores SQL-92 heredados. Contras: El algoritmo exhibe complejidad O(n²); para millones de filas, esto genera productos cartesianos intermedios que consumen terabytes de almacenamiento temporal y no puede completarse en horas, haciéndolo operativamente inviable.
Solución B: La subconsulta escalar correlacionada. Aquí, la cláusula SELECT incorpora una subconsulta: (SELECT COUNT(DISTINCT merchant_id) FROM merchant_signups m2 WHERE m2.region_code = m1.region_code AND m2.signup_timestamp <= m1.signup_timestamp). Pros: Es declarativa y lógicamente transparente para leer. Contras: La subconsulta se ejecuta una vez por fila (120 millones de veces), impidiendo la reducción de predicados y causando un I/O aleatorio masivo; los optimizadores de base de datos no pueden decorrelacionar agregados distintos a través de diferentes rangos temporales, dando como resultado tiempos de ejecución estimados que superan los 90 minutos.
Solución C: La técnica de función de ventana SQL ANSI. Utilizando ROW_NUMBER() para identificar las primeras apariciones seguido de un SUM() en ejecución como se muestra en el ejemplo de código anterior. Pros: Esto realiza un solo escaneo de la tabla con ordenamiento, utilizando las capacidades de agrupamiento de ventana del optimizador para una complejidad O(n log n) y un uso de memoria limitado. Contras: Requiere un manejo cuidadoso de empates temporales; si dos inscripciones comparten marcas de tiempo idénticas, un ordenamiento no determinístico podría contar dos veces, a menos que se agregue un desempate único (como event_id) a la cláusula ORDER BY.
Solución elegida y resultado. Se implementó la solución C. Al incluir event_id en el ORDER BY para asegurar una detección determinística de la primera aparición, la consulta se ejecutó en 4 minutos en el clúster existente, una mejora de 9 veces. El resultado permitió paneles de cumplimiento en tiempo real, permitiendo a los oficiales de riesgo monitorear la diversidad de incorporación sin retrasos ETL, y la consulta fue completamente portátil a PostgreSQL, Snowflake y BigQuery sin modificación.
¿Por qué COUNT(DISTINCT column) OVER (ORDER BY ...) genera un error de sintaxis en SQL ANSI estricto?
El estándar SQL prohíbe explícitamente la palabra clave DISTINCT dentro del argumento de una función agregada de ventana como COUNT, SUM, o AVG. Mientras que ciertos proveedores (por ejemplo, PostgreSQL 16+, Oracle) ofrecen esto como una extensión propietaria, SQL ANSI:2011 y versiones anteriores restringen los agregados de ventana a operar en todas las filas dentro del marco definido. Esta limitación existe porque no se exige por la gramática estándar mantener una tabla hash de conjunto distinto para cada posible marco de ventana durante la evaluación en streaming. Los candidatos deben reconocer que DISTINCT solo está permitido en funciones agregadas estándar que carecen de cláusulas OVER, o dentro de funciones de distribución inversa como PERCENTILE_CONT, pero nunca como un conteo distinto de ventana.
¿Cómo maneja los duplicados de marcas de tiempo al determinar la "primera" aparición de una entidad?
ROW_NUMBER() asigna valores arbitrarios entre los empates a menos que la cláusula ORDER BY especifique un orden total. Si un comerciante tiene dos entradas con marcas de tiempo idénticas, ambas filas podrían recibir potencialmente rn = 1 si el ordenamiento es no determinístico, causando que el conteo acumulativo se incremente erróneamente dos veces. La resolución es agregar una clave primaria única o ID de incremento automático a la cláusula ORDER BY: ORDER BY signup_timestamp, merchant_signup_id. Esto asegura una secuenciación determinística donde el ID asignado anteriormente se considera la primera aparición, preservando la integridad matemática del conteo distinto en ejecución.
¿Se puede adaptar esta técnica para un conteo distinto móvil sobre un marco de conteo fijo de filas (por ejemplo, las últimas 100 transacciones) en lugar de un límite sin anterior?
No, no de manera eficiente con SQL ANSI puro. El método sin límite anterior tiene éxito porque la distinción es monótona; una vez que aparece una entidad, permanece "contada" para siempre. En una ventana deslizante (por ejemplo, ROWS BETWEEN 100 PRECEDING AND CURRENT ROW), una entidad que sale de la ventana debe decrementar el conteo, requiriendo conocimiento de si la fila que se retira representa la única instancia de esa entidad dentro del marco actual. SQL ANSI carece de agregación de arreglos u operadores de diferencia de conjuntos dentro de los marcos de ventana para rastrear dicha salida eficientemente. Implementar esto requiere CTE recursivos (que degradan a O(n²) para este escenario) o extensiones propietarias como ARRAY_AGG combinadas con operaciones de conjuntos, ambas de las cuales violan el cumplimiento estricto de ANSI.