如何在保留member_nbr与serv_nbr排序的同时随机化SQL结果？

问题描述 #

现有SQL查询结果按member_nbr、serv_nbr顺序排列，需求调整为：每个member_nbr分组内仍按serv_nbr排序，但整体分组的顺序随机化，尝试使用Rand()函数未成功，需可行实现方法。

原SQL代码 #

with STAGE2 as ( select /*+ FULL(ip) FULL(sx) MATERIALIZE PARALLEL(4) */ distinct
                        sx.claim_nbr,
                        sx.member_nbr
                 from
                        mem_process ip  
                        left outer join visit_x sx on ip.claim_nbr = sx.claim_nbr 
                 where
                        ( (ip.business_unit = 'NC' and ip.carrier = 'MD' and ip.bank = '01'))
                        and ip.paid = 'Y'
                        and sx.paid = 'Y' 
                        and substr(sx.claim_nbr,7,1) not in ('5', '6', '7', '8')
                        and substr(sx.serv_nbr,15,2) = '00'
        )
select distinct
            STAGE1.member_nbr,
            STAGE1.claim_nbr, STAGE1.serv_nbr, STAGE1.ymdeff, STAGE1.ymdend, STAGE1.ymdpaid,
            STAGE1.carrier, STAGE1.bu, STAGE1.bank, STAGE1.PROG_NBR, STAGE1.REGION
from (
select 
                sx.member_nbr,
                sx.claim_nbr, sx.serv_nbr, sx.ymdeff, sx.ymdend, sx.ymdpaid,
                ip.carrier, ip.business_unit as bu, ip.bank, ip.prog_nbr, ip.region
           
from
                STAGE2 
                left outer join mem_process ip on STAGE2.claim_nbr = ip.claim_nbr
                left outer join visit_x sx on ip.claim_nbr = sx.claim_nbr
   
where
                ip.paid = 'Y'
                and sx.paid = 'Y'
                and length(sx.ymdpaid) = 8
    ) STAGE1
order by
  STAGE1.member_nbr,
  STAGE1.serv_nbr
;

当前结果示例 #

member-a, serv-1-1
member-a, serv-1-2
member-a, serv-2-1
member-a, serv-2-2
member-a, serv-3-1

member-b, serv-4-1
member-b, serv-4-2
member-b, serv-5-1

member-c, serv-6-1
member-c, serv-7-1

member-h, serv-8-1
member-h, serv-8-2

member-z, serv-10-1
member-z, serv-10-2
member-z, serv-10-3
member-z, serv-11-1

期望结果示例 #

member-c, serv-6-1
member-c, serv-7-1

member-b, serv-4-1
member-b, serv-4-2
member-b, serv-5-1

member-z, serv-10-1
member-z, serv-10-2
member-z, serv-10-3
member-z, serv-11-1

member-a, serv-1-1
member-a, serv-1-2
member-a, serv-2-1
member-a, serv-2-2
member-a, serv-3-1

member-h, serv-8-1
member-h, serv-8-2

解决方案 #

核心思路是：为每个唯一的member_nbr分配一个固定的随机值，通过这个随机值排序实现分组顺序随机，同时组内保持serv_nbr的排序逻辑。

修改后的SQL代码 #

with STAGE2 as ( 
    select /*+ FULL(ip) FULL(sx) MATERIALIZE PARALLEL(4) */ distinct
        sx.claim_nbr,
        sx.member_nbr
    from
        mem_process ip  
        left outer join visit_x sx on ip.claim_nbr = sx.claim_nbr 
    where
        (ip.business_unit = 'NC' and ip.carrier = 'MD' and ip.bank = '01')
        and ip.paid = 'Y'
        and sx.paid = 'Y' 
        and substr(sx.claim_nbr,7,1) not in ('5', '6', '7', '8')
        and substr(sx.serv_nbr,15,2) = '00'
),
-- 新增子查询：为每个member_nbr生成唯一随机排序键
MEMBER_RAND as (
    select distinct member_nbr, DBMS_RANDOM.VALUE as rand_key
    from STAGE2
)
select distinct
    STAGE1.member_nbr,
    STAGE1.claim_nbr, STAGE1.serv_nbr, STAGE1.ymdeff, STAGE1.ymdend, STAGE1.ymdpaid,
    STAGE1.carrier, STAGE1.bu, STAGE1.bank, STAGE1.PROG_NBR, STAGE1.REGION
from (
    select 
        sx.member_nbr,
        sx.claim_nbr, sx.serv_nbr, sx.ymdeff, sx.ymdend, sx.ymdpaid,
        ip.carrier, ip.business_unit as bu, ip.bank, ip.prog_nbr, ip.region
    from
        STAGE2 
        left outer join mem_process ip on STAGE2.claim_nbr = ip.claim_nbr
        left outer join visit_x sx on ip.claim_nbr = sx.claim_nbr
    where
        ip.paid = 'Y'
        and sx.paid = 'Y'
        and length(sx.ymdpaid) = 8
) STAGE1
join MEMBER_RAND mr on STAGE1.member_nbr = mr.member_nbr
order by
    mr.rand_key,  -- 按随机键排序，实现分组随机
    STAGE1.serv_nbr;  -- 组内按serv_nbr保持有序

说明 #

1. 新增MEMBER_RAND子查询：从STAGE2中提取所有唯一的member_nbr，并为每个member_nbr生成一个固定的随机数值rand_key（Oracle用DBMS_RANDOM.VALUE，MySQL替换为RAND()即可）。
2. 关联随机键：主查询关联MEMBER_RAND，确保同一个member_nbr的所有行都对应同一个随机键。
3. 排序逻辑调整：先按rand_key排序，实现整个member_nbr分组的随机顺序；再按serv_nbr排序，保证分组内的有序性。

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