이 쿼리를 postgresql 9.3에서 작성해야합니다.
- 각 국가에서 가장 인기있는 영화를 나열합니다. 가장 인기있는 영화 / 영화는 해당 국가의 모든 사용자에서 가장 높은 평균 등급을받은 영화입니다. 동점 인 경우 모든 영화 순서를 알파벳순으로 반환합니다. (2 열)
필요한 테이블 :
CREATE TABLE movie (
id integer,
name varchar(200),
year date
);
CREATE TABLE userProfile (
userid varchar(200),
gender char(1),
age integer,
country varchar(200),
registered date
);
CREATE TABLE ratings (
mid integer,
userid varchar(200),
rating integer
);
CREATE INDEX movie_id_idx ON movie (id);
CREATE INDEX userProfile_userid_idx ON userProfile (userid);
CREATE INDEX ratings_userid_idx ON ratings (userid);
CREATE INDEX ratings_mid_idx ON ratings (mid);
CREATE INDEX ratings_userid_mid_idx ON ratings (userid, mid);
내 쿼리는 다음과 같습니다.
CREATE TEMP TABLE tops AS SELECT country, name
FROM ratings AS r INNER JOIN userProfile AS u
ON r.userid=u.userid
INNER JOIN movie AS m ON m.id = r.mid LIMIT 0;
~10 min
CREATE TEMP TABLE avg_country AS
SELECT country, r.mid, AVG(rating) AS rate
FROM ratings AS r INNER JOIN userProfile AS u
ON r.userid=u.userid
GROUP BY country, r.mid;
~8 min
DO $$
DECLARE arrow record;
BEGIN
CREATE TABLE movie_names AS SELECT id, name FROM movie;
FOR arrow IN SELECT DISTINCT country FROM userProfile ORDER BY country
LOOP
CREATE TABLE movies AS SELECT mid FROM (SELECT MAX(rate) AS m_rate FROM avg_country
WHERE country=arrow.country) AS max_val CROSS JOIN LATERAL
(SELECT mid FROM avg_country
WHERE country=arrow.country AND rate=max_val.m_rate) AS a;
WITH names AS (DELETE FROM movie_names AS m
WHERE m.id IN (SELECT mid FROM movies) RETURNING name)
INSERT INTO tops
SELECT arrow.country, name FROM names ORDER BY name;
DROP TABLE movies;
END LOOP;
DROP TABLE movie_names;
END$$;
SELECT * FROM tops;
DROP TABLE tops, avg_country;
많은 감사드립니다)
이것은 kordirkos 대답 과 비슷 하지만 하위 쿼리가 하나 더 적습니다.
select country, movie_name, avg_rating
from (select u.country, m.name as movie_name, avg(r.rating) as avg_rating
rank() over (partition by u.country order by avg(r.rating) desc) as seqnum
from userProfile u join
ratings r
on u.userid = r.userid join
movie m
on r.mid = m.id
group by u.country, m.id -- `name` is not needed here because id is unique
) uc
where seqnum = 1;
또는 국가 당 한 행에 목록을 가져 오려면 다음을 수행하십시오.
select country, string_agg(movie_name, '; ') as most_popular_movies
from (select u.country, m.name as movie_name, avg(r.rating) as avg_rating
rank() over (partition by u.country order by avg(r.rating) desc) as seqnum
from userProfile u join
ratings r
on u.userid = r.userid join
movie m
on r.mid = m.id
group by u.country, m.id -- `name` is not needed here because id is unique
) uc
where seqnum = 1
group by country;
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다