LISTMODE 函式

計算在輸入陣列中找到的所有數值中最常見的值。輸入可以是陣列常值、陣列的資料欄或傳回陣列的函式。輸入值必須是整數或小數類型。

叫用此函式時,系統會將輸入陣列的所有值傳送至對應的欄位式函數。叫用此函式時可能會有某些限制。請參閱 MODE 函式一文。

基本用法

常值範例:

derive type:single value:LISTMODE([0,0,2,4,6,8,10,12,14,16,18,20]) as:'myArrayMode'

輸出:產生輸出資料欄,其中包含常值陣列中的所有最常用值:0

資料欄範例:

derive type:single value:LISTMODE(myArray) as:'myArrayMode'

輸出:產生輸出資料欄,其中包含 myArray 資料欄陣列中所有值的模式。

語法

derive type:single value:LISTMODE(array_ref)

引數是否必要?資料類型說明
array_ref陣列陣列常值、含有陣列之資料欄的參照或傳回陣列的函式。

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

array_ref

陣列的參照可以是陣列常值、傳回陣列的函式或含有陣列的單一資料欄。

  • 如果輸入了無效的數字陣列,則會傳回空值。
  • 在輸入陣列中,數字以外的值皆不列入計算。
  • 不支援多個資料欄和萬用字元。

使用須知:

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

陣列

myArray

範例

範例 - (陣列) 清單的數學函式

這個範例說明如何產生隨機的陣列 (清單) 資料,以及如何在陣列中套用下列數學函式。

  • LISTSUM - 對陣列的所有值進行加總。請參閱 LISTSUM 函式一文。
  • LISTMIN - 陣列所有值的最小值。請參閱 LISTMIN 函式一文。
  • LISTMAX - 陣列所有值的最大值。請參閱 LISTMAX 函式一文。
  • LISTAVERAGE - 陣列所有值的平均值。請參閱 LISTAVERAGE 函式一文。
  • LISTVAR - 陣列所有值的變異數。請參閱 LISTVAR 函式一文。
  • LISTSTDEV - 陣列所有值的標準差。請參閱 LISTSTDEV 函式一文。
  • LISTMODE - 陣列所有值最常見的值。請參閱 LISTMODE 函式一文。

來源:

在這個範例中,您可以使用下列步驟產生某些隨機排列的資料。首先,您必須使用 RANGE 函式,將特定範圍的值植入陣列中:

derive type: single value: RANGE(5, 50, 5) as: 'myArray1'

然後,將這個陣列拆分,才能夠新增隨機因素:

unnest col: myArray1 keys: '[0]', '[1]', '[2]', '[3]', '[4]', '[5]', '[6]', '[7]', '[8]', '[9]' pluck: true markLineage: true

新增隨機排列的因素。您可以在此處新增介於 x-1 < x < x+4 個別值的隨機排列。

set col: myArray1_0~myArray1_8 value: IF(RAND() > 0.5, $col + (5 * RAND()), $col - RAND())

您可以將數值四捨五入到小數第二位,以便輕鬆管理數字:

set col: myArray1_0~myArray1_8 value: ROUND($col, 2)

在陣列中重新建立這些巢狀資料欄:

nest col: myArray1_0, myArray1_1, myArray1_2, myArray1_3, myArray1_4, myArray1_5, myArray1_6, myArray1_7, myArray1_8 into: array as: 'myArray2'

刪除不使用的資料欄:

drop col: myArray1_0~myArray1_8,myArray1 action: Drop

畫面會顯示類似以下的資料內容:

myArray2
["8.29","9.63","14.63","19.63","24.63","29.63","34.63","39.63","44.63"]
["8.32","14.01","19.01","24.01","29.01","34.01","39.01","44.01","49.01"]
["4.55","9.58","14.58","19.58","24.58","29.58","34.58","39.58","44.58"]
["9.22","14.84","19.84","24.84","29.84","34.84","39.84","44.84","49.84"]
["8.75","13.36","18.36","23.36","28.36","33.36","38.36","43.36","48.36"]
["8.47","14.76","19.76","24.76","29.76","34.76","39.76","44.76","49.76"]
["4.93","9.99","14.99","19.99","24.99","29.99","34.99","39.99","44.99"]
["4.65","14.98","19.98","24.98","29.98","34.98","39.98","44.98","49.98"]
["7.80","14.62","19.62","24.62","29.62","34.62","39.62","44.62","49.62"]
["9.32","9.96","14.96","19.96","24.96","29.96","34.96","39.96","44.96"]

