Select only those values that are not contained by another line entirely-CodePudding

I have a table like

user_id	thing_id	start	end
1	1	2022-01-01	2022-01-31
1	2	2022-01-05	2022-01-10
1	3	2022-02-01	2022-02-05
2	4	2022-01-01	2022-01-01
2	5	2022-01-02	2022-01-04

I want to select all things but skip those that are contained entirely in another thing by the same user. So the result should be

thing_id
1
3
4
5

I assume I need to play around with window functions and conditions, but am at a loss how to efficiently query this.

CodePudding user response：

You can use NOT EXISTS.

For your sample data it would be as simple as:

SELECT t1.*
FROM tablename t1
WHERE NOT EXISTS (
  SELECT *
  FROM tablename t2
  WHERE t2.user_id = t1.user_id
    AND t2.thing_id <> t1.thing_id
    AND t1.start > t2.start AND t1.end < t2.end
);

See the demo.

Depending on your requirement, you may change the date conditions to:

AND t1.start >= t2.start AND t1.end =< t2.end

or:

AND ((t1.start >= t2.start AND t1.end < t2.end) OR (t1.start > t2.start AND t1.end <= t2.end))