优化SQL查询：按层级规则生成客户唯一商品价格列表

要解决重复条目问题，核心是给每条符合规则的价格记录分配优先级，再为每个商品筛选出优先级最高的唯一记录。以下是具体实现方案：

核心思路 #

1. 按需求定义优先级权重：
   • tran_type = 'Quot'的交易价：优先级最高（权重3）
   • 匹配default_level的目录价：次优先级（权重2）
   • 层级为5的目录价：最低优先级（权重1），且需取最新日期的记录
2. 使用窗口函数ROW_NUMBER()为每个商品的记录按优先级降序、日期降序排序，最终只保留排序后行号为1的记录。

示例SQL代码 #

假设你的价格表名为customer_prices，包含字段customer_id、product_id、tran_type、default_level、price_level、price、effective_date，代码如下：

WITH ranked_price_records AS (
    SELECT
        customer_id,
        product_id,
        price,
        tran_type,
        price_level,
        effective_date,
        -- 按规则计算优先级并排名
        ROW_NUMBER() OVER (
            PARTITION BY product_id
            ORDER BY
                CASE
                    WHEN tran_type = 'Quot' THEN 3
                    WHEN price_level = default_level THEN 2
                    WHEN price_level = 5 THEN 1
                    ELSE 0  -- 排除不符合规则的无效记录
                END DESC,
                effective_date DESC  -- 层级5的记录取最新日期
        ) AS rank_num
    FROM customer_prices
    WHERE customer_id = '7001364'
    -- 仅保留符合规则的记录
    AND (
        tran_type = 'Quot'
        OR (tran_type IS NULL AND price_level IN (default_level, 5))
    )
)
-- 筛选每个商品的最高优先级记录
SELECT
    customer_id,
    product_id,
    price,
    tran_type,
    price_level,
    effective_date
FROM ranked_price_records
WHERE rank_num = 1;

代码说明 #

• CTE部分：通过ROW_NUMBER()按商品分组，先按优先级权重降序排序，同优先级（比如都是层级5的记录）再按日期降序，确保最新记录排在前面。
• 过滤条件：提前排除不符合规则的记录，减少计算量。
• 最终筛选：只保留每个商品排名第一的记录，即符合最高优先级规则的价格。

如果你的表结构或字段名略有不同，只需调整字段名和过滤条件即可适配。

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