轉換:

以下步驟示範了如何將個別數學函式在不解除巢狀的狀況下,就套用到清單資料上。

附註:每個清單函式前後會加上 NUMFORMAT 函式,才能計算任何浮點錯誤或是結果中的其他數字。

陣列 (清單) 所有值的總和:

derive type: single value: NUMFORMAT(LISTSUM(myArray2), '#.##') as: 'arraySum'

陣列 (清單) 所有值的最小值:

derive type: single value: NUMFORMAT(LISTMIN(myArray2), '#.##') as: 'arrayMin'

陣列 (清單) 所有值的最大值:

derive type: single value: NUMFORMAT(LISTMAX(myArray2), '#.##') as: 'arrayMax'

陣列 (清單) 所有值的平均值:

derive type: single value: NUMFORMAT(LISTAVERAGE(myArray2), '#.##') as: 'arrayAvg'

陣列 (清單) 所有值的變異數:

derive type: single value: NUMFORMAT(LISTVAR(myArray2), '#.##') as: 'arrayVar'

陣列 (清單) 所有值的標準差:

derive type: single value: NUMFORMAT(LISTSTDEV(myArray2), '#.##') as: 'arrayStDv'

陣列 (清單) 所有值的模式 (最常見的值):

derive type: single value: NUMFORMAT(LISTMODE(myArray2), '#.##') as: 'arrayMode'

結果:

前四個數學函式的結果:

myArray2arrayAvgarrayMaxarrayMinarraySum
["8.29","9.63","14.63","19.63","24.63","29.63","34.63","39.63","44.63"]25.0444.638.29225.33
["8.32","14.01","19.01","24.01","29.01","34.01","39.01","44.01","49.01"]28.9349.018.32260.4
["4.55","9.58","14.58","19.58","24.58","29.58","34.58","39.58","44.58"]24.5844.584.55221.19
["9.22","14.84","19.84","24.84","29.84","34.84","39.84","44.84","49.84"]29.7749.849.22267.94
["8.75","13.36","18.36","23.36","28.36","33.36","38.36","43.36","48.36"]28.448.368.75255.63
["8.47","14.76","19.76","24.76","29.76","34.76","39.76","44.76","49.76"]29.6249.768.47266.55
["4.93","9.99","14.99","19.99","24.99","29.99","34.99","39.99","44.99"]24.9844.994.93224.85
["4.65","14.98","19.98","24.98","29.98","34.98","39.98","44.98","49.98"]29.3949.984.65264.49
["7.80","14.62","19.62","24.62","29.62","34.62","39.62","44.62","49.62"]29.4249.627.8264.76
["9.32","9.96","14.96","19.96","24.96","29.96","34.96","39.96","44.96"]25.4444.969.32229

統計函式的結果:

myArray2arrayModearrayStDvarrayVar
["8.29","9.63","14.63","19.63","24.63","29.63","34.63","39.63","44.63"] 12.32151.72
["8.32","14.01","19.01","24.01","29.01","34.01","39.01","44.01","49.01"] 13.03169.78
["4.55","9.58","14.58","19.58","24.58","29.58","34.58","39.58","44.58"] 12.92166.8
["9.22","14.84","19.84","24.84","29.84","34.84","39.84","44.84","49.84"] 13.02169.46
["8.75","13.36","18.36","23.36","28.36","33.36","38.36","43.36","48.36"] 12.84164.95
["8.47","14.76","19.76","24.76","29.76","34.76","39.76","44.76","49.76"] 13.14172.56
["4.93","9.99","14.99","19.99","24.99","29.99","34.99","39.99","44.99"] 12.92166.93
["4.65","14.98","19.98","24.98","29.98","34.98","39.98","44.98","49.98"] 13.9193.16
["7.80","14.62","19.62","24.62","29.62","34.62","39.62","44.62","49.62"] 13.23175.08
["9.32","9.96","14.96","19.96","24.96","29.96","34.96","39.96","44.96"] 12.21149.17

個別陣列中的所有值都不可重複,因此只要 arrayMode 資料欄其中任一個陣列產生空白值,就不會有最常見的值。

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

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

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