Redshift场景下基于Product_ID的订单内产品类型子分类实现问题

假设你的订单产品关联表名为 order_product_rel，可按以下逻辑实现：

核心思路 #

• 优先明确判定优先级：Mixed Set > Large Set > Small Set > Single，避免多规则同时满足时判定冲突
• 先给每个产品映射规格属性，再按订单聚合计算组合特征，最后按规则匹配标签
• 最终将订单级标签关联回原表每行，得到每个product_id对应的分类结果

完整实现SQL #

WITH product_attr AS (
    -- 映射每个product_id的规格属性
    SELECT 
        order_id,
        product_id,
        CASE WHEN product_id IN (1,2) THEN 'large'
             WHEN product_id IN (3,4) THEN 'small'
             ELSE 'single' END AS spec_type,
        MAX(CASE WHEN product_id = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY order_id) AS has_p1,
        MAX(CASE WHEN product_id = 2 THEN 1 ELSE 0 END) OVER (PARTITION BY order_id) AS has_p2,
        MAX(CASE WHEN product_id = 3 THEN 1 ELSE 0 END) OVER (PARTITION BY order_id) AS has_p3,
        MAX(CASE WHEN product_id = 4 THEN 1 ELSE 0 END) OVER (PARTITION BY order_id) AS has_p4,
        MAX(CASE WHEN product_id IN (1,2) THEN 1 ELSE 0 END) OVER (PARTITION BY order_id) AS has_large,
        MAX(CASE WHEN product_id IN (3,4) THEN 1 ELSE 0 END) OVER (PARTITION BY order_id) AS has_small
    FROM order_product_rel
),
order_type AS (
    -- 按订单粒度判定标签
    SELECT DISTINCT
        order_id,
        CASE WHEN has_large = 1 AND has_small = 1 THEN 'Mixed Set'
             WHEN has_p1 = 1 AND has_p2 = 1 THEN 'Large Set'
             WHEN has_p3 = 1 AND has_p4 = 1 THEN 'Small Set'
             ELSE 'Single' END AS product_type
    FROM product_attr
)
-- 关联回原表得到每个product_id对应的标签
SELECT 
    a.order_id,
    a.product_id,
    b.product_type
FROM order_product_rel a
LEFT JOIN order_type b ON a.order_id = b.order_id
ORDER BY a.order_id, a.product_id;

逻辑说明 #

• 如果同一个订单内存在重复product_id，可在product_attr层先对order_id + product_id去重后再计算，不影响判定结果
• 窗口函数替代group by聚合的写法，也可以换成按order_id分组聚合后再关联，性能差异根据数据量大小调整即可

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