Função ROLLINGAVERAGE

Calcula a média contínua de valores para a frente ou para trás da linha atual dentro da coluna especificada.
  • Um valor de entrada ausente ou nulo não é considerado no cálculo. Por exemplo, para a primeira linha no conjunto de dados, a média contínua dos valores anteriores é o valor na primeira linha.
  • A linha a partir da qual extrair um valor é determinada pela ordem em que as linhas são organizadas, com base no parâmetro order.

    Se você estiver trabalhando em uma amostra do conjunto de dados gerada aleatoriamente, os valores que você vê para esta função poderão não corresponder aos que são gerados no conjunto de dados completo durante a execução do job.

  • A função leva um nome de coluna e dois parâmetros inteiros opcionais que determinam a janela para trás e para frente da linha atual.
    • Os parâmetros inteiros, que têm valores padrão que são -1 e 0, calculam a média contínua a partir da linha atual até a primeira linha do conjunto de dados.
  • Essa função é compatível com as seguintes transformações:

Para mais informações sobre uma versão não contínua desta função, consulte Função AVERAGE.

Uso básico

Exemplo de coluna:

derive type:single value:ROLLINGAVERAGE(myCol)

Saída: gera uma nova coluna que contém a média contínua de todos os valores na coluna myCol da primeira linha do conjunto de dados para a linha atual.

Exemplo de linhas anteriores:

window value:ROLLINGAVERAGE(myNumber, 3)

Saída: gera a nova coluna que contém a média contínua da linha atual e dos valores das duas linhas anteriores na coluna myNumber.

Exemplo de linhas antes e depois:

window value:ROLLINGAVERAGE(myNumber, 3, 2)

Saída: gera a nova coluna que contém a média contínua dos valores das duas linhas anteriores, do valor da linha atual e das duas linhas após a atual na coluna myNumber.

Sintaxe

window value:ROLLINGAVERAGE(col_ref, rowsBefore_integer, rowsAfter_integer) order: order_col [group: group_col]

ArgumentoObrigatório?Tipo de dadosDescrição
col_refSstringNome da coluna cujos valores são aplicados à função
rowsBefore_integerNnúmero inteiroNúmero de linhas antes da atual a incluir no cálculo
rowsAfter_integerNnúmero inteiroNúmero de linhas após a atual a incluir no cálculo

Para mais informações sobre os parâmetros order e group, consulte Transformação Window.

Para mais informações sobre os padrões de sintaxe, consulte Observações de sintaxe da documentação da linguagem.

col_ref

Nome da coluna cujos valores são usados para calcular a média continua.

  • Não há compatibilidade com várias colunas e caracteres curinga.

Observações sobre o uso:

Obrigatório?Tipo de dadosValor de exemplo
SimString (referência de coluna para valores inteiros ou decimais)myColumn

rowsBefore_integer, rowsAfter_integer

Inteiros que representam o número de linhas antes ou depois da atual para calcular a média continua, incluindo a linha atual. Por exemplo, se o primeiro valor for 5, a linha atual e as quatro linhas depois serão usadas no cálculo. Os valores negativos de k calculam a média contínua das linhas anteriores à atual.

  • rowBefore=1 gera apenas o valor da linha atual.
  • rowBefore=-1 usa todas as linhas anteriores à atual.
  • Se rowsAfter não for especificado, será aplicado o valor 0.
  • Se um parâmetro group for aplicado, esses valores de parâmetros não poderão ultrapassar o número máximo de linhas nos grupos.

Observações sobre o uso:

Obrigatório?Tipo de dadosValor de exemplo
NãoNúmero inteiro4

Exemplos

Exemplo: calcular os valores do trimestre anterior

O conjunto de dados a seguir contém informações sobre ordem nos últimos 12 meses. O objetivo é comparar a média do mês atual com o trimestre anterior.

Origem:

DataValor
31/12/15118
30/11/156
31/10/15443
30/09/15785
31/08/1577
31/07/15606
30/06/15421
31/05/15763
30/04/15305
31/03/15824
28/02/15135
31/01/15523

Transformação:

Com a função ROLLINGAVERAGE, gere uma coluna contendo a média contínua do mês atual e dos dois meses anteriores:

window value: ROLLINGAVERAGE(Amount, 3, 0) order: -Date

Observe o sinal do segundo parâmetro e do parâmetro order. A classificação está na ordem inversa do parâmetro Date, que preserva a ordem de classificação atual. Consequentemente, o segundo parâmetro, que identifica o número de linhas a usar no cálculo, precisa ser positivo para capturar os meses anteriores.

