0
137
ViewsDesafiante: BigQuery multiplica y suma recursivamente
Tengo lo que pensé que era un problema simple, pero tal vez lo subestimé (o la falta de CTE recursivos de BigQuery):
Digamos que tengo esta tabla:
SELECT DATE("2021-09-01") AS date, 50 AS a, 0.5 AS b, 2 AS c UNION ALL SELECT DATE("2021-09-02") AS date, NULL AS a, 0.6 AS b, 1 AS c UNION ALL SELECT DATE("2021-09-03") AS date, NULL AS a, 0.4 AS b, 3 AS cY así sucesivamente hasta finales de 2021. Es decir:
La columna a solo tiene un valor en la primera fila, mientras que las demás varían hasta el final de la tabla.
Y deseo generar otra columna ('cálculo'), con la siguiente operación hasta el final de la tabla:
row 1 = a * (1 - b) + c row 2 = row 1 * (1 - b) + c row 3 = row 2 * (1 - b) + c etc.Dando así un resultado como este:
date abc calculation 2021-09-01 50 0.5 2 27 2021-09-02 0.6 1 11.8 2021-09-03 0.4 3 10.08La clave es: necesito obtener el resultado de la fila anterior y luego aplicarle la misma operación y así sucesivamente.
¿Cuál sería una buena manera de hacer esto?
(Nota: C puede ser mayor que 709.7827, lo que descarta usar la respuesta de EXP(C) Mikhail a continuación, ¡aunque es un comienzo prometedor!)
¡Gracias!
1 answers
Answer question0
Tiene razón: BigQuery no es compatible [aunque con suerte] CTE recursivo, pero siempre hay una solución.
Por lo tanto, su problema se puede expresar mediante la siguiente fórmula para la fila N.
que puede [relativamente fácil] implementarse con funciones de ventana/analíticas como en el siguiente ejemplo
with temp as ( select *, row_number() over(order by date) pos, exp(sum(ln(1-b)) over(order by date)) p, min(a) over() * exp(sum(ln(1-b)) over(order by date)) + c calculation, from `project.dataset.table` ) select any_value(struct(t1.date, t1.a, t1.b, t1.c)).*, any_value(t1.calculation) + sum( if(t1.pos > t2.pos, t2.c * t1.p / t2.p, 0) ) calculation from temp t1 join temp t2 on t1.pos >= t2.pos group by to_json_string(t1)si se aplica a datos de muestra en su pregunta, el resultado es
Como puede ver aquí, un truco adicional es usar las funciones LN y luego EXP en combinación con la función analítica SUM.
LN le permite transformar la multiplicación (de valores en filas dentro de la ventana establecida) en la suma de esos valores - LN(v1 * v2 * ... * vN) = LN(v1)+LN(v2)+...+LN( vN). Y luego, usar EXP le da un resultado de multiplicación real: EXP (LN (v1 v2 ... vN)) = v1 v2 * ... * vN. - que es principalmente lo que es su fórmula de cálculo