Filter multiple rows of data based on one row's data

user_15107538 0
Viewed 49

I have flight segment data that I'm trying to filter out a bit, so my dataset is a more manageable size and hopefully make my life easier. Every row is a flight segment that I will later group together by acid, index, and date to make a single flight record. Example table:

acid index date segment us_air
xyz 123 2020-10-01 1 1
xyz 123 2020-10-01 2 0
xyz 123 2020-10-01 3 1
abc 456 2020-10-02 1 0
abc 456 2020-10-02 2 0
def 789 2020-10-03 1 1
def 789 2020-10-03 2 1

Rows 1, 2, and 3 with be one record, 4 and 5 another, and 6 and 7 another.

The issue: I need to filter out rows where us_air=0, unless it equals 1 in a different row with the same combination of acid, index, and date. But I don't want the rows grouped/aggregated by these three columns yet.

End result should look like this:

acid index date segment us_air
xyz 123 2020-10-01 1 1
xyz 123 2020-10-01 2 0
xyz 123 2020-10-01 3 1
def 789 2020-10-03 1 1
def 789 2020-10-03 2 1

This is the query I've been testing out:

CREATE TABLE table2 AS (
SELECT * 
FROM table1 
WHERE acid IN (SELECT acid 
              FROM table1 
              WHERE us_air=1) AND 
    index IN (SELECT index 
              FROM table1 
              WHERE us_air=1) AND
    date IN (SELECT date 
             FROM table1 
             WHERE us_air=1));

I appreciate the help!

postgresql sql
1 Answers

Use a window function to achieve this. Partitioning by (acid, index, date), every row in the check_us CTE will have a has_us column with the sum of the us_air values for that partition. The main query can exclude rows where has_us is 0.

with check_us as (
  select *, sum(us_air) over (partition by acid, index, date) as has_us
    from table1
)
select acid, index, date, segment, us_air
  from check_us
 where has_us > 0
 order by acid, date, segment;
user_138083190
Related