文章段落
BigQuery Data types,Functions
這一篇我們要來介紹BQ的資料型態與它的功用，BQ支援數字/文字/時間/地理位置/結構化/半結構化等資料型態。底下為大家一一說明
INT64
這是一個數字的整數型的資料也就是數字沒帶小數點，範圍可以從10的負19次方到10的19次方。若是real-valued的數字哪麼就需要用FLOAT64的資料型態。
Numeric
提供小數點前38位數及小數點之後9個位數的數字型態，這一類的資料型態適合被拿來做計算。若您的資料有需要在資料庫做計算就是適合此類的資料型態。
STRING
文字字串，提供不特定長度的文字字串以Unicode為基礎。
TimeStamp
一個絕對數字的時間值資料型態
DateTime
這是一個日曆的時間型態，Dtae 跟Time可以分開來。
Geography
顯示的是經緯度的資料型態
Struct/Array
這個在我們前面的BQ SQL基本操作裡有提到並且有範例。
我們來看一個Numeric型態的例子
with examples as (
select ‘Mon’ as day, 1481 as numrides, 1051 as oneway
union all select ‘Sun’, 2336, 2936
)
select *, (oneway/numrides) as frac_oneway from examples
我們看到這是一個簡單的四則運算，但運算的結果就是有好多小數點的位數。我們這時可以用另一個方式來取小數點後的位數。請參考以下範例。
with examples as (
select ‘Mon’ as day, 1481 as numrides, 1051 as oneway
union all select ‘Sun’, 2336, 2936
)
select *, Round(oneway/numrides, 3) as frac_oneway from examples
我們可以用Round 在運算出來的數字後，後面接一個數字來決定我們要取小數點後的幾位數。但有一個特殊狀況，若運算中分母為零的話哪不管什麼數算出來都是零。這時若有這種狀況我們必須再加一個參數上去，請看底下範例。
with examples as (
select ‘Mon’ as day, 1481 as numrides, 1051 as oneway
union all select ‘Sun’, 2336, 2936
union all select ‘Thu’, 0, 0
)
select *, Round(IEEE_Divide(oneway, numrides), 3) as frac_oneway from examples
使用IEEE_Divide這個參數就會針對在運算的數字中特別的資料回傳一個NaN(Not a Number)的值回來。在上面的兩個例子中都是有一到多個數字的input然後回傳一個單一數字。
做完運算後通常我們都會需要做比較，可能是大於/小於/不等於，讓我們看底下的範例
with examples as (
select ‘Mon’ as day, 1481 as numrides, 1051 as oneway
union all select ‘Sun’, Null, Null
union all select ‘Thu’, Ieee_Divide(-3,0), 0
)
select *, from examples
order by numrides
在這邊我們看到三種狀況，正常的數字運算Mon,Null值Sun及運算後為-Infinity – Thu。我們用-3除與0在numrides就變成無限的狀況了，這樣我們在比較數字如果有Null就會出狀況。請參考以下範例
with examples as (
select ‘Mon’ as day, 1481 as numrides, 1051 as oneway
union all select ‘Sun’, Null, Null
union all select ‘Thu’, Ieee_Divide(-3,0), 0
)
select *, from examples
where numrides < 2000
我們在認知上會覺得Null值應該也是小於2000,但卻沒有顯示出來。這是因為用where條件式，它只會比對結果“是”或“否”的小於兩千。跟Null相比後得出來的結果是Null就不是”True是“的值了。所以怎麼處理Null值在BQ裡就變得很重要了，由於BQ不建議update row，所以還是建議在ETL到BQ過程中處理好Null值。
雖然INT64 與FLOAT64的資料型態能給提供彈性的數字長度與快速的運算，但這是屬於二進制的數字運算對於財務方面的數字運算(以十進制），BQ提供了Numeric的38位的數字型態，其中小數點後有9個數字位。我們假設今天需要計算薪資，我們分別用FLOAT64與numeric資料型態來看一下有哪邊不一樣的地方。請看以下兩個範例。
with salary as (
select 1023.5 as payment
union all select 1045.65
union all select 134.76
)
select
sum(payment) as total_paid,
avg(payment) as avg_paid
from salary
with salary as (
select Numeric ‘1023.5’ as payment
union all select numeric ‘1045.65’
union all select numeric ‘134.76’
)
select
sum(payment) as total_paid,
avg(payment) as avg_paid
from salary
我們看到上面兩個範例中，如果是FLOAT64資料型態我們在total paid看到的小數字會變得很長。我們原來的資料位數最多只有兩位數但使用FLOAT64會讓數字變得很長，但改成Numeric之後就total paid這一欄就變得跟我們原來的數字位數一樣的。特別要注意的是BQ的數字預設型態是FLOAT64，若妳處理的資料是要用Numeric型態，在將資料匯入BQ時就必需要指定為Numeric資料。
BOOL布林運算
用過資料庫的人應該很熟悉布林運算，BQ也提供了相同的功能。
我們回顧之前提到過CITI Bike的範例
在where 條件式裡我們就用到了布林運算，讓我們看一下一個簡單的範例
With examples as (
select null as is_vowel, null as letter, -1 as position
union all select true, ‘a’, 1
union all select false, ‘b’, 3
union all select false, ‘c’, 5
)
select * from examples where is_vowel != false
這個簡單的範例是要比對 is_vowel 這個欄位中資料不等於 false的。很明顯三個列的資料只有一個不等於false.我們也可以是 is not的條件式。請看以下範例
With examples as (
select null as is_vowel, null as letter, -1 as position
union all select true, ‘a’, 1
union all select false, ‘b’, 3
union all select false, ‘c’, 5
)
select * from examples where is_vowel IS NOT false
這裡有一點不一樣的是，我們若用 IS NOT條件式來比對，它會把NULL值也給顯示出來，只有當我們用符號(=,!=,< 等）,NULL值才不會被顯示。所以如同之前提到過要將資料匯入進BQ時要特別處理NULL值的部分。不然在ＢＱ做資料分析時分析出來的結果很可能會失真。
除了我們可以用where條件式來做布林運算當然還有其他的方式，例如我們有一個成本資料庫裡面有公司的進貨成本對不同的經銷商有不同的利潤及稅率。請看以下範例
With catalog as (
select 300 as costPrice, 0.2 as markup, 0.05 as taxRate
union all select NULL, 0.3, 0.15
union all select 300, NULL, 0.06
union all select 300, 0.3, NULL
union all select 300, NULL, NULL
)
select
*, Round(
costPrice * IF(markup is NULL, 1.05, 1+markup) *
IF(TaxRate IS NULL, 1.10, 1+taxRate), 2) as salesPrice
from Catalog
在這裡我們用IF條件式來達到我們要做布林運算的目的。在IF判斷式中若比對成立是True哪我們就用第一個參數，例如在Markup欄位若Markup欄位是NULL就乘與1.05，若不是就用原來的markup數字在加1來相乘。
但我們在利潤與稅率中都看到有Null值的出現，IF條件式只能幫我們擇其一來選擇。若我們要ㄧ並處理Null值呢？這時就可以用COALESCE合併來做計算，請看以下範例
With catalog as (
select 300 as costPrice, 0.2 as markup, 0.05 as taxRate
union all select NULL, 0.3, 0.15
union all select 300, NULL, 0.06
union all select 300, 0.3, NULL
union all select 300, NULL, NULL
)
select
*, Round(COALESCE(
costPrice * (1+markup) * (1+taxRate),
costPrice * 1.05 * (1+taxRate),
costPrice * (1+markup) * 1.10,
NULL
),2) as salesPrice
from Catalog
由上面的COALESCE條件式我們可以指定遇到Null值時該用什麼值填入做計算，
例如第二行 1.05 * (1+taxRate)，在遇到markup是Null值時就用1.05這個數字來代替。
接下來我們來看一個員工工時計算了範例
with empwork as (
select ‘Jason’ as employee, ‘Paternity Leave’ as hours_worked
union all select ‘Aaron’, ’40’
union all select ‘Bob’, ‘Vaction’
union all select ‘Bill’, ’40’
)
select sum(hours_worked) from empwork
這個範例沒有辦法運作，因為運算只能針對數字。而hours_worked欄位中有字串型態的資料。這時我們在運算前要宣告要運算的資料型態使用safe_cast的條件式，請參考以下範例
with empwork as (
select ‘Jason’ as employee, ‘Paternity Leave’ as hours_worked
union all select ‘Aaron’, ’40’
union all select ‘Bob’, ‘Vaction’
union all select ‘Bill’, ’40’
)
select sum(SAFE_CAST(hours_worked as INT64)) from empwork
這其實是一個不好的資料庫設計，這個欄位裡不應該有數字與文字混再一起。上面的做法就是在計算時跳過所有的文字，如同之前提過這樣後續在做資料分析時會有失真的狀況。
另一種狀況是雖然欄位的資料都是數字但在匯入資料時的欄位卻是文字型態，這可以用casting的解決，請參考以下範例
with empwork as (
select ‘Jason’ as employee, ‘0’ as hours_worked
union all select ‘Aaron’, ’40’
union all select ‘Bob’, ’35’
union all select ‘Bill’, ’40’
)
select sum(CAST(hours_worked as INT64)) from empwork
BQ可以將INT64資料性型態轉換成FLOAT64跟Numeric.
Numeric可以轉換成FLOAT64。但如果我們要計算的欄位是True/False的總數呢？請看一下範例
我們先看第一種做法
with examples as (
select true as is_vowel, ‘a’ as letter, 1 as position
union all select true, ‘b’, 2
union all select false, ‘c’, 3
union all select false, ‘d’, 4
)
select sum(cast (is_vowel as INT64)) as num_vowels from examples
上面範例我們直接強制將欄位轉化成INT64後做計算。計算到true的欄位有兩列是True，但我們其實應該避免這樣的做法。比較好的方式應該是用COUNTIF，請參考以下範例
with examples as (
select true as is_vowel, ‘a’ as letter, 1 as position
union all select true, ‘b’, 2
union all select false, ‘c’, 3
)
select countif(is_vowel) as num_vowels from examples
以上就是介紹一些BQ資料型態的支援，下一篇我們將繼續介紹BQ其他的資料型態。
