mardi 9 janvier 2018

Select random elements

I need to sel3ect 10 random elements from at table.I know how to do that, else this question have been answered a million times here on SO. By my problem is the randomization isn't good enough

I've sat up da test case showing my problem :

DECLARE @Random TABLE
(
  Id int, 
  [Count] int  
)

DECLARE @TestData TABLE
(
  Id int
)

declare @runs int = 0;

 WHILE (@runs <=800)
 begin
   insert into @TestData values(@runs)
   set @runs = @runs +1 
 end;

 set @runs = 0 

 WHILE (@runs <=100)
 begin
     MERGE @Random AS target  
--      USING (SELECT ID FROM @TestData  where 0.01 >= CAST(CHECKSUM(NEWID(), id) & 0x7fffffff AS float) / CAST (0x7fffffff AS int) ) 
--      USING (SELECT top 10 ID FROM @TestData order by newid()) 
        USING (SELECT top 10 ID FROM @TestData order by abs(checksum(newid())) % 100)

AS SOURCE
        ON (target.id = source.id)  
        WHEN MATCHED THEN               
            UPDATE SET Target.[Count]  = Target.[Count] + 1  
        WHEN NOT MATCHED THEN  
            INSERT (ID, [Count])  VALUES (source.ID, 1);
    set @runs = @runs +1 
end

 select [count], count(*) "count(*)" from @Random group by [count] order by 1 desc 

As you can see I've tried several methods in order of randomization. But everytime I end up with a result like this :

enter image description here

So in short how do I select really random elements from a table?

Scope: SQL Server 2017, so every language feature is acceptable




Aucun commentaire:

Enregistrer un commentaire