基于DATE与PRICE条件的TAB1表数据筛选需求（用PARTITION BY/RANK）

需求说明 #

处理数据表TAB1（主键列NBR），需按以下规则筛选记录：

• 仅针对同一NBR存在两条记录的场景：
  1. 若其中一条PRICE值为0，需判断该分组最大DATE对应的PRICE：
     • 最大DATE的PRICE为0 → 保留该NBR的两条记录
     • 最大DATE的PRICE不为0 → 忽略该NBR的两条记录
  2. 若两条记录PRICE均不为0 → 保留该NBR的两条记录
• 要求使用PARTITION BY/RANK函数实现，避免聚合函数（因行中包含更多列）

原表数据（TAB1） #

期望输出 #

解决方案SQL #

WITH ranked_data AS (
    SELECT 
        *,
        -- 按NBR分组，DATE降序排名，最大DATE排第1
        RANK() OVER (PARTITION BY NBR ORDER BY DATE DESC) AS date_rank,
        -- 标记分组内是否存在PRICE=0的记录
        MAX(CASE WHEN PRICE = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY NBR) AS has_zero_price,
        -- 获取分组内最大DATE对应的PRICE值
        FIRST_VALUE(PRICE) OVER (PARTITION BY NBR ORDER BY DATE DESC) AS max_date_price,
        -- 获取分组内记录总数
        COUNT(*) OVER (PARTITION BY NBR) AS record_count
    FROM TAB1
)
SELECT NBR, DATE, PRICE
FROM ranked_data
WHERE 
    -- 只处理分组内有两条记录的情况
    record_count = 2
    AND (
        -- 分组内无PRICE=0的记录，直接保留
        has_zero_price = 0
        -- 分组内有PRICE=0的记录，且最大DATE对应的PRICE为0
        OR (has_zero_price = 1 AND max_date_price = 0)
    );

逻辑说明 #

1. CTE部分：
   • date_rank：按NBR分组、DATE降序排名，用于定位最新记录
   • has_zero_price：标记当前NBR分组中是否存在PRICE=0的记录
   • max_date_price：通过FIRST_VALUE获取分组内最大DATE对应的PRICE值
   • record_count：统计每个NBR分组的记录总数，确保只处理两条记录的场景
2. 筛选条件：
   • 先限定仅处理record_count=2的分组
   • 满足以下任一条件则保留记录：分组内无0值价格；或有0值且最新记录价格为0

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