Postres中用后续非零值填充groupid列的零值

Got it, let's work through this. Looking at your expected output, it seems you want to forward-fill the 0 values in the groupid column—meaning replace each 0 with the closest non-zero value that comes before it in the sorted result (your description mentioned "the first non-zero value that appears later", but the example doesn't match that, so I'll align with the expected output you provided).

Here are two reliable ways to do this in PostgreSQL:

Method 1: Using Window Functions (PostgreSQL 11+) #

This is the most concise approach, leveraging LAST_VALUE with the IGNORE NULLS clause (available in PostgreSQL 11 and newer):

WITH base_data AS (
    -- First, get your original query results and convert 0s to NULL
    SELECT 
        a.assessmentid,
        CASE WHEN b.groupid = 0 THEN NULL ELSE b.groupid END AS non_zero_groupid
    FROM wo_assessment a 
    LEFT JOIN wo_group_info b ON a.assessmentid = b.assessmentid 
    WHERE a.workorderid = 2 
    ORDER BY a.assessmentid
)
SELECT 
    assessmentid,
    -- Grab the last non-NULL value from the start of the dataset up to the current row
    LAST_VALUE(non_zero_groupid) OVER (
        ORDER BY assessmentid
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        IGNORE NULLS
    ) AS groupid
FROM base_data;

How it works: #

1. We first wrap your original query in a CTE and turn all groupid = 0 values into NULL—this lets the window function ignore them.
2. The LAST_VALUE window function scans from the first row up to the current row, skipping NULLs, and returns the most recent non-NULL value (which is the last valid groupid before the current row).

Method 2: Compatible with Older PostgreSQL Versions #

If you're using a version before PostgreSQL 11 (where IGNORE NULLS isn't supported), use this grouping approach:

WITH grouped_data AS (
    -- Assign a group number that increments every time we hit a non-zero groupid
    SELECT 
        a.assessmentid,
        b.groupid,
        SUM(CASE WHEN b.groupid != 0 THEN 1 ELSE 0 END) OVER (ORDER BY a.assessmentid) AS group_segment
    FROM wo_assessment a 
    LEFT JOIN wo_group_info b ON a.assessmentid = b.assessmentid 
    WHERE a.workorderid = 2 
    ORDER BY a.assessmentid
),
segment_values AS (
    -- Get the non-zero groupid for each segment
    SELECT 
        group_segment,
        MAX(groupid) AS filled_groupid
    FROM grouped_data
    WHERE groupid != 0
    GROUP BY group_segment
)
-- Join back to get the filled values
SELECT 
    gd.assessmentid,
    sv.filled_groupid AS groupid
FROM grouped_data gd
JOIN segment_values sv ON gd.group_segment = sv.group_segment
ORDER BY gd.assessmentid;

How it works: #

1. The first CTE creates a group_segment number: every time we encounter a non-zero groupid, we add 1 to the segment count. This groups each 0 value with the last preceding non-zero value.
2. The second CTE extracts the valid non-zero groupid for each segment.
3. Finally, we join these back to replace all 0s in the original dataset with their segment's valid groupid.

Both methods will produce exactly the expected output you shared.

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