Snowflake是否为CTE创建临时表？如何优化股票数据聚合查询性能？

原查询及问题 #

原SQL用于计算股票各月度收盘价等指标的平均值，并横向拼接2020-2022年的月度收盘价均值：

-- use ZEPL_STOCKS;
with MONTHLY_AVERAGES AS
(SELECT DISTINCT
  SYMBOL
  , YEAR(DATE) as YEAR
  , month(DATE) as MONTH
  , avg(close) over(partition by symbol, year(DATE), month(DATE)) as MONTHLY_CLOSE_AVG 
  , avg(adjclose) over(partition by symbol, year(DATE), month(DATE)) as MONTHLY_ADJCLOSE_AVG 
  , avg(open) over(partition by symbol, year(DATE), month(DATE)) as MONTHLY_OPEN_AVG 
  , avg(high) over(partition by symbol, year(DATE), month(DATE)) as MONTHLY_HIGH_AVG
  , avg(low) over(partition by symbol, year(DATE), month(DATE)) as MONTHLY_LOW_AVG 
  , avg(volume) over(partition by symbol, year(DATE), month(DATE)) as MONTHLY_VOLUME_AVG 
 
from 
STOCK_HISTORY)
select 
  M_AVG_2020.SYMBOL
  , M_AVG_2020.MONTH
  , M_AVG_2020.MONTHLY_CLOSE_AVG CLOSE_AVG_2020 
  , M_AVG_2021.MONTHLY_CLOSE_AVG CLOSE_AVG_2021
  , M_AVG_2022.MONTHLY_CLOSE_AVG CLOSE_AVG_2022
  FROM MONTHLY_AVERAGES as M_AVG_2020
inner join MONTHLY_AVERAGES as M_AVG_2021
  on M_AVG_2020.SYMBOL=M_AVG_2021.SYMBOL AND M_AVG_2020.MONTH=M_AVG_2021.MONTH
inner join MONTHLY_AVERAGES as M_AVG_2022
  on M_AVG_2020.SYMBOL=M_AVG_2022.SYMBOL AND M_AVG_2020.MONTH=M_AVG_2022.MONTH
where M_AVG_2020.YEAR=2020 and M_AVG_2021.YEAR=2021 and M_AVG_2022.YEAR=2022 --and M_AVG_2020.SYMBOL='ORCL'
ORDER BY SYMBOL, MONTH;

该查询可正常运行，但分析更多年份或使用更细时间粒度时速度极慢，核心原因包括：窗口函数+DISTINCT的冗余计算、多次JOIN导致的重复数据扫描。

Snowflake对CTE的处理逻辑 #

Snowflake默认不会为CTE创建持久化临时表，它会将CTE作为子查询进行内联处理。如果CTE被多次引用（如本查询中MONTHLY_AVERAGES被JOIN三次），Snowflake会重复执行CTE的逻辑，多次扫描STOCK_HISTORY表，这是性能瓶颈之一。

若需要强制Snowflake暂存CTE结果，可使用MATERIALIZED关键字显式物化CTE，但这并非最优方案，仅适用于特定场景。

最优优化方案 #

1. 用GROUP BY替换窗口函数+DISTINCT #

原CTE通过窗口函数计算均值后再用DISTINCT去重，本质是先为每行生成聚合值再去重，开销远大于直接用GROUP BY聚合。改用GROUP BY可一次性计算月度指标均值，避免冗余计算：

WITH MONTHLY_AVERAGES AS (
    SELECT
        SYMBOL,
        YEAR(DATE) AS YEAR,
        MONTH(DATE) AS MONTH,
        AVG(close) AS MONTHLY_CLOSE_AVG,
        AVG(adjclose) AS MONTHLY_ADJCLOSE_AVG,
        AVG(open) AS MONTHLY_OPEN_AVG,
        AVG(high) AS MONTHLY_HIGH_AVG,
        AVG(low) AS MONTHLY_LOW_AVG,
        AVG(volume) AS MONTHLY_VOLUME_AVG
    FROM STOCK_HISTORY
    WHERE YEAR(DATE) IN (2020, 2021, 2022) -- 提前过滤年份，减少扫描数据量
    GROUP BY SYMBOL, YEAR(DATE), MONTH(DATE)
)

2. 用PIVOT/CASE表达式替代多表JOIN #

原查询通过三次JOIN横向拼接不同年份的均值，改用PIVOT或CASE表达式可在聚合后直接转置结果，避免多次表关联的性能损耗：

基于CASE表达式的完整优化版本

WITH MONTHLY_AVERAGES AS (
    SELECT
        SYMBOL,
        YEAR(DATE) AS YEAR,
        MONTH(DATE) AS MONTH,
        AVG(close) AS CLOSE_AVG,
        AVG(adjclose) AS ADJCLOSE_AVG,
        AVG(open) AS OPEN_AVG,
        AVG(high) AS HIGH_AVG,
        AVG(low) AS LOW_AVG,
        AVG(volume) AS VOLUME_AVG
    FROM STOCK_HISTORY
    WHERE YEAR(DATE) IN (2020, 2021, 2022)
    GROUP BY SYMBOL, YEAR(DATE), MONTH(DATE)
)
SELECT
    SYMBOL,
    MONTH,
    -- 收盘价均值
    MAX(CASE WHEN YEAR = 2020 THEN CLOSE_AVG END) AS CLOSE_AVG_2020,
    MAX(CASE WHEN YEAR = 2021 THEN CLOSE_AVG END) AS CLOSE_AVG_2021,
    MAX(CASE WHEN YEAR = 2022 THEN CLOSE_AVG END) AS CLOSE_AVG_2022,
    -- 可按需添加其他指标的年份均值
    MAX(CASE WHEN YEAR = 2020 THEN ADJCLOSE_AVG END) AS ADJCLOSE_AVG_2020,
    MAX(CASE WHEN YEAR = 2021 THEN ADJCLOSE_AVG END) AS ADJCLOSE_AVG_2021,
    MAX(CASE WHEN YEAR = 2022 THEN ADJCLOSE_AVG END) AS ADJCLOSE_AVG_2022
FROM MONTHLY_AVERAGES
GROUP BY SYMBOL, MONTH
ORDER BY SYMBOL, MONTH;

优化效果说明 #

• 减少数据扫描：提前过滤目标年份，避免扫描全表数据；
• 消除冗余计算：用GROUP BY替代窗口函数+DISTINCT，仅执行一次聚合；
• 避免多次关联：用CASE或PIVOT替代多表JOIN，减少数据 shuffle 和关联开销；
• 扩展性更强：新增年份仅需修改WHERE条件和CASE分支，无需增加JOIN语句。

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