How to get maximum appearance count of number from comma separated number string from multiple rows-CodePudding

My MySQL table having column with comma separated numbers. See below example -

| style_ids  |
| ---------- |
| 5,3,10,2,7 |
| 1,5,12,9   |
| 6,3,5,9,4  |
| 8,3,5,7,12 |
| 7,4,9,3,5  |

So my expected result should have top 5 numbers with maximum appearance count in descending order as 5 rows as below -

| number | appearance_count_in_all_rows | 
| -------|----------------------------- |
| 5      | 5                            |
| 3      | 4                            |
| 9      | 3                            |
| 7      | 2                            |
| 4      | 2                            |

Is it possible to get above result by MySQL query ?

CodePudding user response：

As already alluded to in the comments, this is a really bad idea. But here is one way of doing it -

WITH RECURSIVE seq (n) AS (
    SELECT 1 UNION ALL SELECT n 1 FROM seq WHERE n < 20
), tbl (style_ids) AS (
    SELECT '5,3,10,2,7' UNION ALL
    SELECT '1,5,12,9' UNION ALL
    SELECT '6,3,5,9,4' UNION ALL
    SELECT '8,3,5,7,12' UNION ALL
    SELECT '7,4,9,3,5'
)
SELECT seq.n, COUNT(*) appearance_count_in_all_rows
FROM seq
JOIN tbl ON FIND_IN_SET(seq.n, tbl.style_ids)
GROUP BY seq.n
ORDER BY appearance_count_in_all_rows DESC
LIMIT 5;

Just replace the tbl cte with your table.

CodePudding user response：

As already pointed out you should fix the data if possible.

For further details read Is storing a delimited list in a database column really that bad?.

You could use below answer which is well explained here and a working fiddle can be found here.

Try,

select distinct_nr,count(distinct_nr) as appearance_count_in_all_rows 
from   ( select substring_index(substring_index(style_ids, ',', n),  ',',  -1) as distinct_nr
         from test
         join numbers on char_length(style_ids) - char_length(replace(style_ids, ',', ''))  >= n - 1 
        ) x
group by distinct_nr
order by appearance_count_in_all_rows desc  ;

CodePudding user response：

SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX(style_ids, ',', n), ',', -1) AS number,
    COUNT(SUBSTRING_INDEX(SUBSTRING_INDEX(style_ids, ',', n), ',', -1)) AS appearance_count_in_all_rows
FROM
    (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) nums
        JOIN
    table_name
WHERE
    n <= LENGTH(style_ids) - LENGTH(REPLACE(style_ids, ',', ''))   1
GROUP BY number
ORDER BY appearance_count_in_all_rows DESC
LIMIT 5;

This query uses a combination of the SUBSTRING_INDEX() and UNION statements to extract each number from the comma-separated list, and then uses the COUNT() function to count the number of appearances of each number in all rows.

It's important to note that in the above query, you should replace "table_name" with the actual name of your table and the column name "style_ids" with the actual column name.