如何总和每个值和每个阶段

Question

用BigQuery在每个赛季的每个阶段为每个团队增加要点

saison  |stage |team_home |team_away|home_goal|away_goal| home_point| away_point|   
---------------------------------------------------------------------------------
2002    |  1   |France    |Bresil   |2        |1        |  3        |0          |    
2002    |  1   |Italie    |Bresil   |4        |3        |  3        |0          |    
2002    |  1   |France    |Italie   |1        |1        |  1        |1          |    
2002    |  2   |Italie    |Bresil   |3        |4        |  0        |3          | 
2002    |  2   |France    |Italie   |1        |1        |  1        |1          |    
2002    |  2   |Italie    |Bresil   |3        |4        |  0        |3          | 
2002    |  3   |France    |Italie   |1        |1        |  1        |1          |    
2003    |  1   |Italie    |Bresil   |3        |4        |  0        |3          |    
2003    |  1   |France    |Italie   |1        |2        |  0        |3          |    
2003    |  1   |Bresil    |France   |0        |1        |  0        |3          |    
2003    |  2   |France    |Italie   |1        |2        |  0        |3          |    
2003    |  2   |Bresil    |France   |0        |1        |  0        |3          |    
2003    |  2   |France    |Italie   |1        |2        |  0        |3          |    
2003    |  3   |Italie    |France   |0        |1        |  0        |3          |    

想

saison  |stage |team      |team_point|   
--------------------------------------
2002    |  1   |France    |4          |    
2002    |  1   |Italie    |4          |    
2002    |  1   |Bresil    |0          |  
2002    |  2   |France    |5          |    
2002    |  2   |Italie    |5          |    
2002    |  2   |Bresil    |3          |  
2002    |  3   |France    |6          |    
2002    |  3   |Italie    |6          |    
2002    |  3   |Bresil    |3          | 
2003    |  1   |France    |3          |    
2003    |  1   |Italie    |3          |    
2003    |  1   |Bresil    |3          |  
2003    |  2   |France    |6          |    
2003    |  2   |Italie    |9          |    
2003    |  2   |Bresil    |3          |  
2003    |  3   |France    |9          |    
2003    |  3   |Italie    |9          |    
2003    |  3   |Bresil    |3          |  

对于这张桌子，我

Answer 1

考虑以下查询：

SELECT saison, stage, p.team, SUM(SUM(p.point)) OVER (PARTITION by saison, team ORDER BY stage) team_point,
  FROM sample, UNNEST([STRUCT(team_home AS team, home_point AS point), (team_away, away_point)]) p
 GROUP BY 1, 2, 3
 ORDER BY 1, 2, 4 DESC;

输出与您的预期略有不同：
多亏了 @saransh的友​​好解释，除了一件事，输出更接近您的预期输出

（更新）

WITH team_points AS (
  SELECT saison, stage, p.team, SUM(SUM(p.point)) OVER (PARTITION by saison, team ORDER BY stage) team_point,
    FROM sample, UNNEST([STRUCT(team_home AS team, home_point AS point), (team_away, away_point)]) p
   GROUP BY 1, 2, 3
)
SELECT saison, stage, team, IFNULL(team_point, LAST_VALUE(team_point IGNORE NULLS) OVER w) AS team_point
  FROM UNNEST([2002, 2003]) saison, UNNEST([1, 2, 3]) stage, UNNEST(['France', 'Bresil', 'Italie']) team
  LEFT JOIN team_points p ON p.saison = saison AND p.stage = stage AND p.team = team
WINDOW w AS (PARTITION BY saison, team ORDER BY stage)  
 ORDER BY 1, 2, 3;

Answer 2

请考虑以下方法

with temp as (
  select *, 0 goal, 0 point from
  (select distinct saison from your_table),
  (select distinct stage from your_table),
  (select distinct team from your_table, unnest([team_home, team_away]) team)
)
select distinct saison, stage, team, 
  sum(goal) over prev_stages team_goals,
  sum(point) over prev_stages team_points
from (
  select * except(col) from your_table 
  unpivot ((team, goal, point) for col in 
    ((team_home, home_goal, home_point), (team_away, away_goal, away_point))
  )
  union all select * from temp
)
window prev_stages as (
  partition by saison, team order by stage 
  range between unbounded preceding and current row
)             

如果应用于问题中的示例数据 - 输出为

Answer 3

如果所有团队都在各个阶段和saisons中同时参加了主场和外摆动，那么您可以创建一个列（team_point），该专栏总结了home_point的添加和由saison，stage and stage and team_home组成的home_point和avey_point（使用Team Away不会有所作为）：

SELECT
  saison,
  stage,
  team_home AS team,
  SUM(home_point + away_point) AS team_point
FROM
  your_dataset.your_table
GROUP BY
  saison,
  stage,
  team
ORDER BY
  saison,
  stage,
  team_point DESC --this is how teams with most points will show up first for each saison and stage.

如果任何团队只有在任何阶段或赛义德都在家（或离开）的机会，那么您可以首先列出所有不同的球队。

WITH
  AllTeams AS (
    SELECT DISTINCT
      team_away AS team
    FROM
      your_dataset.your_table
    UNION DISTINCT
    SELECT DISTINCT
      team_home AS team
    FROM
      your_dataset.your_table)

SELECT
  saison,
  stage,
  team,
  SUM(home_point + away_point) AS team_point
FROM
  your_dataset.your_table
LEFT JOIN
  AllTeams ON team = team_home OR team = team_away
GROUP BY
  saison,
  stage,
  team
ORDER BY
  saison,
  stage,
  team_point DESC

Answer 4

OP您的帖子要求不太清楚，请对其进行更新以反映实际要求。我可以从上面的输出中推断出的是，您需要每个团队的累计总和分别向第2阶段和第3阶段转移到第2阶段。

WITH
  unioned_expr AS (
  SELECT
    saison,
    stage,
    team_home AS team,
    home_goal AS goals,
    home_Point AS points
  FROM
    `project.dataset.table`
  UNION ALL
  SELECT
    saison,
    stage,
    team_away AS team,
    away_goal AS goals,
    away_point AS points
  FROM
    `project.dataset.table` ),
  summed_expr AS (
  SELECT
    saison,
    stage,
    team,
    SUM(points) AS points
  FROM
    unioned_expr
  GROUP BY
    saison,
    stage,
    team
  ORDER BY
    saison,
    team,
    stage )
SELECT
  saison,
  stage,
  team,
  SUM(points) OVER(PARTITION BY team, saison ORDER BY stage ROWS UNBOUNDED PRECEDING ) as team_point
FROM
  summed_expr
ORDER BY
  saison,
  team,
  stage

输出：

saison,stage,team,team_points
2002,1,Bresil,0
2002,2,Bresil,6
2002,1,France,4
2002,2,France,5
2002,3,France,6
2002,1,Italie,4
2002,2,Italie,5
2002,3,Italie,6
2003,1,Bresil,3
2003,2,Bresil,3
2003,1,France,3
2003,2,France,6
2003,3,France,9
2003,1,Italie,3
2003,2,Italie,9
2003,3,Italie,9