如何为订单特征匹配最优PriceRule：基于特征交集数量

需要确定某个Feature的有效定价，规则如下：

• 一个Feature可关联1个或多个PriceRule（规则数量<10，通常<4）
• Feature的定价可能依赖订单中的其他Feature
• 定价可能根据订单中的其他Feature（如F2、F3）上调或下调
• 部分Feature（如F6、F8、F9）对F1的定价无影响

需从订单中为每个Feature（如F1）匹配最优PriceRule：仅考虑所有规则Feature都存在于订单中的PriceRule，然后选择其中包含Feature数量最多的规则（即与订单Feature完全匹配的规则里，覆盖最全面的）。

示例说明 #

以F1的3个PriceRule和3个订单为例：

• 仅含F1的订单，F1对应正确价格为6（匹配仅含F1的PriceRule）
• 含F1+F2的订单，F1对应正确价格为4（匹配含F1+F2的PriceRule）
• 含F1+F2+F3的订单，F1对应正确价格为2（匹配含F1+F2+F3的PriceRule）

现有问题 #

当前尝试的SQL查询返回大量重复及错误关联记录：

SELECT op.OrderId, op.Id as OrderPositionId, 
       op.FeatureId, f.Featurename, pr.Price 
FROM OrderPositions op 
INNER JOIN FeatureCombinations fc ON op.FeatureId = fc.FeatureId
INNER JOIN Features f on op.FeatureId = f.Id
INNER JOIN PriceRules pr ON fc.PriceRuleId = pr.Id
ORDER BY op.OrderId, op.Id, f.Id;

核心问题 #

如何筛选出所有规则Feature都存在于订单中的PriceRule，并从中为每个订单的Feature选择包含Feature数量最多的最优规则？

表结构与测试数据 #

建表语句 #

CREATE TABLE Features(
  Id    INTEGER PRIMARY KEY, 
  Featurename  TEXT
);
CREATE TABLE PriceRules(
  Id     INTEGER PRIMARY KEY, 
  Price  REAL   
);
CREATE TABLE FeaturesPriceRules(
  FeatureId   INTEGER, 
  PriceRuleId INTEGER,
  FOREIGN KEY(FeatureId) REFERENCES Features(Id),
  FOREIGN KEY(PriceRuleId) REFERENCES PriceRules(Id)
);

CREATE TABLE FeatureCombinations(
  PriceRuleId INTEGER,
  FeatureId   INTEGER,   
  FOREIGN KEY(PriceRuleId) REFERENCES PriceRules(Id),
  FOREIGN KEY(FeatureId) REFERENCES Features(Id)  
);

CREATE TABLE OrderPositions(
  Id INTEGER  PRIMARY KEY, 
  OrderId   INTEGER,   
  FeatureId   INTEGER,     
  FOREIGN KEY(FeatureId) REFERENCES Features(Id)  
);

测试数据插入语句 #

INSERT INTO Features (Id, Featurename)
VALUES (1, 'F1'), (2, 'F2'), (3, 'F3'),
       (4, 'F6'), (5, 'F7'), (6, 'F9'),
       (7, 'E1'), (8, 'ZA2');

INSERT INTO PriceRules (Id, Price)
VALUES (101, '6.00'), (102, '4.00'), (103, '2.00'),
       (105, '24.00'), (106, '22.00');  
    
INSERT INTO FeaturesPriceRules (FeatureId, PriceRuleId)
VALUES (1, 101), (1, 102), (1, 103),(7, 105),(7, 106);  
               
INSERT INTO FeatureCombinations (PriceRuleId, FeatureId)
VALUES (101, 1), (102, 1), (102, 2),
       (103, 1), (103, 2),(103, 3), 
       (105, 7), (106, 7), (106, 8); 
       
INSERT INTO OrderPositions(Id, OrderId, FeatureId)
VALUES (211, 401, 1), (221, 402, 1),(222, 402, 2),
(231, 403, 1),(232, 403, 2),(233, 403, 3);         

解决方案 #

以下SQL通过CTE（公共表表达式）分步处理，最终筛选出每个订单Feature的最优PriceRule：

WITH OrderFeatures AS (
    -- 提取每个订单包含的所有Feature
    SELECT OrderId, FeatureId
    FROM OrderPositions
),
PriceRuleFeatureCounts AS (
    -- 统计每个PriceRule包含的Feature总数
    SELECT PriceRuleId, COUNT(*) AS TotalFeatures
    FROM FeatureCombinations
    GROUP BY PriceRuleId
),
ValidPriceRules AS (
    -- 筛选出所有规则Feature都存在于当前订单中的PriceRule
    SELECT 
        op.OrderId,
        op.Id AS OrderPositionId,
        op.FeatureId,
        f.Featurename,
        pr.Id AS PriceRuleId,
        pr.Price,
        prfc.TotalFeatures
    FROM OrderPositions op
    JOIN Features f ON op.FeatureId = f.Id
    JOIN FeaturesPriceRules fpr ON op.FeatureId = fpr.FeatureId
    JOIN PriceRules pr ON fpr.PriceRuleId = pr.Id
    JOIN PriceRuleFeatureCounts prfc ON pr.Id = prfc.PriceRuleId
    -- 检查当前PriceRule的所有Feature是否都在订单中存在
    WHERE NOT EXISTS (
        SELECT 1
        FROM FeatureCombinations fc
        WHERE fc.PriceRuleId = pr.Id
        AND NOT EXISTS (
            SELECT 1
            FROM OrderFeatures ofs
            WHERE ofs.OrderId = op.OrderId AND ofs.FeatureId = fc.FeatureId
        )
    )
),
RankedValidRules AS (
    -- 对每个订单的Feature，按规则包含的Feature数量降序排序，取最优规则
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY OrderId, FeatureId 
            ORDER BY TotalFeatures DESC, PriceRuleId ASC
        ) AS RuleRank
    FROM ValidPriceRules
)
-- 输出排名第一的最优定价规则
SELECT 
    OrderId,
    OrderPositionId,
    FeatureId,
    Featurename,
    Price
FROM RankedValidRules
WHERE RuleRank = 1
ORDER BY OrderId, OrderPositionId;

逻辑说明 #

1. OrderFeatures：提取每个订单的所有Feature，用于后续规则校验。
2. PriceRuleFeatureCounts：统计每个PriceRule包含的Feature总数，用于后续筛选最优规则。
3. ValidPriceRules：通过双重NOT EXISTS校验，只保留所有规则Feature都存在于当前订单中的PriceRule。
4. RankedValidRules：使用窗口函数ROW_NUMBER()对每个订单的Feature对应的有效规则排序，优先选择包含Feature数量最多的规则；若数量相同，按PriceRuleId升序保证结果稳定。
5. 最后筛选出排名第一的记录，即为每个订单Feature的最优定价。

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