我如何启用“小组”聚合维度作为在BigQuery中创建的函数的输入

Question

例如，我需要在bigquery中以多个不同级别的多个不同级别创建一个表：

SELECT 
dimension_a, 
dimension_b, 
SUM(value) AS value 
FROM mydataset.table 
GROUP BY dimension_a, dimension_b

UNION ALL 

SELECT 
dimension_a, 
NULL AS dimension_b, 
SUM(value) AS value 
FROM mydataset.table 
GROUP BY dimension_a

UNION ALL

SELECT 
NULL AS dimension_a, 
dimension_b, 
SUM(value) AS value 
FROM mydataset.table 
GROUP BY dimension_b

我想这不是最优雅的代码...例如，我有18个不同的聚合维度代码与Union All的代码块18次，我想知道是否有一个函数可以使我可以将聚合维度作为输入？

例如，类似：

CREATE OR REPLACE TABLE FUNCTION mydataset.aggregation_dimension(X type, Y type)
AS
  SELECT
  X as dimension_a, Y as dimension_b,
  SUM(value) AS value
  FROM mydataset.table 
  GROUP BY X, Y;

SELECT * FROM mydataset.aggregation_dimension(dimension_a, dimension_b)
UNION ALL
SELECT * FROM mydataset.aggregation_dimension(dimension_a, NULL)
UNION ALL
SELECT * FROM mydataset.aggregation_dimension(NULL, dimension_b)

其中x，y应该是表mydataset.table.table ...但是，我不知道如何定义这种输入的类型。我也不知道是否有可能有这样的设置...

谢谢您的帮助！