Teradata抽样求助：按Group抽样本且保证ID唯一的实现方法

你遇到的核心问题很明确：同一个ID可能属于多个满足rank=1的Group，直接用SAMPLE会导致ID重复出现在不同Group的结果里。下面给你两种实用的解决方案，你可以根据业务场景选择。

方案一：分步抽样+排除已选中ID #

这种方法逻辑直观，先给样本需求大的Group优先抽取唯一ID，再依次处理剩余Group，每次抽样都排除已被选中的ID，确保最终每个ID只出现一次。

WITH ranked_groups AS (
    SELECT 
        ID,
        "Group",
        -- 可选：给每个ID的Group设置优先级，优先处理配额高的Group
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY 
            CASE "Group" 
                WHEN 'dog' THEN 1 
                WHEN 'cat' THEN 2 
                WHEN 'elephant' THEN 3 
                WHEN 'lion' THEN 4 
            END) AS group_priority
    FROM YourTable 
    WHERE Rank = 1
),
-- 抽取dog的10个唯一ID
dog_samples AS (
    SELECT DISTINCT ID
    FROM ranked_groups
    WHERE "Group" = 'dog'
    SAMPLE 10
),
-- 从未被dog选中的ID中抽取cat的10个
cat_samples AS (
    SELECT DISTINCT ID
    FROM ranked_groups
    WHERE "Group" = 'cat'
      AND ID NOT IN (SELECT ID FROM dog_samples)
    SAMPLE 10
),
-- 从未被dog、cat选中的ID中抽取elephant的5个
elephant_samples AS (
    SELECT DISTINCT ID
    FROM ranked_groups
    WHERE "Group" = 'elephant'
      AND ID NOT IN (SELECT ID FROM dog_samples UNION ALL SELECT ID FROM cat_samples)
    SAMPLE 5
),
-- 从剩余ID中抽取lion的5个
lion_samples AS (
    SELECT DISTINCT ID
    FROM ranked_groups
    WHERE "Group" = 'lion'
      AND ID NOT IN (SELECT ID FROM dog_samples UNION ALL SELECT ID FROM cat_samples UNION ALL SELECT ID FROM elephant_samples)
    SAMPLE 5
)
-- 合并所有样本结果
SELECT ID, 'dog' AS "Group" FROM dog_samples
UNION ALL
SELECT ID, 'cat' AS "Group" FROM cat_samples
UNION ALL
SELECT ID, 'elephant' AS "Group" FROM elephant_samples
UNION ALL
SELECT ID, 'lion' AS "Group" FROM lion_samples;

方案一特点： #

• 逻辑清晰，容易调试和修改
• 能严格保证每个Group的抽样数量（只要该Group有足够多未被占用的唯一ID）
• 单个Group候选ID不足时，不会影响其他Group的抽样结果

方案二：窗口函数+优先级分配 #

这种方法效率更高，通过窗口函数一次性处理ID唯一性和抽样配额，自动给每个ID分配到优先级最高的Group，同时控制每个Group的样本量。

WITH all_candidates AS (
    SELECT 
        ID,
        "Group",
        -- 给每个Group内的ID随机排序，保证抽样随机性
        ROW_NUMBER() OVER (PARTITION BY "Group" ORDER BY RANDOM()) AS rn
    FROM YourTable 
    WHERE Rank = 1
),
priority_assignment AS (
    SELECT 
        ID,
        "Group",
        rn,
        -- 给每个ID的Group设置优先级，确保一个ID只对应一个Group
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY 
            CASE "Group" 
                WHEN 'dog' THEN 1 
                WHEN 'cat' THEN 2 
                WHEN 'elephant' THEN 3 
                WHEN 'lion' THEN 4 
            END) AS id_priority
    FROM all_candidates
)
SELECT ID, "Group"
FROM priority_assignment
WHERE id_priority = 1 -- 每个ID只保留优先级最高的Group记录
QUALIFY 
    -- 控制每个Group的抽样数量
    CASE "Group"
        WHEN 'dog' THEN SUM(1) OVER (PARTITION BY "Group" ORDER BY rn) <= 10
        WHEN 'cat' THEN SUM(1) OVER (PARTITION BY "Group" ORDER BY rn) <= 10
        WHEN 'elephant' THEN SUM(1) OVER (PARTITION BY "Group" ORDER BY rn) <= 5
        WHEN 'lion' THEN SUM(1) OVER (PARTITION BY "Group" ORDER BY rn) <= 5
    END;

方案二特点： #

• 执行效率更高，无需多次子查询
• 自动处理ID唯一性，一个ID只会出现在一个Group的结果中
• 优先级设置会影响结果：如果某个ID属于多个Group，会被分配到优先级高的Group，可能导致低优先级Group样本量不足（若候选ID不够）

注意事项： #

• Group是Teradata的关键字，查询中需要用双引号"Group"引用该字段，避免语法错误
• 确保Rank = 1的筛选条件符合你的业务需求，这是原始查询的前提
• 如果某个Group的符合条件的唯一ID数量少于指定抽样数，查询会返回该Group所有可用的唯一ID，无法强制生成更多样本
• RANDOM()用于随机排序保证抽样随机性，若需要固定抽样结果，可替换为其他排序字段

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