CODEWITHSUNDEEP
mysqlsql
ScalaBoy
ScalaBoy

Reputation: 3392

How to retrieve individual tags from TAGS column?

I have the following table in MySQL database:

id    creation_date                         score     tags
1     2016-02-09 07:24:59.097000+00:00      -1        html|javascript
2     2016-02-09 08:10:00.000000+00:00      0         xml|css
3     2016-02-10 08:00:15.000000+00:00      2         html|javascript
4     2016-02-11 07:00:45.000000+00:00      -5        html|css

I want to retrieve the tags and order them by scores. Then I want to sort the tags by the frequency of negative scores, so that the worst tags would appear on top.

The expected result for the above-given query would be:

TAG          FREQUENCY
html         2
css          1
javascript   1
xml          0

I get stuck with the retrieval of individual tags from columns. 

SELECT tags, COUNT(*) 
FROM my_table 
WHERE score < 0

Upvotes: 0

Views: 140

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

When you are stuck with such an awful data format, you can do something with it. A table of numbers can help, but here is an example that will extract up to the first 3 items:

select substring_index(substring_index(tags, '|', n.n), '|', -1) as tag, count(*)
from (select 1 as n union all
      select 2 as n union all
      select 3 as n
     ) n join
     t
     on n.n <= length(tags) - length(replace(t.tags, '|', '')) + 1
group by tag;

What is this doing? The on clause is making sure there are at least n tags in the string, for a given value of n (larger values are filtered out).

The two substring_index() functions are extracting the nth tag from the list. And then there is aggregation.

Upvotes: 1

Related Questions