BigQuery中GROUP BY GROUPING SETS是否比GROUP BY UNION性能更优？

BigQuery新增了GROUP BY GROUPING SETS语法，相比传统的GROUP BY结合UNION的写法更简洁。由于GROUPING SETS只需扫描一次源表，而传统UNION方式要多次扫描数据，这里我们来确认它是否在性能上更具优势。

示例1：使用GROUPING SETS的实现 #

-- GROUP BY with GROUPING SETS
WITH
  Products AS (
    SELECT 'shirt' AS product_type, 't-shirt' AS product_name, 3 AS product_count UNION ALL
    SELECT 'shirt', 't-shirt', 8 UNION ALL
    SELECT 'shirt', 'polo', 25 UNION ALL
    SELECT 'pants', 'jeans', 6
  )
SELECT product_type, product_name, SUM(product_count) AS product_sum
FROM Products
GROUP BY GROUPING SETS (product_type, product_name)
ORDER BY product_name

执行结果：

--------------+--------------+-------------+
 | product_type | product_name | product_sum |
 +--------------+--------------+-------------+
 | shirt        | NULL         | 36          |
 | pants        | NULL         | 6           |
 | NULL         | jeans        | 6           |
 | NULL         | polo         | 25          |
 | NULL         | t-shirt      | 11          |
 +--------------+--------------+-------------+

示例2：不使用GROUPING SETS的传统UNION方式 #

-- GROUP BY without GROUPING SETS
-- (produces the same results as GROUPING SETS)
WITH
  Products AS (
    SELECT 'shirt' AS product_type, 't-shirt' AS product_name, 3 AS product_count UNION ALL
    SELECT 'shirt', 't-shirt', 8 UNION ALL
    SELECT 'shirt', 'polo', 25 UNION ALL
    SELECT 'pants', 'jeans', 6
  )
SELECT product_type, NULL, SUM(product_count) AS product_sum
FROM Products
GROUP BY product_type
UNION ALL
SELECT NULL, product_name, SUM(product_count) AS product_sum
FROM Products
GROUP BY product_name
ORDER BY product_name

性能优势说明 #

• 更少的表扫描：GROUPING SETS仅对源表进行一次全量扫描，所有分组计算逻辑在单次处理中完成；而传统UNION写法需要对源表执行多次扫描（每个分组分支对应一次），数据量越大，IO开销的差距越明显。
• 更优的资源利用：BigQuery的查询优化器会针对GROUPING SETS做专项优化，能更高效地分配计算资源，避免重复的数据读取和预处理操作。
• 更简洁的代码：两种写法输出结果完全一致，但GROUPING SETS的语法更紧凑，减少了重复代码，降低了编写和维护时的出错概率。

内容的提问来源于stack exchange，提问作者Hui Zheng