Скачать или смотреть Filtering SQL Data: How to Exclude Rows with Specific Conditions in PostgreSQL

Discover how to efficiently filter SQL data in PostgreSQL by excluding rows based on specific conditions using window functions.
---
This video is based on the question https://stackoverflow.com/q/72974180/ asked by the user 'mlf' ( https://stackoverflow.com/u/15107538/ ) and on the answer https://stackoverflow.com/a/72974849/ provided by the user 'Mike Organek' ( https://stackoverflow.com/u/13808319/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Filter multiple rows of data based on one row's data

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Filtering SQL Data: How to Exclude Rows with Specific Conditions in PostgreSQL

When working with datasets, especially those containing flight segments, filtering your data can streamline analysis and make it more manageable. For those dealing with complex scenarios, like filtering multiple rows based on a single row's data, it can become quite challenging. In this guide, we’ll explore how to tackle such a problem in PostgreSQL using window functions.

The Problem

Imagine you have a dataset consisting of flight segments — each row corresponding to a part of a flight. You want to filter out rows where the us_air column equals 0, except in cases where there is at least one row with the same acid, index, and date that has us_air equal to 1.

Example Dataset

Here’s a brief look at the structure of your dataset:

acidindexdatesegmentus_airxyz1232020-10-0111xyz1232020-10-0120xyz1232020-10-0131abc4562020-10-0210abc4562020-10-0220def7892020-10-0311def7892020-10-0321From the given dataset, your desired output would look something like this:

acidindexdatesegmentus_airxyz1232020-10-0111xyz1232020-10-0120xyz1232020-10-0131def7892020-10-0311def7892020-10-0321The Solution

To achieve this filtering, you can utilize PostgreSQL's window functions. This approach will allow you to create a new column that sums the us_air values for each partition defined by acid, index, and date, and then filter based on that. Below is a step-by-step breakdown of the SQL query you should use.

Step 1: Create a Common Table Expression (CTE)

Using a CTE allows you to first calculate the summed us_air values for each combination of acid, index, and date.

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Filter the Results

Now that we've marked each row with a has_us column that reflects the summed value, we can easily filter out rows where has_us is 0.

[[See Video to Reveal this Text or Code Snippet]]

Complete SQL Query

Putting it all together, your SQL query would look like this:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

Filtering SQL data based on multiple row conditions can seem daunting at first, but with the use of window functions, PostgreSQL provides an elegant solution. By applying a common table expression (CTE) and leveraging partitioning, you can mark and filter the data efficiently, achieving the desired result without aggregating the rows beforehand.

Now, you can easily manage your flight segment data while filtering out rows that do not meet your criteria! Happy querying!