我有一套(舞蹈)表演(https://dbfiddle.uk/jMl5dgRK)，每个表演都有一个相关的作品，一个地点，一个季节和一个日期.
大多数作品在一个赛季中都演出了好几次.在某些情况下，几场演出在同一天和同一地点举行(例如，在戏剧节上或作为双人/三人/.比尔).
我想为在一个特定的场地一起演出的所有作品安排一个团体(表演"系列")，大概是这样:
| Venue ID | Season ID | Piece IDs | Dates |
|---|---|---|---|
| 1 | 1 | 1, 2, 3 | 1980-01-01, 1980-05-01 |
| 1 | 1 | 2, 3, 4 | 1980-02-01, 1980-02-02 |
用于测试的表格和一些数据:
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语言做繁重的工作，但代码相当慢，而且涉及很多查询.我一直在想，你们中有没有人有 idea 将表演结合到一个(或几个)问题中？
到目前为止，我在波斯格雷斯还没有成功地做到这一点.
Update:个
这就是我最终想到的:
- 查找所有地点/季节/片断/日期组合
- 将相同场地/季节/片断的行合在一起
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