google-bigquery - 在BigQuery SQL中使用IF

Question

案例：我在BQ中有Sales表，并且item_num列包含值1，-1和0。我想计算每个值有多少个案例。

在下面尝试了一个简单的查询，但每种情况下count返回的数字都完全相同。

SELECT 
count(if(item_num > 0,1, 0)) as buysplus,
count(if(item_num < 0,1, 0)) as buysminus,
count(if(item_num = 0,1, 0)) as buyszero
from MyShop.Sales

谢谢

Answer 1

SELECT 
  SUM(IF(item_num > 0, 1, 0)) AS buysplus,
  SUM(IF(item_num < 0, 1, 0)) AS buysminus,
  SUM(IF(item_num = 0, 1, 0)) AS buyszero
FROM MyShop.Sales

甚至更详细的版本：

SELECT 
  SUM(item_num > 0) AS buysplus,
  SUM(item_num < 0) AS buysminus,
  SUM(item_num = 0) AS buyszero
FROM MyShop.Sales

这将给你这样的结果

buysplus    buysminus   buyszero     
4           2           3

另一个选择是它的转置版本

SELECT 
  item_num AS buys,
  COUNT(1) AS volume
FROM MyShop.Sales
GROUP BY 1  

结果如下

 buys        volume  
 0          3    
 1          4    
-1          2