aud08_ngram3.sql

select 		
n1, 		
n2, 		
n3, 		
count(*) as cnt 		
from npath ( 		
	on ( 	
		select 
		docid, 
		seqno, 
		norm 
		from jumbo.aud06_mecab_vertical 
		where pos not in ('記号','助詞','助動詞') 
		and not (pos='名詞' and word in ('他','ん','よう','の','.','!」')) 
		and not (pos='動詞' and word in ('いる','し','さ','いる','れ','する','せ') ) 
	) 	
	partition by docid 	
	order by seqno 	
	using 	
	mode (overlapping) 	
	symbols (true as A) 	
	pattern ('A.A.A') 	
	result (	
		first(docid of A) as docid, 
		first(norm of A) as n1, 
		nth(norm,2 of A) as n2, 
		last(norm of A) as n3 
	) 	
) a1 		
group by 1,2,3 		
order by 4 desc 		
;