jeudi 7 mars 2019

Selecting a 1% sample in Aginity Workbench SQL

I need to scoop up a random sample of 1% of the records in a table (with the number of rows growing every second).

My idea is to

select DISTINCT
   random()
   ,name
   ,age
   ,registrationNumber
from
   everGrowingTable
order by random desc
limit ((select count(*) from everGrowingTable) * 0.01)//this is attempting to get 1%

The compiler complains about the * operator. It is fine when I hard code the table size however.

I've tried IBM documentation, but this talks about calculations using known values, not values that grow (such is that case in my table)

There doesn't seem to be a Aginity SQL function that does this. I've notice the MINUS function in the Aginity Workbench Intellisense, but alas, no multiplication equivalent.




Aucun commentaire:

Enregistrer un commentaire