问在组中不重复的SQL随机数

EN

/* Iterative hack to fill 6 slots with a random number between 1 and 13.
   A random number *must not* repeat within a household_id.
*/
update c3_lalfinal a
set a.rand_inst = b.rnum
from (
    select household_id
          ,slot_nbr
          ,row_number() over (partition by household_id,rnd order by null) as rnum
    from c3_lalfinal
) b
where a.household_id = b.household_id
  and a.slot_nbr = b.slot_nbr
;

update c3_lalfinal
set rnd = CAST(0.5 + random() * (13-1+1) as INT)
where rand_inst>1
;

/* Repeat until this query returns 0: */
select count(*) from (
  select household_id from c3_lalfinal group by 1 having count(distinct(rnd)) <> 6
) x
;

select hh, slot, row_number() over (partition by hh order by random()) as rn
from t;

with nums as (
      select 1 as n union all select 2 union all select 3 union all select 4 union all select 5 union all
      select 6 union all select 7 union all select 8 union all select 9
     ),
     randoms as (
      select n, row_number() over (order by random()) as pos
      from nums
     )
select t.hh, t.slot, hnum.n
from (select hh, randoms.n, randoms.pos
      from (select distinct hh
            from t
           ) t cross join
           randoms
     ) hnum join
     t
     on t.hh = hnum.hh and
        t.slot = hnum.pos;