Permalink
Switch branches/tags
Nothing to show
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
643 lines (552 sloc) 18.5 KB
/*
JSON_ARRAY_FIRST
Returns the first element of a JSON array as a string
Examples:
select json_array_first('["a", "b"]') --> 'a'
select json_array_first('[1, 2, 3]') --> '1'
*/
create or replace function json_array_first (j varchar(max))
returns varchar(max)
stable as $$
import json
if not j:
return None
try:
arr = json.loads(j)
except ValueError:
return None
if len(arr) == 0:
return None
return str(arr[0])
$$ language plpythonu;
/*
JSON_ARRAY_LAST
Returns the last element of a JSON array as a string
Examples:
select json_array_last('["a", "b"]') --> 'b'
select json_array_last('[1, 2, 3]') --> '3'
*/
create or replace function json_array_last (j varchar(max))
returns varchar(max)
stable as $$
import json
if not j:
return None
try:
arr = json.loads(j)
except ValueError:
return None
if len(arr) == 0:
return None
return str(arr[-1])
$$ language plpythonu;
/*
JSON_ARRAY_NTH
Returns the Nth 0-indexed element of a JSON array as a string
Examples:
select json_array_nth('["a", "b"]', 0) --> 'a'
select json_array_nth('[1, 2, 3]', 1) --> '2'
*/
create or replace function json_array_nth (j varchar(max), i integer)
returns varchar(max)
stable as $$
import json
if not j or (not i and i != 0) or i < 0:
return None
try:
arr = json.loads(j)
except ValueError:
return None
if len(arr) <= i:
return None
return str(arr[i])
$$ language plpythonu;
/*
JSON_ARRAY_SORT
Returns sorts a JSON array and returns it as a string, second param sets direction
Examples:
select json_array_sort('["a","c","b"]', true) --> '["a", "b", "c"]'
select json_array_sort('[1, 3, 2]', true) --> '[1, 2, 3]'
*/
create or replace function json_array_sort (j varchar(max), ascending boolean)
returns varchar(max)
stable as $$
import json
if not j:
return None
try:
arr = json.loads(j)
except ValueError:
return None
if not ascending:
arr = sorted(arr, reverse=True)
else:
arr = sorted(arr)
return json.dumps(arr)
$$ language plpythonu;
/*
JSON_ARRAY_REVERSE
Reverses a JSON array and returns it as a string
Examples:
select json_array_reverse('["a","c","b"]') --> '["b", "c", "a"]'
select json_array_reverse('[1, 3, 2]') --> '[2, 3, 1]'
*/
create or replace function json_array_reverse (j varchar(max))
returns varchar(max)
stable as $$
import json
if not j:
return None
try:
arr = json.loads(j)
except ValueError:
return None
return json.dumps(arr[::-1])
$$ language plpythonu;
/*
JSON_ARRAY_POP
Removes the last element from a JSON array and returns the remaining array as a string
Examples:
select json_array_pop('["a","c","b"]') --> '["a", "c"]'
select json_array_pop('[1, 3, 2]') --> '[1, 3]'
*/
create or replace function json_array_pop (j varchar(max))
returns varchar(max)
stable as $$
import json
if not j:
return None
try:
arr = json.loads(j)
except ValueError:
return None
if len(arr) > 0:
arr.pop()
return json.dumps(arr)
$$ language plpythonu;
/*
JSON_ARRAY_PUSH
Adds a new element to a JSON array and returns the new array as a string
Examples:
select json_array_push('["a","c","b"]', 'd') --> '["a", "c", "b", "d"]'
select json_array_push('[1, 3, 2]', '4') --> '[1, 3, 2, "4"]'
*/
create or replace function json_array_push (j varchar(max), value varchar(max))
returns varchar(max)
stable as $$
import json
if not j:
arr = []
else:
try:
arr = json.loads(j)
except ValueError:
arr = []
arr.append(value)
return json.dumps(arr)
$$ language plpythonu;
/*
JSON_ARRAY_CONCAT
Concatenates two JSON arrays and returns the new array as a string
Examples:
select json_array_concat('["a","c","b"]', '["d","e"]') --> '["a", "c", "b", "d", "e"]'
select json_array_concat('[1, 3, 2]', '[4]') --> '[1, 3, 2, 4]'
*/
create or replace function json_array_concat (j varchar(max), k varchar(max))
returns varchar(max)
stable as $$
import json
if not j:
return k
if not k:
return j
try:
arr_j = json.loads(j)
arr_k = json.loads(k)
except ValueError:
return None
arr_j.extend(arr_k)
return json.dumps(arr_j)
$$ language plpythonu;
/*
MYSQL_YEAR
Extract the year from a datetime
Examples:
select mysql_year('2015-01-03T04:05:06.07'::timestamp) --> 2015
select mysql_year('2016-02-04T04:05:06.07 -07'::timestamp) --> 2016
select mysql_year('2017-03-05'::timestamp) --> 2017
*/
create or replace function mysql_year (ts timestamp)
returns integer
stable as $$
if not ts:
return None
return ts.year
$$ language plpythonu;
/*
MYSQL_MONTH
Extract the month from a datetime
Examples:
select mysql_month('2015-01-03T04:05:06.07'::timestamp) --> 1
select mysql_month('2016-02-04T04:05:06.07 -07'::timestamp) --> 2
select mysql_month('2016-03-05'::timestamp) --> 3
*/
create or replace function mysql_month (ts timestamp)
returns integer
stable as $$
if not ts:
return None
return ts.month
$$ language plpythonu;
/*
MYSQL_DAY
Extract the day from a datetime
Examples:
select mysql_day('2015-02-03T04:05:06.07'::timestamp) --> 3
select mysql_day('2016-02-04T04:05:06.07 -07'::timestamp) --> 4
select mysql_day('2016-02-05'::timestamp) --> 5
*/
create or replace function mysql_day (ts timestamp)
returns integer
stable as $$
if not ts:
return None
return ts.day
$$ language plpythonu;
/*
MYSQL_HOUR
Extract the hour from a datetime
Examples:
select mysql_hour('2015-02-03T04:05:06.07'::timestamp) --> 4
select mysql_hour('2016-02-03T04:05:06.07 -07'::timestamp) --> 4
select mysql_hour('2016-02-03'::timestamp) --> 0
*/
create or replace function mysql_hour (ts timestamp)
returns integer
stable as $$
if not ts:
return None
return ts.hour
$$ language plpythonu;
/*
MYSQL_MINUTE
Extract the minute from a datetime
Examples:
select mysql_minute('2015-02-03T04:05:06.07'::timestamp) --> 5
select mysql_minute('2016-02-03T04:15:06.07 -07'::timestamp) --> 15
select mysql_minute('2016-02-03'::timestamp) --> 0
*/
create or replace function mysql_minute (ts timestamp)
returns integer
stable as $$
if not ts:
return None
return ts.minute
$$ language plpythonu;
/*
MYSQL_SECOND
Extract the second from a datetime
Examples:
select mysql_second('2015-02-03T04:05:06.07'::timestamp) --> 6
select mysql_second('2016-02-03T04:15:16.07 -07'::timestamp) --> 16
select mysql_second('2016-02-03'::timestamp) --> 0
*/
create or replace function mysql_second (ts timestamp)
returns integer
stable as $$
if not ts:
return None
return ts.second
$$ language plpythonu;
/*
MYSQL_YEARWEEK
Extract the week of the year from a datetime
Examples:
select mysql_yearweek('2015-02-03T04:05:06.07'::timestamp) --> '201506'
select mysql_yearweek('2016-02-03T04:15:16.07 -07'::timestamp) --> '201605'
select mysql_yearweek('2016-02-03'::timestamp) --> '201605'
*/
create or replace function mysql_yearweek (ts timestamp)
returns varchar(max)
stable as $$
if not ts:
return None
cal = ts.isocalendar()
return str(cal[0]) + str(cal[1]).zfill(2)
$$ language plpythonu;
/*
NOW
Returns the current time as a timestamp in UTC
Examples:
select now() --> '2015-03-30 21:32:15.553489+00'
*/
create or replace function now ()
returns timestamp
stable as $$
from datetime import datetime
datetime.utcnow()
$$ language plpythonu;
/*
POSIX_TIMESTAMP
Returns the number of seconds from 1970-01-01 for this timestamp
Examples:
select posix_timestamp('2015-03-30 21:32:15'::timestamp) --> '1427751521.107629'
*/
create or replace function posix_timestamp (ts timestamp)
returns real
stable as $$
from datetime import datetime
if not ts:
return None
return (ts - datetime(1970, 1, 1)).total_seconds()
$$ language plpythonu;
/*
EMAIL_NAME
Gets the part of the email address before the @ sign
Examples:
select email_name('sam@company.com') --> 'sam'
select email_name('alex@othercompany.com') --> 'alex'
*/
create or replace function email_name (email varchar(max))
returns varchar(max)
stable as $$
if not email:
return None
return email.split('@')[0]
$$ language plpythonu;
/*
EMAIL_DOMAIN
Gets the part of the email address after the @ sign
Examples:
select email_domain('sam@company.com') --> 'company.com'
select email_domain('alex@othercompany.com') --> 'othercompany.com'
*/
create or replace function email_domain (email varchar(max))
returns varchar(max)
stable as $$
if not email:
return None
return email.split('@')[-1]
$$ language plpythonu;
/*
URL_PROTOCOL
Gets the protocol of the URL
Examples:
select url_protocol('http://www.google.com/a') --> 'http'
select url_protocol('https://gmail.com/b') --> 'https'
select url_protocol('sftp://company.com/c') --> 'sftp'
*/
create or replace function url_protocol (url varchar(max))
returns varchar(max)
stable as $$
from urlparse import urlparse
if not url:
return None
try:
u = urlparse(url)
return u.scheme
except ValueError:
return None
$$ language plpythonu;
/*
URL_DOMAIN
Gets the domain (and subdomain if present) of the URL
Examples:
select url_domain('http://www.google.com/a') --> 'www.google.com'
select url_domain('https://gmail.com/b') --> 'gmail.com'
*/
create or replace function url_domain (url varchar(max))
returns varchar(max)
stable as $$
from urlparse import urlparse
if not url:
return None
try:
u = urlparse(url)
return u.netloc
except ValueError:
return None
$$ language plpythonu;
/*
URL_PATH
Gets the domain (and subdomain if present) of the URL
Examples:
select url_path('http://www.google.com/search/images?query=bob') --> '/search/images'
select url_path('https://gmail.com/mail.php?user=bob') --> '/mail.php'
*/
create or replace function url_path (url varchar(max))
returns varchar(max)
stable as $$
from urlparse import urlparse
if not url:
return None
try:
u = urlparse(url)
return u.path
except ValueError:
return None
$$ language plpythonu;
/*
URL_PARAM
Extract a parameter from a URL
Examples:
select url_param('http://www.google.com/search/images?query=bob', 'query') --> 'bob'
select url_param('https://gmail.com/mail.php?user=bob&account=work', 'user') --> 'bob'
*/
create or replace function url_param (url varchar(max), param varchar(max))
returns varchar(max)
stable as $$
import urlparse
if not url:
return None
try:
u = urlparse.urlparse(url)
return urlparse.parse_qs(u.query)[param][0]
except KeyError:
return None
$$ language plpythonu;
/*
SPLIT_COUNT
Split a string on another string and count the members
Examples:
select split_count('foo,bar,baz', ',') --> 3
select split_count('foo', 'bar') --> 1
*/
create or replace function split_count (str varchar(max), delim varchar(max))
returns int
stable as $$
if not str or not delim:
return None
return len(str.split(delim))
$$ language plpythonu;
/*
TITLECASE
Format a string as titlecase
Examples:
select titlecase('this is a title') --> 'This Is A Title'
select titlecase('Already A Title') --> 'Already A Title'
*/
create or replace function titlecase (str varchar(max))
returns varchar(max)
stable as $$
if not str:
return None
return str.title()
$$ language plpythonu;
/*
STR_MULTIPLY
Repeat a string N times
Examples:
select str_multiply('*', 10) --> '**********'
select str_multiply('abc ', 3) --> 'abc abc abc '
*/
create or replace function str_multiply (str varchar(max), times integer)
returns varchar(max)
stable as $$
if not str:
return None
return str * times
$$ language plpythonu;
/*
STR_INDEX
Find the index of the first occurrence of a substring, or -1 if not found
Examples:
select str_index('Apples Oranges Pears', 'Oranges') --> 7
select str_index('Apples Oranges Pears', 'Bananas') --> -1
*/
create or replace function str_index (full_str varchar(max), find_substr varchar(max))
returns integer
stable as $$
if not full_str or not find_substr:
return None
return full_str.find(find_substr)
$$ language plpythonu;
/*
STR_RINDEX
Find the index of the last occurrence of a substring, or -1 if not found
Examples:
select str_rindex('A B C A B C', 'C') --> 10
select str_rindex('Apples Oranges Pears Oranges', 'Oranges') --> 21
select str_rindex('Apples Oranges', 'Bananas') --> -1
*/
create or replace function str_rindex (full_str varchar(max), find_substr varchar(max))
returns integer
stable as $$
if not full_str or not find_substr:
return None
return full_str.rfind(find_substr)
$$ language plpythonu;
/*
STR_COUNT
Counts the number of occurrences of a substring within a string
Examples:
select str_count('abbbc', 'b') --> 3
select str_count('Apples Bananas', 'an') --> 2
select str_count('aaa', 'A') --> 0
*/
create or replace function str_count (full_str varchar(max), find_substr varchar(max))
returns integer
stable as $$
if not full_str or not find_substr:
return None
return full_str.count(find_substr)
$$ language plpythonu;
/*
FORMAT_NUM
Format a number with Python's string format notation
Examples:
select format_num(2.17189, '.2f') --> '2.17'
select format_num(2, '0>4d') --> '0002'
select format_num(1234567.89, ',') --> '1,234,567.89'
select format_num(0.1234, '.2%') --> '12.34%'
*/
create or replace function format_num (num float, format varchar)
returns varchar
stable as $$
if not num or not format:
return None
try:
return ("{:" + format + "}").format(num)
except ValueError:
try:
return ("{:" + format + "}").format(int(num))
except ValueError:
return None
$$ language plpythonu;
/*
EXPERIMENT_RESULT_P_VALUE
Returns a p-value for a controlled experiment to determine statistical significance
Examples:
select round(experiment_result_p_value(5000,486, 5000, 527),3) --> 0.185
select case when experiment_result_p_value(5000,486, 5000, 527) < 0.05 then 'yes' else 'no' end as signifcant --> 'no'
select experiment_result_p_value(20000,17998,20000, 17742) --> 3.57722238820663e-05
select case when experiment_result_p_value(20000,17998,20000, 17742) < 0.05 then 'yes' else 'no' end as significant --> 'yes'
*/
create or replace function experiment_result_p_value (control_size float, control_conversion float, experiment_size float, experiment_conversion float)
returns float
stable as $$
from scipy.stats import chi2_contingency
from numpy import array
observed = array([
[control_size - control_conversion, control_conversion],
[experiment_size - experiment_conversion, experiment_conversion]
])
result = chi2_contingency(observed, correction=True)
chisq, p = result[:2]
return p
$$ language plpythonu;
/*
REMOVE_ACCENTS
Remove accents from a string
Examples:
select remove_accents('café') --> 'cafe'
*/
create or replace function remove_accents (str varchar(max))
returns varchar(max)
stable as $$
import unicodedata
if not str:
return None
str = str.decode('utf-8')
return unicodedata.normalize('NFKD', str).encode('ASCII', 'ignore')
$$ language plpythonu;