Tecnicamente, esse cálculo não captura o trimestre anterior, já que inclui o trimestre atual. Você pode usar a coluna a seguir para capturar a média contínua do mês anterior, que depois se torna a verdadeira média contínua do trimestre anterior. A coluna window refere-se ao nome da coluna gerada a partir da etapa anterior:

window value: NEXT(window, 1) order: -Date

Observe que o parâmetro "order" precisa ser preservado. Esta nova coluna, window1, contém sua média contínua trimestral anterior:

rename col:window1 to:'Amount_PriorQtr'

Reformate esse valor numérico:

set col:Amount_PriorQtr value:NUMFORMAT(Amount_PriorQtr, '###.00')

Use a transformação abaixo para calcular a variação líquida. Esta fórmula calcula a variação como uma porcentagem do trimestre anterior e depois a formata como uma porcentagem de dois dígitos.

derive type:single value:NUMFORMAT(((Amount - Amount_PriorQtr) / Amount_PriorQtr) * 100, '##.##') as:'NetChangePct_PriorQtr'

Resultados:

OBSERVAÇÃO: há valores calculados para Amount_PriorQtr referentes a fevereiro e março. Esses valores não são considerados nos três meses completos porque os dados não estão presentes. O valor de janeiro não existe porque não há dados que o precedem.

DataValorAmount_PriorQtrNetChangePct_PriorQtr
31/12/15118411,33-71,31
30/11/156435,00-98,62
31/10/15443489,33-9,47
30/09/15785368,00113,32
31/08/1577596,67-87,10
31/07/15606496,3322,10
30/06/15421630,67-33,25
31/05/15763421,3381,09
30/04/15305494,00-38,26
31/03/15824329,00150,46
28/02/15135523,00-.74,19
31/01/15523

Exemplo: funções de janela contínua

Este exemplo descreve como usar as funções de cálculo contínuo:

  • ROLLINGSUM: calcula uma soma contínua de uma janela de linhas antes e depois da linha atual. Consulte Função ROLLINGSUM.
  • ROLLINGAVERAGE: calcula uma média contínua a partir de uma janela de linhas antes e depois da linha atual. Consulte Função ROLLINGAVERAGE.
  • ROWNUMBER: calcula o número de cada linha, conforme determinado pela coluna de ordenação. Consulte Função ROWNUMBER.

O conjunto a seguir contém dados sobre vendas no último trimestre do ano.

Origem:

DateSales
02/10/16200
09/10/16500
16/10/16350
23/10/16400
30/10/16190
06/11/16550
13/11/16610
20/11/16480
27/11/16660
04/12/16690
11/12/16810
18/12/16950
25/12/161.020
01/01/17680


Transformação:

Primeiro, mantenha as informações da linha como uma coluna separada. Uma vez que os dados já foram ordenados pela coluna Date, use o seguinte comando:

window value:ROWNUMBER() order:Date

Mude o nome dessa coluna para rowId por semana do trimestre.

Agora, extraia informações de mês e semana dos valores de Date. Para derivar o valor do mês:

derive type:single value:MONTH(Date) as:'Month'

Para derivar o valor do trimestre:

derive type:single value:(1 + FLOOR(((month-1)/3))) as:'QTR'

Para derivar o valor de semana do trimestre:

window value:ROWNUMBER() order:Date group:QTR

Renomeie essa coluna como WOQ (semana do trimestre).

Para derivar o valor de semana do mês:

window value:ROWNUMBER() group:Month order:Date

Renomeie essa coluna como WOM (semana do mês).

Agora, execute os cálculos contínuos. Para calcular o total contínuo de vendas, use o comando a seguir:

window value: ROLLINGSUM(Sales, -1, 0) order: Date group:QTR

O parâmetro -1 é usado no cálculo acima para reunir a soma contínua de todas as linhas de dados, da atual para a primeira. Observe o uso da coluna QTR para agrupamento, o que move o valor de 01/01/2017 para o próprio intervalo de cálculo. Isso é opcional, conforme a preferência.

Renomeie essa coluna como QTD (acumulado no trimestre). Agora, gere uma coluna semelhante para calcular a média contínua das vendas semanais no trimestre:

window value: ROUND(ROLLINGAVERAGE(Sales, -1, 0)) order: Date group:QTR

Com a função ROLLINGAVERAGE, é possível calcular valores fracionários e, por isso, ela é envolvida na função ROUND para melhorar a organização. Renomeie essa coluna como avgWeekByQuarter.

Resultados:

