DAX 第六篇：統計函式

悅光陰發表於2019-07-25
函式

統計函式用於建立聚合，對資料進行統計分析。在使用統計函式時，必須考慮到資料模型，表之間關係，資料重複等因素，一般都會搭配過濾函式實現資料的提取和分析。

統計量一般是：均值、求和、計數、最大值、最小值、求中位數、獲得分位數等。

一，求均值

均值分為幾何均值和算術均值，幾何平均數是n個變數值連乘積的n次方根：

常用下面兩個函式計算列值的算術平均值，AVERAGE函式用於對錶中的數值型的列計算均值，並且只能用於基礎表，引數的格式是table[decimal_column]：

AVERAGE(<column>) 
AVERAGEX(<table>,<expression>)

而AVERAGEX函式功能更為強大，引數table可以是基礎表，也可以是返回表值的函式；引數 expression 是關於列的表示式，函式計算表示式的均值：

=AVERAGEX(InternetSales, InternetSales[Freight]+ InternetSales[TaxAmt])

對於幾何均值，有下面兩個函式來計算：

GEOMEAN(<column>)
GEOMEANX(<table>, <expression>)

二，求和

通過以下兩個函式來計算加和，SUM函式只能用於數值型的列，並且只能用於基礎表，引數的格式是table[decimal_column]：

SUM(<column>)
SUMX(<table>, <expression>)

SUMX函式從表中計算每一個行的加和，引數table可以是基礎表，也可以是返回表值的函式；引數 expression 是關於列的表示式，只有數值會被加和，忽略空值，date，邏輯值或文字值：

 示例，第一個引數是過濾器返回的表值，計算[Freight]的加和：

=SUMX(FILTER(InternetSales, InternetSales[SalesTerritoryID]=5),[Freight])

可以把SUMX函式，轉換為CALCULATE函式：

=CALCULATE( SUM(InternetSales[Freight]), FILTER(InternetSales, InternetSales[SalesTerritoryID]=5))

三，計數

常用的計數函式有7個，函式的語法如下：

COUNT(<column>)
COUNTA(<column>)
DISTINCTCOUNT(<column>)
COUNTBLANK(<column>)
COUNTROWS(<table>)
COUNTX(<table>,<expression>)
COUNTAX(<table>,<expression>)

這7個函式都用於計數，根據函式的名稱，大致分為5類：

  • 函式名稱中的 A 是指適用於Any 資料型別，不帶A的函式只能用於數值、日期和字串，不支援邏輯型別；不統計Blank值；
  • 函式名稱帶字尾X的函式適用於基礎表和返回表值的表示式，不帶X的函式只能用於基礎表；不統計Blank值；
  • 統計Blank值
  • 統計總行數
  • 唯一值計數

下面的5個函式用於基礎表，根據列值和列的型別進行計數：

  • COUNT                  ：統計列值不為Blank的行的數量，列值的型別可以是：數值、日期和字串，不支援邏輯型別，Blank值會被忽略。
  • COUNTA                ：統計列值不為Blank的行的數量，列值的型別可以是：數值、日期、字串和邏輯型別，Blank值會被忽略。
  • COUNTBLANK      ：統計列值是Blank的行的數量，列值的型別可以是任意型別，該函式只統計包含Blank值的行的數量。
  • COUNTROWS      ：統計表的總行數
  • DISTINCTCOUNT ：統計列值不為Blank，且不重複的數量，列值的型別可以是任意型別

下面的2個函式用於基礎表，或返回表的表示式中：

  • COUNTX           ：統計列值不為Blank的行的數量，列值的型別可以是：數值、日期和字串，不支援邏輯型別，Blank值會被忽略。
  • COUNTAX         ：統計列值不為Empty（Blank）的行的數量，列值的型別可以是：數值、日期、字串和邏輯型別，Blank值會被忽略。

注意：在COUNTAX函式中，如果列中包含表示式，而表示式的結果是空值，在這種情況下，COUNTAX函式把包含公式的列值作為非空（nonblank）看待，計數值會增加。如果COUNTAX函式的列中不包含表示式，當列值為Blank時，COUNTAX函式會忽略Blank值，計數值不會增加。

4，最大，最小

求最大值和最小值的函式：

MAX(<column>)
MAX(<expression1>, <expression2>)
MAXA(<column>)
MAXX(<table>,<expression>)
MIN(<column>)
MIN(<expression1>, <expression2>)
MINA(<column>)
MINX(<table>, < expression>)

5，中位數

中位數和分位數都是針對數值型進行統計的，Blank、日期、邏輯值和文字會被忽略。

