Как получить отдельные теги из столбца TAGS?
У меня есть следующая таблица в базе данных MySQL:
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
Я хочу получить теги и отсортировать их по очкам. Затем я хочу отсортировать теги по частоте отрицательных оценок, чтобы наихудшие теги отображались наверху.
Ожидаемый результат для вышеуказанного запроса будет:
TAG FREQUENCY
html 2
css 1
javascript 1
xml 0
Я застреваю с извлечением отдельных тегов из столбцов.
SELECT tags, COUNT(*)
FROM my_table
WHERE score < 0
@LukaszSzozda: Запрос, представленный в принятом ответе, очень сложен и вообще не объясняется. Поэтому спасибо за ссылку, но жду, что кто-нибудь подробно объяснит, как решить мою задачу.
Единственный верный ответ - изменить вашу схему на 3NF. Хранение тегов в формате CSV (с разделителями | -) - плохой дизайн.
@ScalaBoy. . . Почему вы храните несколько значений в одном столбце? Это не способ хранения вещей в SQL. В SQL есть отличные способы хранения списков - он называется таблицей, а не строковым столбцом.
@GordonLinoff: Вот как данные хранятся в Kaggle (kaggle.com/stackoverflow/stackoverflow). Взгляните на таблицу post_questions. Так что этот вопрос не следует адресовать мне :)
Ответы 1
Когда вы застряли с таким ужасным форматом данных, вы можете что-то с ним сделать. Таблица чисел может помочь, но вот пример, который извлечет до первых трех элементов:
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;
Что это делает? Предложение on гарантирует, что в строке есть как минимум теги n для заданного значения n (большие значения отфильтровываются).
Две функции substring_index() извлекают n-й тег из списка. А еще есть агрегация.
Что будет, если будет 4 тега? Будет ли он обрабатывать 3-й и 4-й теги как один тег?
Вам необходимо расширить подзапрос n для обработки максимального количества тегов.