Built in functions

List of Built-in SQL functions.

Numpy functions

np.*

np.* - All the functions from the numpy package are supported by default via the np.* namespace.

For example:

select np.log(total) from passengers
select np.power(np.min(size), 3) as cubed from measurements

Type cast functions

to_bool

to_bool(arg) - Casts argument to bool type.

to_float

to_float(arg) - Casts argument to float type.

to_int

to_int(arg) - Casts argument to int type.

to_str

to_str(arg) - Casts argument to str type.

Math functions

abs

abs(arg) - returns the absolute value of the numerical argument.

See: numpy.absolute

sqrt

sqrt(arg) - returns the square root of the numerical argument.

See: numpy.sqrt

cos

cos(arg) - returns the cosine of the argument.

See: numpy.cos

sin

sin(arg) - returns the sine of the argument.

See: numpy.sin

tan

tan(arg) - returns the tangent of the argument.

See: numpy.tan

power

power(arg, power) - returns the argument(s) raised to the power.

See: numpy.power

log

log(arg) - returns the natural log of the argument.

See: numpy.log

log2

log2(arg) - returns the log base 2 of the argument.

See: numpy.log2

log10

log10(arg) - returns the log base 10 of the argument.

See: numpy.log10

Math constants

e

e() - returns the e constant.

pi

pi() - returns the pi constant.

String functions

concat

concat(arg1, arg2, …) - concatenate string arguments.

If argument is not a string type, would be converted to string.

See: numpy.char.add

upper

upper(arg) - convert a string to uppercase.

See: numpy.char.upper

lower

lower(arg) - convert a string to lowercase.

See: numpy.char.lower

Datetime functions

now

now() - returns current datetime.

Returns current time as a datetime with seconds resolution.

date

date(arg) - converts the argument to date type.

Input is either a string in ISO8601 format or integer timestamp.

Use date(‘now’) for current date.

See: numpy.datetime

datetime

datetime(arg, unit) - converts the argument to datetime type.

Input is either a string in ISO8601 format or integer timestamp.

Supported units are: [‘D’, ‘s’, ‘ms’, ‘us’, ‘ns’]

‘D’ - days
‘s’ - seconds
‘ms’ - milliseconds
‘us’ - microseconds
‘ns’ - nanoseconds

Use datetime(‘now’) for current datetime.

See: numpy.datetime

from_timestamp

from_timestamp(arg, unit) - converts the integer timestamp to datetime type. Argument represents integer value of the timestamp, ie number of seconds (or milliseconds) since epoch.

Supported units are : [‘s’, ‘ms’, ‘us’, ‘ns’]

‘s’ - seconds
‘ms’ - milliseconds
‘us’ - microseconds
‘ns’ - nanoseconds

See: numpy.datetime

timedelta

timedelta(arg, unit) - returns the timedelta type. Argument represents the duration.

Supported units are : [‘Y’, ‘M’, ‘W’, ‘D’, ‘h’, ‘m’, ‘s’, ‘ms’, ‘us’, ‘ns’]

‘Y’ - years
‘M’ - months
‘W’ - weeks
‘D’ - days
‘h’ - hours
‘m’ - minutes
‘s’ - seconds
‘ms’ - milliseconds
‘us’ - microseconds
‘ns’ - nanoseconds

See: numpy.datetime.timedelta

is_busday

is_busday(arg) - returns True if the argument is a ‘business’ day.

See: numpy.datetime.is_busday

Aggregate functions

count

count(*) - returns the number of all rows in the group.
count(expr | column) - returns the number of non-null rows in the group.

min

min(expr | column) - returns the minimum value in the group.

max

max(expr | column) - returns the maximum value in the group.

sum

sum(expr | column) - returns the sum of the values in the group.

avg

avg(expr | column) - returns the arithmetic mean of the values in the group.