Хранимые процедуры SQL

Question

Имеется таблица с 4 столбцами (genres, title, mov_year, rating).

так же есть хранимая процедура, которой на вход мы передаем несколько значений жанров(Например (Drama|Romance)) и процедура должна вернуть ровно numbers фильмов для каждого переданного жанра для numbers=2 должны получить что то типо такого, то есть фильмы выбираются по максимальному рейтингу

genre, Title , mov_year, rating
Drama,xxx,yyyy,rr 
Drama,xxx,yyyy,rr
Romance,xxx,yyyy,rr
Romance,xxx,yyyy,rr

CREATE TABLE `selected_movies`(
            genres TEXT,
            title TEXT,
            movie_year INT,
            rating DOUBLE
        ) ;

INSERT INTO `selected_movies` (`genres`,`title`,`mov_year`,`rating`) VALUES ('Crime|Drama','Brother (Brat) (1997)','1997',5);
INSERT INTO `selected_movies` (`genres`,`title`,`mov_year`,`rating`) VALUES ('Animation|Children','Winnie Pooh (1969)','1969',3);
INSERT INTO `selected_movies` (`genres`,`title`,`mov_year`,`rating`) VALUES ('Drama|Romance','Sandpiper, The (1965)','1965',2);
INSERT INTO `selected_movies` (`genres`,`title`,`mov_year`,`rating`) VALUES ('Drama|Romance','Cruel Romance, A (Zhestokij Romans) (1984)','1984',5);
INSERT INTO `selected_movies` (`genres`,`title`,`mov_year`,`rating`) VALUES ('Comedy|Drama|Romance','Down Argentine Way (1940)','1940',3);
INSERT INTO `selected_movies` (`genres`,`title`,`mov_year`,`rating`) VALUES ('Comedy','Siam Sunset (1999)','1999',3);
INSERT INTO `selected_movies` (`genres`,`title`,`mov_year`,`rating`) VALUES ('Comedy|Romance','Spellbound (2011)','2011',2);
INSERT INTO `selected_movies` (`genres`,`title`,`mov_year`,`rating`) VALUES ('Documentary|Drama','On the Ropes (1999)','1999',4);
INSERT INTO `selected_movies` (`genres`,`title`,`mov_year`,`rating`) VALUES ('Comedy|Documentary','Animals are Beautiful People (1974)','1974',5);
INSERT INTO `selected_movies` (`genres`,`title`,`mov_year`,`rating`) VALUES ('Comedy','Calcium Kid, The (2004)','2004',4);

для этой таблице результат должен быть такой:

genre, Title , mov_year, rating
Drama,Brother (Brat) (1997), 1997, 5
Drama, "Cruel Romance, A (Zhestokij Romans) (1984)", 1984, 5
Romance, "Cruel Romance, A (Zhestokij Romans) (1984)", 1984, 5
Romance, Down Argentine Way (1940), 1940,3

Подскажите пожалуйста как это сделать?

Answer 1

WITH 
cte1 AS ( SELECT *
          FROM JSON_TABLE(CONCAT('["', REPLACE(@criteria, '|', '","'), '"]'),
                          '$[*]' COLUMNS (criteria VARCHAR(255) PATH '$')) jsontable ),
cte2 AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY cte1.criteria 
                                       ORDER BY selected_movies.rating DESC, RAND()) rn 
          FROM cte1
          JOIN selected_movies ON LOCATE(cte1.criteria, selected_movies.genres) )
SELECT criteria, title, movie_year, rating
FROM cte2
WHERE rn <= 2;

fiddle

В принципе, всё можно упаковать в один CTE... только надо ли? ускорить не ускорит, а понятности может стать немного меньше.

БЛОГ НА HUSL

Хранимые процедуры SQL

Ответы (1 шт):