基于orders与order_details表创建嵌套表且免逐字段声明的实现方案

最优实现（兼容BigQuery/Spark SQL/Presto/Hive 2.0+等主流大数据引擎） #

推荐先聚合订单详情表再关联订单主表的写法，既不需要逐个声明字段，也完全不需要对主表所有字段做分组，性能也更优：

WITH agg_order_details AS (
    SELECT 
        order_id,
        -- 直接取详情表全字段封装为struct，再聚合为数组
        ARRAY_AGG(STRUCT(t.*)) AS order_details
    FROM `order_details` t
    GROUP BY order_id
)
SELECT 
    o.*, -- 直接取订单主表全字段
    -- 无匹配详情时返回空数组，避免null值
    COALESCE(ad.order_details, ARRAY()) AS order_details
FROM `orders` o
LEFT JOIN agg_order_details ad
USING(order_id)

写法优势 #

• 无需逐个手写两张表的查询字段，直接用*取全量
• 完全规避主表字段需要全部写入group by的问题，同时先聚合再关联的逻辑可以减少关联时的数据量，查询效率更高

兼容低版本引擎的写法 #

如果你的SQL引擎不支持上述语法，或者要求所有非聚合字段必须出现在group by中，可以使用下面的写法，仅需要按主表主键分组即可：

SELECT 
    o.*,
    ARRAY_AGG(
        -- 左连接无匹配时忽略空值，避免数组中出现空struct
        IF(d.order_id IS NULL, NULL, STRUCT(d.*)) 
        IGNORE NULLS
    ) AS order_details
FROM `orders` o
LEFT JOIN `order_details` d
USING(order_id)
-- order_id是主表主键，所有主表字段和order_id唯一关联，仅需按主键分组即可
GROUP BY o.order_id

如果引擎强制要求主表所有字段都写入group by，你也可以用ANY_VALUE(o.*)代替o.*，无需修改group by逻辑：

SELECT 
    ANY_VALUE(o).* AS orders,
    ARRAY_AGG(
        IF(d.order_id IS NULL, NULL, STRUCT(d.*)) 
        IGNORE NULLS
    ) AS order_details
FROM `orders` o
LEFT JOIN `order_details` d
USING(order_id)
GROUP BY o.order_id

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