Postgres sql для выполнения соединения
Просто общий вопрос: может ли кто-нибудь помочь, как выполнить левое соединение для данных объединения. У меня есть запрос, в котором я объединяю данные (используя таблицу1 и таблицу2) на основе нескольких условий. Теперь мне нужно выполнить левое внешнее соединение с третьей таблицей (таблица5), поэтому я ищу помощь, как это сделать. Просто образец запроса подойдет.
Я создал сценарий на скрипке, где я выполнил объединение на основе нескольких условий, используя таблицу 1 и таблицу 2.https://dbfiddle.uk/WN6uC5MH Теперь мне нужно использовать выходные данные объединения и выполнить левое соединение для третьей таблицы, то есть таблицы 5. Поэтому мне нужно выполнить левое соединение в поле 6 таблицы 5 с выходным полем 1 данных объединения, если оно совпадает, а затем вставить строки в таблицу 5 со всеми значениями. останется прежним, за исключением поля 9, которое мы получим из выходных данных объединения (поле3):
Вывод будет выглядеть так:
'j','abc','def','xyz_inc'
'j','abc','def','5_abc'
'j','abc','def','6_abc'
@JNeville-Спасибо, что поделились, на самом деле я знаю этот синтаксис и пробовал, но в конце получаю ошибку, когда использую псевдоним после закрытия скобки. Можете ли вы помочь со скрипкой (dbfiddle.uk/WN6uC5MH), как это сделать, используя мой запрос?
Поскольку вы уже используете CTE, я реорганизовал ваш запрос, а затем вместо того, чтобы полностью вставлять ваше объединение в подзапрос, я просто добавил новый CTE, который ссылается на два исходных. Затем в конце всех определений CTE мы можем выполнить соединение: dbfiddle.uk/U-P4vSpW
Ответы 1
- Вы можете
translate()один раз вместо того, чтобы звонитьreplace()дважды. Вы также можете объединить свои вызовыtranslate()вместо того, чтобы вкладывать их. - Можно
regexp_split_to_table()прямо вместо упаковкиunnest(string_to_array()). - Как отметил @JNevill, все, что вам нужно сделать, это вложить имеющийся у вас запрос либо в CTE, либо в подзапрос, а затем присоединиться к нему.
with your_big_union as (
select field1,field2,string_agg(distinct fld,',') field3
from(
select *
from(
select field1,field2,fld
from table1 t1
left join (
select * from table2
cross join regexp_split_to_table(trim(translate(field4,'{}','')),',') fld
)t2
on concat(t1.field2,';') similar to concat('%',t2.field3,'[();]%')
)x
)b
where field2 like '%fn@_%' escape '@' and fld is not null
group by field1,field2
union
(with cte as (
select field1,field2
,''''
||translate( btrim(field2)
,E'\n"'''
,'' )
||'''' as edited_field2
from table1)
,cte2 as (
select *,t.spotted_table as spotted_target,s.spotted_table as spotted_source
from cte
left join regexp_matches(
edited_field2
,'(?:UPDATE|INTO)(?:\s+ONLY)?\s+([[:alpha:]_]+[\.\w]*|"[^"]+"(?:\."[^"]+")?)'
,'gi'
) with ordinality as target_matches(hits,n1) on true
left join unnest(target_matches.hits) with ordinality as t(spotted_table,n2) on true
left join regexp_matches(
edited_field2
,'(?:FROM|JOIN|USING|TABLE)(?:\s+ONLY)?\s+([[:alpha:]_]+[\.\w]*|"[^"]+"(?:\."[^"]+")?)'
,'gi'
) with ordinality as source_matches(hits,n1) on true
left join unnest(source_matches.hits) with ordinality as s(spotted_table,n2) on true)
select field1
,string_agg(distinct spotted_target,',') as spotted_targets
-- ,string_agg(distinct spotted_source,',') as spotted_sources
,edited_field2
from cte2 where edited_field2 not like '%fn@_%' escape '@' and spotted_target is not null
and coalesce(split_part(spotted_target,'.',1) !~* '_inc"?$',true)
and coalesce(split_part(spotted_source,'.',1) !~* '_inc"?$',true)
group by field1,edited_field2
order by field1))
select *
from table5 left join your_big_union
on lower(your_big_union.field1) = table5.field6;
- Если
table5.field6соединяется сfield1, совпадений не будет, потому что вы написали'J'в верхнем регистре вfield, аfield6удерживает строчную. Я не понимаю, почему вы ожидаете 3 результата, если у вас есть только один'j'с обеих сторон соединения.
Пример: dbfiddle.uk/sj8S5lK2 поэтому здесь мне не нужны строки таблицы «INC». Как это отфильтровать?
@Ram Вы можете отфильтровать их точно так же, как и другой запрос: !~* '_inc"?$' и лучше всего сделать это перед агрегированием: демо. Если вы стремитесь агрегировать, а затем отключить вложение, возможно, будет проще array_agg(), а затем unnest() напрямую — меньше накладных расходов, более надежно и не заставляет вас дезинфицировать вещи на случай, если проскользнет дополнительный ,.
Вопрос. Есть ли способ сравнить поле 3 с полем 9 после объединения, где вы в конце выбираете. dbfiddle.uk/MXhCQg5R. в основном в приведенном ниже запросе выбора: выберите * из таблицы 5 слева присоединиться к your_big_union на нижнем уровне (your_big_union.field1) = table5.field6; Я попытался сделать field3!=unnest(string_to_array(field9,',')) но он выдал ОШИБКУ: функции, возвращающие набор, не разрешены в условиях JOIN.
Если бы вы использовали обычный array_agg(), это было бы тривиально field3<>all(field9), но вы все равно можете сделать field3!=all(string_to_array(field9,',')): демо. Я думаю, что в демо-версии поле3 содержит несколько значений, а поле9 — одно.
@Zegarek-Большое спасибо. У меня есть пара вопросов, в некоторых местах у нас есть комментарии, которые также начинаются с ('--','/*'). Есть ли способ пропустить источник и цели для комментариев, поскольку это не так? необходимый. Я знаю, что синтаксический анализатор sql подойдет для этого лучше всего, но мне просто интересно найти обходной путь. Кроме того, в настоящее время в обнаруженных целях мы использовали регулярное выражение для INSERT и UPDATE... Есть ли способ включить регулярное выражение (Удалить из или Удалить * from). Я пробовал сохранить INSERT|UPDATE|DELETE таким образом, но он не работает должным образом. Я думаю, может быть, это связано с тем, что предложение FROM используется в источниках Spotted?
@Ram Второе регулярное выражение ищет элементы FROM, поэтому оно должно найти идентификатор после DELETE FROM. Проблема в том, что он также выявляет источники UPDATE и INSERT, а не только их цели. Удаление/игнорирование конца строки --... и встроенных/многострочных комментариев /*...*/ можно обрабатывать как отдельное регулярное выражение. Я думаю, что время, которое вы уже потратили на изучение синтаксического анализа SQL на основе регулярных выражений, возможно, уже превысило то, что вам нужно было бы потратить на интеграцию SQLParse.
@ Зегарек-верно, я посмотрю на это. Но я ценю твою помощь со сценарием.
@Zegarek-for string_agg(distinct fld,',') ... Я получаю повторяющиеся значения. Пример: если у меня есть test3 дважды, я получаю test3, test3 ... Есть ли способ сохранить 1 значение вместо дубликата .
@Ram distinct не может пропустить неразличимые значения. Вы либо добавляете дубликаты в другое место, либо упускаете из виду разницу в значениях, которые, по вашему мнению, являются повторяющимися. Проверьте конечные/начальные пробелы, разницу в регистре перед преобразованием в нижний регистр или проверьте, выглядят ли символы одинаково, но имеют ли они разные коды ascii/unicode: посмотрите эту демонстрацию.
ох, извините, я имею в виду, что это строковые значения, такие как test,test, но они все равно получаются с разными значениями.
Если фактическое значение строки равно test,test — два дубликата с разделителем/разделителем внутри, и именно так оно и поступает, то вы можете разделить, дедуплицировать и повторно агрегировать: demo. Еще раз: array_agg() всегда предпочтительнее.
@Zegarek-Извиняюсь, я неправильно понял ваш последний комментарий... Вы молодцы, раньше были пробелы... Я использовал обрезку, чтобы удалить их, и теперь получаю ожидаемые значения :)
Вы просто помещаете весь этот SQL в подзапрос и выполняете для него левое соединение.
SELECT * FROM (<big union SELECT here>) as sub LEFT OUTER JOIN table3 ON sub.field1 = table3.field6;