如何使用SQL为数据行分配带权重的测试分组？

SQL实现（以MySQL为例） #

随机分配（快速落地） #

通过生成0-1区间的随机数，匹配权重对应的区间完成分组：

SELECT 
    customer_id,
    customer_name,
    CASE
        WHEN RAND() < 0.5 THEN 'Group 1'
        WHEN RAND() < 0.75 THEN 'Group 2'
        WHEN RAND() < 0.95 THEN 'Group 3'
        ELSE 'Group 4'
    END AS test_group
FROM customers;

注：小样本量下可能出现比例偏差，适合对精度要求不高的场景。

精准比例分配（无偏差） #

先给客户排序，再按总数量的权重比例划分分组，确保比例完全符合要求：

WITH ranked_customers AS (
    SELECT 
        customer_id,
        customer_name,
        ROW_NUMBER() OVER (ORDER BY customer_id) AS rn,
        COUNT(*) OVER () AS total_customers
    FROM customers
)
SELECT 
    customer_id,
    customer_name,
    CASE
        WHEN rn <= total_customers * 0.5 THEN 'Group 1'
        WHEN rn <= total_customers * 0.75 THEN 'Group 2'
        WHEN rn <= total_customers * 0.95 THEN 'Group 3'
        ELSE 'Group 4'
    END AS test_group
FROM ranked_customers;

Python Pandas实现 #

随机分配 #

利用numpy.random.choice指定权重完成随机分配：

import pandas as pd
import numpy as np

# 加载客户数据
df = pd.read_csv('customers.csv')

# 定义分组与对应权重
groups = ['Group 1', 'Group 2', 'Group 3', 'Group 4']
weights = [0.5, 0.25, 0.2, 0.05]

# 分配分组
df['test_group'] = np.random.choice(groups, size=len(df), p=weights)

精准比例分配 #

计算每个分组应有的客户数，生成分组列表后打乱顺序分配，确保数量完全匹配权重：

import pandas as pd
import numpy as np

df = pd.read_csv('customers.csv')
total_customers = len(df)

# 计算各分组客户数量
group_counts = {
    'Group 1': int(total_customers * 0.5),
    'Group 2': int(total_customers * 0.25),
    'Group 3': int(total_customers * 0.2),
    'Group 4': total_customers - int(total_customers * 0.5) - int(total_customers * 0.25) - int(total_customers * 0.2)
}

# 生成分组列表并打乱
group_assignments = []
for group, count in group_counts.items():
    group_assignments += [group] * count

np.random.shuffle(group_assignments)
df['test_group'] = group_assignments

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