Hello,
I'm facing the following task - I need to be able to generate a unique sample from a table/view. A couple of notes:
These are the things I've tried/investigated:
a!localVariables( local!random: ri!min + tointeger(rand() * (ri!max - ri!min)), local!arrray: append(ri!array, local!random), if( length(union(local!arrray, local!arrray)) >= ri!count, local!arrray, rule!KS_recursion_rand_dev_selector( min: ri!min, max: ri!max, count: ri!count, array: union(local!arrray, local!arrray) ) ) )
Any suggestions? Thank you!
Discussion posts and replies are publicly visible
The SP can recreate much of the view logic you're thinking of internally and do more efficient / more advanced calculations in advance (i assume up to and including using some db-internal logic to select some samples) - since it's all done internal to the DB, it'll process faster than trying to query through Appian.
Do you have examples of random functions that can be done on the db side? The only ones I've been able to find are DBMS_RANDOM.VALUE and SAMPLE() for oracle, but I've heard DBMS_RANDOM.VALUE can be very slow on large tables, and SAMPLE() tends to return skewed data.
True random values often appear "skewed" due to observer bias. If you want something that's evenly distributed instead of truly random, you'll probably need to determine your own algorithm for that (i.e. take indexes from 1 to N divided by samples desired, then randomize each sample index by taking some deviation based on a generated random). The DB software has loads of functions available and I don't even begin to claim to fathom them all. If you were working in MariaDB i would be more confident in sharing some of my stored proc tricks (disclaimering that i haven't done much play with random numbers in it), but you could probably discover the ones you need by googling the Oracle DB docs.
gotcha, I'll do some more investigation into that then..thanks!
Since you are using stored procedure, you should be able to filter out using certain parameters to bring down the data size