jeudi 8 février 2018

Random numbers and temp tables in SQL

I have a problem with using random numbers together with temp tables. I have a table from which I want to select some random user_id's and then in the second table select some more, but with no repetition from the first one. A quick example follows (it is simplified, so pls don't write to simply select user_ids in the first place):

drop table if exists test; 

create table test (
user_id int,
g int);

insert into test values (1, 1);
insert into test values (2, 1);
insert into test values (3, 1);
insert into test values (4, 1);
insert into test values (5, 1);
insert into test values (6, 1);

with temp as (
select t.user_id
from (select tt.*, row_number() over (partition by tt.g order by randomint(100)) as seqnum
      from test tt) t 
where t.seqnum <= 2
)
select user_id from temp union all 
select user_id from test where user_id not in (select user_id from temp)

The result of the query shoul ideally return all user_ids, however, it results in an incomplete set but with repetitions. Do you know how I can solve this problem?




Aucun commentaire:

Enregistrer un commentaire