英文:
Join multiple table on array column
问题
我有许多要连接的表，其中一些具有作为数组的连接列，
当我尝试使用两个表时，它正常工作
selecttr.report_id,tr.sponsor_ids,array_agg(tsp.sponsor)--from public.tbl_report_page trpfrom public.tbl_report tr --on trp.report_id = tr.report_idleft join public.tbl_sponsor tsp on tsp.sponsor_id = any(tr.sponsor_ids)--left join public.tbl_tag tg on tg.tag_id = any(tr.tags)group by tr.report_id,tr.sponsor_ids--,trp.page_idhaving tr.report_id = 109
这个工作正常，
109 {1,2,3,4,5,6} "{sponsor_1,sponsor_2,sponsor_3,sponsor_4,sponsor_5,sponsor_6}"
但是当我取消注释
left join public.tbl_tag tg on tg.tag_id = any(tr.tags)
在赞助商中获取了许多值
109 {1,2,3,4,5,6} "{sponsor_1,sponsor_1,sponsor_1,sponsor_2,sponsor_2,sponsor_2,sponsor_3,sponsor_3,sponsor_3,sponsor_4,sponsor_4,sponsor_4,sponsor_5,sponsor_5,sponsor_5,sponsor_6,sponsor_6,sponsor_6}"
我必须添加更多包含ID的表作为数组。
英文:
I have many tables to join, some of them have joining columns as array,
When I tried with two tables it worked fine
selecttr.report_id,tr.sponsor_ids,array_agg(tsp.sponsor)--from public.tbl_report_page trpfrom public.tbl_report tr --on trp.report_id = tr.report_idleft join public.tbl_sponsor tsp on tsp.sponsor_id = any(tr.sponsor_ids)--left join public.tbl_tag tg on tg.tag_id = any(tr.tags)group by tr.report_id,tr.sponsor_ids--,trp.page_idhaving tr.report_id = 109
This works fine,
> 109 {1,2,3,4,5,6} "{sponsor_1,sponsor_2,sponsor_3,sponsor_4,sponsor_5,sponsor_6}"
but when I uncomment
left join public.tbl_tag tg on tg.tag_id = any(tr.tags)
getting lots of values in sponsors
109 {1,2,3,4,5,6} "{sponsor_1,sponsor_1,sponsor_1,sponsor_2,sponsor_2,sponsor_2,sponsor_3,sponsor_3,sponsor_3,sponsor_4,sponsor_4,sponsor_4,sponsor_5,sponsor_5,sponsor_5,sponsor_6,sponsor_6,sponsor_6}"
I have to add more tables which contains ids as array.
答案1
得分: 1
首先，通常最好将这些数组替换为关系设计中的标准化多对多关系。参见：
如果你目前只能使用当前的设计，请考虑连接操作如何扩大行数：
你的查询可能像这样工作：
SELECT tr.report_id, tr.sponsor_ids, sp.sponsors, sp.tagsFROM public.tbl_report trLEFT JOIN LATERAL (SELECT array_agg(DISTINCT tsp.sponsor) AS sponsors, array_agg(DISTINCT tg.tag) AS tagsFROM public.tbl_sponsor tspLEFT JOIN public.tbl_tag tg ON tg.tag_id = ANY (tr.tags)WHERE tsp.sponsor_id = ANY (tr.sponsor_ids)) sp ON trueWHERE tr.report_id = 109;
根据你的实际目标，可能还有更多的优化可能。
这不保留数组项的原始顺序。参见：
相关链接：
英文:
For starters, it's typically superior to replace those arrays with normalized many-to-many relationships in your relational design. See:
While stuck with your current design, consider how joins can multiply rows:
Your query probably works like this:
SELECT tr.report_id, tr.sponsor_ids, sp.sponsors, sp.tagsFROM public.tbl_report trLEFT JOIN LATERAL (SELECT array_agg(DISTINCT tsp.sponsor) AS sponsors, array_agg(DISTINCT tg.tag) AS tagsFROM public.tbl_sponsor tspLEFT JOIN public.tbl_tag tg ON tg.tag_id = ANY (tr.tags)WHERE tsp.sponsor_id = ANY (tr.sponsor_ids)) sp ON trueWHERE tr.report_id = 109;
Depending on your actual objective, more optimization may be possible.
This does not preserve original order of array items. See:
Related:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论