verticapy.sql.functions.E#

verticapy.sql.functions.E = EXP(1)#

This class is utilized to represent SQL strings, with vDataColumn, for instance, inheriting from this class. Its purpose is to streamline SQL operations in Python, enabling the use of Python operators with specific SQL strings to simplify operations and enhance the usability of the generated SQL.

Parameters#

alias: str: Name of the StringSQL.
category: str, optional: Category of the StringSQL. This parameter is crucial for performing accurate operations. For instance, it plays a significant role in distinguishing between the treatment of floats and text data.
init_transf: str, optional: Initial Transformation. It is employed to streamline certain operations on vDataColumn.

Attributes#

No relevant attributes present.

Examples#

We import verticapy:

import verticapy as vp

Hint

By assigning an alias to verticapy, we mitigate the risk of code collisions with other libraries. This precaution is necessary because verticapy uses commonly known function names like “average” and “median”, which can potentially lead to naming conflicts. The use of an alias ensures that the functions from verticapy are used as intended without interfering with functions from other libraries.

Let’s create various StringSQL objects.

num1 = vp.StringSQL('numerical_col1', 'float')

num2 = vp.StringSQL('numerical_col2', 'int')

num3 = vp.StringSQL('numerical_col3', 'int')

str1 = vp.StringSQL('varchar_col1', 'text')

str2 = vp.StringSQL('varchar_col2', 'text')

bool1 = vp.StringSQL('bool_col1', 'bool')

bool2 = vp.StringSQL('bool_col2', 'bool')

The StringSQL representation is a straightforward string.

display(num1)
numerical_col1

display(str1)
varchar_col1

display(bool1)
bool_col1

Exploring Mathematical Operators#

All mathematical operators are supported for numerical StringSQL.

import math

# Mathematical Functions
abs(num1)
Out[13]: ABS(numerical_col1)

round(num1, 3)
Out[14]: ROUND(numerical_col1, 3)

math.floor(num1)
Out[15]: FLOOR(numerical_col1)

math.ceil(num1)
Out[16]: CEIL(numerical_col1)

# Unary Operators
- num1
Out[17]: -(numerical_col1)

+ num1
Out[18]: +(numerical_col1)

~ num1
Out[19]: -(numerical_col1) - 1

# Binary Operators
num1 == num2
Out[20]: (numerical_col1) = (numerical_col2)

num1 != num2
Out[21]: (numerical_col1) != (numerical_col2)

num1 + num2
Out[22]: (numerical_col1) + (numerical_col2)

num1 - num2
Out[23]: (numerical_col1) - (numerical_col2)

num1 / num2
Out[24]: (numerical_col1) / (numerical_col2)

num1 // num2
Out[25]: (numerical_col1) // (numerical_col2)

num1 * num2
Out[26]: (numerical_col1) * (numerical_col2)

num1 ** num2
Out[27]: POWER(numerical_col1, numerical_col2)

num2 % num3
Out[28]: MOD(numerical_col2, numerical_col3)

num1 > num2
Out[29]: (numerical_col1) > (numerical_col2)

num1 >= num2
Out[30]: (numerical_col1) >= (numerical_col2)

num1 < num2
Out[31]: (numerical_col1) < (numerical_col2)

num1 <= num2
Out[32]: (numerical_col1) <= (numerical_col2)

# Extension
num1._between(num2, num3)
Out[33]: (numerical_col1) BETWEEN (numerical_col2) AND (numerical_col3)

Note

Most mathematical operators can be applied to the date datatype.

Exploring String Operators#

A lot of operators are supported for text data-type StringSQL.

# Equality and Inequality
str1 == str2
Out[34]: (varchar_col1) = (varchar_col2)

str1 != str2
Out[35]: (varchar_col1) != (varchar_col2)

# Concatenating two strings
str1 + str2
Out[36]: (varchar_col1) || (varchar_col2)

# Repeating a string
str1 * 3
Out[37]: REPEAT(varchar_col1, 3)

