Coin Flip User Defined SQL Function
Jim_Knicely
- Select Field - Administrator
In the past when my wife and I couldn’t decide between which of two restaurants to grab dinner, we opened the Coin Flip app on one of our phones to let it decide.
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/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATEFUNCTIONSQLFunctions.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Mathematical/RANDOMINT.htm
Have fun!
0