Snowflake-SQL -将行从一个查询添加到另一个查询

Question

我正在寻找组合查询结果的帮助。

我有一个查询，结果是：

YEAR    MONTH   T_DATE  TypeACount
2021    9   2021-09-01  13280
2021    8   2021-08-01  24508
2021    7   2021-07-01  21014
2021    6   2021-06-01  19666

查询：

select
    EXTRACT(YEAR FROM  f.value:paymentDate::date) as year,
    EXTRACT(MONTH FROM  f.value:paymentDate::date) as month,
    DATEFROMPARTS(year,month, 1) as t_date,
    count (case when v:payments[0].paymentMethod.source = 'SourceA' then 1 end) as "TypeACount"
from public.transactions t,
        lateral flatten(input => t.v, path => 'payments') f
where 
    f.value:paymentDate::date between DATEADD(dd, 1, last_day(DATEADD(mm, -4, GETDATE()))) and getdate()
    and
    f.value:paymentMethod.source = 'SourceA'
    and
    f.value:status = 'paid'
GROUP by
    month,year
ORDER by
    year DESC,
    month DESC

我还有另一个查询，结果是：

YEAR    MONTH   T_DATE  TypeBCount  TypeCCount  TypeDCount  TypeECount
2021    9   2021-09-01  3639        1340        6800        56401
2021    8   2021-08-01  6185        2482        11707       96122
2021    7   2021-07-01  5485        1680        10820       92394
2021    6   2021-06-01  5423        521         10643       97303

查询：

select
    EXTRACT(YEAR FROM v:created::date) as year,
    EXTRACT(MONTH FROM v:created::date) as month,
    DATEFROMPARTS(year,month, 1) as t_date,
    count (case when v:category[0].source = 'TypeB' then 1 end) as TypeBCount,
    count (case when v:category[0].source = 'TypeC' then 1 end) as TypeCCount,
    count (case when v:category[0].source = 'TypeD' then 1 end) as TypeDCount,
    count (case when v:category[0].source = 'TypeE' then 1 end) as TypeECount
from PUBLIC.TRANSACTIONS
where 
    v:created::date between DATEADD(dd, 1, last_day(DATEADD(mm, -4, GETDATE()))) and getdate()
GROUP by
    month,year
ORDER by
    year DESC,
    month DESC

有没有办法将TypeACount列添加到第二个查询中？

Answer 1

您可以在CTE中的表表达式中运行这两个查询，然后将它们连接起来。

with A as
(
select
    EXTRACT(YEAR FROM  f.value:paymentDate::date) as year,
    EXTRACT(MONTH FROM  f.value:paymentDate::date) as month,
    DATEFROMPARTS(year,month, 1) as t_date,
    count (case when v:payments[0].paymentMethod.source = 'SourceA' then 1 end) as "TypeACount"
from public.transactions t,
        lateral flatten(input => t.v, path => 'payments') f
where 
    f.value:paymentDate::date between DATEADD(dd, 1, last_day(DATEADD(mm, -4, GETDATE()))) and getdate()
    and
    f.value:paymentMethod.source = 'SourceA'
    and
    f.value:status = 'paid'
GROUP by
    month,year
ORDER by
    year DESC,
    month DESC
),
B as
(
select
    EXTRACT(YEAR FROM v:created::date) as year,
    EXTRACT(MONTH FROM v:created::date) as month,
    DATEFROMPARTS(year,month, 1) as t_date,
    count (case when v:category[0].source = 'TypeB' then 1 end) as TypeBCount,
    count (case when v:category[0].source = 'TypeC' then 1 end) as TypeCCount,
    count (case when v:category[0].source = 'TypeD' then 1 end) as TypeDCount,
    count (case when v:category[0].source = 'TypeE' then 1 end) as TypeECount
from PUBLIC.TRANSACTIONS
where 
    v:created::date between DATEADD(dd, 1, last_day(DATEADD(mm, -4, GETDATE()))) and getdate()
GROUP by
    month,year
ORDER by
    year DESC,
    month DESC
)
select * from A
  left join B
    on A.YEAR = B.YEAR and A.MONTH = B.MONTH
;