时间戳

首页 > 解决方案 > 随机分配值，但在分组内的分布范围内

sql - 随机分配值，但在分组内的分布范围内

问题描述

我想为记录随机分配一个名称，但基于百分比分布。例如，假设我有以下数据：

name    type sub_type reg pct
a       xx    yy      n    .7
a       xx    yy      n    .7
NO Name xx    yy      n    NULL
NO Name xx    yy      n    NULL
NO Name xx    yy      n    NULL
b       xx    yy      n    .3
NO Name xx    yy      n    NULL

a       bb    yy      n    .1
b       bb    yy      n    .8
c       bb    yy      n    .1
NO Name bb    yy      n    NULL

a       xx    cc      n    .1
a       xx    cc      n    .1
NO Name xx    cc      n    NULL
NO Name xx    cc      n    NULL
NO Name xx    cc      n    NULL
b       xx    cc      n    .9
NO Name xx    cc      n    NULL

a       bb    cc      n    .5
b       bb    cc      n    .2
c       bb    cc      n    .3
NO Name bb    cc      n    NULL

a       xx    cc      x    .2
a       xx    cc      x    .2
NO Name xx    cc      x    NULL
NO Name xx    cc      x    NULL
NO Name xx    cc      x    NULL
b       xx    cc      x    .8
NO Name xx    cc      x    NULL

a       bb    cc      x    .3
b       bb    cc      x    .1
c       bb    cc      x    .6
NO Name bb    cc      x    NULL

我想根据 type、sub_type 和 reg 类别分组中的 pct 分布为 NO Name 记录分配一个随机名称。因此，例如，当 type = xx、sub_type = cc 和 reg = n 时，四个 NO 名称记录将被随机分配名称 a 或 b，但结果分布在 type = xx、sub_type = cc 和 reg = 内的 NO 名称记录n 分组为 a 的 70%，b 的 30%，因为这是它们在该分组中的 pct 值。因此，在给定分组内的名称 pct 分配的情况下，按 type/sub_type/reg 分组按名称随机分配记录。

结果可能如下所示：

 name    type sub_type reg pct
a       xx    yy      n    .7
a       xx    yy      n    .7
a       xx    yy      n    NULL
a       xx    yy      n    NULL
a       xx    yy      n    NULL
b       xx    yy      n    .3
b       xx    yy      n    NULL

a       bb    yy      n    .1
b       bb    yy      n    .8
c       bb    yy      n    .1
b       bb    yy      n    NULL

a       xx    cc      n    .1
a       xx    cc      n    .1
b       xx    cc      n    NULL
b       xx    cc      n    NULL
b       xx    cc      n    NULL
b       xx    cc      n    .9
b       xx    cc      n    NULL

a       bb    cc      n    .5
b       bb    cc      n    .2
c       bb    cc      n    .3
a       bb    cc      n    NULL

a       xx    cc      x    .2
a       xx    cc      x    .2
b       xx    cc      x    NULL
b       xx    cc      x    NULL
b       xx    cc      x    NULL
b       xx    cc      x    .8
a       xx    cc      x    NULL

a       bb    cc      x    .3
b       bb    cc      x    .1
c       bb    cc      x    .6
c       bb    cc      x    NULL

我的虚构数据中没有足够的记录来真正显示分布，但希望足以说明我的问题。

这与我想要的类似，但是在此示例中百分比是固定的，并且它们不是像我这样的任何组： 根据百分比随机划分表数据

希望这是有道理的。

编辑 1：我想我有一部分水桶部分下来了

select
    xx.*
    ,NTILE((select count(*)
        from (
                select distinct 
                    REG
                ,TYPE
                ,SUB_TYPE 
                from tbl1
                group by
                REG
                ,TYPE
                ,SUB_TYPE ) x))
        over (partition by REG
                    ,TYPE
                    ,SUB_TYPE order by newid()) buckets
    from tbl1 xx

标签： sqlsql-server

解决方案

也许这个片段会返回您正在寻找的结果

它的逻辑与您之前引用的我的其他答案略有不同。但是，我相信在当前情况下ROW_NUMBER是更合适的替代方案NTILE

