ROLLINGAVERAGE 函式

針對指定資料欄中當前資料列往前或往後的值計算累計平均值。
  • 如果輸入值遺漏或為空值,則不會列入計算。例如,針對資料集裡的第一列,此列之前的值累計平均值就是第一列的值。
  • 擷取值的來源資料列由 order 參數指定的資料列排序決定。

    如果您正在處理隨機產生的資料集樣本,則您在此函式中看到的值未必會對應到工作執行期間在完整資料集中產生的值。

  • 此函式使用資料欄名稱,以及決定目前資料列往後及往前範圍的兩個選用整數參數。
    • 預設整數參數值是 -10,用以計算目前資料列往後到資料集第一列的累計平均值。
  • 此函式可以搭配下列轉換指令一起使用:

如要進一步瞭解此函式的非累計版本,請參閱 AVERAGE 函式

基本用法

資料欄範例:

derive type:single value:ROLLINGAVERAGE(myCol)

輸出:產生新資料欄,針對資料集第一個資料列至當前資料列之間的 myCol 資料欄,列出其中所有值的累計平均值。

計算前幾列範例:

window value:ROLLINGAVERAGE(myNumber, 3)

輸出:產生新資料欄,其中包含 myNumber 資料欄中現行列和前兩列值的累計平均值。

計算前後幾列範例:

window value:ROLLINGAVERAGE(myNumber, 3, 2)

輸出:產生新資料欄,其中包含 myNumber 資料欄中現行列、其前兩列,以及其後兩列值的累計平均值。

語法

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

引數是否必要?資料類型說明
col_ref字串資料欄名稱,其值會套用於函式
rowsBefore_integer整數待併入計算的現行資料列之前的列數
rowsAfter_integer整數待併入計算的現行資料列之後的列數

如要進一步瞭解 ordergroup 參數,請參閱 Window 轉換

如要進一步瞭解語法標準,請參閱語言文件語法附註

col_ref

值要用來計算累計平均值的資料欄名稱。

  • 不支援多個資料欄和萬用字元。

使用須知:

是否必要?資料類型範例值
字串 (整數或小數值的資料欄參照)myColumn

rowsBefore_integer、rowsAfter_integer

整數代表現行列之前或之後的列數,系統會從現行列開始來計算之前或之後指定列數 (包括現行列在內) 的累計平均值。例如,如果第一個值是 5,則計算範圍為現行列和現行列之後的四列。負值的 k 代表計算現行列之前指定列數的累計平均值。

  • rowBefore=1 只會產生現行列值。
  • rowBefore=-1 使用現行列之前的所有列。
  • 如果沒有指定 rowsAfter,則會套用 0 值。
  • 如果套用 group 參數,則這些參數值不應超過群組中的最大列數。

使用須知:

是否必要?資料類型範例值
整數4

範例

範例 - 計算前一季值

下列資料集包含前 12 個月的訂單資訊。您想要比較當前月份與前一季的平均值。

來源:

DateAmount
12/31/15118
11/30/156
10/31/15443
9/30/15785
8/31/1577
7/31/15606
6/30/15421
5/31/15763
4/30/15305
3/31/15824
2/28/15135
1/31/15523

轉換:

使用 ROLLINGAVERAGE 函式產生的資料欄會包含當前月份和前兩個月的累計平均值:

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

請注意第二個參數和 order 參數的符號。排序與 Date 參數的順序相反,此參數維持目前的排序順序。因此,第二個參數 (識別計算所使用的列數) 必須是正數,才能擷取前幾個月的記錄。

就技術層面而言,這樣的計算並不會擷取前一季的記錄,因為計算中還包含了目前這一季。您可以使用下列資料欄來擷取前一個月的累計平均值,然後變成前一季真正的累計平均值。window 資料欄會參照前一步驟產生的資料欄名稱:

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

請務必保留排序參數。這個新的資料欄 (window1) 含有前一季的累計平均值:

rename col:window1 to:'Amount_PriorQtr'

您可以重新設定這個數字值的格式:

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

您可以使用下列轉換指令來計算淨變更值。此公式會以前一季的百分比來計算變更,然後格式化為兩位數的百分比。

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

結果:

注意:您會發現二月和三月的 Amount_PriorQtr 都有計算值。這些值並非完整三個月的計算,因為沒有三個月的資料可以計算。由於沒有先前月份的資料,因此一月的值不存在。

