You need to enable JavaScript to run this app. 表记录分组:使用SQL row_number取产品最新配方的问题解决
优惠活动
大模型
产品
解决方案
定价
更多

表记录分组:使用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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.05 18:45:03