PostgreSQL中按场地、赛季分组关联舞蹈演出的实现方案

我有一组舞蹈演出数据，每个演出关联剧目（piece）、场地（venue）、赛季（season）和日期（date）。多数剧目在一个赛季内会被多次演出，部分场景下同一场地同一日会有多场演出（如戏剧节或多剧目连演）。我希望将同一赛季、同一场地中同台演出的剧目归为一个“演出系列”，得到包含场地ID、赛季ID、剧目ID集合、演出日期集合的分组结果。

测试用表结构及数据如下：

CREATE TABLE pieces (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL
);

CREATE TABLE venues (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL
);

CREATE TABLE seasons (
    id SERIAL PRIMARY KEY,
    title varchar(100) NOT NULL
);

CREATE TABLE performances (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    date DATE NOT NULL,
    piece_id INT NOT NULL,
    venue_id INT NOT NULL,    
    season_id INT NOT NULL,
          
    CONSTRAINT fk_piece FOREIGN KEY(piece_id) REFERENCES pieces(id),    
    CONSTRAINT fk_season FOREIGN KEY(season_id) REFERENCES seasons(id),
    CONSTRAINT fk_venue FOREIGN KEY(venue_id) REFERENCES venues(id)
);


INSERT INTO pieces (title) VALUES ('Alice’s Piece'), ('Bob’s Piece'), ('Charlie’s Piece');
INSERT INTO seasons (title) VALUES ('Season 1980/81'), ('Season 1981/82'), ('Season 1982/83');
INSERT INTO venues (title) VALUES ('Old theater New York'), ('Globe Theatre London'), ('Edinburgh Congress Hall');

INSERT INTO performances (title, date, piece_id, venue_id, season_id) VALUES
('Performance 1', '1981-01-01', 1, 1, 1),
('Performance 2', '1981-01-01', 2, 1, 1),
('Performance 3', '1981-01-01', 3, 1, 1),
('Performance 4', '1981-01-05', 2, 2, 1),
('Performance 5', '1981-01-08', 2, 3, 1),
('Performance 6', '1982-01-01', 1, 1, 2),
('Performance 7', '1982-01-01', 2, 1, 2),
('Performance 8', '1982-01-02', 3, 1, 2),
('Performance 9', '1982-01-05', 2, 2, 2),
('Performance 10', '1982-01-08', 2, 3, 2);

此前我通过Python处理该需求，但代码运行缓慢且涉及大量查询，想寻求可通过单条或少量PostgreSQL查询实现的方案。目前我已自行实现了基于CTE的查询语句，具体代码如下：

WITH all_dates AS (
    SELECT
        ARRAY_AGG(DISTINCT piece_id ORDER BY piece_id) AS piece_ids,
        season_id,
        venue_id,
        date as date
    FROM
        performances
    WHERE
        season_id IS NOT NULL AND
        venue_id IS NOT NULL AND
        piece_id IS NOT NULL AND
        date IS NOT NULL
    GROUP BY
        venue_id,
        season_id,
        date
)

SELECT DISTINCT
    season_id,
    venue_id,
    piece_ids,
    ARRAY_AGG(DISTINCT all_dates.date ORDER BY date) AS dates
FROM all_dates
GROUP BY season_id, piece_ids, venue_id

内容的提问来源于stack exchange，提问作者Julian