Snowflake-SQL 如何将TypeACount列添加到另一个查询结果中

完全可以将TypeACount列整合到第二个查询的结果中，使用前请注意：第一个查询按付款日期paymentDate聚合统计，第二个查询按创建日期created聚合统计，若业务逻辑中要求两个指标的统计时间维度一致，需要先对齐日期字段后再操作。

以下是两种常用实现方式：

方案1：CTE临时结果集关联（推荐，逻辑清晰不易出错） #

将两个查询分别作为CTE临时结果集，按年月维度关联即可：

WITH query1_result 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
),
query2_result 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
)
-- 关联两个结果集
SELECT 
    q2.*,
    COALESCE(q1.TypeACount, 0) AS TypeACount -- 无匹配月份时默认返回0，可按需调整
FROM query2_result q2
LEFT JOIN query1_result q1 
    ON q2.year = q1.year 
    AND q2.month = q1.month
ORDER BY q2.year DESC, q2.month DESC

方案2：合并逻辑到单个查询 #

如果数据量较小，也可以直接将第一个查询的逻辑整合到第二个查询中，避免两次扫描表：

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,
    -- 新增TypeACount统计逻辑，符合原查询的筛选条件
    count(
        case 
            when f.value:paymentMethod.source = 'SourceA' 
                and f.value:status = 'paid'
                and f.value:paymentDate::date between DATEADD(dd, 1, last_day(DATEADD(mm, -4, GETDATE()))) and getdate()
            then 1 
        end
    ) as TypeACount
from PUBLIC.TRANSACTIONS t
-- 左关联拆解payments数组，避免过滤掉无付款记录的交易
left join lateral flatten(input => t.v, path => 'payments') f
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

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