Athena/Presto 生成字符串bigram时自连接匹配transaction_id返回0行问题

自连接返回0行的核心原因 #

• CTE默认不做结果物化，自连接两端是独立执行的分支
  Presto/Athena的优化器默认对CTE做内联展开，不会预先计算一次CTE结果供多次复用。当对word_list做自连接时，引擎会将两个CTE引用分别替换为完整的CTE查询逻辑，生成两个完全独立的执行分支，分别计算连接两端的数据集。
• 错位的limit直接造成两个结果集无交集
  示例代码中将limit 50加在了UNNEST单词展开后的最外层行集上，意味着两个独立执行的CTE分支，都会各自独立对展开后的单词行取前50条。由于分布式查询的分片扫描不保证全局顺序完全一致，两个分支取到的前50条记录可能来自完全不同的交易批次，两边的transaction_id几乎没有重叠，按交易ID做等值连接时自然返回0行。
• 纯序号匹配的结果属于无效错配
  仅用wl1.n = wl2.n-1做连接条件时能返回结果，本质是两个独立分支的单词序号都是从1开始递增，不校验交易归属的情况下自然能匹配序号，但匹配到的两个单词根本不属于同一笔交易，生成的二元组完全没有实际价值。

修正方案 #

• 调整limit粒度：先在源交易层筛出符合条件的记录，取最多50笔交易做测试，不要在单词展开后的行集上做limit，避免切错数据范围。
• 固定CTE结果集：对需要被多次引用的CTE加上MATERIALIZED关键字，要求引擎预先将CTE结果计算存储一次，供自连接两端复用，避免两次独立执行拿到不一致的行集。
• 优化正则计算逻辑：提前在源表层计算一次提取后的单词数组，后续UNNEST、字段选择、过滤逻辑全复用该数组，避免反复调用正则函数浪费计算资源。
• 完善连接条件：同时绑定同一交易ID、相邻单词序号两个约束，确保生成的二元组来自同一笔交易的连续单词。

修正后的可直接运行的示例代码：

with materialized valid_transactions as (
    select 
        transaction_id,
        f70_remittance_info,
        regexp_extract_all(f70_remittance_info, '([a-zA-Z]+)') as word_arr
    from exploration_transaction
    where 
        f70_remittance_info is not null
        and cardinality(regexp_extract_all(f70_remittance_info, '([a-zA-Z]+)')) > 1
    limit 50
),
materialized word_list as (
    select
        transaction_id,
        words,
        n as word_position,
        word_arr as f70_words
    from valid_transactions
    cross join unnest(word_arr) with ordinality as t (words, n)
)
select
    wl1.transaction_id,
    wl1.words as first_word,
    wl2.words as second_word,
    concat(wl1.words, ' ', wl2.words) as bigram
from word_list wl1
inner join word_list wl2
    on 
        wl1.transaction_id = wl2.transaction_id
        and wl1.word_position = wl2.word_position - 1

注意：如果使用不支持MATERIALIZED关键字的旧版Athena引擎，可以将word_list的计算结果先写入临时表，再基于临时表做自连接，本质也是为了固定供两次引用的数据集，避免独立执行带来的行集不一致问题。

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