Получить строку с максимальным совпадением?

Question

[alex4answ]

Нужно получить список описания фильмов для текущего языка и "сайта"
Есть таблица movie_desc:
id | movie_id | text | site_id | lang

Есть запрос:

SELECT * FROM `movie` where ( site_id = 15 OR site_id = 1 OR site_id IS NULL) AND ( lang = 'ru' OR lang = 'en' OR lang IS NULL)

Запрос возвращает почти все вхождения (оно и понятно, потому что site_id, lang может быть NULL)

Как мне вернуть по 1 строку с наибольшим совпадением по условию на каждый фильм?

Думал использовать GROUP BY по movie_id, но это не агрегированное поле.

P.S. Суть сия извращенства:
Есть фильмы, а есть описания к ним на разных языках, у некоторых фильмов не заполнено описания для какого-то языка, и должна выводиться либо для стандартного языка (lang en) либо общее описание (lang IS NULL)

Answer 1

[Game Master]

select *
from (
         select movie_desc.*,
                row_number()
                        over (partition by movie_id order by domain_id desc) as rn_domain,
  		row_number()
                        over (partition by movie_id order by language_id desc) as rn_lang
         from movie_desc
     ) x
where x.rn_domain = 1 and x.rn_lang = 1;

https://www.db-fiddle.com/f/ipkTSyKiavc4oQ8iRPTKN6/0

Comments

[alex4answ]

Sauran Baitarakhov , запрос не выполняется, "у каждый таблицы должны быть свои алиасы"
Подскажите пожалуйста, в какую сторону мне копать?
Группировке данных, или вообще как-то перестраивать запрос?

[Game Master]

alex4answ, дай плз свой пример, например на sqlfiddle.com
После будет понятно, как помочь тебе с этой задачей

[alex4answ]

Sauran Baitarakhov, sqlfiddle.com/#!9/fb499b/2

В общем нужен просто приоритет,
сначала пытаемся найти для domain_id = 2, если нет -> то = 1, если нет -> IS NULL
такая же история с language_id

Такая в принципе простая задача просто загнала меня в тупик)

[Game Master]

alex4answ, sqlfiddle.com/#!9/fb499b/9
пока посмотри такой вариант...

Скажи, какая у тебя версия MySQL?
Просто на 8-ой версии есть поддержка оконных функции, что облегчит эту задачу.
Можно будет сделать демо на https://www.db-fiddle.com/

[Game Master]

alex4answ, выше в теле ответа ссылка на решение с оконной функцией

[Сергей c0re]

Sauran Baitarakhov,

order by domain_id desc

а если сайтов сотня, если "приоритетный" сайт/язык имеет меньший id чем менее приоритетный сайт/язык?

[Game Master]

Ну если есть конкретные входные параметры, то эти параметры добавляем в условия выборки.
Как-то так:

select *
from (
         select x.*,
                row_number()
                        over (partition by movie_id order by case when domain_id = '2' then 0 else 1 end, domain_id desc) as rn_domain
         from (
                  select movie_desc.*,
                         row_number()
                                 over (partition by movie_id, domain_id order by case when language_id = '1' then 0 else 1 end, language_id desc) as rn_lang
                  from movie_desc
              ) x
     ) t
where t.rn_domain = 1
  and t.rn_lang = 1;

https://www.db-fiddle.com/f/ipkTSyKiavc4oQ8iRPTKN6/1

Сергей,, касательно "меньший id", то здесь уже должен быть конкретный "контракт" между разработчиком и тем, кто заполняет эти приоритеты или тем, кто определяет приоритеты. Я же даю наводку, как можно решить эту задачу оптимально, без лишних соединений и агрегации "group by", что неизбежно добавит необходимость соединения (join) нескольких агрегированных выборок. Решение остается за топикстартером.

[Сергей c0re]

Sauran Baitarakhov, да я то знаю как решить )