vastorbit.sql.functions.NAN¶

vastorbit.sql.functions.NAN = CAST('NaN' AS DOUBLE)¶

This class is utilized to represent SQL strings, with VastColumn, 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 VastColumn.

Variables:

present. (No relevant attributes)

Examples

We import vastorbit:

import vastorbit as vo

Hint

By assigning an alias to vastorbit, we mitigate the risk of code collisions with other libraries. This precaution is necessary because vastorbit 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 vastorbit are used as intended without interfering with functions from other libraries.

Let’s create various StringSQL objects.

num1 = vo.StringSQL('numerical_col1', 'float')
num2 = vo.StringSQL('numerical_col2', 'int')
num3 = vo.StringSQL('numerical_col3', 'int')
str1 = vo.StringSQL('varchar_col1', 'text')
str2 = vo.StringSQL('varchar_col2', 'text')
bool1 = vo.StringSQL('bool_col1', 'bool')
bool2 = vo.StringSQL('bool_col2', 'bool')

The StringSQL representation is a straightforward string.

display(num1)
display(str1)
display(bool1)

Exploring Mathematical Operators¶

All mathematical operators are supported for numerical StringSQL.

import math

# Mathematical Functions
abs(num1)
round(num1, 3)
math.floor(num1)
math.ceil(num1)

# Unary Operators
- num1
+ num1
~ num1

# Binary Operators
num1 == num2
num1 != num2
num1 + num2
num1 - num2
num1 / num2
num1 // num2
num1 * num2
num1 ** num2
num2 % num3
num1 > num2
num1 >= num2
num1 < num2
num1 <= num2

# Extension
num1._between(num2, num3)

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
str1 != str2

# Concatenating two strings
str1 + str2

# Repeating a string
str1 * 3

Exploring Boolean Operators¶

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

# Equality and Inequality
bool1 == bool2
bool1 != bool2

# AND
bool1 & bool2

# OR
bool1 | bool2

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'])

# NOT IN
str1._not_in(['A', 'B', 'C'])

# DISTINCT
str1._distinct()

# ALIAS
str1._as('new_name')

Note

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

Using vastorbit SQL Functions¶

Numerous SQL functions are accessible in the vastorbit.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 vastorbit.sql.functions as vof
(num1 - vof.min(num1)._over([str1])) / (vof.max(num1)._over([str1]) - vof.min(num1)._over([str1]))

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)

# Example of a string expression
((str1 + str2) ** 2)._in(['ABAB', 'ACAC'])

Note

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

Examples Using VastFrame¶

VastColumn inherit from StringSQL, enabling operations on VastFrame in a pandas-like manner.

import vastorbit as vo

vdf = vo.VastFrame(
    {
        "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 VastFrame.

	123 num1 Integer 100%	123 num2 Integer 100%	123 num3 Integer 100%
1	20	10	15
2	30	15	23
3	40	20	30
4	10	5	8

For more examples, refer to the VastFrame class.