Snowflake中按时间条件聚合合并多表文本字段的实现问询

我有一张source_table，其中source_id与source_timestamp的组合唯一，product_id不唯一。需要从两张target_table中，针对source_table的每条记录，聚合对应product_id且text_timestamp早于该记录source_timestamp的所有text字段，最终生成以source_id分组的结果表，且聚合后的文本需按时间顺序排序。

原始source_table #

原始target_table1 #

原始target_table2 #

期望输出 #

现有SQL尝试 #

with main_cte as (
select id,
  timestamp
  product_id
from source_table
),
cte1 as (
select
 st.id,
 st.timestamp,
 LISTAGG(tt1.text, ' ') within group (ORDER BY st.timestamp) as text
from source_table st
 left join target_table1 tt1 on tt1.product_id = st.product_id
     and tt1.timestamp < st.timestamp
group by 1,2
),
cte2 as (
select
 st.id,
 st.timestamp,
 LISTAGG(tt2.text, ' ') within group (ORDER BY st.timestamp) as text
from source_table
 left join target_table2 tt2 on tt2.product_id = st.product_id
    and tt2.timestamp < st.timestamp
group by 1,2
)

最终拼接逻辑：

select st.id,
 (cte1.text || cte2.text) as aggregated_text
from source_table
 left join cte1 on cte1.id = source_table.id
 left join cte2 on cte2.id = source_table.id

现有写法的问题：无法保证合并后的文本按时间顺序排序，且拼接时可能出现多余空格（比如某侧为null时）。

正确解决方案 #

核心思路是先合并两张target表的有效数据，再和source表关联后统一聚合，确保所有文本按text_timestamp排序：

WITH combined_targets AS (
    -- 合并两张target表的有效数据，保留关键字段
    SELECT product_id, text_timestamp, text
    FROM target_table1
    UNION ALL
    SELECT product_id, text_timestamp, text
    FROM target_table2
),
source_with_texts AS (
    -- 关联source表和合并后的target数据，筛选时间符合条件的记录
    SELECT 
        s.source_id,
        s.product_id,
        ct.text,
        ct.text_timestamp
    FROM source_table s
    LEFT JOIN combined_targets ct 
        ON s.product_id = ct.product_id 
        AND ct.text_timestamp < s.source_timestamp
)
-- 按source_id分组，聚合文本并按时间排序
SELECT 
    source_id,
    product_id,
    -- 无符合条件文本时返回null，否则按时间拼接
    CASE 
        WHEN COUNT(text) = 0 THEN NULL
        ELSE LISTAGG(text, ' ') WITHIN GROUP (ORDER BY text_timestamp)
    END AS text
FROM source_with_texts
GROUP BY source_id, product_id
ORDER BY source_id;

说明 #

1. 合并target表：用UNION ALL合并两张target表的数据，避免分别聚合后拼接导致的顺序混乱。
2. 关联筛选：仅保留text_timestamp早于对应source_timestamp的记录，确保数据符合需求。
3. 统一聚合：按source_id和product_id分组，用LISTAGG按text_timestamp排序拼接文本，无数据时返回null，完全匹配期望输出。

内容的提问来源于stack exchange，提问作者E. Lutins