Redshift中按类别用前序有效值填充Null值的替代方案咨询

Got it, let's tackle this problem since Redshift doesn't support the RANGE window frame clause yet. We need a reliable way to carry forward the last non-null score for each name group, ordered by date.

Core Approach #

We'll create "group IDs" that increment every time we hit a non-null score for a given name. All null rows after that non-null score will share the same group ID, allowing us to propagate the last valid score to those nulls.

Full Working Example #

First, let's use sample data that matches your scenario:

WITH sample_data AS (
    SELECT 'Jane' AS name, '2024-01-01' AS date, 85 AS score UNION ALL
    SELECT 'Jane' AS name, '2024-01-02' AS date, NULL AS score UNION ALL
    SELECT 'Jane' AS name, '2024-01-03' AS date, NULL AS score UNION ALL
    SELECT 'Jane' AS name, '2024-01-04' AS date, 90 AS score UNION ALL
    SELECT 'Jon' AS name, '2024-01-01' AS date, 78 AS score UNION ALL
    SELECT 'Jon' AS name, '2024-01-02' AS date, NULL AS score UNION ALL
    SELECT 'Jon' AS name, '2024-01-03' AS date, 82 AS score
),
-- Step 1: Assign group IDs to cluster rows around the last non-null score
grouped_records AS (
    SELECT 
        name,
        date,
        score,
        -- Increment group ID each time we find a non-null score for the name
        SUM(CASE WHEN score IS NOT NULL THEN 1 ELSE 0 END) OVER (
            PARTITION BY name 
            ORDER BY date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS score_group
    FROM sample_data
)
-- Step 2: Fill nulls with the valid score from their group
SELECT 
    name,
    date,
    MAX(score) OVER (PARTITION BY name, score_group) AS filled_score
FROM grouped_records
ORDER BY name, date;

Breakdown of the Logic #

1. Group ID Creation:

   • The SUM(CASE...) window function runs separately for each name, ordered by date. Every time it encounters a non-null score, it adds 1 to the group ID. Null rows that come after will keep the same group ID until the next non-null score for that name.

2. Filling Null Values:

   • Using MAX(score) over each name + score_group partition works because each group has exactly one non-null score (the most recent valid one before the nulls). The max function picks that value and applies it to all rows in the group, replacing nulls.

Multi-Category Compatibility #

Since we use PARTITION BY name in both window functions, each name's data is handled independently. Jane's scores won't interfere with Jon's, which is exactly what you need for multi-category scenarios.

This method works even if your dates have gaps—since it's based on the ordered rows within each name group, not strict date ranges. Redshift fully supports the ROWS BETWEEN clause used here, so no compatibility issues.

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