英文:
repeat the same aggregation on different columns in BigQuery
问题
我有一个如下所示的表格：
产品 | 重量 | 列1 | 列2 | 列3 | ... | 列100
产品A | 1 | 1 | 2 | 3 | ... | 100
产品B | 1 | 1 | 2 | 3 | ... | 100
产品A | 2 | 0.5 | 20 | 3 | ... | 200
产品B | 3 | 0.5 | 20 | 3 | ... | 200
我想编写一个查询，使用每个产品的权重计算列1到100的加权平均值。查询很简单：
SELECT
产品,
SUM(重量*列1)/SUM(重量) OVER(PARTITION BY 产品) AS 加权平均列1
FROM 产品表
但是我不想重复100次加权平均值的行，对于每个列。是否有一种方法可以在这里创建一个循环，以遍历列并创建如下所示的输出：
我有一个如下所示的表格：
产品 | 加权平均列1 | 加权平均列2 | 加权平均列3 ... | 加权平均列100
产品A | 0.33 | x | y | z
产品B | 0.37 | n | m | l
英文:
I have a table like below:
product | weight | col1 | col2 | col3 | ... | col100
productA | 1 | 1 | 2 | 3 | ... | 100
productB | 1 | 1 | 2 | 3 | ... | 100
productA | 2 | 0.5 | 20 | 3 | ... | 200
productB | 3 | 0.5 | 20 | 3 | ... | 200
I want to write a query that calculates the weighted average of columns 1 to 100 using column weight for each product. The query is simple:
SELECT
product,
SUM(weight*col1)/SUM(weight) OVER(partition by product) AS wighted_average_col1
FROM product_tbl
But I don't want to repeat the weighted average line 100 times for each column. Is there a way to create a loop here to iterate through the columns and create an output like below:
I have a table like below:
product | wighted_average_col1 | wighted_average_col2 | wighted_average_col3 ... | wighted_average_col100
productA | 0.33 | x | y | z
productB | 0.37 | n | m | l
答案1
得分: 1
如@Mikhail Berlyant所提到的，您可以使用EXECUTE IMMEDIATE!来动态构建SQL语句。
您可以参考这个文档以获取有关执行动态SQL语句的更多信息。
您还可以参考这个链接，这是您需求的示例之一。
将答案发布为社区维基，以使将来可能遇到此用例的社区受益。
如需进一步信息，请随时编辑此答案。
英文:
As mentioned by @Mikhail Berlyant, you can use EXECUTE IMMEDIATE! to dynamically build a sql for it.
You can follow this documentation for more information about executing a dynamic SQL statement.
You can also refer to this link which is one of the examples for your requirement
Posting the answer as community wiki for the benefit of the community that might encounter this use case in the future.
Feel free to edit this answer for additional information.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论