上一篇BQ part 1我們介紹了一些簡單的指令，這一篇我們將介紹在BQ上稍微難一些的SQL語法
第一個是平均”AVG”，我們一樣以citibike作為範例
底下的語法我們計算了關於男性的騎乘平均的時間。計算tripduration這一個欄位除60之後單位就是分鐘，在針對這個欄位所有的資料做一個平均值其結果是13.4分鐘
select
AVG(tripduration / 60) AS avgtripduration
from
`bigquery-public-data`.new_york_citibike.citibike_trips
where
gender = ‘male’
若我們要分配在更細一點對“性別”做分組 使用group by，範例如下
select
gender, AVG(tripduration / 60) AS AvgTripDuration
from
`bigquery-public-data`.new_york_citibike.citibike_trips
where
tripduration is not NULL
Group BY
gender
Order BY
AvgTripDuration
若要計算row的總數就須使用“count”指令。從以下範例我們針對三個性別群做騎乘趟數的加總計算。由結果得知男性騎乘趟數最多有3千5百多萬筆
select
gender,
count(*) As Rides,
AVG(tripduration / 60) As AvgTripDuration
from
`bigquery-public-data`.new_york_citibike.citibike_trips
where
tripduration is not NULL
Group BY
gender
Order BY
AvgTripDuration
接下來我們需要再篩選,例如我想要知道每個性別群租用時間是大於10分鐘以上的平均時間是多少，這時可以使用 “Having“指令.請參考以下範例
從這範例可以看到雖然上一個例子中男性地租借次數較多，但女性平均騎乘的時間比較長。
當然這一個篩選方式也可以用where條件來尋找，但是卻無法使用AVG這一個指令。因為這一個範例事先把性別做完群組分類後再做平均。
select
gender, AVG(tripduration / 60) As AvgTripDuration
from
`bigquery-public-data`.new_york_citibike.citibike_trips
where tripduration is not NULL
Group BY gender
Having AvgTripDuration > 10
Order BY
AvgTripDuration
在這範例中我們可以看到被我們用group by群組起來的性別累別有三種，但若一開始我們只想知道這一個欄位有多少不同的資料或數字類別呢？用group by就很麻煩了，我們可以用distinct指令。請參考以下範例
select Distinct
gender
from
`bigquery-public-data`.new_york_citibike.citibike_trips
我們看到有四個row，但最後一個是空的？
讓我們在看仔細一點
select
bikeid,
tripduration,
gender
from
`bigquery-public-data`.new_york_citibike.citibike_trips
where gender = “”
limit 10
我們使用where來尋找為什麼是空白的，看到這些是Null值。這表示程式面這邊可能沒做好處理而後續的ETL工具也沒為這類這Null補上資料。
接下來我們將介紹array 數組的資料型態 ，底下一個簡單的範例
select
city, split(city, ‘ ‘) as Parts
from (
select * from UNNEST([
‘Seattle WA’, ‘New York’, ‘Singapore’
]) AS City
)
在這個範例中我們使用了split 及unnest，在上面的例子中我們將這三個row裡的
‘Seattle WA’, ‘New York’, ‘Singapore’
資料分開來變成新的欄位，在每一個row中以 space為基準分開成兩個row
有分開來的方式當然也有將資料組合起來的方式，請參考以下範例
with examples AS (
select ‘Sat’ as day, 1451 as numrides, 1018 as oneways
Union all select ‘Sun’, 2376, 936
Union all select ‘Mon’, 1476, 736
)
select * from examples
where numrides < 3000
我們使用UNION ALL將資料組合起來成一個row
剛才提到我們若要處理一個數組Array，若以一般的狀況語法大部分會是如下
select
gender
, extract(Year from starttime) as Year
, count(*) As numtrips
from
`bigquery-public-data`.new_york_citibike.citibike_trips
where gender != ‘unknow’ and starttime is not NULL
group by gender, Year
Having Year > 2015
在上面的結果中我會看到大於2015年的性別欄位會有好幾個，這樣子看資料時會很麻煩。所以我們要以性別做一個數組Array， 所以我們使用ARRAY_AGG來處理。請參考以下範例
select
gender
, array_agg(numtrips order by year) as numtrips
from (
select
gender
, extract(Year from starttime) as Year
, count(1) As numtrips
from `bigquery-public-data`.new_york_citibike.citibike_trips
where gender != ‘unknow’ and starttime is not NULL
group by gender, Year
Having Year > 2016
)
group by gender
我們從前面的例子看到資料最多到2018年，所以這一個範例我們將資料縮小到2016年以上。所以這一個Array的性別為主的Array就有2017/2018兩個欄位資料。之前我們用group by 跟AVG來計算每個性別的整個資料庫做單一欄位(性別）的計算。但無法做到依每年來做計算加總，array_agg可以讓你做到再依不同的欄位（年份）做個別的計算加總。
故BQ也可以匯入/匯出結構化的資料型態，如JSON(如下圖）
以上就是BQ part 2 SQL語法的介紹，下一篇我們教介紹如何在BQ中使用 “Joining Tables”