MEDIAN(<column>)
MEDIANX(<table>, <expression>)

6，分位數

k表示期望的百分位值，其中INC是指inclusive（包含），EXC是指exclusive（不包含）。字尾帶EXC的函式，引數k的取值範圍是0-1，不包含0和1；字尾帶INC的函式，引數k的取值範圍是0-1，包含0和1。

PERCENTILE.EXC(<column>, <k>) 
PERCENTILE.INC(<column>, <k>)
PERCENTILEX.EXC(<table>, <expression>, k) 
PERCENTILEX.INC(<table>, <expression>, k)

當指定百分位數的值介於陣列中的兩個值之間時，這4個函式都會進行插值。 如果無法插入指定的k百分位數，則返回錯誤。

  • 對於 INC函式，如果k不是1 /（n - 1）的倍數，則這4個函式將進行插值以確定第k個百分位數的值。
  • 對於 EXC函式，如果k不是1 /（n + 1）的倍數，則這4個函式將進行插值以確定第k個百分位數的值。

PERCENTILE.INC

計算原理是：對於陣列中的每個值，都會按照從小到大的順序給定一個百分位(基於n-1)，假如陣列有n個數值，這n個百分位分別是：0/(n-1)、1/(n-1)、2/(n-1)……n-1/(n-1)，當k值與這些百分位相同時，即k是1/(n-1)的倍數，直接返回陣列中對應的數值，如果k不是 1/(n-1) 的倍數，則 PERCENTILE.INC 使用插值法來確定第k個百分點的值。

PERCENTILE.EXC

計算原理是：對於陣列中的每個值，都會按照從小到大的順序給定一個百分位(基於n+1)，假如陣列有n個數值，這n個百分位分別是：1/(n+1)、2/(n+1)、3/(n+1)……n/(n+1)，當k值與這些百分位相同時，即k是1/(n+1)的倍數，直接返回陣列中對應的數值，如果k不是 1/(n+1) 的倍數，則 PERCENTILE.EXC 使用插值法來確定第k個百分點的值。

引用簡書上《 分位數計算，分析Excel中函式實現原理》的一個例子，作者是過橋0811 ：

Python程式碼實現：

import math

def percentile_inc(array,k):
    
    if len(array) == 0:
        return "陣列不能為空"
    if k > 1 or k < 0:
        return "係數需為 0 到 1 之間的百分點值，包含 0 和 1"
    array_sort = sorted(array)

    address = (len(array_sort) - 1) * k + 1

    if address == len(array_sort):
        return array_sort[len(array_sort) - 1]

    i = int(math.modf(address)[1]) #取出整數部分
    j = math.modf(address)[0] #取出小數部分

    value = array_sort[i-1] + (array_sort[i] - array_sort[i-1]) * j
    #print("陣列為：" + str(array), "係數為：" + str(k),"百分位數為：" + str(value))
    return value

def percentile_exc(array,k):
    
    if len(array) == 0:
        return "陣列不能為空"
    if k >= 1 or k <= 0:
        return "係數需為 0 到 1 之間的百分點值，不包含 0 和 1 "
    array_sort = sorted(array)

    address = (len(array_sort) + 1) * k
    if address < 1:
        return "因係數過小，不能通過插入值來確定指定的百分點的值"

    i = int(math.modf(address)[1]) #取出整數部分
    j = math.modf(address)[0] #取出小數部分

    value = array_sort[i-1] + (array_sort[i] - array_sort[i-1]) * j
    #print("陣列為：" + str(array), "係數為：" + str(k),"百分位數為：" + str(value))
    return value

print(percentile_inc([10,9,8,7,6,5,4,3,2,1,0],0))
print(percentile_inc([10,9,8,7,6,5,4,3,2,1,0],0.01))
print(percentile_inc([10,9,8,7,6,5,4,3,2,1,0],0.25))
print(percentile_inc([10,9,8,7,6,5,4,3,2,1,0],1))
print(percentile_inc([1,3,2,4],0.3)) # 官網測試資料

print(percentile_exc([10,9,8,7,6,5,4,3,2,1,0],0))
print(percentile_exc([10,9,8,7,6,5,4,3,2,1,0],0.01))
print(percentile_exc([10,9,8,7,6,5,4,3,2,1,0],0.09))
print(percentile_exc([10,9,8,7,6,5,4,3,2,1,0],0.25))
print(percentile_exc([1,2,3,6,6,6,7,8,9],0.25)) # 官網測試資料
View Code

 

 

 

 

 

參考文件：

Statistical functions

相關文章