es.davy.ai

Preguntas y respuestas de programación confiables

¿Tienes una pregunta?

Si tienes alguna pregunta, puedes hacerla a continuación o ingresar lo que estás buscando.

¿Cómo obtener la suma acumulativa de una columna de agregado?

15 Jul, 2023 Programación

Tengo esta consulta en BigQuery que devuelve la representación del total de contributing_factor_vehicle_1.


SELECT
TBL_TOTAL.contributing_factor_vehicle_1,
TBL_TOTAL.TOTAL,
(TBL_TOTAL.TOTAL / SUM(TBL_TOTAL.TOTAL) OVER ()) * 100 AS PERCENTAGE
FROM
(SELECT
contributing_factor_vehicle_1,
COUNT(contributing_factor_vehicle_1) AS TOTAL
FROM
bigquery-public-data.new_york_mv_collisions.nypd_mv_collisions
WHERE
borough = 'BROOKLYN'
AND contributing_factor_vehicle_1 <> 'Unspecified'
GROUP BY
contributing_factor_vehicle_1
ORDER BY
TOTAL DESC) TBL_TOTAL
ORDER BY
TOTAL DESC

Salida:

contributing_factor_vehicle_1 TOTAL PERCENTAGE
Falta de atención del conductor/Distracción 65427 28.913538237178777
Falta de ceder el paso 25831 11.415250679452903
Retroceder de manera insegura 16384 7.240426895286917
Seguir demasiado cerca 12605 5.570408997503148
Pasar demasiado cerca 10875 4.805886382217116

Ahora necesito obtener el porcentaje acumulativo de PERCENTAGE para realizar un análisis de Pareto.
¿Cómo puedo lograrlo por favor? ¿Es posible usar la columna PERCENTAGE en una función de ventana nuevamente?

contributing_factor_vehicle_1 TOTAL PERCENTAGE PERCENTAGE CUM
Falta de atención del conductor/Distracción 65427 28.91% 28.91%
Falta de ceder el paso 25831 11.42% 40.33%
Retroceder de manera insegura 16384 7.24% 47.57%
Seguir demasiado cerca 12605 5.57% 53.14%
Pasar demasiado cerca 10875 4.81% 57.95%
Tags:  , ,

Answer

  1. Avatar for davy.ai
    davy.ai

    Para obtener el porcentaje acumulativo, se puede utilizar la función de ventana SUM con la cláusula ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW para encontrar la suma de la columna PERCENTAGE hasta la fila actual.

    Entonces, la consulta se vería así:

    SELECT
    TBL_TOTAL.contributing_factor_vehicle_1,
    TBL_TOTAL.TOTAL,
    (TBL_TOTAL.TOTAL / SUM(TBL_TOTAL.TOTAL) OVER ()) * 100  AS PERCENTAGE,
    SUM((TBL_TOTAL.TOTAL / SUM(TBL_TOTAL.TOTAL) OVER ()) * 100) OVER (ORDER BY PERCENTAGE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS PERCENTAGE_CUM
FROM 
    (SELECT 
         contributing_factor_vehicle_1,
         COUNT(contributing_factor_vehicle_1) AS TOTAL
     FROM 
         `bigquery-public-data.new_york_mv_collisions.nypd_mv_collisions` 
     WHERE 
         borough = 'BROOKLYN' 
         AND contributing_factor_vehicle_1 <> 'Unspecified'
     GROUP BY 
         contributing_factor_vehicle_1
     ORDER BY 
         TOTAL DESC) TBL_TOTAL
ORDER BY 
    PERCENTAGE_CUM DESC

    En la nueva consulta, añadimos la función de ventana SUM con la cláusula ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW a la columna PERCENTAGE para obtener el porcentaje acumulativo. También agregamos PERCENTAGE_CUM como la nueva columna de salida y ordenamos el resultado por PERCENTAGE_CUM en orden descendente.

    Esta consulta devolverá el resultado con la columna de porcentaje acumulativo de la siguiente manera:

    contributing_factor_vehicle_1 TOTAL PERCENTAGE PERCENTAGE_CUM
    Driver Inattention/Distraction 65427 28.913538237178777 28.913538237178777
    Failure to Yield Right-of-Way 25831 11.415250679452903 40.32878891663168
    Backing Unsafely 16384 7.240426895286917 47.5692158119186
    Following Too Closely 12605 5.570408997503148 53.13962480942175
    Passing Too Closely 10875 4.805886382217116 57.94551119163887

    Sí, es posible utilizar la columna PERCENTAGE en una función de ventana nuevamente para obtener el porcentaje acumulativo.

Comments are closed.