Data Analysis: Excluding Outliers in PostgreSQL

Outliers can significantly skew the results of your data analysis. In this article, we’ll explore how to exclude outliers from your dataset using PostgreSQL. We will use the Interquartile Range (IQR) method, a common technique for detecting outliers.

Step 1: Create the Table and Insert Data

We’ll start by creating a table named data_points and inserting some sample data. This will provide us with a dataset to work with.

CREATE TABLE data_points (
    id SERIAL PRIMARY KEY,
    value NUMERIC
);

INSERT INTO data_points (value) VALUES 
    (-20), (12), (13), (13), (14), (14), (14), (15), (16), (16),
    (19), (21), (22), (23), (24), (30), (32), (35), (100), (105);

Step 2: Calculate Quartiles, IQR, and Outlier Boundaries

Next, we calculate the 1st quartile (Q1), the 3rd quartile (Q3), and the IQR. Using these, we determine the lower and upper bounds for identifying outliers.

WITH quartiles AS (
    SELECT 
        percentile_cont(0.25) WITHIN GROUP (ORDER BY value) AS q1,
        percentile_cont(0.75) WITHIN GROUP (ORDER BY value) AS q3
    FROM data_points
),
iqr_values AS (
    SELECT q1, q3, q3 - q1 AS iqr
    FROM quartiles
),
bounds AS (
    SELECT q1 - 1.5 * iqr AS lower_bound,
        q3 + 1.5 * iqr AS upper_bound
    FROM iqr_values
)
SELECT q1, q3, iqr, lower_bound, upper_bound
FROM bounds;

Step 3: Exclude Outliers

Finally, we filter the dataset to exclude values outside the calculated bounds.

WITH quartiles AS (
    SELECT 
        percentile_cont(0.25) WITHIN GROUP (ORDER BY value) AS q1,
        percentile_cont(0.75) WITHIN GROUP (ORDER BY value) AS q3
    FROM data_points
),
iqr_values AS (
    SELECT q1, q3, q3 - q1 AS iqr
    FROM quartiles
),
bounds AS (
    SELECT q1 - 1.5 * iqr AS lower_bound, q3 + 1.5 * iqr AS upper_bound
    FROM iqr_values
)
SELECT 
    id, value
FROM data_points, bounds
WHERE value >= lower_bound AND value <= upper_bound;

By following these steps, you can effectively exclude outliers from your dataset in PostgreSQL. The IQR method is a simple yet powerful technique to ensure your data analysis remains robust and reliable.

If you found this article helpful, please give it +10 claps 👏 and leave a comment below! Your feedback and engagement help me create more content that benefits the community. Feel free to share your thoughts and any questions you might have. Happy coding!

TheCodeCraft