verticapy.sql.functions.PI#
- verticapy.sql.functions.PI = PI()#
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 fromverticapy
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 twoStringSQL
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 themin
andmax
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 anorder_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 fromStringSQL
, enabling operations onvDataFrame
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
.123num1Integer100%... 123num2Integer100%123num3Numeric(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.