Как написать правильный sql запрос для фильтрации и вывода фильмов?

Question

[Denis]

у меня есть такая таблица, которая называется joined_df:

+-------+--------------------+---------+----+-------+-----------------+
|movieId|               title|   genres|year|movieId|      avg(rating)|
+-------+--------------------+---------+----+-------+-----------------+
|   1580|Men in Black (a.k...|   Sci-Fi|1997|   1580|3.487878787878788|
|   1580|Men in Black (a.k...|   Comedy|1997|   1580|3.487878787878788|
|   1580|Men in Black (a.k...|   Action|1997|   1580|3.487878787878788|
|   2366|          King Kong |   Horror|1933|   2366|             3.64|
|   2366|          King Kong |  Fantasy|1933|   2366|             3.64|
|   2366|          King Kong |Adventure|1933|   2366|             3.64|
|   2366|          King Kong |   Action|1933|   2366|             3.64|
|   3175|       Galaxy Quest |   Sci-Fi|1999|   3175|             3.58|
|   3175|       Galaxy Quest |   Comedy|1999|   3175|             3.58|
|   3175|       Galaxy Quest |Adventure|1999|   3175|             3.58|
|   1088|      Dirty Dancing |  Romance|1987|   1088|3.369047619047619|
|   1088|      Dirty Dancing |  Musical|1987|   1088|3.369047619047619|
|   1088|      Dirty Dancing |    Drama|1987|   1088|3.369047619047619|
|  32460|Knockin' on Heave...|    Drama|1997|  32460|             4.25|
|  32460|Knockin' on Heave...|    Crime|1997|  32460|             4.25|
|  32460|Knockin' on Heave...|   Comedy|1997|  32460|             4.25|
|  32460|Knockin' on Heave...|   Action|1997|  32460|             4.25|
|  44022|Ice Age 2: The Me...|   Comedy|2006|  44022|3.217391304347826|
|  44022|Ice Age 2: The Me...| Children|2006|  44022|3.217391304347826|
|  44022|Ice Age 2: The Me...|Animation|2006|  44022|3.217391304347826|
+-------+--------------------+---------+----+-------+-----------------+
only showing top 20 rows

и у меня есть переменные для фильтрации

ARG_GENRES = 'Sci-Fi|Action'       #by default = ''   фильтр по жанру 
YEAR_TO = 2007                     #by default = 2030. фильтр по году 
YEAR_FROM = 1900                   #by default = 1800  фильтр по году 
REGEXP = 'Terminator'              #by default = ''   фильтр по названию фильма
N = 5                              #by default = 10000  фильтр по количеству строк в выводимом результате

я планировал передавать эти аргументы через формат строку в sql запрос

основная проблема с запросом у меня связана с жанром, то есть с тем, что жанров может быть несколько и жанры могут перечисляться через |

как мне написать sql запрос, который будет фильтровать фильмы исходя из всех аргументов для фильтрации?

Например:
если аргументы для фильтрации такие -N 4 -ARG_GENRES 'Action'

то результат вывода должен быть таким:

genre;title;year;rating
Action;Tombstone ;1993;5.00
Action;Star Wars: Episode IV - A New Hope ;1977;5.00
Action;Rob Roy ;1995;5.00
Action;Desperado ;1995;5.00

а если аргументы для фильтрации такие: -N 4 -ARG_GENRES 'Drama|Adventure' -YEAR_FROM 2015 -YEAR_TO 2020

то результат должен быть такой:
genre;title;year;rating
Drama;Wonder ;2017;5.00
Drama;Inside Out ;2015;5.00
Drama;Three Billboards Outside Ebbing, Missouri ;2017;5.00
Drama;The Martian ;2015;5.00
Adventure;Mad Max: Fury Road ;2015;5.00
Adventure;Rogue One: A Star Wars Story ;2016;5.00
Adventure;Spectre ;2015;5.00
Adventure;Star Wars: Episode VII - The Force Awakens ;2015;5.00

То есть, если у нас указан не один жанр, а несколько, то N (аргумент для фильтрации по количеству выводимого результата), должен применяться к каждому жанру отдельно

правильно сортировки:
жанр(в алфавитном порядке), рейтинг(по убыванию), год выпуска фильма(по убыванию), и название фильма(в алфавитном порядке)

Comments

[Akina]

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

Я ведь правильно понимаю, что:
ARG_GENRES = 'Sci-Fi|Action' - это запрос на фильмы, которые помечены хотя бы одним из тегов списка?
REGEXP = 'Terminator' - это всегда подстрока? или там реально может быть паттерн?

Ну и уж коли захотелось задавать "фильтр по количеству строк в выводимом результате" - то неплохо бы указать точную версию SQL-сервера. А для того, чтобы народ мог проверить свои предложения - создай online fiddle (или хотя бы выложи CREATE TABLE + INSERT INTO) и дай пару наборов (список критериев + требуемый результат на этих данных).

[Denis]

Akina,

ARG_GENRES = 'Sci-Fi|Action' - это запрос на фильмы, которые помечены хотя бы одним из тегов списка? Да

REGEXP = 'Terminator' - это всегда подстрока? или там реально может быть паттерн? Это подстрока

вот то что я написал, но это работает только если мы передаем один жанр

SELECT      
            genres,
            title,
            year,
            ROUND(`avg(rating)`, 2) as rating

FROM        joined_df

WHERE
            year BETWEEN {YEAR_FROM} and {YEAR_TO}
            AND title LIKE "%{REGEXP}%"
            AND genres LIKE "%{ARG_GENRES}%"

ORDER BY    
            genres,
            rating DESC, 
            year DESC, 
            title

LIMIT       {N};

[Akina]

AND genres LIKE "%{ARG_GENRES}%"
Это фигня.
AND LOCATE(genres, REPLACE({ARG_GENRES}, '|', ',')
При этом сами genres не должны содержать запятых, а передаваемый параметр - паразитных пробелов между разделителем и значением.

Answer 1

[Николай Турнавиотов]

OR, AND операторы пробовали?

Comments

[Denis]

Да, но мне нужно чтобы это работало при любых условиях, то есть должна быть возможность передавать как один жанр так и несколько через |