如何优化这段T-SQL查询？嵌套CTE是否有更优实现方案？

现有如下SQL查询代码，由于TABLE_FCA没有唯一键，COLUMN_TX存储JSON值，存在相同VD对应不同DL_Date的情况，当前用嵌套CTE处理后关联其他表，想知道有没有更优的实现方式（比如临时表）。

原查询代码 #

WITH CTE AS
(
   WITH flattened_data AS 
   (
    SELECT
        DISTINCT
        MAX(CASE WHEN (entry.value:Id::STRING)='123' THEN entry.value:Answer:text::STRING END) AS VD,
        MAX(CASE WHEN (entry.value:Id::STRING)='3456' THEN entry.value:Answer:text::STRING END) AS DPL,
        MAX(CASE WHEN (entry.value:Id::STRING)='8971' THEN entry.value:Answer:text::STRING END) AS DL_Date
    FROM TABLE_FCA,
         LATERAL FLATTEN(input => COLUMN_TX:Question) AS entry
    WHERE entry.value:Answer:text IS NOT NULL
        AND entry.value:Id IN ('123','3456','8971')
  GROUP BY COLUMN_TX
  HAVING VD IS NOT NULL
  )

SELECT VD,MAX(DPL) AS DPL ,MAX(DL_Date) AS DL_Date 
FROM flattened_data GROUP BY VD

)

Select * from
TABLE_A A
Join TABLE_B B ON...
.
.
.
LEFT JOIN CTE ON CTE.VD=A.VD

几种优化实现方式 #

1. 简化嵌套CTE为单层CTE

原嵌套CTE可以合并成一层，减少逻辑嵌套，提升可读性，同时Snowflake的查询优化器也能更好地处理单层CTE：

WITH CTE AS
(
    SELECT
        MAX(CASE WHEN entry.value:Id::STRING = '123' THEN entry.value:Answer:text::STRING END) AS VD,
        MAX(CASE WHEN entry.value:Id::STRING = '3456' THEN entry.value:Answer:text::STRING END) AS DPL,
        MAX(CASE WHEN entry.value:Id::STRING = '8971' THEN entry.value:Answer:text::STRING END) AS DL_Date
    FROM TABLE_FCA,
         LATERAL FLATTEN(input => COLUMN_TX:Question) AS entry
    WHERE entry.value:Answer:text IS NOT NULL
        AND entry.value:Id IN ('123','3456','8971')
    GROUP BY COLUMN_TX
    HAVING VD IS NOT NULL
    -- 用QUALIFY+ROW_NUMBER替代第二次聚合，逻辑和原查询取MAX一致
    QUALIFY ROW_NUMBER() OVER(PARTITION BY VD ORDER BY DPL DESC, DL_Date DESC) = 1
)
SELECT * FROM TABLE_A A
JOIN TABLE_B B ON ...
...
LEFT JOIN CTE ON CTE.VD = A.VD

2. 使用临时表

如果TABLE_FCA数据量较大，或者CTE结果需要在多个查询中复用，临时表是更优选择——Snowflake会为临时表生成统计信息，提升后续关联查询的性能：

-- 创建临时表存储第一次聚合的中间结果
CREATE OR REPLACE TEMP TABLE TEMP_FCA_AGG AS
SELECT
    MAX(CASE WHEN entry.value:Id::STRING = '123' THEN entry.value:Answer:text::STRING END) AS VD,
    MAX(CASE WHEN entry.value:Id::STRING = '3456' THEN entry.value:Answer:text::STRING END) AS DPL,
    MAX(CASE WHEN entry.value:Id::STRING = '8971' THEN entry.value:Answer:text::STRING END) AS DL_Date
FROM TABLE_FCA,
     LATERAL FLATTEN(input => COLUMN_TX:Question) AS entry
WHERE entry.value:Answer:text IS NOT NULL
    AND entry.value:Id IN ('123','3456','8971')
GROUP BY COLUMN_TX
HAVING VD IS NOT NULL;

-- 对临时表按VD聚合得到最终关联数据
CREATE OR REPLACE TEMP TABLE TEMP_FCA_FINAL AS
SELECT VD, MAX(DPL) AS DPL, MAX(DL_Date) AS DL_Date
FROM TEMP_FCA_AGG
GROUP BY VD;

-- 关联其他表
SELECT * FROM TABLE_A A
JOIN TABLE_B B ON ...
...
LEFT JOIN TEMP_FCA_FINAL ON TEMP_FCA_FINAL.VD = A.VD;

临时表的优势在于可单独查看中间结果方便调试，且多次使用时无需重复计算。

3. 窗口函数一步到位

如果不需要保留中间聚合结果，可以直接用窗口函数在扁平化后的数据上处理，跳过两次聚合步骤：

WITH CTE AS
(
    SELECT
        entry.value:Answer:text::STRING AS VD,
        MAX(CASE WHEN sub_entry.value:Id::STRING = '3456' THEN sub_entry.value:Answer:text::STRING END) OVER(PARTITION BY entry.value:Answer:text::STRING) AS DPL,
        MAX(CASE WHEN sub_entry.value:Id::STRING = '8971' THEN sub_entry.value:Answer:text::STRING END) OVER(PARTITION BY entry.value:Answer:text::STRING) AS DL_Date
    FROM TABLE_FCA,
         LATERAL FLATTEN(input => COLUMN_TX:Question) AS entry,
         LATERAL FLATTEN(input => COLUMN_TX:Question) AS sub_entry
    WHERE entry.value:Answer:text IS NOT NULL
        AND entry.value:Id = '123'
        AND sub_entry.value:Id IN ('3456','8971')
        AND sub_entry.value:Answer:text IS NOT NULL
    QUALIFY ROW_NUMBER() OVER(PARTITION BY VD ORDER BY DPL DESC, DL_Date DESC) = 1
)
SELECT * FROM TABLE_A A
JOIN TABLE_B B ON ...
...
LEFT JOIN CTE ON CTE.VD = A.VD;

这种方式减少了聚合层级，适合数据量较大的场景，能有效降低查询执行时间。

方案选择建议 #

• 数据量小、逻辑简单：优先用简化单层CTE，保持代码简洁易维护。
• 需要复用中间结果或数据量较大：选择临时表，利用Snowflake的缓存和统计信息优化性能。
• 追求极致性能：尝试窗口函数一步到位的方案，减少聚合操作开销。

内容的提问来源于stack exchange，提问作者Aswin S P