使用分组依据和计数创建赢/输记录

Question

im 尝试创建 3 字段查询结果、市场、胜利和损失。 输赢结果基于布尔字段，只是计算记录的数量，无论是真还是假，我创建了 3 个单独的查询并让它们给我我想要的结果，只是在谷歌大查询中组合它们时遇到问题，收到错误"标量子查询产生了多个元素"，并没有真正理解该语句， 谢谢

select market, count(*)TotalGamesPLayed, 
(select count(*)
from `bigquery-public-data.ncaa_basketball.mbb_teams_games_sr` 
WHERE
season IN (2017)
AND conf_name IN ("Big Ten") and win = true 
group by season, market
order by market asc)win,
(select count(*)
from `bigquery-public-data.ncaa_basketball.mbb_teams_games_sr` 
WHERE
season IN (2017)
AND conf_name IN ("Big Ten") and win = false 
group by season, market
order by market asc)loss,
from `bigquery-public-data.ncaa_basketball.mbb_teams_games_sr` 
WHERE
season IN (2017)
AND conf_name IN ("Big Ten") 
group by market
order by market asc

Answer 1

下面是 BigQuery Standard SQL

#standardSQL
SELECT 
market, 
COUNT(*) AS TotalGamesPLayed, 
COUNTIF(win) AS win, 
COUNTIF(NOT win) AS loss 
FROM `bigquery-public-data.ncaa_basketball.mbb_teams_games_sr` 
WHERE season IN (2017)
AND conf_name IN ("Big Ten") 
GROUP BY market
ORDER BY market ASC

Answer 2

目前，您正在尝试运行子查询SELECT原因通常需要与外部查询相关联才能返回单个值。相反，将子查询联接为FROM|和JOIN子句中的派生表。但是，由于这两个查询共享相同的源，因此请运行一个条件聚合查询，其中WHERE条件将移动到CASE语句。

select market,
count(*) as total_games,
sum(case 
when win = true 
then 1
else 0
end) as win,
sum(case 
when win = false
then 1
else 0
end) as loss
from `bigquery-public-data.ncaa_basketball.mbb_teams_games_sr`
where season = 2017 
and conf_name = 'Big Ten'
group by market
order by market asc

此外，在某些数据库中，您甚至可以绕过CASE并对逻辑条件求和：

sum(win = true) as win,
sum(win = false) as loss