根据百分比随机划分表格数据 [英] Divide the Table data randomly based on percentages

问题描述

我有一个名为 Employees 的表，有 100 条记录.列是 ID 和 Name.

I have a table called Employees, having 100 records. The columns are ID and Name.

100 只是一个样本数，它可以有任意数量的记录，例如 15 或 115.应根据以下给出的百分比进行抽样.

100 is just a sample number, it can have any number of records like 15 or 115. The sampling should be done based on the percentages given below.

现在我想添加新列Group.Group 值为 Group1、Group2、Group3.

Now I would like to add new column Group. Group value will be Group1, Group2, Group3.

如何将行分配给组，使每行都在随机选择的组中，但结果分布遵循以下百分比.

How can assign the rows to groups so that each row is in a randomly chosen group, but the resulting distribution follows the below percentages.

65% Employees Should go to Group1
20% Employee Should go to Group2
15% Employee Should go to Group3

推荐答案

答案与 Michal 类似，他的答案也正确，但 NTILE 可用作替代方案，因为它将数据集拆分为 100 个相等的块.ROW_Number 不适用于行数小于 100 的数据集.:

Answer is similar to those form Michal and his answer is also correct, however NTILE to be used as alternative, since it will split a dataset to 100 equal chunks. ROW_Number will not work for a case for a dataset with a number of rows smaller than 100.:

select id,a.name,
       case when rn <= 65 then 'group 1'
            else case when rn <= 85 then 'group 2' else 'group 3' end
       end
from
(
    --newid() will generate random order of records
    select ID, name , NTILE(100) OVER (ORDER BY NEWID()) [rn] from dbo.Employees
) [a]

这篇关于根据百分比随机划分表格数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！