Exploring Boolean Operators#

A lot of operators are supported for boolean data-type StringSQL.

# Equality and Inequality
bool1 == bool2
Out[38]: (bool_col1) = (bool_col2)

bool1 != bool2
Out[39]: (bool_col1) != (bool_col2)

# AND
bool1 & bool2
Out[40]: (bool_col1) AND (bool_col2)

# OR
bool1 | bool2
Out[41]: (bool_col1) OR (bool_col2)

Important

The ‘&’ and ‘|’ operators in StringSQL are distinct from the Python ‘and’ and ‘or’ operators.

In Python, ‘and’ and ‘or’ are logical operators used for boolean expressions. They perform short -circuit evaluation, meaning that the second operand is only evaluated if necessary.

In StringSQL, ‘&’ and ‘|’ are used for boolean concatenation, not logical operations. They combine two StringSQL without short-circuiting, meaning both sides of the operator are always evaluated.

General Operators#

Some general operators are available for all types.

# IN
str1._in(['A', 'B', 'C'])
Out[42]: (varchar_col1) IN ('A', 'B', 'C')

# NOT IN
str1._not_in(['A', 'B', 'C'])
Out[43]: (varchar_col1) NOT IN ('A', 'B', 'C')

# DISTINCT
str1._distinct()
Out[44]: DISTINCT (varchar_col1)

# ALIAS
str1._as('new_name')
Out[45]: (varchar_col1) AS new_name

Note

The result is a StringSQL object that can be reused iteratively until we obtain the final SQL statement.

Using VerticaPy SQL Functions#

Numerous SQL functions are accessible in the verticapy.sql.functions module. In this example, we will utilize the min and max aggregations to normalize the ‘num1’ column. We will utilize a partition by ‘str1’ to normalize within this specific partition.

import verticapy.sql.functions as vpf

(num1 - vpf.min(num1)._over([str1])) / (vpf.max(num1)._over([str1]) - vpf.min(num1)._over([str1]))
Out[47]: ((numerical_col1) - (MIN(numerical_col1) OVER (PARTITION BY varchar_col1 ))) / ((MAX(numerical_col1) OVER (PARTITION BY varchar_col1 )) - (MIN(numerical_col1) OVER (PARTITION BY varchar_col1 )))

Note

The _over operator also includes an order_by parameter for sorting using a specific column.

Combining the different Operators#

It is possible to combine as many operators as desired, as long as they adhere to SQL logic. This allows the building of SQL expressions using a Pythonic structure.

# Example of a numerical expression
round(abs((num1 ** (num2 + num3)) - 15), 2)
Out[48]: ROUND(ABS((POWER(numerical_col1, (numerical_col2) + (numerical_col3))) - (15)), 2)

# Example of a string expression
((str1 + str2) ** 2)._in(['ABAB', 'ACAC'])
Out[49]: (POWER((varchar_col1) || (varchar_col2), 2)) IN ('ABAB', 'ACAC')

Note

It is recommended to use these operators to construct VerticaPy code, as they adapt seamlessly to the corresponding SQL database. These functionalities are easily maintainable and extendable.

Examples Using vDataFrame#

vDataColumn inherit from StringSQL, enabling operations on vDataFrame in a pandas-like manner.

import verticapy as vp

vdf = vp.vDataFrame(
    {
        "num1": [10, 20, 30, 40],
        "num2": [5, 10, 15, 20],
    },
)


vdf["num3"] = abs(vdf["num1"] * 2 - 5 * vdf["num2"] / 2)

Let’s display the result. It is noteworthy that the generated SQL was applied and utilized by the vDataFrame.

	123 num1 Integer 100%	...	123 num2 Integer 100%	123 num3 Numeric(38) 100%
1	10	...	5	7.5
2	20	...	10	15.0
3	30	...	15	22.5
4	40	...	20	30.0

For more examples, refer to the vDataFrame class.