;WITH cte  ([name], [type], sub_type, reg, pct)
AS 
(
SELECT 'a', 'xx', 'yy', 'n',  .7           UNION ALL
SELECT 'a', 'xx', 'yy', 'n',  .7           UNION ALL
SELECT 'NO Name', 'xx', 'yy', 'n',  NULL   UNION ALL
SELECT 'NO Name', 'xx', 'yy', 'n',  NULL   UNION ALL
SELECT 'NO Name', 'xx', 'yy', 'n',  NULL   UNION ALL
SELECT 'b', 'xx', 'yy', 'n',  .3           UNION ALL
SELECT 'NO Name', 'xx', 'yy', 'n',  NULL   UNION ALL

SELECT 'a', 'bb', 'yy', 'n',  .1           UNION ALL
SELECT 'b', 'bb', 'yy', 'n',  .8           UNION ALL
SELECT 'c', 'bb', 'yy', 'n',  .1           UNION ALL
SELECT 'NO Name', 'bb', 'yy', 'n',  NULL   UNION ALL

SELECT 'a', 'xx', 'cc', 'n',  .1           UNION ALL
SELECT 'a', 'xx', 'cc', 'n',  .1           UNION ALL
SELECT 'NO Name', 'xx', 'cc', 'n',  NULL   UNION ALL
SELECT 'NO Name', 'xx', 'cc', 'n',  NULL   UNION ALL
SELECT 'NO Name', 'xx', 'cc', 'n',  NULL   UNION ALL
SELECT 'b', 'xx', 'cc', 'n',  .9           UNION ALL
SELECT 'NO Name', 'xx', 'cc', 'n',  NULL   UNION ALL

SELECT 'a', 'bb', 'cc', 'n',  .5           UNION ALL
SELECT 'b', 'bb', 'cc', 'n',  .2           UNION ALL
SELECT 'c', 'bb', 'cc', 'n',  .3           UNION ALL
SELECT 'NO Name', 'bb', 'cc', 'n',  NULL   UNION ALL

SELECT 'a', 'xx', 'cc', 'x',  .2           UNION ALL
SELECT 'a', 'xx', 'cc', 'x',  .2           UNION ALL
SELECT 'NO Name', 'xx', 'cc', 'x',  NULL   UNION ALL
SELECT 'NO Name', 'xx', 'cc', 'x',  NULL   UNION ALL
SELECT 'NO Name', 'xx', 'cc', 'x',  NULL   UNION ALL
SELECT 'b', 'xx', 'cc', 'x',  .8           UNION ALL
SELECT 'NO Name', 'xx', 'cc', 'x',  NULL   UNION ALL

SELECT 'a', 'bb', 'cc', 'x',  .3           UNION ALL
SELECT 'b', 'bb', 'cc', 'x',  .1           UNION ALL
SELECT 'c', 'bb', 'cc', 'x',  .6           UNION ALL
SELECT 'NO Name', 'bb', 'cc', 'x',  NULL   

) 

-- Records without name
SELECT CASE             
            WHEN d.TotalRecordsInGroup  = 1 THEN 'a' --only one record in the group
            WHEN d.RecordNr/CAST(d.TotalRecordsInGroup AS FLOAT) < .7 THEN 'a'
            WHEN d.RecordNr/CAST(d.TotalRecordsInGroup AS FLOAT) <= 1.0 THEN 'b'

            ELSE NULL 
        END  AS [name]
            ,
       d.type,
       d.sub_type,
       d.reg,
       d.pct       
FROM (
SELECT cte.name
,       cte.type
,       cte.sub_type
,       cte.reg
,       cte.pct
        -- obtain record number randomly of members in a group
,       ROW_NUMBER() OVER (PARTITION BY type, cte.sub_type,reg ORDER BY NEWID()) AS RecordNr
        -- obtain the numbers of members in a group
,       COUNT(*) OVER (PARTITION BY type, cte.sub_type,reg) AS TotalRecordsInGroup 
FROM cte 
WHERE cte.name = 'No Name'
) d
UNION ALL
-- Records with a known name
SELECT cte.name,
       cte.type,
       cte.sub_type,
       cte.reg,
       cte.pct 
FROM cte
WHERE cte.name <> 'No Name'
ORDER BY d.type, sub_type, reg

推荐阅读