如何从Snowflake中的两个公共表达式(CTEs)中提取数据。

huangapple go评论59阅读模式
英文:

How to pull data from two CTEs in Snowflake

问题

I'm trying to pull current ytd sales and prior ytd sales for the same period. I have the SQL that pulls the data correctly (separately), but I don't know how to put it together in a CTE, or if that is even the best way to do it. Here is what I have so far:

with current_year AS 
(
SELECT 
    SUM(TOTAL_REVENUE_USD) AS 当年总收入
FROM 
    FACT_ORDER
WHERE 
    year(COMMISSION_DATE) = year(current_date)
),

prior_year AS 
(
SELECT 
    SUM(TOTAL_REVENUE_USD) AS 去年同期总收入
FROM   
    FACT_ORDER
WHERE 
    COMMISSION_DATE BETWEEN date_from_parts((date_part(year,current_date())-1),1,1) 
    AND dateadd(year,-1,current_date());
)

I've tried using CTEs (see the above code), but I am stuck. I don't know where to go from here. I would like the result to be something like:

2022 $10,000,000
2023 $11,000,000

SAMPLE DATA:
如何从Snowflake中的两个公共表达式(CTEs)中提取数据。

英文:

I'm trying to pull current ytd sales and prior ytd sales for the same period. I have the SQL that pulls the data correctly (separately), but I don't know how to put it together in a CTE, or if that is even the best way to do it. Here is what I have so far:

with current_year AS 
(
SELECT 
    SUM(TOTAL_REVENUE_USD) AS CURRENT_YEAR_REVENUE
FROM 
    FACT_ORDER
WHERE 
    year(COMMISSION_DATE) = year(current_date)
),

prior_year AS 
(
SELECT 
    SUM(TOTAL_REVENUE_USD) AS PRIOR_YEAR_REVENUE
FROM   
    FACT_ORDER
WHERE 
    COMMISSION_DATE BETWEEN date_from_parts((date_part(year,current_date())-1),1,1) 
    AND dateadd(year,-1,current_date());
)

I've tried using CTEs (see the above code), but I am stuck. I don't know where to go from here. I would like the result to be something like:

2022   $10,000,000
2023   $11,000,000

SAMPLE DATA:
如何从Snowflake中的两个公共表达式(CTEs)中提取数据。

答案1

得分: 1

你似乎想要一个 union

select * 
from current_year
union all
select *
from previous year

但由于CTE缺少列 year

select 2023 year, * 
from current_year
union all
select 2022, *
from previous year
英文:

It seems you want an union:

select * 
from current_year
union all
select *
from previous year

But since the CTEs are missing the column year:

select 2023 year, * 
from current_year
union all
select 2022, *
from previous year

huangapple
  • 本文由 发表于 2023年5月18日 03:31:34
  • 转载请务必保留本文链接:https://go.coder-hub.com/76275617.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定