Quando as colunas desnecessárias são descartadas e alguma reordenação é aplicada, o conjunto de dados fica assim:

DateWOQSalesQTDavgWeekByQuarter
02/10/161200200200
09/10/162500700350
16/10/1633501.050350
23/10/1644001.450363
30/10/1651901.640328
06/11/1665502.190365
13/11/1676102.800400
20/11/1684803.280410
27/11/1696603.940438
04/12/16106904.630463
11/12/16118105.440495
18/12/16129506.390533
25/12/16131.0207.410570
01/01/171680680680


Exemplo: cálculos contínuos para parciais de corrida

Neste exemplo, descrevemos como usar as funções de cálculo contínuo:

  • ROLLINGAVERAGE: calcula uma média contínua de uma janela de linhas antes e depois da linha atual. Consulte Função ROLLINGAVERAGE.
  • ROLLINGMIN: calcula um mínimo contínuo de uma janela de linhas. Consulte Função ROLLINGMIN.
  • ROLLINGMAX: calcula um máximo contínuo de uma janela de linhas. Consulte Função ROLLINGMAX.
  • ROLLINGSTDEV: calcula um desvio padrão contínuo de uma janela de linhas. Consulte Função ROLLINGSTDEV.
  • ROLLINGVAR: calcula uma variância contínua de uma janela de linhas. Consulte Função ROLLINGVAR.

Origem:

Neste exemplo, os dados a seguir vêm de tempos registrados em intervalos regulares durante uma corrida de três voltas ao redor de uma pista. Os dados de origem estão no tempo acumulado em segundos (time_sc). Use ROLLING e outras funções de janela para dividir os dados em métricas mais significativas.

lapquartertime_sc
100,000
1119,554
1239,785
1360,021
2080,950
21101,785
22121,005
23141,185
30162,008
31181,887
32200,945
33220,856

Transformação:

Chave primária: como as informações sobre o quarto da corrida se repetem a cada volta, não há identificador exclusivo para cada linha. As etapas a seguir criam esse identificador:

settype col: lap,quarter type: 'String'

derive type:single value: MERGE(['l',lap,'q',quarter]) as: 'splitId'

Conseguir tempos parciais: use a seguinte transformação para dividir as parciais de cada quarto da corrida:

derive type:single value: ROUND(time_sc - PREV(time_sc, 1), 3) order: splitId as: 'split_time_sc'

Fazer cálculos contínuos: use os tipos de cálculos a seguir para fornecer métricas contínuas nas três parciais anteriores e na atual:

derive type:single value: ROLLINGAVERAGE(split_time_sc, 3) order: splitId as: 'ravg'

derive type:single value: ROLLINGMAX(split_time_sc, 3) order: splitId as: 'rmax'

derive type:single value: ROLLINGMIN(split_time_sc, 3) order: splitId as: 'rmin'

derive type:single value: ROUND(ROLLINGSTDEV(split_time_sc, 3), 3) order: splitId as: 'rstdev'

derive type:single value: ROUND(ROLLINGVAR(split_time_sc, 3), 3) order: splitId as: 'rvar'

Resultados:

Quando as transformações acima tiverem sido concluídas, os resultados serão semelhantes a estes:

lapquartersplitIdtime_scsplit_time_scrvarrstdevrminrmaxravg
10l1q00
11l1q120,09620,0960020,09620,09620,096
12l1q240,5320,4340,0290,16920,09620,43420,265
13l1q361,03120,5010,0310,17720,09620,50120,344
20l2q081,08720,0560,0390,19820,05620,50120,272
21l2q1101,38320,2960,0290,1720,05620,50120,322
22l2q2122,09220,7090,0590,24220,05620,70920,39
23l2q3141,88619,7940,1130,33719,79420,70920,214
30l3q0162,58120,6950,1390,37319,79420,70920,373
31l3q1183,01820,4370,1380,37119,79420,70920,409
32l3q2203,49320,4750,1130,33619,79420,69520,35
33l3q3222,89319,40,2520,50219,420,69520,252

Reduza o número de etapas aplicando uma transformação window como esta:

window value: window1 = lap,rollingaverage(split_time_sc, 0, 3), rollingmax(split_time_sc, 0, 3),rollingmin(split_time_sc, 0, 3),round(rollingstdev(split_time_sc, 0, 3), 3),round(rollingvar(split_time_sc, 0, 3), 3) group: lap order: lap

No entanto, você precisa renomear todas as colunas windowX geradas.

Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Documentação do Google Cloud Dataprep
Precisa de ajuda? Acesse nossa página de suporte.