How to iterate a calculation through sql table in bigquery?-CodePudding

I have a table that looks like this in bigquery.

groups  created_utc            score    count_comments  d_posts ups  downs
group1  2011-07-11T19:05:19Z    6988    3742             56     8530  1572
group2  2011-04-23T21:29:12Z    10455   4695             512    11756 1303

I want to calculate a ratio with the ups column as the value of ups column per row/sum(ups). I am not sure how to apply the divisor in big query to every row.

I tried this query:

select (select sum(ups)
          from table group by ups,group )/(select count(ups)
          from table)*100 as ratio
from table
;

I get error: Scalar subquery produced more than one element. How do I apply the divisor, similar to a FOR loop in python to every row from the first part of the ratio calculation?

The new table should look like this:

groups  created_utc            score    count_comments  d_posts ups  downs   ratio
group1  2011-07-11T19:05:19Z    6988    3742             56     8530  1572   .42(8530/20286)
group2  2011-04-23T21:29:12Z    10455   4695             512    11756 1303   .58(11756/20286)

CodePudding user response：

Use SUM() as an analytic function to obtain the sum over the entire table.

SELECT *, ups * 1.0 / SUM(ups) OVER () AS ratio
FROM yourTable;