鑽石舞台

大家好，今天為大家介紹一下有數BI內「計算字段」的使用技巧，關於計算字段的基礎用法和概念，可以移步產品的幫助中心，裡面非常詳細地介紹了每一種函數的具體用法。

在實際的數據分析工作中，我們的數據經常是不「完美」的（例如數據格式不對、字段類型不對、只有明細數據缺少匯總合計等等），這時候計算字段就能幫上大忙。筆者從實際工作經驗出發，挑選了一些場景來演示計算字段的一些技巧。

1.1 求取任意日期所在月份的天數

答案：

思路：我們拿到一個日期（例如2021-01-11）的時候，首先在日期基礎上+1月份（「dateadd 1 month」，得到2021-02-11），然後格式化（「datetrunc month」）得到下月第一天（得到2021-02-01），再對下月第一天-1天（「dateadd -1 day」，得到2021-01-31）得到日期所在月的最後一天，最後再取出日部分（「datepart day」，得到31）。

講解：在數據分析的時候，我們需要以月份天數作為分母的情況比較常見，如果要求的精度不高，我們直接除以30就可以了，但如果對數據精度要求高，那就需要具體的獲取天數了，就可以使用這個技巧。同時，在這個過程中我們一口氣用到了四個日期函數之中最常用三個：dateadd（對日期做常數的加減）、datetrunc（格式化日期）、datepart（取日期部分）。

1.2 選取一段時間範圍，數據每隔7天為粒度進行匯總

答案：{fixed : min([日期])} +int(( datediff("day",{fixed : min([日期])} ,[日期])/7 ))*7 ，再把該字段拖入報表，按此字段聚合以後，選擇用的日期過濾器勾選「優先篩選」。

思路：通過{fixed : min([日期])}獲得所選日期範圍內的最小時間，然後通過datediff讓日期和最小的日期作差，以7為間隔分組，最後聚合，即可實現每7天匯總一次。

講解：這個場景在分析活動數據的時候經常使用，比如我們開展一個活動，但並不總是那麼巧會在周一開始，如果我們按照自然周去統計分析數據，難免在第一周和最後一周的數據上不完整。同時，有一些活動它的性周期可能不是自然周或者自然月的，而是以5天、10天或者自定義的周期而呈現，就可以通過這個方法自定義任意周期進行匯總數據。同時，在這個場景里我們學習了datediff的使用，它的作用是讓兩個日期作差，返回指定的結果（在這個例子裡返回的是天數，也可以返回小時數、月數等等）；還有一個是{fixed :min([日期])}是指獲取結果集裡的最小日期，關於fixed的用法我們在下文繼續介紹。

1.3 若今天是本周周一，則展示上周數據，否則展示本周數據。

答案：if today()=datetrunc("week",today()) then [日期] else dateadd("day",-7,[日期]) ，再把該計算字段拖入日期篩選器，選擇「上周」即可。

思路：首先日期篩選器固定篩選上周，假如今天是周三12.05號，那麼實際上我們要計算的是，12.03至12.05這三天。因此我們如果將所有的日期減去7天，就可以使得12.03至12.05變為11.26至11.28，這樣在日期篩選器上周的作用下，實際上得到本周的數據（相當於把所有表里的日期向前平移一周）。同理，如果今天是周一12.03，那麼我們維持日期不平移，這樣日期篩選器上周得到的就是實際上的11.26至12.02的數據。

講解：這個場景來源於很多離線計算中 T+1 的場景，比如我們的指標展示本周的任務達成率，實際上在周一的時候，數據在周二才能出來，這就會導致周一看數的時候看板上顯示數據為空，而同時在周日看數的時候又看不到周日本身的數據，故而有這個需求。類似的還有，月初第一天看上月數據，否則看本月數據等都是類似的做法。同時，在這個例子裡，我們需要掌握邏輯語法（if …… then…… else…… ），還有系統常量（today()，表示獲取系統當前日期；還有一個是now()，表示獲取系統當前時間。）

2.1 利用正則表達式截取一級目錄標題

答案：REGEXP_EXTRACT_NTH([目錄], "(.+)\_" , 1)

思路：使用正則表達式之前需要先觀察字符串的「模式」，也就是字符串的規律，然後把規律正則化再提取出來。有數的正則表達式有三個參數，依次代表「字符串」、「模式」、「匹配第幾個模式」。我們發現一級目錄總是在一個下劃線的左側，需要取下劃線左側的N個字符作為一級目錄，那麼匹配的模式是 (.+)，點號代表任意字符，加號代表字符是一個或多個，對於模式我們必須要用括號括起來，然後一級目錄匹配的是第一個括號的內容，也就是模式的第一個。

同理，如果取二級目錄，那就是 REGEXP_EXTRACT_NTH([目錄], "(.+)\_(.+)",2)，匹配的是第二個括號的內容。

