PostgreSQL（Redash）下Level 1产品部分采购记录的SQL标记方法

核心逻辑回顾 #

仅针对Level1采购记录：若客户购买了Level1产品，但未购买该Level1关联的任何Level2产品，则标记为部分采购。

假设表结构 #

假设采购记录表名为purchase_records，包含以下关键列：

• customer_id: 客户ID
• product_id: 产品ID
• product_level: 产品级别（1=Level1，2=Level2）
• dependent_level1_ids: Level2产品关联的Level1 ID列表（PostgreSQL数组类型，如int[]；若为字符串分隔列表需额外处理）

具体SQL实现 #

WITH customer_level2_associated_level1 AS (
    -- 提取每个客户已购买的Level2产品对应的所有关联Level1 ID（去重）
    SELECT 
        customer_id,
        ARRAY_AGG(DISTINCT UNNEST(dependent_level1_ids)) AS purchased_associated_level1
    FROM purchase_records
    WHERE product_level = 2
    GROUP BY customer_id
)
SELECT
    pr.customer_id,
    pr.product_id,
    pr.product_level,
    pr.dependent_level1_ids,
    -- 标记部分采购的逻辑
    CASE
        WHEN pr.product_level = 1 
             AND (cla.purchased_associated_level1 IS NULL 
                  OR pr.product_id <> ALL(cla.purchased_associated_level1))
        THEN '部分采购'
        ELSE '正常采购'
    END AS purchase_status
FROM purchase_records pr
LEFT JOIN customer_level2_associated_level1 cla
    ON pr.customer_id = cla.customer_id;

代码解释 #

1. CTE部分：

   • 筛选所有Level2采购记录，用UNNEST将关联的Level1 ID列表拆分为单行，再通过ARRAY_AGG(DISTINCT ...)聚合得到每个客户已覆盖的Level1 ID集合。
   • 若dependent_level1_ids是逗号分隔的字符串（如"101,102"），需将UNNEST(dependent_level1_ids)替换为UNNEST(STRING_TO_ARRAY(dependent_level1_ids, ',')::int[])，先转成数组再拆分。

2. 主查询部分：

   • 用LEFT JOIN关联客户的Level2关联Level1集合，确保不会遗漏没有购买过Level2产品的客户。
   • CASE判断逻辑：
     • 仅对Level1采购记录进行判断；
     • 若客户无任何Level2采购（cla.purchased_associated_level1 IS NULL），直接标记为部分采购；
     • 若客户有Level2采购，但当前Level1产品ID不在其关联集合中，也标记为部分采购。

适配调整 #

• 根据实际表名、列名替换代码中的字段（如product_level可能为level，dependent_level1_ids可能为parent_ids）；
• 若需过滤仅显示Level1采购记录，可在主查询末尾添加WHERE pr.product_level = 1。

内容的提问来源于stack exchange，提问作者Safak Atakan Celik