'how to repeat a block of code in bigquery for multiple columns?

I would like to repeat a block of code with a complex operation (defined in a subquery) for many columns in bigquery. I cannot find a better way than copy and paste the same code block for each column name multiple times. Just for an example, same operation for three different columns:

SELECT 
    SQRT(a_name1 + b_name1) AS result_name1,
    SQRT(a_name2 + b_name2) AS result_name2,
    SQRT(a_name3 + b_name3) AS result_name3
FROM
(
SELECT
  SUM(name1_col1 + name1_col2 / name1_col3) / SUM(name1_col1)  AS a_name1,
  SUM(name1_col1 * name1_col2 + name1_col3) / SUM(name1_col2) AS b_name1,

  SUM(name2_col1 + name2_col2 / name2_col3) / SUM(name2_col1) AS a_name2,
  SUM(name2_col1 * name2_col2 + name2_col3) / SUM(name2_col2) AS b_name2,

  SUM(name3_col1 + name3_col2 / name3_col3) / SUM(name3_col1)  AS a_name3,
  SUM(name3_col1 * name3_col2 + name3_col3) / SUM(name3_col2) AS b_name3
FROM data
)

where data is like

name1_col1 | name1_col2 | name1_col3 | name2_col1 | name2_col2 | name2_col3 | name3_col1 | name3_col2 | name3_col3 |
     1     |        2   |     3.1    |     1.2    |    -0.4    |   0.3      |    2       |    3       |     3.4    |
...

As there are aggregation operations it seems it's not possible to define a UDF function.

I have looked online and in other questions, but I have not been able to find any relevant answer.

Could you please help me?

sqlgoogle-cloud-platformgoogle-bigquerysubqueryaggregate


Solution 1:[1]

If possible Create view with this query and use view for your operation just using column names.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Vishal Bulbule

Related Questions