PostgreSQL: Intersection within a table

Question

I have a table in the form

Column A: String Column B: Integer

These are the only columns and they both form the PK.

Sample data:

Column A	Column B
'abc'	1
'abc'	2
'abc'	3
'def'	1
'def'	3
'ghi'	3

I need a view that gives me the intersection of column B for all values of A from the query.

I have already asked a few AIs, but without any meaningful results.

Expectation: select A, B from myView where A in ('abc','def','ghi') which returns B=3 since 3 is the intersection of the values of B for all values of A from the query.

The best result would be (to be able to continue joining)

Column A	Column B
'abc'	3
'def'	3
'ghi'	3

No extension should be necessary for this.

Thanks for your help and ideas :)

With intersection, do you mean "3" is the biggest value for all occurrences of that specific value from your first column? You could just use GROUPBY Column A and use MAX for Column B in that case, — Beowolve
– Beowolve, Commented May 29, 2024 at 5:48
what if your table get updated, and now has a new row: Column A: 'jkl' Coliumn B: 4 How will your output table look? — hassaan mustafa
– hassaan mustafa, Commented May 29, 2024 at 6:11
@Beowolve Not the highest value, but the intersection. If I select by A, 3 is the only value that occurs in all result rows for each value of A. — Andreas Beyer
– Andreas Beyer, Commented May 29, 2024 at 8:57
@hassaanmustafa It is unlikely that a new column will be added. But in this case, it does not have to be included in the view. — Andreas Beyer
– Andreas Beyer, Commented May 29, 2024 at 8:58
stackoverflow.com/q/7020264/1048572 might be relevant, though you'd first have to array_agg(b) … GROUP BY a. It could then compute the intersection of {1, 2, 3}, {2, 3} and {3} to {3}. — Bergi
– Bergi, Commented May 29, 2024 at 18:51

Beowolve · Accepted Answer · 2024-05-29 17:01:09Z

0

Not sure if I understood you correct, but I guess you want something like this:

SELECT colA, colB
FROM yourTable
WHERE colB IN (
    SELECT colB
    FROM yourTable
    GROUP BY colB
    HAVING COUNT(DISTINCT colA) = (SELECT COUNT(DISTINCT colA) FROM yourTable)
);

https://www.db-fiddle.com/f/24RniAr72kNddd995pysVb/0

answered May 29, 2024 at 17:01

Beowolve

5782 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andreas Beyer Over a year ago

Your fiddle works well, but with my real data I get no result db-fiddle.com/f/8SMyjqhCJi5GDtT457uLXk/0

pilchard Over a year ago

I would guess that there isn't a perfect intersection, based on your response above '... But in this case, it does not have to be included in the view. ' it seems you are actually looking for the maximum overlap — the most As with matching Bs.

Bergi · Accepted Answer · 2024-05-29 18:47:37Z

0

I think the most straighforward query to get the desired result would be

SELECT array_agg(a), b
FROM example
GROUP BY b
HAVING array_agg(a) @> ARRAY['abc', 'def', 'ghi'];

^{(online demo)}

however that returns one row with an array of all the a values for that intersected b, not multiple rows. Maybe you don't need those though, in particular if you already know the a values you're looking for? Otherwise you'd have to write

SELECT a, b
FROM example
WHERE b = (
  SELECT b
  FROM example
  GROUP BY b
  HAVING array_agg(a) @> ARRAY['abc', 'def', 'ghi']
);

^{(online demo)}

answered May 29, 2024 at 18:47

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

2 Comments

Andreas Beyer Over a year ago

That won't work for my application. I need this for a view that I can use to make any selection.

Bergi Over a year ago

select A, B from myView where A in ('abc','def','ghi') is not possible. You cannot do intersection by adding a where clause outside of the view.

pilchard · Accepted Answer · 2024-05-31 08:40:00Z

0

I think addresses what you are looking for which is the maximum overlap: As with the most matching Bs.

WITH overlap AS (
  SELECT b
  FROM example
  GROUP BY b
  ORDER BY COUNT(a) DESC, b ASC
  LIMIT 1 
)
SELECT
  a,
  b
FROM example
WHERE b = (SELECT b FROM overlap);

fiddle: https://www.db-fiddle.com/f/3LyhsXEv6xSSfb1JRVbjNV/4
with your sample data: https://www.db-fiddle.com/f/aee9RnDbbcs5wfKXJHwwVw/5

edited May 31, 2024 at 8:40

answered May 30, 2024 at 22:47

pilchard

13.1k5 gold badges14 silver badges27 bronze badges

Comments

ValNik · Accepted Answer · 2024-05-31 19:32:50Z

I think, it is not possible

select A, B from myView where A in ('abc','def','ghi')

Possible

select a,b from myView where ar @> ARRAY['abc', 'def', 'ghi']

See example

create view vt as 
(
select a,b,array_agg(an) ar 
from (
  select t1.a,t1.b,t2.a an
  from test t1
  left join test t2 on t2.b=t1.b 
  )data
group by a,b
);

select a,b from vt
where ar @> ARRAY['abc', 'def', 'ghi']
order by b, a

Output is

a	b
a-1	3
a-2	3
abc	3
def	3
ghi	3
a-1	4
abc	4
def	4
ghi	4

with test data as

CREATE TABLE test (a text, b int);
INSERT INTO test VALUES
  ('abc', 1),  ('abc', 2),  ('abc', 3),  ('abc', 4),
  ('def', 1),  ('def', 3),  ('def', 4),
  ('a-1', 2),  ('a-1', 3),  ('a-1', 4),
  ('a-2', 2),  ('a-2', 3),--  ('a-2', 4),
  ('ghi', 3),  ('ghi', 4);

Your sample data is quite poor. Maybe you need some like this: more strict query

select a,b from vt
where ar @> ARRAY['abc', 'def', 'ghi']
  and array_position(ARRAY['abc', 'def', 'ghi'],a)>0
order by b, a

Demo

With your real data fiddle

Collectives™ on Stack Overflow

PostgreSQL: Intersection within a table

4 Answers 4

2 Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related