COUNTIF BIGQUERY

Question

在BigQuery中，我想知道每个球员中，谁赢得的分数最多，得分最少。

points_name_one  |points_name_two |name_one |name_two |concat_name  |   
---------------------------------------------------------------------
1                |  1             |Lou      |Max      |Lou - Max    |    
1                |  1             |Max      |Elie     |Max - Elie   |     
3                |  0             |Elie     |Zoe      |Elie - Zoe   |      
1                |  1             |Max      |Elie     |Max - Elie   |     
1                |  1             |Lou      |Max      |Lou - Max    |    
0                |  3             |Lou      |Max      |Lou - Max    |    
1                |  1             |Julien   |Max      |Julien - Max |         
1                |  1             |Max      |Elie     |Max - Elie   |     
3                |  0             |Elie     |Zoe      |Elie - Zoe   |      
0                |  3             |Zoe      |Lou      |Zoe - Lou    | 
3                |  0             |Lou      |Max      |Lou - Max    |    
1                |  1             |Max      |Elie     |Max - Elie   |     

我想我必须使用COUNTIF与over ( https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#countif )，但我尝试，但没有成功。

Answer 1

如果我能很好地理解你的要求，下面的查询会向你的对手玩家展示你从谁那里得到的分数最多和最少。

WITH games AS (
  SELECT name_one AS player, name_two AS opponent, points_name_one AS point  FROM sample
   UNION ALL
  SELECT name_two, name_one, points_name_two FROM sample
)
SELECT player, opponent, SUM(point) AS points,
       FIRST_VALUE(opponent) OVER w AS least_from, 
       LAST_VALUE(opponent) OVER w AS most_from, 
  FROM games
 GROUP BY 1, 2
WINDOW w AS (PARTITION BY player ORDER BY SUM(point) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)  
;

输出：

例如，Elie从Max得到4分，这是最少的，从Zoe得到6分，后者是最多的。

​

​

Answer 2

还请考虑以下方法

select name_one as player, 
  array_agg(struct(name_two as most_from, points as most_points) order by points desc limit 1)[offset(0)].*,
  array_agg(struct(name_two as least_from, points as least_points) order by points limit 1)[offset(0)].* 
from (
  select name_one, name_two, sum(points_name_one) points
  from your_table group by 1,2 
  union all
  select name_two, name_one, sum(points_name_two) 
  from your_table group by 1,2
)
group by player  

如果应用于问题中的样本数据，则输出为

​

​