Teradata中为600万ID均匀分配随机日期的SQL优化问询

问题背景 #

有一张含600万条ID的表t1，以及一张含200个日期的表t2，需要给每个ID随机分配一个日期，且各日期的出现次数近似均匀分布。原方案通过交叉连接两张表后取随机行实现，但交叉连接会产生12亿条中间数据，效率极低，需要更优解法。

示例数据 #

• 左表t1的ID：

A
B
C
D

• 右表t2的日期：

1may2023
2may2023

• 期望结果（随机组合均可）：

A - 1may2023
B - 2may2023
C - 2may2023
D - 1may2023

原方案代码（可行但低效） #

CREATE TABLE cross_join AS 
SELECT t1.id, t2.date, random(1,200) as rnd
from t1
cross join t2;

SELECT id, date
from cross_join
QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY rnd) = 1;

更优解决方案 #

方案1：随机行号映射法 #

直接给日期表分配连续行号，再为每个ID生成对应范围的随机行号做关联，完全避免交叉连接：

WITH date_with_rn AS (
    -- 给日期表分配1到N的连续行号（N为日期总数）
    SELECT 
        date,
        ROW_NUMBER() OVER (ORDER BY date) AS rn
    FROM t2
),
total_dates AS (
    SELECT COUNT(*) AS cnt FROM t2
)
SELECT 
    t1.id,
    d.date
FROM t1
CROSS JOIN total_dates td
JOIN date_with_rn d
ON d.rn = CAST(RANDOM(1, td.cnt) AS INT);

方案2：百分比排名均匀分配法 #

利用PERCENT_RANK()确保日期分配的均匀性，尤其适合海量数据场景：

WITH date_with_rn AS (
    SELECT 
        date,
        ROW_NUMBER() OVER (ORDER BY date) AS rn,
        COUNT(*) OVER () AS total_dates
    FROM t2
),
id_with_rank AS (
    SELECT 
        id,
        -- 基于随机数生成0-1的百分比排名
        PERCENT_RANK() OVER (ORDER BY RANDOM()) AS pr
    FROM t1
)
SELECT 
    i.id,
    d.date
FROM id_with_rank i
JOIN date_with_rn d
ON d.rn = CEIL(i.pr * d.total_dates);

方案优势 #

1. 性能提升：无需生成百亿级中间表，计算量仅基于原表数据，大幅降低存储和CPU消耗；
2. 分布均匀：方案2通过百分比排名映射，能保证每个日期的分配数量接近总ID数/日期数（600万/200=3万条），比原方案的随机取行分布更稳定；
3. 灵活性高：无需提前硬编码日期数量（如原方案的random(1,200)），自动适配日期表的行数变化。

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