如何用PL/SQL实现按年份均匀分布且ID不重复的数据抽样

实现逻辑 #

• 先对ID全局去重：同一个ID仅保留其最早出现的记录，避免跨年份重复入选
• 再按年份分层做随机抽样，保证各年份抽取的样本量接近，实现均匀分布
• 调用Oracle内置的dbms_random.value()生成0-1区间的随机值，用于同年份内的随机排序抽取

完整SQL实现 #

WITH id_dedup AS (
    -- 步骤1：每个ID仅保留最早出现的1条记录，完成ID去重
    SELECT 
        data_dt,
        id_value,
        EXTRACT(YEAR FROM TO_DATE(data_dt, 'dd-mm-yyyy')) AS data_year
    FROM (
        SELECT 
            data AS data_dt,
            value AS id_value,
            ROW_NUMBER() OVER (PARTITION BY value ORDER BY TO_DATE(data, 'dd-mm-yyyy') ASC) AS rn
        FROM 替换为你的原始表名
    ) t
    WHERE rn = 1
),
year_random_rank AS (
    -- 步骤2：按年份分组，组内按随机值排序打序号
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY data_year ORDER BY dbms_random.value()) AS random_rn
    FROM id_dedup
)
-- 步骤3：按需求抽取样本
SELECT data_dt, id_value, data_year
FROM year_random_rank
-- 方案A：总样本量均分至各年份，此处示例总样本量为1000，可自行修改数值
WHERE random_rn <= CEIL(1000 / (SELECT COUNT(DISTINCT data_year) FROM id_dedup))
-- 方案B：固定每年抽取N条，直接替换上面的条件为 WHERE random_rn <= N即可
ORDER BY data_year;

调整说明 #

• 若某年份的有效记录数少于抽样阈值，会自动取该年份全部符合要求的记录，不会报错
• 每次执行dbms_random.value()会重新生成随机排序，抽样结果会刷新；如果需要固定抽样结果，可以提前调用dbms_random.seed(固定数值)设置随机种子

内容的提问来源于stack exchange，提问作者Любовь Пономарева