Me topé con una muy interesante pregunta jOOQ sobre Stack Overflow que requería el cálculo de un promedio ponderado . Por qué es eso.
Descripción del problema
Suponiendo que tiene esta base de datos (utilizando la sintaxis de PostgreSQL):
cree transacciones de tabla ( ID Bigint no nulo clave primaria, líneas bigint no nulas, precio numérico (18, 2) no nulo, beneficio numérico (18, 2) no nulo ); crear líneas de tabla ( ID Bigint no nulo clave primaria, transaction_id bigint no nulo referencias transacciones, Bigint total no nulo, cantidad bigint no nula, beneficio numérico (18, 2) no nulo );
Como puede verse, este esquema está ligeramente desnormalizado ya que el número de líneas por transacción se calcula previamente en la columna transaction.lines . Esto resultará ser bastante útil para este cálculo, pero no es estrictamente necesario.
Ahora, en la pregunta de Desbordamiento de pila previamente vinculada se deseaba un informe que calcularía:
- agregación de sumas según lo provisto por las partidas individuales
- Una agregación de promedios provista por las transacciones
Esto sería sencillo con dos consultas separadas:
Sumas proporcionadas por las partidas [19659004]SELECCIONAR
suma (beneficio) AS total_profit,
suma (total) AS total_sales_amount,
suma (cantidad) AS total_items_sold
De lineas
Medias proporcionadas por las transacciones
SELECCIONE avg (líneas) AS avg_items_p_trx, avg (precio) AS avg_price_p_trx, avg (ganancia) AS avg_profit_p_trx De transacciones
Hasta ahora, bien.
Haciéndolo en una consulta
Ahora, estas consultas están simplificadas desde el original, que necesitaba unir las dos tablas para agregar predicados adicionales. Además, supongamos que estas tablas son bastante grandes, por lo que ejecutar dos consultas puede hacer que el informe sea demasiado lento. Una sola consulta sería mucho mejor.
Podríamos intentar simplemente combinar los dos:
- Consulta incorrecta SELECCIONAR suma (l.profit) AS total_profit, sum (l.total) AS total_sales_amount, sum (l.quantity) AS total_items_sold, avg (t.lines) AS avg_items_p_trx, avg (t.price) AS avg_price_p_trx, avg (t.profit) AS avg_profit_p_trx DESDE lineas como l ÚNETE a las transacciones AS t ON t.id = l.transaction_id
Pero esta consulta es incorrecta. Mientras que las sumas siguen siendo correctas, los promedios no lo son, simplemente porque la unión produce filas de transacciones duplicadas por líneas. Imagine una transacción con 3 o 5 líneas:
SELECCIONE l.id AS line_id, t.id AS transaction_id, lineas precio DESDE lineas como l ÚNETE a las transacciones AS t ON t.id = l.transaction_id
La salida sería:
LINE_ID TRANSACTION_ID LINES PRICE ------------------------------------------- 1 1 3 20.00 2 1 3 20.00 3 1 3 20.00 4 2 5 100.00 4 2 5 100.00 4 2 5 100.00 4 2 5 100.00 4 2 5 100.00
- El número promedio de líneas "avg_items_p_trx" debe ser 4 = (3 líneas + 5 líneas) / 2 transacciones. Pero si calculamos
avg (t.lines)sobre todo el conjunto de datos, obtenemos 4.25 (3 × 3 líneas + 5 × 5 líneas) / 8 artículos. - El precio promedio “avg_price_p_trx” debería be 60.00 = (20.00 + 100.00) / 2 transacciones. Pero si calculamos
avg (t.price)sobre todo el conjunto de datos, obtenemos 80.00 (3 × 20.00 + 5 × 100.00) / 8 elementos.
¿Cómo se puede arreglar esto?
Dado que cada transacción se duplica debido a la unión con líneas, tenemos que calcular un promedio ponderado no un promedio ordinario. La idea es que en lugar de usar la función agregada AVG () ahora tenemos que dividir el valor del que queremos obtener un promedio por el número de elementos (es decir, el número de veces que se repite el valor porque de la unión), y luego divida la suma de esa división por el número de transacciones.
La prosa nunca describe bien la lógica, así que vamos a usar el código. La consulta correcta es:
SELECCIONAR suma (l.profit) AS total_profit, sum (l.total) AS total_sales_amount, sum (l.quantity) AS total_items_sold, sum (t.lines / t.lines) / count (DISTINCT t.id) avg_items_p_trx, sum (t.price / t.lines) / count (DISTINCT t.id) avg_price_p_trx, sum (t.profit / t.lines) / count (DISTINCT t.id) avg_profit_p_trx DESDE lineas como l ÚNETE a las transacciones AS t ON t.id = l.transaction_id
Con el conjunto de datos anterior:
LINE_ID TRANSACTION_ID LINEAS LINEAS / LINEAS PRECIO PRECIO / LINEAS -------------------------------------------------- -------------- 1 1 3 1 20.00 6.66 2 1 3 1 20.00 6.66 3 1 3 1 20.00 6.66 4 2 5 1 100.00 20.00 4 2 5 1 100.00 20.00 4 2 5 1 100.00 20.00 4 2 5 1 100.00 20.00 4 2 5 1 100.00 20.00
Ahora obtenemos los promedios ponderados correctos:
- El número promedio de líneas “avg_items_p_trx” ahora es 4 =
(3/3 + 3/3 + 3/3 + 5/5 + 5/5 + 5/5 + 5/5 + 5/5) / transacciones distintas - El precio promedio "avg_price_p_trx" es ahora 60.00 =
(20.00 / 3 + 20.00 / 3 + 20.00 / 3 + 100.00 / 5 + 100.00 / 5 + 100.00 / 5 + 100.00 / 5 + 100.00 / 5) / 2 transacciones distintas
Tenga en cuenta que “avg_items_p_trx” se puede simplificar:
SELECT suma (l.profit) AS total_profit, sum (l.total) AS total_sales_amount, sum (l.quantity) AS total_items_sold, count (*) / count (DISTINCT t.id) avg_items_p_trx, sum (t.price / t.lines) / count (DISTINCT t.id) avg_price_p_trx, sum (t.profit / t.lines) / count (DISTINCT t.id) avg_profit_p_trx DESDE lineas como l ÚNETE a las transacciones AS t ON t.id = l.transaction_id
¡Listo!
Versión normalizada
Observe que esta solución se benefició del hecho de que el número de líneas por transacción se calculó previamente. Por supuesto, también podemos calcularlo sobre la marcha, por ejemplo. Usando funciones de ventana. Si no estuviera disponible, podríamos hacerlo así:
SELECCIONAR
suma (l.profit) AS total_profit,
sum (l.total) AS total_sales_amount,
sum (l.quantity) AS total_items_sold,
count (*) / count (DISTINCT t.id) avg_items_p_trx,
sum (t.price / l.lines) / count (DISTINCT t.id) avg_price_p_trx,
sum (t.profit / l.lines) / count (DISTINCT t.id) avg_profit_p_trx
DESDE (
SELECCIONAR
l. *,
contar (*) OVER (PARTITION BY l.transaction_id) líneas
DESDE lineas como l
) AS I
ÚNETE a las transacciones AS t ON t.id = l.transaction_id
O, convertimos la unión completa en una relación 1: 1 agregando previamente todos los datos de las líneas en una fila por transacción. Esto funciona porque solo calculamos sumas de la tabla de líneas:
SELECCIONAR
suma (l.profit_per_transaction) AS total_profit,
sum (l.total_per_transaction) AS total_sales_amount,
sum (l.quantity_per_transaction) AS total_items_sold,
avg (l.lines_per_transaction) AS avg_items_p_trx,
avg (t.price) AS avg_price_p_trx,
avg (t.profit) AS avg_profit_p_trx
DESDE (
SELECCIONAR
l.transaction_id
suma (l.profit) AS profit_per_transaction,
suma (l.total) AS total_per_transaction,
sum (l.quantity) AS quantity_per_transaction,
count (*) AS lines_per_transaction
DESDE lineas como l
GRUPO POR l.transaction_id
) AS I
ÚNETE a las transacciones AS t ON t.id = l.transaction_id
No comments:
Post a Comment