GROUP BY and division

Question

在GBQ表中，我有结构(page_location, event_name, count) event_name可以有值'view'， 'add_basket'。我想接收结构- GROUP BY(page_location)，(从event_name = 'add_basket'的值计数/从event_name = 'view'的值计数)。我试过PIVOT和OVER，我已经不知道怎么做了。

有

page_location, event_name, count
A, view, 100
A, add_basket, 10
A, view, 200
A, add_basket, 20`

必要

page_location, count
A, 0.1
B, 0.1

我的表

Answer 1

考虑下面的方法

select page_location, add_basket/view as count
from your_table
pivot (sum(count) for event_name in ('view','add_basket'))

Answer 2

您可以将函数sum和case when结合起来解决您的问题。我的解决方案如下:

select page_location
,sum(case when event_name = 'add_basket' then count else 0 end) 
/ sum(case when event_name = 'view' then count else 0 end) end as ratio
from gbq
group by page_location