Athena / Presto | Não é possível coincidir com a linha de ID na auto-junção

Question

Estou tentando obter os bi-gramas em uma coluna de barbante.

Eu segui a abordagem aqui, mas Athena / Presto está me dando erros nas etapas finais.

Código fonte até agora

with word_list as (
    SELECT 
      transaction_id, 
      words, 
      n, 
      regexp_extract_all(f70_remittance_info, '([a-zA-Z]+)') as f70,
      f70_remittance_info
    FROM exploration_transaction
    cross join unnest(regexp_extract_all(f70_remittance_info, '([a-zA-Z]+)')) with ordinality AS t (words, n)
    where cardinality((regexp_extract_all(f70_remittance_info, '([a-zA-Z]+)'))) > 1
    and f70_remittance_info is not null
    limit 50 )
select wl1.f70, wl1.n, wl1.words, wl2.f70, wl2.n, wl2.words
from word_list wl1
join word_list wl2 
on wl1.transaction_id = wl2.transaction_id

O problema específico que estou tendo está na última linha, quando tento autoassociar os IDs de transação - ele sempre retorna zero linhas. Funciona se eu unir apenas por wl1.n = wl2.n-1(a posição na matriz), o que é inútil se eu não puder restringi-lo a um mesmo id.

Athena não suporta a função ngrams por presto, então fico com esta abordagem.

Alguma pista de por que isso não está funcionando? Obrigado!

Answer 1

Isso é especulação. Mas noto que seu CTE está usando limitsem order by. Isso significa que um conjunto arbitrário de linhas está sendo retornado.

Embora alguns bancos de dados materializem CTEs, muitos não o fazem. Eles executam o código independentemente sempre que ele é referenciado. Meu palpite é que o código é executado de forma independente e o conjunto arbitrário de 50 linhas não tem IDs de transação em comum.

Uma solução seria adicionar order by transacdtion_idna subconsulta.