This is a section of my dataset (for example flavours of ice cream and their ratings):

Now I want to calculate the average rating of the flavours. So that I get following output:

How can I approach this in mysql?

CodePudding user response：

with recursive u as
(select 1 as n
union all select n   1 from u
where n < (select max(length(flavor) - length(replace(flavor, ',', '')))   1 
from ice_cream)),
v as
(select
  LTRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(ice_cream.flavor, ',', u.n), ',', -1)) 
single_flavor,
ice_cream.rating
from
  u inner join ice_cream
  on CHAR_LENGTH(ice_cream.flavor)
  -CHAR_LENGTH(REPLACE(ice_cream.flavor, ',', ''))>=u.n-1
)
(select single_flavor, avg(rating) from v group by single_flavor);

u gets you a table with the numbers 1, 2, ..., max number of single flavours in a row. v makes use of u to split every row in the original table into single flavours and their rating, and the last cte just groups rows from v by single flavour and calculates the average rating for each flavour.

Fiddle

CodePudding user response：

You can use the following query

select flavor,avg(rating)
from 
 
  (select
    tablename.rating,
    SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.flavor, ',', numbers.n), ',', -1) flavor
  from
    (select 1 n union all
     select 2 union all select 3 union all
     select 4 union all select 5) numbers INNER JOIN tablename
    on CHAR_LENGTH(tablename.flavor)
       -CHAR_LENGTH(REPLACE(tablename.flavor, ',', ''))>=numbers.n-1) t

group by flavor

Demo in db<>fiddle

Take a look at this link SQL split values to multiple rows