講解：下表是最常見的幾個正則表達式符號，需要記住（其他的符號用法可以在網絡上搜索）。

( )

標記一個子表達式的開始和結束位置。子表達式可以獲取供以後使用。

*

匹配前面的子表達式零次或多次。要匹配 * 字符，請使用 \*。

+

匹配前面的子表達式一次或多次。要匹配 + 字符，請使用 \+。

.

匹配除換行符 \n 之外的任何單字符。要匹配 . ，請使用 \. 。

?

匹配前面的子表達式零次或一次，或指明一個非貪婪限定符。要匹配 ? 字符，請使用 \?。

\

將下一個字符標記為或特殊字符、或原義字符、或向後引用、或八進制轉義符。例如，'\n' 匹配換行符。

2.2 字符串的一般截取方法

還是2.1中的例子，如果我們不依賴正則表達式應該如何處理才能得到一級目錄呢？

答案：SUBSTR([目錄], 0, FIND([目錄], "_」))

思路：這裡面聯合使用了substr函數和find函數，substr(a,b,c)函數是從字符串a里從第b位開始截取c個字符（例如 substr(「abcd」,0,2)=「ab」 ）。find(a,b,c)是從字符串a里的第c位開始查找字符串b，返回找到的字符串b所在的位置，其中c默認為1（例如 find(「abcda」,」a」,2 )=5）。

通過觀察，一級目錄的位置在第一個下劃線的左側，通過find函數找到第一個下劃線的位置，再截取即可。如果是截取二級目錄，那這個複雜度就會上升一些，沒有正則表達式來得方便。截取二級目錄的方法如下：

可以看出來，截取二級目錄的場景下，普通的截取方法相對正則表達式複雜度會上升不少，但截取一級目錄的場景下普通方法會更簡單易懂。

講解：substr函數、find函數、length函數是經常使用的字符串函數，此外還有CONTAINS(字符串, 子串)函數，用來判斷子串是否在字符串內，常常用於if的條件內做判斷。

第一次接觸有數BI的同學們，對於FIXED表達式應該又愛又恨，它是詳細級別表達式（level of detail expressisons，LOD）的一種，非常簡單地處理了跨粒度計算的問題，但理解起來又比較複雜，稍不留神就容易出現數據對不齊的問題。我們通過一個問題來理解它的用法和原理。

問題：如下圖，如何求城市金額占所在地區銷售總額的占比？

答案：sum([銷售額])/max({fixed [地區]:sum([銷售額])})

思路：我們先理解一下FIXED表達式本身的原理。例如，我想知道每個人的身高與平均身高之間的差別。通常，為了得到結果，我們需要進行兩步查詢，第一步，查出所有人的平均身高：SELECT AVG(height) FROM humans ，假設該值為H；第二步，將每個人的身高減去這個平均值: SELECT height - H FROM humans；

這是一個兩步的過程，如果想使用一個表達式完成這個查詢該怎麼做呢？它的過程就會比較複雜，偽代碼如下：

而LOD就是相當於實現了後者的過程，以便用戶在一個簡單的表達式內就簡單的可以實現這個需求：[身高]- {FIXED : AVG([身高])} 。

可以看出來，FIXED表達式實際上是單獨實現了一個子查詢再連接回父查詢，實現跨粒度的查詢方式，在這個例子中，一個人的身高是明細數據，所有人的身高是匯總數據，在問題里城市的銷售額可以看成明細數據，地區銷售總額則是匯總數據，用FIXED表達式就可以輕鬆實現跨越統計粒度的計算。

用身高這個例子，我們繼續說明一下過濾器對FIXED表達式的影響，大家已經知道FIXED表達式實際上是一個子查詢。如果此時，報表上有一個篩選器，需要過濾性別為男的數據該如何實現呢？直接拖一個性別篩選器，選擇「男」即可？這個篩選等效於

查看偽代碼可以知道，父查詢確實是過濾成了全體男性的數據，但是子查詢呢？實際上還是全體的數據，而非男性的數據。這個時候根據業務場景，如果我們需要比較每個男性身高與平均男性身高的話，這個數據就對不齊了（這就是我們使用FIXED表達式經常遇到的問題，父查詢過濾了，子查詢卻沒有過濾）

我們真正的需求應該是：

也就是說，需要在子查詢里也加上同樣的條件。這個設置，在有數BI的前端，只需要打開篩選器底部的「優先篩選」設置即可。

如果是圖表組件內的數據篩選，勾選「上下文篩選」即可。

有數BI的計算字段功能非常豐富，這篇文章受限於篇幅無法一一介紹，所以優先介紹了一些熱門的計算函數的使用技巧，有機會還會再寫一篇介紹一些比較少用的冷門計算字段，希望大家也多多探索，發現更好用的使用技巧。