问题

I have a table:

Is there any function that I can count how many distinct text contain in these columns? And how many columns contain each text?
I am using MYSQL

Like

And

答案1

得分: 0

To Split comma separated string into rows concatenate square brackets ([]) around your string to make it into a JSON array. Then use JSON_TABLE to convert it into a table.

要将逗号分隔的字符串拆分为行，请在字符串周围连接方括号（[]），使其成为JSON数组。然后使用JSON_TABLE将其转换为表。

To count distinct text use this query :

要计算不同的文本，请使用以下查询：

with cte as (
SELECT t.Rank_, j.genre
FROM mytable t
CROSS JOIN JSON_TABLE(
CONCAT('[', REPLACE(Genre, ',', '","'), ']'),
'$[*]' COLUMNS (genre TEXT PATH '$')
) j
)
select count(distinct genre) as 'Total distinct text'
from cte

要计算不同文本的数量，请使用此查询：

with cte as (
SELECT t.Rank_, j.genre
FROM mytable t
CROSS JOIN JSON_TABLE(
CONCAT('[', REPLACE(Genre, ',', '","'), ']'),
'$[*]' COLUMNS (genre TEXT PATH '$')
) j
)
select count(distinct genre) as 'Total distinct text'
from cte

To count per genre :

按流派计数：

with cte as (
SELECT t.Rank_, j.genre
FROM mytable t
CROSS JOIN JSON_TABLE(
CONCAT('[', REPLACE(Genre, ',', '","'), ']'),
'$[*]' COLUMNS (genre TEXT PATH '$')
) j
)
select genre, count(1) as 'count'
from cte
group by genre

您可以按流派计数：

with cte as (
SELECT t.Rank_, j.genre
FROM mytable t
CROSS JOIN JSON_TABLE(
CONCAT('[', REPLACE(Genre, ',', '","'), ']'),
'$[*]' COLUMNS (genre TEXT PATH '$')
) j
)
select genre, count(1) as 'count'
from cte
group by genre

You can use the conditional aggregation to Pivot rows into columns :

您可以使用条件聚合将行旋转为列：

with cte as (
SELECT t.Rank_, j.genre
FROM mytable t
CROSS JOIN JSON_TABLE(
CONCAT('[', REPLACE(Genre, ',', '","'), ']'),
'$[*]' COLUMNS (genre TEXT PATH '$')
) j
),
cte2 as (
select count(case when genre = 'Action' then 1 end) as 'Action',
count(case when genre = 'Adventure' then 1 end) as 'Adventure',
count(case when genre = 'Sci-Fi' then 1 end) as 'Sci-Fi'
from cte
group by genre
)
select max(Action) AS 'Action', max(Adventure) as 'Adventure', max(Sci-Fi) as 'Sci-Fi'
from cte2

您可以使用条件聚合将行旋转为列：

with cte as (
SELECT t.Rank_, j.genre
FROM mytable t
CROSS JOIN JSON_TABLE(
CONCAT('[', REPLACE(Genre, ',', '","'), ']'),
'$[*]' COLUMNS (genre TEXT PATH '$')
) j
),
cte2 as (
select count(case when genre = 'Action' then 1 end) as 'Action',
count(case when genre = 'Adventure' then 1 end) as 'Adventure',
count(case when genre = 'Sci-Fi' then 1 end) as 'Sci-Fi'
from cte
group by genre
)
select max(Action) AS 'Action', max(Adventure) as 'Adventure', max(Sci-Fi) as 'Sci-Fi'
from cte2

Demo here

演示在这里

with cte as (
  SELECT t.Rank_, j.genre
  FROM mytable t
  CROSS JOIN JSON_TABLE(
                       CONCAT('["', REPLACE(Genre, ',', '","'), '"]'),
                      '$[*]' COLUMNS (genre TEXT PATH '$')
                      ) j
)
select count(distinct genre) as 'Total distinct text'
from cte

    with cte as (
      SELECT t.Rank_, j.genre
      FROM mytable t
      CROSS JOIN JSON_TABLE(
                           CONCAT('["', REPLACE(Genre, ',', '","'), '"]'),
                          '$[*]' COLUMNS (genre TEXT PATH '$')
                          ) j
    )
    select genre, count(1) as 'count'
    from cte
    group by genre

with cte as (
  SELECT t.Rank_, j.genre
  FROM mytable t
  CROSS JOIN JSON_TABLE(
                       CONCAT('["', REPLACE(Genre, ',', '","'), '"]'),
                      '$[*]' COLUMNS (genre TEXT PATH '$')
                      ) j
),
cte2 as (
       select count(case when genre = 'Action' then 1 end) as 'Action',
              count(case when genre = 'Adventure' then 1 end) as 'Adventure',
              count(case when genre = 'Sci-Fi' then 1 end) as 'Sci-Fi'
       from cte
       group by genre
)
select max(Action) AS 'Action', max(Adventure) as 'Adventure', max(`Sci-Fi`) as 'Sci-Fi'
from cte2