PostgreSQL 9.3-array_agg挑战 [英] Postgresql 9.3 - array_agg challenge

问题描述

我试图了解Postgresql 9.3中的array_agg函数.我为每个有兴趣参加的人整理了一个有趣的例子.

I'm trying to understand the array_agg function in Postgresql 9.3. I've put together a fun example for everyone who may be interested in participating.

1980年代的美国电影的任何影迷都可能熟悉"brat pack"，他们一起出现在许多热门电影中.利用有关维基百科上关于臭小子电影的信息，我创建了表格，将它们结合在一起可以告诉我们谁在彼此合作-如果我们有正确的查询！

Any fan of American films from the 1980's may be familiar with the "brat pack" who appeared in many hit films together. Using the information about the brat pack films on wikipedia, I've created tables that when joined together, can tell us who worked with each other -- if we have the right query!

/*
See:  http://en.wikipedia.org/wiki/Brat_Pack_(actors)
*/

CREATE TABLE actor(
    id SERIAL PRIMARY KEY, 
    name VARCHAR(50)
);
insert into actor(name) values ('Emilio Estevez'),('Anthony Michael Hall'),('Rob Lowe'),('Andrew McCarthy'),('Demi Moore'),('Judd Nelson'),('Molly Ringwald'),('Ally Sheedy')

CREATE TABLE movie(
    id SERIAL PRIMARY KEY, 
    title VARCHAR(200)
);
insert into movie(title) values ('The Outsiders'),('Class'),('Sixteen Candles'),('Oxford Blues'),('The Breakfast Club'),('St. Elmos Fire'),
('Pretty in Pink'),('Blue City'),('About Last Night'),('Wisdom'), ('Fresh Horses'),('Betsys Wedding'),('Hail Caesar');

CREATE TABLE movie_brats(
    id SERIAL PRIMARY KEY, 
    movie_id INT REFERENCES movie(id), 
    actor_id INT REFERENCES actor(id)
);
insert into movie_brats(movie_id, actor_id) values (1,1),(1,3),(2,3),(2,4),(3,2),(3,7),(4,3),(4,8),(5,1),(5,2),(5,6),
(5,7),(5,8),(6,1),(6,3),(6,4),(6,5),(6,6),(6,8),(7,4),(7,7),(8,6),(8,8),(9,3),(9,5),(10,1),(10,5),(11,4),(11,7),
(12,7),(12,8),(13,2),(13,6);

查询:显示臭名昭著包的每个成员与谁一起使用的人的独特列表，并在两列中按名称排序

Query: Show a distinct list of who each member of the brat pack worked with, ordered by name in both columns

 Name                      Worked With
 ----------------------------------------------------------------------------------------------------------------
Emelio Estevez       |  Emilio Estevez, Anthony Michael Hall, Rob Lowe, Andrew McCarthy, Demi Moore, Judd Nelson, Molly Ringwald, Ally Sheedy
*/

我坏了的查询:

select a1.name, array_to_string(array_agg(a2.name),', ') as Co_Stars
from actor a1, actor a2, movie m, movie_brats mb
where 
    m.id = mb.movie_id
    and a1.id = mb.actor_id
    and a2.id = mb.actor_id
group by a1.id

推荐答案

SQL提琴

with v as (
    select
        a.id as actor_id,
        a.name as actor_name,
        m.id as m_id
    from
        actor a
        inner join
        movie_brats mb on a.id = mb.actor_id
        inner join
        movie m on m.id = mb.movie_id
)
select
    v1.actor_name as "Name",
    string_agg(
        distinct v2.actor_name, ', ' order by v2.actor_name
    ) as "Worked With"
from
    v v1
    left join
    v v2 on v1.m_id = v2.m_id and v1.actor_id != v2.actor_id
group by 1
order by 1

如果它们在一部以上的电影中一起工作，则上面的不同聚合对于不显示重复的名称是必要的.

The distinct aggregation above is necessary to not show repeated names in case they worked together in more than one movie.

left join对于不抑制与inner join会与列表中的任何其他角色不起作用的演员是必要的.

The left join is necessary to not suppress an actor that did not work with any of the others in the list as would happen with an inner join.

如果要显示他们在哪部电影中一起工作: SQL提琴

If you want to show in which movie they worked together: SQL Fiddle

with v as (
    select
        a.id as actor_id,
        a.name as actor_name,
        m.id as m_id,
        m.title as title
    from
        actor a
        inner join
        movie_brats mb on a.id = mb.actor_id
        inner join
        movie m on m.id = mb.movie_id
)
select
    a1 as "Name",
    string_agg(
        format('%s (in %s)', a2, title), ', '
        order by format('%s (in %s)', a2, title)
    ) as "Worked With"
from (
    select 
        v1.actor_name as a1,
        v2.actor_name as a2,
        string_agg(v1.title, ', ' order by v1.title) as title    
    from
        v v1
        left join
        v v2 on v1.m_id = v2.m_id and v1.actor_id != v2.actor_id
    group by 1, 2
) s
group by 1
order by 1

这篇关于PostgreSQL 9.3-array_agg挑战的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！