Intereting Posts
SQL получает строки как столбцы Данные запроса из XML Я пытаюсь получить данные из базы данных, где date = jdatechooser: Количество уникальных значений в диапазоне дат проката для R оракул – какие заявления нужно совершить? Присвоить CSS-класс HTML-тегам, сгенерированным с помощью SQL 'FOR XML' Можно ли получить объект, который разбит на три таблицы только с одним запросом? Почему левое внешнее соединение? Объединение нескольких строк в один столбец с использованием функции в Oracle с 3 связанными таблицами SQL – используйте ссылку CTE на другой CTE SQL: Получить записи, которые удовлетворяют условиям, поступающим из нескольких записей Как распространять среднее значение между двумя интервалами в оракуле Выполнение счета вместе с четким, чтобы проверить количество раз, когда сообщение было прочитано Заказ SQL Не работает должным образом Запросы, которые неявные соединения SQL не могут сделать?

Left Join Боковые и массивные агрегаты

Я использую Postgres 9.3.

У меня есть две таблицы T1 и T2 и отношение n:m T1_T2_rel между ними. Теперь я хотел бы создать представление, которое в дополнение к столбцам T1 предоставляет столбец, который для каждой записи в T1 содержит массив с идентификаторами первичного ключа всех связанных записей T2. Если в T2 нет соответствующих записей, соответствующие поля этого столбца должны содержать нулевые значения.

Абстрагированная версия моей схемы будет выглядеть так:

 CREATE TABLE T1 ( t1_id serial primary key, t1_data int ); CREATE TABLE T2 ( t2_id serial primary key ); CREATE TABLE T1_T2_rel ( t1_id int references T1( t1_id ) , t2_id int references T2( t2_id ) ); 

Соответствующие выборочные данные могут быть сгенерированы следующим образом:

 INSERT INTO T1 (t1_data) SELECT cast(random()*100 as int) FROM generate_series(0,9) c(i); INSERT INTO T2 (t2_id) SELECT nextval('T2_t2_id_seq') FROM generate_series(0,99); INSERT INTO T1_T2_rel SELECT cast(random()*10 as int) % 10 + 1 as t1_id , cast(random()*99+1 as int) as t2_id FROM generate_series(0,99); 

До сих пор у меня появился следующий запрос:

 SELECT T1.t1_id, T1.t1_data, agg FROM T1 LEFT JOIN LATERAL ( SELECT t1_id, array_agg(t2_id) as agg FROM T1_T2_rel WHERE t1_id=T1.t1_id GROUP BY t1_id ) as temp ON temp.t1_id=T1.t1_id; 

Это работает. Однако можно ли это упростить?

Соответствующую скрипку можно найти здесь: sql-скрипка . К сожалению, sql-скрипт не поддерживает Postgres 9.3 (пока), который необходим для боковых соединений.

[ Обновление ] Как уже указывалось, достаточно простого left join с использованием подзапроса в принципе. Однако, если я сравниваю планы запросов, Postgres прибегает к последовательным сканированиям в агрегированных таблицах при использовании left join тогда как сканирование индекса используется в случае left join lateral .

Поскольку @Denis уже прокомментировал: нет необходимости в LATERAL . Кроме того, ваш подзапрос выбрал неправильный столбец. Это работает:

 SELECT t1.t1_id, t1.t1_data, t2_ids FROM t1 LEFT JOIN ( SELECT t1_id, array_agg(t2_id) AS t2_ids FROM t1_t2_rel GROUP BY 1 ) sub USING (t1_id); 

-SQL скрипка.

Производительность и тестирование

Что касается последующего последовательного сканирования, вы указываете: если вы запрашиваете всю таблицу, последовательное сканирование часто происходит быстрее . Зависит от используемой версии, вашего оборудования, ваших настроек и статистики мощностей и распространения ваших данных. Поэкспериментируйте с выборочными WHERE например WHERE t1.t1_id < 1000 или WHERE t1.t1_id = 1000 и объединитесь с настройками планировщика, чтобы узнать о вариантах:

 SET enable_seqscan = off; SET enable_indexscan = off; 

Для сброса:

 RESET enable_seqscan; RESET enable_indexscan; 

Имейте в виду только на своей местной сессии! Этот ответ на dba.SE содержит больше инструкций.
Конечно, ваша настройка тоже может быть выключена:

  • Храните PostgreSQL от выбора плохого плана запроса