PySpark STACK转Snowflake LATERAL JOIN报错：VALUES子句表达式无效

你遇到的Invalid expression [T.ACTIVE_SHARE_PCT] in VALUES clause错误，是因为Snowflake不允许在LATERAL关联的VALUES子句中直接引用外部表的列。由于你需要保留NULL值且无法使用UNPIVOT，建议将VALUES替换为UNION ALL拼接的SELECT语句来实现列转行。

修改后的Snowflake查询代码如下：

SELECT
    t.as_of_date,
    t.product_code,
    v.*
    
FROM (
    SELECT
        to_date(dd.date) AS as_of_date,
        pd.product_code AS product_code,
        pv.*
       
    FROM
        integration.product_valuation_accounting_fact_v01 pv
      
        JOIN integration.date_dim_v01 dd
            ON pv.valuation_date_key = dd.date_key

        JOIN integration.product_dim_v01 pd
            ON pv.product_key = pd.product_key
            AND dd.date BETWEEN pd.start_date AND pd.end_date

        LEFT JOIN integration.currency_dim_v01 cd
            ON pv.base_currency_key = cd.currency_key
        
    WHERE
        dd.date = '2023-06-30'
) AS t

CROSS JOIN LATERAL (
    SELECT 'Active Share End Weight' AS display_name, t.active_share_pct AS portfolio, NULL AS benchmark
    UNION ALL
    SELECT 'Wtd Avg Market Cap Base' AS display_name, t.weighted_avg_mkt_cap_base AS portfolio, NULL AS benchmark
    UNION ALL
    SELECT 'Price to Book Ratio – Latest Reported' AS display_name, t.price_book_ratio_latest_rptd AS portfolio, NULL AS benchmark
) AS v

关键说明 #

• 用UNION ALL拼接独立的SELECT语句，每个语句对应一行转换后的记录，这样可以正常引用外部表t中的列
• 这种方式会保留原始列中的NULL值，符合你的需求
• 若后续需要添加benchmark列的对应值，只需在对应的SELECT语句中替换NULL为t表中的对应字段即可

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