在Amazon Redshift中能否基于双列设置条件？如何实现特定分组规则的数据提取？

Short Answer #

Absolutely! Amazon Redshift fully supports setting query conditions based on multiple columns—this is basic SQL functionality, and you can combine multi-column rules using AND/OR, window functions, or grouping logic without any issues. Plus, your desired group-based row extraction logic is totally achievable with Redshift's robust support for window functions.

Detailed Solution for Your Rule #

Let’s clarify your requirement first to make sure we’re aligned:

• For each Group, prioritize extracting the row where Record_Flag = 0 and Row_Flag is the smallest in the group
• If the row with the overall smallest Row_Flag in the group has Record_Flag ≠ 0, skip it and instead take the row with the smallest Row_Flag among all Record_Flag = 0 rows in the group

Here are two tailored approaches:

Approach 1: Simplified (Filter Valid Rows First)

This works if your core goal is to always select the smallest Row_Flag from only Record_Flag = 0 rows (which aligns with skipping non-zero Record_Flag rows entirely):

WITH ranked_valid_rows AS (
    SELECT
        *,
        -- Rank rows within each Group, sorted by Row_Flag ascending (only for Record_Flag=0)
        ROW_NUMBER() OVER (
            PARTITION BY "Group"
            ORDER BY Row_Flag ASC
        ) AS row_rank
    FROM your_table
    WHERE Record_Flag = 0 -- Filter out invalid rows upfront
)
SELECT *
FROM ranked_valid_rows
WHERE row_rank = 1; -- Pick the smallest Row_Flag in each Group

Approach 2: Exact Match to Your Rule Check

If you need to first verify if the overall smallest Row_Flag row is valid (Record_Flag=0), and only fall back to valid rows if it’s not, use this version:

WITH all_group_rows AS (
    SELECT
        *,
        -- Rank all rows in the group by Row_Flag (regardless of Record_Flag)
        ROW_NUMBER() OVER (PARTITION BY "Group" ORDER BY Row_Flag ASC) AS overall_rank,
        -- Rank only valid (Record_Flag=0) rows in the group by Row_Flag
        ROW_NUMBER() OVER (
            PARTITION BY "Group"
            ORDER BY CASE WHEN Record_Flag = 0 THEN Row_Flag ELSE NULL END ASC
        ) AS valid_row_rank
    FROM your_table
),
group_validation AS (
    SELECT
        "Group",
        -- Check if the smallest Row_Flag row in the group is valid
        MAX(CASE WHEN overall_rank = 1 THEN Record_Flag END) AS top_row_flag
    FROM all_group_rows
    GROUP BY "Group"
)
SELECT agr.*
FROM all_group_rows agr
JOIN group_validation gv ON agr."Group" = gv."Group"
WHERE
    -- Take the overall smallest Row_Flag if it's valid
    (gv.top_row_flag = 0 AND agr.overall_rank = 1)
    -- Otherwise take the smallest valid Row_Flag in the group
    OR (gv.top_row_flag != 0 AND agr.valid_row_rank = 1);

Key Notes #

• PARTITION BY "Group" ensures we only rank rows within each individual group
• ROW_NUMBER() guarantees a single row per group (use RANK() instead if you want to keep all tied rows with the same smallest Row_Flag)
• Redshift handles these window functions efficiently even on large datasets, especially if you’ve set appropriate distribution and sort keys for your table

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