从不同位置的同一列中选择不同的计数 [英] Selecting different counts from the same column with different where conditions

问题描述

我有这样的结构:

event_name      country
a               us
a               gb
b               us
b               us
c               gb
c               gb
c               au
d               au
d               au

我正在尝试进行大查询:

I am trying to get in big query:

country     a_count     b_count     c_count     d_count
us          1           2           0           0
gb          1           0           2           0
au          0           0           0           2

我尝试过:

select geo.country as country, 
sum(CASE WHEN event_name like 'a' THEN 1 ELSE 0) END AS `a_count`,  
sum(CASE WHEN event_name like 'b' THEN 1 ELSE 0) END AS `b_count `,
sum(CASE WHEN event_name like 'c' THEN 1 ELSE 0) END AS `c_count `,
sum(CASE WHEN event_name like 'd' THEN 1 ELSE 0) END AS `d_count `
from `table`
group by country;

但是我得到了

语法错误:意外的)"；在[2:62]

Syntax error: Unexpected ")" at [2:62]

如何在大查询中获得此结果?

How can I achieve this result in big query?

推荐答案

在BigQuery中，您将使用 countif():

In BigQuery you would use countif():

select geo.country as country, 
       countif(event_name like 'a') AS a_count,  
       countif(event_name like 'b') AS b_count,
       countif(event_name like 'c') AS c_count,
       countif(event_name like 'd') AS d_count
from `table`
group by country;

我怀疑您的问题是 b_count 末尾的空格.也就是说，语法错误非常明显:

I suspect your problem is the space at the end of b_count . That said, the syntax error is pretty clear:

字段必须仅包含字母，数字和下划线，以字母或下划线开头，并且最多为128个字符.

Fields must contain only letters, numbers, and underscores, start with a letter or underscore, and be at most 128 characters long.

这篇关于从不同位置的同一列中选择不同的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！