PostgreSQL中多规则价格查询及子查询参数传递问题

问题描述 #

需要编写PostgreSQL查询语句，按以下优先级规则获取商品的正确价格：

• 若tran_type等于Quot，则选取该条目的price；
• 若default_level大于0（取值为1-8的数字），则从同表中选取start_cust_or_group为空、level_匹配当前default_level且start_date最新的price。

default_level是标识1-8定价等级的数值占位符，对应价格存储在同表中start_cust_or_group为空、level_匹配该等级的最新日期记录中。

示例数据 #

原始数据表（部分） #

期望查询结果 #

现有代码及问题 #

当前使用的CASE语句查询如下：

SELECT 
    item_or_pricecode, uom_code, 
    CASE 
        WHEN tran_type <> '' THEN price 
        WHEN default_level > 0 THEN XXXXXX
        ELSE 0 
    END AS LevelResult 
FROM
    test.tablepricing.pricing 
WHERE 
    start_cust_or_group = '7000192' 
    AND cust_shipto_num = '2' 
    AND RowDeleted = 0 
ORDER BY 
    start_date DESC

注：PostgreSQL中无需用方括号包裹字段名（除非字段为关键字或含特殊字符），此处已移除多余方括号。

其中tran_type为Quot的逻辑已正常工作，但XXXXXX位置需编写子查询，将当前行的default_level传递以匹配level_获取对应价格，目前无法实现该参数传递。

用于获取等级价格的单独查询示例：

SELECT 
    x.item_or_pricecode, x.uom_code, x.level_, x.price, x.start_date
FROM 
    (SELECT
         x.item_or_pricecode, x.uom_code, x.level_, x.price, x.start_date,
         ROW_NUMBER() OVER (PARTITION BY x.item_or_pricecode ORDER BY x.start_date DESC, x.level_ ASC) AS rn
     FROM 
         test.tablepricing.pricing AS x
     WHERE
         start_cust_or_group = '' 
         AND RowDeleted = 0  
         AND x.item_or_pricecode = '27MIETE11K') AS x 
WHERE 
    x.rn <= 8

该查询可获取指定商品各等级的最新价格，但无法与主查询的default_level关联。

解决方案 #

方法1：关联子查询直接匹配 #

在CASE语句中嵌入关联子查询，通过主查询的item_or_pricecode和default_level匹配目标记录，并筛选最新的start_date：

SELECT 
    p.item_or_pricecode, 
    p.uom_code, 
    CASE 
        WHEN p.tran_type = 'Quot' THEN p.price 
        WHEN p.default_level > 0 THEN (
            SELECT price
            FROM test.tablepricing.pricing AS lp
            WHERE lp.item_or_pricecode = p.item_or_pricecode
              AND lp.start_cust_or_group = ''
              AND lp.level_ = p.default_level
              AND lp.RowDeleted = 0
            ORDER BY lp.start_date DESC
            LIMIT 1
        )
        ELSE 0 
    END AS LevelResult 
FROM
    test.tablepricing.pricing AS p
WHERE 
    p.start_cust_or_group = '7000192' 
    AND p.cust_shipto_num = '2' 
    AND p.RowDeleted = 0 
ORDER BY 
    p.start_date DESC;

该子查询会针对主查询的每一行，找到对应商品、匹配等级的最新价格记录。

方法2：CTE预计算最新等级价格 #

先通过CTE预计算每个商品、每个等级的最新价格，再与主查询关联，避免重复执行子查询，提升性能：

WITH latest_level_prices AS (
    SELECT 
        item_or_pricecode,
        level_,
        price,
        ROW_NUMBER() OVER (
            PARTITION BY item_or_pricecode, level_ 
            ORDER BY start_date DESC
        ) AS rn
    FROM test.tablepricing.pricing
    WHERE start_cust_or_group = ''
      AND RowDeleted = 0
      AND level_ BETWEEN 1 AND 8
)
SELECT 
    p.item_or_pricecode,
    p.uom_code,
    CASE 
        WHEN p.tran_type = 'Quot' THEN p.price
        WHEN p.default_level > 0 THEN llp.price
        ELSE 0
    END AS LevelResult
FROM test.tablepricing.pricing AS p
LEFT JOIN latest_level_prices AS llp
    ON p.item_or_pricecode = llp.item_or_pricecode
    AND p.default_level = llp.level_
    AND llp.rn = 1
WHERE 
    p.start_cust_or_group = '7000192'
    AND p.cust_shipto_num = '2'
    AND p.RowDeleted = 0
ORDER BY p.start_date DESC;

CTE会先筛选出所有等级价格记录，为每个商品+等级组合标记最新的记录（rn=1），之后主查询通过LEFT JOIN匹配对应的最新价格。

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