嵌套表列拆分问询：如何将嵌套列转为单行多列并消除空行？

• 我有一个嵌套表，尝试通过以下SQL代码进行UNNEST操作以展开表结构：

SELECT purchaseId
    , CAST (PURCHASECREATED AS DATE) AS SALE_DT
    
    , (SELECT ARRAY_AGG (DISTINCT UPPER (DISCOUNTTYPE) IGNORE NULLS) FROM 
           UNNEST(PURCHASEDISCOUNTS)) AS DISCOUNT_TYPE
    
    , (SELECT ARRAY_AGG (DISTINCT UPPER (DISCOUNTCODE) IGNORE NULLS) FROM 
           UNNEST(PURCHASEDISCOUNTS)) AS DISCOUNT_CODE
    
    , UPPER (g.DISCOUNTTYPE) AS DISCOUNT_SALESSYSTEM

FROM `TABLE`
    , UNNEST(PURCHASELINEITEMS) f
    , UNNEST(ITEMDISCOUNTS) g

• 执行上述代码后得到如下结果，可见SALE_DT和DISCOUNT_SALESSYSTEM列存在空行，且行不唯一：

• 希望拆分嵌套列，得到如下单行多列的目标格式：

解决方案 #

问题根源是同时UNNESTPURCHASELINEITEMS和ITEMDISCOUNTS导致数据笛卡尔积展开，外层UNNEST抵消了子查询的聚合效果，最终生成多行重复数据。要实现目标单行格式，需先聚合嵌套数组内容，再处理折扣系统信息，避免不必要的展开。

以下是调整后的BigQuery兼容SQL：

WITH aggregated_data AS (
    SELECT 
        purchaseId,
        CAST(PURCHASECREATED AS DATE) AS SALE_DT,
        -- 为PURCHASEDISCOUNTS的每个元素添加索引，方便按位置拆分列
        ARRAY(
            SELECT AS STRUCT 
                UPPER(d.DISCOUNTTYPE) AS discount_type,
                UPPER(d.DISCOUNTCODE) AS discount_code,
                ROW_NUMBER() OVER() AS idx
            FROM UNNEST(PURCHASEDISCOUNTS) d
        ) AS discount_details,
        -- 获取ITEMDISCOUNTS中的折扣系统值（假设每个订单对应唯一值，多值可调整聚合逻辑）
        (SELECT UPPER(g.DISCOUNTTYPE) FROM UNNEST(ITEMDISCOUNTS) g LIMIT 1) AS DISCOUNT_SALESSYSTEM
    FROM `TABLE`
)
SELECT
    SALE_DT,
    -- 按索引提取第1、2个折扣类型
    MAX(CASE WHEN idx = 1 THEN discount_type END) AS DISCOUNT_TYPE_1,
    MAX(CASE WHEN idx = 2 THEN discount_type END) AS DISCOUNT_TYPE_2,
    DISCOUNT_SALESSYSTEM,
    -- 按索引提取第1、2个折扣编码
    MAX(CASE WHEN idx = 1 THEN discount_code END) AS DISCOUNT_CODE_1,
    MAX(CASE WHEN idx = 2 THEN discount_code END) AS DISCOUNT_CODE_2
FROM aggregated_data,
UNNEST(discount_details)
GROUP BY purchaseId, SALE_DT, DISCOUNT_SALESSYSTEM;

关键逻辑说明： #

1. 预聚合层（CTE）：先提取订单基础信息，将PURCHASEDISCOUNTS展开并添加索引，后续可按索引将数组元素拆分为独立列；通过子查询获取ITEMDISCOUNTS的折扣系统值，避免外层UNNEST导致的多行问题。
2. 列拆分与分组：将聚合后的折扣详情数组UNNEST，用CASE WHEN结合MAX函数按索引拆分出对应列，最后按订单维度分组，确保每行对应一个订单的完整信息。

如果折扣类型/编码数量不固定，可使用动态SQL生成对应列；若数量固定，上述写法更简洁直接。

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