Teradata同列补全同ID非零数据咨询（禁用子查询/MAX）

Got it, let's figure out how to fix those zero values without messing with your already complex query.

First, let's recap your scenario: you've got a result table that counts names by type for each id and seq_num, but when an id has multiple types, some rows end up with a 0 for name_count. You want to replace those 0s with the non-zero name_count from the same id and seq_num pair—and you can't add subqueries or use MAX() with a GROUP BY because your original query is too tangled.

The Solution: Window Functions #

Window functions are perfect here because they let you calculate aggregate values (like max) across a group of rows without needing a GROUP BY clause on the outer query. Here's how to implement it:

Assuming your original complex query outputs columns id, type, seq_num, and name_count, wrap it in a simple SELECT that uses a windowed MAX() to fill in the zeros:

SELECT
    id,
    type,
    seq_num,
    -- Replace 0 with the max name_count from the same id + seq_num group
    CASE
        WHEN name_count = 0 THEN MAX(name_count) OVER (PARTITION BY id, seq_num)
        ELSE name_count
    END AS name_count
FROM
    (-- Paste your existing complex query here) AS original_results;

How This Works #

• MAX(name_count) OVER (PARTITION BY id, seq_num): This calculates the highest name_count value for every row in the same id and seq_num group. Since your non-zero value is the only non-zero in the group, this will grab exactly that value.
• The CASE statement checks if the current row's name_count is 0—if yes, it replaces it with the group's max value; if not, it keeps the original count.

This approach doesn't require modifying your original query at all—you just layer this simple transformation on top. No extra subqueries or GROUP BY clauses cluttering things up.

Example #

If your original results look like this:

The query above will output:

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