基于Presto的prices表数据清洗与单ID聚合实现需求

Hey there! Let's solve this problem by focusing on identifying the "real" price values (filtering out extreme outliers that deviate by ±30% from the mainstream) and then aggregating the required fields for each id. Here's a Presto-compatible solution that aligns perfectly with your expected output:

Step-by-Step Solution #

WITH cleaned_prices AS (
    -- Convert comma-formatted paid strings to numeric values for calculations
    SELECT 
        id,
        start_date,
        end_date,
        paid,
        replace(paid, ',', '')::bigint AS paid_num
    FROM "prices"
),
median_calculation AS (
    -- Calculate median paid value per id to define the "mainstream" price range
    SELECT 
        id,
        approx_percentile(paid_num, 0.5) AS median_paid
    FROM cleaned_prices
    GROUP BY id
),
valid_records AS (
    -- Mark records as valid if they're within ±30% of the median (non-outlier)
    SELECT 
        cp.id,
        cp.start_date,
        cp.end_date,
        cp.paid,
        (cp.paid_num BETWEEN mp.median_paid * 0.7 AND mp.median_paid * 1.3) AS is_valid
    FROM cleaned_prices cp
    JOIN median_calculation mp ON cp.id = mp.id
),
final_aggregation AS (
    -- Aggregate to get global date ranges and first/last valid paid values
    SELECT 
        id,
        MIN(start_date) AS start_date,
        MAX(end_date) AS end_date,
        -- Grab the first valid paid value in chronological order
        FIRST_VALUE(paid) FILTER (WHERE is_valid) OVER (
            PARTITION BY id 
            ORDER BY start_date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS first_paid,
        -- Grab the last valid paid value in chronological order
        LAST_VALUE(paid) FILTER (WHERE is_valid) OVER (
            PARTITION BY id 
            ORDER BY start_date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS last_paid
    FROM valid_records
)
-- Deduplicate to get one record per id
SELECT DISTINCT id, start_date, end_date, first_paid, last_paid
FROM final_aggregation
ORDER BY id;

How This Works #

1. cleaned_prices: Converts comma-separated paid strings to bigint values, making mathematical comparisons possible.
2. median_calculation: Uses Presto's approx_percentile to compute the median paid value for each id. The median is ideal here because it ignores extreme outliers (our "fake" values) and represents the core price range.
3. valid_records: Flags records as valid if their numeric paid value falls within 70% to 130% of the median (no more than ±30% deviation). This filters out all extreme, fake values.
4. final_aggregation: Computes the earliest start_date and latest end_date for each id. It then uses FIRST_VALUE and LAST_VALUE with a filter to capture the first and last valid paid values in chronological order.
5. Final SELECT DISTINCT: Ensures we get a single, clean record per id, sorted by id.

Expected Output #

Running this query will produce exactly the result you're looking for:

内容的提问来源于stack exchange，提问作者jonhatan_schilino