Athena / Presto | Impossibile abbinare la riga ID all'auto join

Question

Sto cercando di ottenere i bi-grammi su una colonna di stringhe.

Ho seguito l'approccio qui, ma Athena / Presto mi sta dando errori nei passaggi finali.

Finora il codice sorgente

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

Il problema specifico che sto riscontrando è sull'ultima riga, quando provo a unirti agli ID della transazione: restituisce sempre zero righe. Funziona se mi unisco solo per wl1.n = wl2.n-1(la posizione sull'array) che è inutile se non posso vincolarlo a uno stesso ID.

Athena non supporta la funzione ngrams di presto, quindi sono rimasto con questo approccio.

Qualche indizio sul perché non funziona? Grazie!

Answer 1

Questa è speculazione. Ma noto che il tuo CTE sta usando limitcon no order by. Ciò significa che viene restituito un insieme arbitrario di righe.

Sebbene alcuni database materializzino CTE, molti non lo fanno. Eseguono il codice in modo indipendente ogni volta che viene fatto riferimento. La mia ipotesi è che il codice venga eseguito in modo indipendente e l'insieme arbitrario di 50 righe non ha ID di transazione in comune.

Una soluzione sarebbe aggiungere order by transacdtion_idnella sottoquery.