SQL-группировка по списку предпочтительных значений

Прежде всего: я не совсем уверен, что добавить в заголовок вопроса, я не знаю, как вызвать такой запрос, возможно, поэтому я не нашел ответа.

У меня есть таблица радиостанций и таблица потоков. Каждая радиостанция может иметь несколько потоков, для разных форматов, битрейтов и т.д. Я хочу получить список всех станций с потоком в предпочтительном формате для данного приложения.

Теперь, когда это становится сложно, я хочу, чтобы предпочтительный формат был списком, а моя база данных должна возвращать поток первый подходящий.

Итак, у меня может быть такой список: ('MP3', 'AAC', 'OGG')

Затем я хочу, чтобы MySQL возвращала для каждой станции поток типа "MP3", но если он не существует, он должен вернуть поток "AAC" для этой станции и т.д. Если найденный подходящий поток не найден, он не должен возвращать станцию в al.

Пример:

CREATE TABLE `stations` (
  `id` INT(11),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `streams` (
  `id` INT(11),
  `station` INT(11),
  `media_type` ENUM('MP3', 'OGG', 'AAC', 'Flash'),
  PRIMARY KEY (`id`),
  KEY (`station`),
  CONSTRAINT `fk_1` FOREIGN KEY (`station`) REFERENCES `stations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

INSERT INTO `stations` (`id`) VALUES (1), (2), (3);
INSERT INTO `streams` (`id`, `station`, `media_type`) VALUES (1, 1, 'MP3'), (2, 1, 'AAC'), (3, 2, 'Flash'), (4, 2, 'AAC'), (5, 3, 'Flash');

Я сделал SQLFiddle здесь

Если предпочтительным списком типов носителей является ('MP3', 'AAC'), то желаемый результат с использованием приведенных выше примерных данных должен быть:

station stream  type
1       1       MP3
2       4       AAC

У станции 1 должен быть поток 1 типа MP3 (также поддерживается AAC, но MP3 предпочтительнее AAC)
Станция 2 должна иметь поток 4 типа AAC (MP3 не предлагается станцией 2, но AAC)
Станция 3 не должна быть в результате, поскольку она предлагает только потоковое воспроизведение через Flash.

Я пробовал это:

SELECT
    st.id AS station_id,
    str.id AS stream_id,
    str.media_type,
    FIELD(str.media_type, 'MP3', 'AAC') AS preference
FROM
    stations st
LEFT JOIN
    streams str ON str.station = st.id
GROUP BY 
    st.id
HAVING
    MIN(preference)

Но это возвращает только 1 или 0 записей, зависящих от того, что первая запись в таблице потоков является предпочтительным типом носителя, я не понимаю, почему.

Единственное решение, которое я смог найти, это упорядочить потоки, используя подзапрос, а затем группировать по станции_ид, например:

SELECT sub.* FROM
    (SELECT
        st.id AS station_id,
        str.id AS stream_id,
        str.media_type
    FROM
        stations st
    LEFT JOIN
        streams str ON str.station = st.id
    WHERE
        str.media_type IN ('MP3', 'AAC')
    ORDER BY
        FIELD(str.media_type, 'MP3', 'AAC')
    ) AS sub
GROUP BY sub.station_id

Но это приведет к полному сканированию таблицы временной таблицы, создаваемой подзапросом, производительность неприемлема. Поскольку мы не можем ограничить внутренний запрос (поскольку он еще не сгруппирован), таблица temp будет очень большой.

B.T.W., я запускаю MySQL 5.6

Итак, какой запрос я должен использовать для работы со списком предпочтительных свойств?

Ответ 1

Вам не нужно внешнее соединение, если вы хотите вернуть только строки, где существуют 'MP3' или 'AAC'.

Это стандартное SQL-решение, которое будет работать как есть в mysql, см. fiddle:

SELECT
   st.id AS station_id,
   COALESCE(MAX(CASE WHEN str.media_type = 'MP3' THEN str.id END)
           ,MAX(CASE WHEN str.media_type = 'AAC' THEN str.id END)
           ) AS stream_id,
   COALESCE(MAX(CASE WHEN str.media_type = 'MP3' THEN str.media_type END)
           ,MAX(CASE WHEN str.media_type = 'AAC' THEN str.media_type END)
           ) AS media_type
FROM stations st
JOIN streams str 
  ON str.station = st.id
WHERE -- only stations with the requested media types
   str.media_type IN ('MP3', 'AAC')
GROUP BY st.id

Легко добавлять больше типов носителей, в основном вырезать и вставлять. COALESCE возвращает первый подходящий тип носителя на основе порядка CASE.

Ответ 2

Edit

Чтобы получить максимальный поток предпочтений на радиостанцию, вы можете использовать переменную для ранжирования каждого потока на радиостанцию в соответствии со своим значением media_type и выбирать только строки с рангом 1:

select * from (
  select *, 
    @rn := if(@prevStationId = station_id, @rn+1, 1) rn,
    @prevStationId := station_id
  from streams
  where media_type in ('MP3','AAC')
  order by station_id, FIELD(media_type,'MP3','AAC')
) t1 where rn = 1