Как составить составной SQL-запрос с обращением к сторонней таблице?

Question

HSQLDB

Есть таблица Cinemas:

CREATE TABLE cinemas
(
    id   INTEGER GENERATED BY DEFAULT AS SEQUENCE GLOBAL_SEQ PRIMARY KEY,
);

Таблица Films (ссылается на Cinema отношением ManyToOne):

CREATE TABLE films
(
    id        INTEGER GENERATED BY DEFAULT AS SEQUENCE GLOBAL_SEQ PRIMARY KEY,
    cinema_id INTEGER              NOT NULL,
    date      DATE DEFAULT today() NOT NULL,
    CONSTRAINT films_cinema_id_date UNIQUE (cinema_id, date),
    FOREIGN KEY (cinema_id) REFERENCES cinemas (id) ON DELETE CASCADE
);

Таблица Users:

CREATE TABLE users
(
    id   INTEGER GENERATED BY DEFAULT AS SEQUENCE GLOBAL_SEQ PRIMARY KEY,
);

И последняя, четвёртая таблица Votes отображает голосование пользователей за фильмы. То есть: есть несколько кинотеатров. В каждом кинотеатре каждый день идёт новый фильм. Пользователи голосуют за этот фильм, что отражается в таблице Votes:

CREATE TABLE votes
(
    user_id INTEGER NOT NULL,
    film_id INTEGER NOT NULL,
    CONSTRAINT votes_user_film_idx UNIQUE (user_id, film_id),
    FOREIGN KEY (user_id) REFERENCES users (id),
    FOREIGN KEY (film_id) REFERENCES films (id)
);

Какой запрос мне составить, чтобы получить кинотеатр-победитель за конкретную дату? То есть кинотеатр, за фильм которого на конкретную дату проголосовало больше всего людей.

Я подозреваю, что надо использовать MAX и EXISTS, но без понятия как. Не могли бы вы привести пример правильного запроса?

Answer 1

Можно использовать для решения оконные функции. Запрос выведет кинотеатры победители за каждую дату, если нужна конкретная дата, просто дополните условие WHERE. Стоит добавить, что запрос не выведет дату в которую были показаны фильмы, если за них вообще никто не проголосовал

SELECT
    cinema_id,
    cinema_name,
    films_date,
    rating
FROM (
    SELECT
        *,
        CASE
            WHEN rating = MAX(rating) OVER(PARTITION BY films_date) THEN 1 ELSE 0
        END max_rating
    FROM (
        SELECT
            c.ID cinema_id,
            c.NAME cinema_name,
            f.DATE films_date,
            COUNT(*) rating
        FROM cinemas c
            INNER JOIN films f ON c.ID = f.CINEMA_ID
            INNER JOIN votes v ON f.ID = v.FILM_ID
        GROUP BY c.ID, c.NAME, f.DATE
    ) date_rating
) max_date_rating
WHERE max_rating = 1
ORDER BY films_date;

Answer 2

Удалось справить при помощи такого кода (синтаксис HSQLDB):

SELECT *
    FROM CINEMAS
    WHERE ID IN (
    SELECT CINEMA_ID
    FROM (
             SELECT CINEMA_ID, COUNT(*) AS CINEMA_COUNT
             FROM VOTES as VOTE,
                  FILMS as FILM,
                  CINEMAS as CINEMA
             WHERE VOTE.FILM_ID = FILM.ID
               AND FILM.CINEMA_ID = CINEMA.ID
               AND FILM.DATE = :DATE
             GROUP BY CINEMA_ID
             ORDER BY CINEMA_COUNT DESC
             LIMIT 1));

SELECT в шестой строке возвращает таблицу вида:

  СINEMA_ID    CINEMA_COUNT
  ---------    ------------
   100004           4

А именно: считает количество голосов для фильма по определённой дате для каждого кинотеатра, а потом группирует по CINEMA_ID. Я не придумал, как сюда прикрутить MAX, чтобы выбирать максимальное из CINEMA_COUNT, а возвращать соответствующий максимальному значению CINEMA_ID, поэтому просто сортирую группу по количеству голосов и при помощи LIMIT 1 беру только верхнюю запись.

Потом парочка костылей. Я буду не против, если вы подскажете, как сделать этот запрос грамотней, но пока есть что есть. В строке 4 я получаю из полученной таблицы только одну колонку, а потом при помощи IN в строке 3 нахожу и возвращаю подходящий CINEMA, id которого соответствует CINEMA_ID.

БЛОГ НА HUSL

Как составить составной SQL-запрос с обращением к сторонней таблице?

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