表记录分组:使用SQL row_number取产品最新配方的问题解决
问题背景
本次需求为从配方明细表ppm_details中,获取指定产品最新生效版本对应的全量配方原料条目。
字段说明:
- 产品标识:
pmd_ppm_pr_code - 原料标识:
pmd_pi_code - 配方生效日期:
pmd_ppm_date_effective
本次查询固定通过WHERE子句缩小范围,筛选pmd_ppm_sio_reference = 1801且产品代码为A24052的记录,排除无关干扰数据。
原逻辑SQL(存在缺陷)
select pmd_ppm_sio_reference ,pmd_ppm_pr_code ,pmd_ppm_date_effective ,pmd_percentage ,pmd_pi_code ,row_number() over(partition by pmd_ppm_pr_code, pmd_pi_code order by pmd_ppm_pr_code, pmd_pi_code, trunc(pmd_ppm_date_effective) desc) as "row" from ppm_details where pmd_ppm_sio_reference = 1801 and pmd_ppm_pr_code = 'A24052'
现存问题
原逻辑采用「产品+原料」作为分区维度计算行号,本质是为每个原料单独匹配历史上最近一次的生效记录,存在两类偏差:
- 若产品最新版配方已经移除了某款旧原料,该原料历史上最后一次被使用的记录依然会被标记为
row=1返回,不属于目标最新配方的内容 - 曾尝试将生效日期加入分区条件,但会导致所有行的
row值均为1,无法实现筛选需求
问题根因
窗口函数的分区粒度错配:我们需要的是单产品维度下的全局最大生效日期,匹配该日期下的所有原料条目;而原逻辑按「产品+原料」分区,是为每个原料单独找历史上最近一次的记录,天然无法识别哪些原料已经在最新版本的配方中被移除。
修正方案
核心思路是先锁定查询范围内该产品的全局最新生效日期,再提取该日期下的所有配方条目,两种实现方式均可:
方案1:子查询定位最新日期
直接通过聚合函数算出目标产品的最大生效日期,精准筛选对应日期的全量记录:
SELECT pmd_ppm_sio_reference, pmd_ppm_pr_code, pmd_ppm_date_effective, pmd_percentage, pmd_pi_code FROM ppm_details WHERE pmd_ppm_sio_reference = 1801 AND pmd_ppm_pr_code = 'A24052' -- 匹配该产品的全局最大生效日期 AND trunc(pmd_ppm_date_effective) = ( SELECT MAX(trunc(pmd_ppm_date_effective)) FROM ppm_details WHERE pmd_ppm_sio_reference = 1801 AND pmd_ppm_pr_code = 'A24052' )
方案2:单表窗口函数实现(无需关联子查询)
按产品维度做分区,计算当前查询范围内每个产品的全局最大生效日期,再筛选日期匹配的行:
SELECT pmd_ppm_sio_reference, pmd_ppm_pr_code, pmd_ppm_date_effective, pmd_percentage, pmd_pi_code FROM ( SELECT pmd_ppm_sio_reference, pmd_ppm_pr_code, pmd_ppm_date_effective, pmd_percentage, pmd_pi_code, -- 按产品(而非产品+原料)分区计算全局最新生效日期 MAX(trunc(pmd_ppm_date_effective)) OVER(PARTITION BY pmd_ppm_pr_code) AS latest_effective_date FROM ppm_details WHERE pmd_ppm_sio_reference = 1801 AND pmd_ppm_pr_code = 'A24052' ) t WHERE trunc(t.pmd_ppm_date_effective) = t.latest_effective_date
内容的提问来源于stack exchange,提问作者SkinnyPete63
相关产品推荐
相关产品推荐

