PostgreSQL 9.6按规则筛选商户最优优惠方案

需求回顾 #

先把咱们的筛选规则明确下来，避免走偏：

• 折扣值越高越优先，不受benefit_type影响
• 折扣相同时，benefit_type为ALL的优惠比FOOD的更优
• 折扣和类型都相同时，任选其一（比如选ID最小的那条）

表结构与样本数据 #

先看一下咱们的表结构：

create table offer ( 
    id bigserial not null, 
    discount int4, 
    benefit_type varchar(25), 
    merchant_id int8 not null 
);

对应的样本数据：

解决方案：用窗口函数实现优先级排序 #

直接用max()只能拿到最大折扣，没法关联对应的benefit_type，所以咱们用ROW_NUMBER()窗口函数给每个商户的优惠按规则排个序，然后取排序第一的那条就行。

完整SQL如下：

SELECT merchant_id, discount AS max_discount, benefit_type
FROM (
    SELECT 
        merchant_id,
        discount,
        benefit_type,
        -- 按商户分组，然后按规则排序，给每条优惠编序号
        ROW_NUMBER() OVER (
            PARTITION BY merchant_id
            ORDER BY 
                discount DESC, -- 第一优先级：折扣从高到低
                -- 第二优先级：ALL比FOOD优先，用CASE转成数字排序
                CASE benefit_type WHEN 'ALL' THEN 1 WHEN 'FOOD' THEN 2 END ASC,
                id ASC -- 第三优先级：同折扣同类型时取ID最小的
        ) AS rn
    FROM offer
) ranked_offers
WHERE rn = 1; -- 只取每个商户排序第一的优惠

结果验证 #

执行上面的SQL后，会得到你期望的结果：

补充说明 #

如果后续需求调整，允许保留所有同优先级的优惠（比如折扣和类型都相同的全部返回），可以把ROW_NUMBER()换成RANK()——这样同条件的优惠序号都是1，会全部被筛选出来。

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