
Now I let Vertica choose for us via a simple User Defined SQL Function!
Example:
dbadmin=> CREATE OR REPLACE FUNCTION coin_flip() RETURN VARCHAR(5)
dbadmin-> AS
dbadmin-> BEGIN
dbadmin-> RETURN CASE RANDOMINT(2)
dbadmin-> WHEN 0 THEN 'HEADS'
dbadmin-> WHEN 1 THEN 'TAILS'
dbadmin-> END;
dbadmin-> END;
CREATE FUNCTION
dbadmin=> SELECT coin_flip, COUNT(*) FROM (SELECT coin_flip() AS coin_flip FROM big_table LIMIT 100000) foo GROUP BY coin_flip;
coin_flip | COUNT
-----------+-------
TAILS | 49899
HEADS | 50101
(2 rows)
dbadmin=> SELECT coin_flip, COUNT(*) FROM (SELECT coin_flip() AS coin_flip FROM big_table LIMIT 100000) foo GROUP BY coin_flip;
coin_flip | COUNT
-----------+-------
TAILS | 49848
HEADS | 50152
(2 rows)
Helpful Links:
https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATEFUNCTIONSQLFunctions.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/Mathematical/RANDOMINT.htm
Have fun!