Как в Postgresql 9.3 объединить несколько строк отдельной таблицы в одну ?

Question

[decenter]

Добрый день!
Имеется разветвлённая база в POSTGRESQL 9.3 общим объемом около 10 GB (по 7К- 2M записей в каждой таблице). Встала задача объединение нескольких строк отдельной таблицы в одну. Попробую объяснить на абстрактном примере. Предположим что есть таблица films с несколькими атрибутами (id фильм и его название). Также есть таблица actors где хнарится список актиеров. Данные таблицы связаны еще через промежуточную (для обеспечения связи многие-ко-многим).
films

id	Title film
1	матрица
2	Джонни-мнемоник
3	Байкеры

actor_in_films

id_film	id_actor
1	1
1	2
2	1
3	2

actors

id	name
1	Киану Ривз
2	Лоуренс Фишбёрн

В таблице films создается дополнительная колонка acters в которой необходимо хранить сведения об актерах (имя актера и идентификатор). Предположим в формате «id-name» с разделителем в виде знака «|» . как альтернатива данные в JSON.

films

id	Title film	acters
1	матрица	1- Киану Ривз | 2- Лоуренс Фишбёрн
2	Джонни-мнемоник	1- Киану Ривз
3	Байкеры	2- Лоуренс Фишбёрн

Можно ли одним запросом в posgresql 9.3 выполнить такую манипуляцию?

Answer 1

[decenter]

Такая задача встала для формирования промежуточной таблицы на основе которой веб-приложение выводит необходимую информацию. Изначально выборка делалась из 4 связанных таблиц. но такой запрос выполняется несколько секунд, что в принципе для приложения через чур. тесты с промежуточной таблице дали результат в 0,8-0,9 секунд что становится более менее приемлемым. в дальнейшем планируется совмещение с nosql решением, но перевод данных и переписывание кода под совместное использование postgresql и nosql потребует значительных временных затрат. поэтому собственно и встала создание промежуточного решения.

изучение мануалов и метод тыка пока привел вот к такому варианту.

UPDATE films SET acters = (SELECT json_agg (ALL(actors.id, actors.name))
FROM actor_in_films  INNER JOIN  actors ON actors.id=actor_in_films.id_actor WHERE actor_in_films.id_film= films.id GROUP BY actor_in_films.id_film);

если кто может помочь с более оптимальным вариантом буду безмерно благодарен.

Кэширование вариант хороший, но вот проблема в том что данные ежедневно обновляются. (в среднем в день ок 100К-200К записей обновляются, и еще столько же добавляются. Кеширование было бы просто великолепно, есл бы к одним и тем же данным обращались по 5 и более раз (конкретной записи в таблице). Но увы более 2-3 раз к одной и той же записи обращаются очень редко. Основную нагрузку делает большое количество единичных обращений

Comments

[sim3x]

для коментариев есть коментарии