下面是bigquery标准sql

# standardSQL

SELECT email, first_item_purchased, 
  MAX(purchases_within_90_days) OVER(PARTITION BY email) AS purchases_within_90_days, 
  MAX(purchases_within_180_days) OVER(PARTITION BY email) AS purchases_within_180_days, 
  MAX(purchases_within_270_days) OVER(PARTITION BY email) AS purchases_within_270_days
FROM `project.dataset.table`

如果要应用到问题中的样本数据-输出是

Row email       first_item_purchased    purchases_within_90_days    purchases_within_180_days   purchases_within_270_days    
1   abc@gmail   null                    1                           1                           2    
2   abc@gmail   product_1               1                           1                           2    
3   abc@gmail   product_2               1                           1                           2    
4   def@gmail   null                    0                           2                           3    
5   def@gmail   product_1               0                           2                           3    
6   def@gmail   product_2               0                           2                           3