BigQuery SQLの勘所Ⅱ IF文、CASE文 分析関数

今回はIF文、CASE文のパターンと分析関数について触れる。

IF文

if(tune_id>=10,'J POP','K POP') as category
as で列名を追加可能。trueでJ POP

CASE文

case
when
regexp_countans(country,r'US | UK | France | Japan | Itary | German | Canada ') then 'G7'
else 'not G7'
end as G7ornotG7

CASE文の流れ：
case
when-then
else
end as ***

case
when tune_id <=6 then 'J POP'
when tune_id <=10 then 'K POP'
when tune_id <=18 then 'Euro'
else 'American'
end as category

*≧は上から順に処理される。

case round(sales_umaibou,-3)
when 0.0 then '499以下'
when 1000.0 then '500以上'
when 2000.0 then '1500以上'
else '2500以上'
end as rounded_sales

CASE文の流れ：
case 関数
when-then
else
end as ***
*caseの後に関数が付くケースがある。

分析関数

over(
partition by* 分析対象
order by*
window frame
) as ****
window frame は省略可
*特定の列内要素を分けてそれぞれ集計関数やorder by を適用する。
*sum()等の集計関数を伴っている。

window frame：
between [A] and [B]
で表現

[A]
unbounded preceding パーティション上限
*[正の整数] preceding 現在行から正の整数分上

[B]
unbounded following パーティション下限
current row 現在行
*[正の整数] following 現在行から正の整数分下

sum()
over(
**********
**********
rows between undounded preceding and current row
)
*累計

avg()
over(
**********
**********
rows between 6 preceding and current row
)
*直近7日の移動平均

分析関数例

rank()
over(
partition by tune_id
order by quantity desc
) as rank
* tune_id 内で quantity 降順で　ランキング

page,
row_number()
over(
partition by session_count
order by timestamp) as order_t
* ページ遷移の順位付け

first_value(page)
over
(
partition by session_count
order by timestamp
rows between unbounded preceding and unbounded following
) as first_p,
last_value(page)
over
(
partition by session_count
order by timestamp
rows between unbounded preceding and unbounded following
) as last_p
*ランディングページと離脱ページをセッション別で返している

参考：BigQuery で学ぶ非エンジニアのための SQL データ分析入門