如何在Snowflake中通过CTE提取本年及上年同期YTD销售额数据

方案一：完善你的CTE写法 #

你已经定义了两个CTE，但缺少将结果合并为目标格式的步骤。以下是补全后的代码，同时修正了当前年份YTD的过滤逻辑（原代码仅匹配年份，未限制日期不超过当前日期，可能包含无效数据）：

WITH current_year AS (
    SELECT 
        YEAR(CURRENT_DATE) AS sales_year,
        SUM(TOTAL_REVENUE_USD) AS revenue
    FROM 
        FACT_ORDER
    WHERE 
        YEAR(COMMISSION_DATE) = YEAR(CURRENT_DATE)
        AND COMMISSION_DATE <= CURRENT_DATE -- 确保是截至当前的本年累计
),
prior_year AS (
    SELECT 
        YEAR(CURRENT_DATE) - 1 AS sales_year,
        SUM(TOTAL_REVENUE_USD) AS revenue
    FROM   
        FACT_ORDER
    WHERE 
        COMMISSION_DATE BETWEEN DATE_FROM_PARTS(YEAR(CURRENT_DATE)-1, 1, 1) 
        AND DATEADD(YEAR, -1, CURRENT_DATE)
)
SELECT 
    sales_year,
    CONCAT('$', FORMAT(revenue, 0)) AS formatted_revenue
FROM current_year
UNION ALL
SELECT sales_year, CONCAT('$', FORMAT(revenue, 0))
FROM prior_year
ORDER BY sales_year;

这段代码给每个CTE添加了年份标识，通过UNION ALL将两个结果合并为两行，最后格式化金额为带千分位的美元格式并按年份排序。

方案二：更高效的单表聚合方案 #

两次扫描FACT_ORDER表会增加数据库负载，尤其是数据量较大时。下面的方案仅扫描一次表，通过条件判断标记年份后聚合，效率更高：

SELECT 
    sales_year,
    CONCAT('$', FORMAT(SUM(revenue), 0)) AS formatted_revenue
FROM (
    SELECT 
        CASE 
            WHEN YEAR(COMMISSION_DATE) = YEAR(CURRENT_DATE) AND COMMISSION_DATE <= CURRENT_DATE 
                THEN YEAR(CURRENT_DATE)
            WHEN COMMISSION_DATE BETWEEN DATE_FROM_PARTS(YEAR(CURRENT_DATE)-1, 1, 1) 
                AND DATEADD(YEAR, -1, CURRENT_DATE)
                THEN YEAR(CURRENT_DATE)-1
        END AS sales_year,
        TOTAL_REVENUE_USD AS revenue
    FROM FACT_ORDER
    WHERE 
        -- 仅保留目标时间段的数据
        (YEAR(COMMISSION_DATE) = YEAR(CURRENT_DATE) AND COMMISSION_DATE <= CURRENT_DATE)
        OR (COMMISSION_DATE BETWEEN DATE_FROM_PARTS(YEAR(CURRENT_DATE)-1, 1, 1) AND DATEADD(YEAR, -1, CURRENT_DATE))
) AS filtered_sales
WHERE sales_year IS NOT NULL -- 排除不符合条件的行
GROUP BY sales_year
ORDER BY sales_year;

说明 #

• 金额格式化函数（FORMAT）因数据库不同可能有差异，比如MySQL可用FORMAT(revenue, 0)，PostgreSQL可用TO_CHAR(revenue, 'FM$999,999,999')，可根据你使用的数据库调整。
• 两种方案都能输出你需要的两行结果格式，方案二更适合大数据量场景。

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