Count the number of instances for each value in a delimited string

Mustafa Tahir 0
Viewed 31

I have a field delimited by '->'. I want to do a total count of which value appears most or top 3

ROW 1- "Q -> Res -> tes -> Res -> twet"
ROW 2- "rw -> gewg -> tes -> Res -> twet"
ROW 3- "Y -> Res -> Res -> Res -> twet"

Answer to this example would be:

Top 1- Res |6
Top 2- tewt|3
Top 3- tes |2

Ive tried:

( select count(*)    from unnest(split(text, ' -> ')) word where word = 'Res'  ) cnt from table

but i need to do it dynamically instead of putting in every word

google-bigquery sql
1 Answers

Consider below

select 'Top ' || row_number() over(order by cnt desc) rank, 
  word, cnt
from (
  select word, count(*) cnt
  from your_table, unnest(split(text, ' -> ')) word
  group by word
  order by cnt desc
  limit 3
)

with output

enter image description here

Mikhail Berlyant0
Related