DateAmountAmount_PriorQtr NetChangePct_PriorQtr
12/31/15118411.33-71.31
11/30/156435.00-98.62
10/31/15443489.33-9.47
9/30/15785368.00113.32
8/31/1577596.67-87.1
7/31/15606496.3322.1
6/30/15421630.67-33.25
5/31/15763421.3381.09
4/30/15305494.00-38.26
3/31/15824329.00150.46
2/28/15135523.00-.74.19
1/31/15523

範例 - 累計範圍函式

這個範例說明如何使用累計計算函式:

  • ROLLINGSUM - 計算現行列前後某一列數範圍的累計總和。請參閱 ROLLINGSUM 函式
  • ROLLINGAVERAGE - 計算現行列前後某一列數範圍的累計平均值。請參閱 ROLLINGAVERAGE 函式
  • ROWNUMBER - 依排序資料欄的指定,計算每一列的資料列編號。請參閱 ROWNUMBER 函式

下列資料集包含一年中最後一季的銷售資料。

來源:

DateSales
10/2/16200
10/9/16500
10/16/16350
10/23/16400
10/30/16190
11/6/16550
11/13/16610
11/20/16480
11/27/16660
12/4/16690
12/11/16810
12/18/16950
12/25/161020
1/1/17680


轉換:

首先,您要以個別的資料欄保存資料列資訊。由於資料已經依 Date 資料欄排序,您可以使用下列指令:

window value:ROWNUMBER() order:Date

將這個資料欄重新命名為 rowId,代表一季中的週別。

現在,您要從 Date 值中擷取月份及週別資訊。取得月份值:

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

取得季度值:

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

取得季度中的週別值:

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

重新命名此資料欄 WOQ (季度的週別)。

取得月份中的週別值:

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

重新命名此資料欄 WOM (月份中的週別)。

現在,執行累計計算。使用下列指令計算銷售的累積總計:

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

在上述計算中,使用 -1 參數來收集從目前列到第一列所有資料列值的累計總和。請注意,使用 QTR 資料欄進行分組,會將 01/01/2017 的值移至它自己的計算值區。這不一定是您想要的結果。

重新命名此資料欄 QTD (本季至今)。現在,系統會產生類似的資料欄,計算該季每週銷售的累計平均值。

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

ROLLINGAVERAGE 函式可以計算小數值,因此可內含於 ROUND 函式中來取得精簡值。重新命名此資料欄 avgWeekByQuarter

結果:

捨棄不需要的資料欄並套用部分重新排序後,資料集應如下所示:

DateWOQSalesQTDavgWeekByQuarter
10/2/161200200200
10/9/162500700350
10/16/1633501050350
10/23/1644001450363
10/30/1651901640328
11/6/1665502190365
11/13/1676102800400
11/20/1684803280410
11/27/1696603940438
12/4/16106904630463
12/11/16118105440495
12/18/16129506390533
12/25/161310207410570
1/1/171680680680


範例:賽車拆分的累計計算

這個範例說明如何使用累計計算函式:

  • ROLLINGAVERAGE - 計算現行列前後某一列數範圍的累計平均值。請參閱 ROLLINGAVERAGE 函式
  • ROLLINGMIN - 計算某一列數範圍的累計最小值。參閱 ROLLINGMIN 函式
  • ROLLINGMAX - 計算某一列數範圍的累計最大值。參閱 ROLLINGMAX 函式
  • ROLLINGSTDEV - 計算某一列數範圍的累計標準差。參閱 ROLLINGSTDEV 函式
  • ROLLINGVAR - 計算某一列數範圍的累計變異數。參閱 ROLLINGVAR 函式

來源:

在此範例中,下列資料是來自三圈賽道賽車競賽期間依標準間隔記錄的時間。來源資料使用累計時間表示,單位為秒 (time_sc)。您可使用 ROLLING 和其他窗型函式,將資料細分為更具意義的指標。

單圈計時季度time_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

轉換:

主鍵:由於每一圈的季度資訊重複,因此每個資料列並無不重複的 ID。下列步驟會建立此 ID:

settype col: lap,quarter type: 'String'

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

取得拆分時間:使用下列轉換,將賽程的每個季度細分為拆分資料:

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

計算累計運算結果:您可使用下列類型的運算,針對當前與前三個拆分提供累計指標:

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'

結果:

完成上述轉換作業後,會呈現類似如下的結果:

單圈計時季度splitIdtime_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

您可套用諸如以下的 window 轉換,以減少步驟數:

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

不過,您必須重新命名所有已產生的 windowX 資料欄。

本頁內容對您是否有任何幫助?請提供意見:

傳送您對下列選項的寶貴意見...

這個網頁
Google Cloud Dataprep 說明文件
需要協助嗎?請前往我們的支援網頁