El cálculo se basa en el método de los mínimos cuadrados. La pendiente (β) se define como la covarianza de la variable independiente X y la variable dependiente Y dividida por la varianza de X. El intercepto (α) se deriva de la media de Y menos el producto de la pendiente y la media de X. En ANSI SQL, implementas estas definiciones algebraicas utilizando agregados SUM, AVG y COUNT, típicamente dentro de una cláusula GROUP BY o como funciones de ventana con una cláusula OVER. La consulta debe calcular explícitamente la suma de los productos cruzados (Σ(X - X̄)(Y - Ȳ)) y la suma de las desviaciones al cuadrado para X (Σ(X - X̄)²) para resolver los coeficientes finales.
Un equipo de análisis minorista necesitaba determinar la elasticidad del precio de la demanda para cada categoría de producto para optimizar estrategias de precios dinámicos. Contaban con una tabla de transacciones que contenía unit_price y quantity_sold, y requerían una línea de tendencia que cuantificara cómo cambió la cantidad vendida con el precio para cada category_id distinto.
Una solución propuesta consistía en exportar agregados diarios a un script externo de Python utilizando scikit-learn para ajustar modelos de regresión. Este enfoque ofrecía simplicidad en la implementación y acceso a diagnósticos estadísticos ricos. Sin embargo, introducía una latencia significativa de datos, violaba estrictas políticas de gobernanza de datos al crear copias externas de datos de ventas sensibles y impedía las actualizaciones de panel en tiempo real requeridas para los algoritmos de precios automatizados.
Otra opción considerada fue crear una función agregada definida por el usuario (UDAF) dentro del motor de base de datos, lo que permitiría una sintaxis como REGRESS_SLOPE(price, quantity). Si bien era elegante y reutilizable, esto sacrificaba la portabilidad entre diferentes sistemas de bases de datos y requería privilegios administrativos elevados para implementarse, haciéndola inadecuada para un entorno de almacén de datos en la nube multiusuario bloqueado.
La solución elegida implementó las fórmulas algebraicas directamente en ANSI SQL utilizando agregados estándar. El equipo aprovechó las funciones de ventana SUM y AVG particionadas por category_id para calcular los términos necesarios de covarianza y varianza en una sola pasada sobre los datos. Este enfoque mantuvo el cálculo colocalizado con los datos, eliminó los delays de extracción-transformación-carga (ETL) y se adhirió estrictamente a los estándares portátiles de ANSI SQL sin extensiones propietarias. El resultado fue un panel de elasticidad de precios con una latencia de menos de un segundo que se actualizaba automáticamente a medida que llegaban nuevas transacciones, habilitando directamente los algoritmos de precios automatizados para ajustar márgenes en tiempo real.
¿Cómo manejas los valores NULL en X o Y sin invalidar el cálculo de todo el grupo?
Los candidatos a menudo olvidan que, aunque las funciones de agregación de ANSI SQL ignoran los NULL, las operaciones aritméticas que involucran NULL devuelven NULL. Al calcular el término de covarianza SUM((x - avg_x) * (y - avg_y)), si x o y es NULL para una fila específica, el producto se vuelve NULL y esa fila se excluye de la suma. Esto efectivamente realiza una eliminación par a par, que es generalmente deseada, pero se debe asegurar que el COUNT utilizado para los grados de libertad en los cálculos de varianza refleje la cuenta de pares no NULL, no el total de filas. La solución es filtrar WHERE x IS NOT NULL AND y IS NOT NULL en una subconsulta o usar COUNT(x) (que es igual a COUNT(y) después de filtrar) en lugar de COUNT(*), asegurando denominadores consistentes en todos los términos agregados.
¿Cuál es la distinción entre calcular la regresión sobre una población frente a una muestra, y cómo afecta esto a tu consulta SQL?
Muchos candidatos aplican la fórmula de varianza muestral (dividiendo por n - 1) de manera inconsistente con la fórmula de covarianza. En ANSI SQL, funciones integradas como VAR_POP y VAR_SAMP manejan esta distinción, pero al calcular manualmente la varianza como SUM(POWER(x - avg_x, 2)) / COUNT(*), debes elegir conscientemente el denominador. Para el cálculo de la pendiente, si calculas manualmente la varianza de X en el denominador, debes emparejarla con el divisor del cálculo de covarianza. Mezclarlos (por ejemplo, covarianza muestral dividida por varianza poblacional) produce una pendiente sesgada. El enfoque corregido es decidir sobre el marco estadístico (población vs. muestra) y aplicar la misma lógica de divisores (ya sea n o n-1) tanto al numerador de la covarianza como al denominador de la varianza.
¿Cómo calcularías el coeficiente de determinación (R²) para medir la bondad de ajuste dentro de la misma consulta?
Los candidatos frecuentemente omiten métricas de validación. R² se calcula como 1 - (SS_res / SS_tot), donde SS_res es la suma de los residuos al cuadrado (Σ(y - ŷ)²) y SS_tot es la suma total de cuadrados (Σ(y - ȳ)²). Calcular ŷ (y predicho) requiere la pendiente y el intercepto calculados en pasos anteriores. En ANSI SQL, puedes calcular esto utilizando Expresiones de Tabla Comunes apiladas (CTEs): primero calculas las medias, luego calculas la pendiente y el intercepto en un segundo CTE, y finalmente calculas las diferencias al cuadrado entre los valores reales y los predichos en la consulta exterior. Un error común es intentar hacer referencia a la pendiente calculada dentro del mismo nivel de agregación donde se calcula, lo que viola el orden lógico de procesamiento. La solución es separar la lógica en CTEs secuenciales para permitir que los coeficientes calculados se reutilicen como constantes en la agregación final para R².