The cumulative sum or running total is one of the interesting problem. Most of the modern analytical database line Netezza, Teradata, Oracle, Vertica provides supports to analytical functions. You can make use of those analytical functions along with window specification to calculate cumulative sum and average. In this article, we will check how to calculate Vertica Cumulative Sum (running total) or cumulative average. Vertica Cumulative Sum As explained earlier, cumulative sum or a running total is the summation of a sequence of numbers which is updated each time a new…

The Vertica set operators are used to combine similar data sets from two or more SELECT statements. Here the similar data set literally means, the data type of the result set should also match, otherwise you have to explicitly use type conversion function for data columns when using set operators. The set operators in SQL are extensively used to combine results from various query sets. Based on your requirement, you can choose suitable Vertica set operators. Related Articles: Vertica WITH Clause, Syntax, Usage and ExamplesVertica Type Conversion Functions and Examples…

The NULL values are ‘unknown’ or unavailable values. You cannot treat these values like how you treat blank values. The data generation system may add NULL values if values are missing or unknown value. Vertica provides various NULL functions using those you can tell the database how to treat NULL values. In this article, we will check Vertica NULL handling functions usage with some examples. Vertica NULL Handling Functions As mentioned earlier, you can tell Vertica database on how to treat NULL values using NULL handling functions. Below is the…

A sequence in Vertica is named object in an individual Vertica database schema, which provides unique incremental values. In most of the cases, sequence in Vertica are used to generate unique numbers that can use used as a primary key or surrogate key. In this article, we will check what is Vertica sequence, how to create and use it, types of sequences and restriction on using sequences. Vertica Sequence Overview A sequence output value is an integer. You can use this integer values wherever you would use numeric values. You…

Vertica analytics data warehouse system supports various types of conversion functions. These Vertica type conversion functions are commonly used to changed data type of values stores in database tables. In SQL, type conversion is sometimes called type casting as well. These type conversion functions use common calling functions i.e. the first argument is the value to be formatted which will be either expression or table column name, and the second argument is a template that defines the output or input format. Related articles: Vertica Regular Expression Functions and ExamplesVertica Set…

Data warehouse basically integrates data from various heterogeneous sources. You may not receive perfect data every time you process or refresh data warehouse tables. It is very common to receive NULL values as most of the time source system will generate NULLs for any unknown or blank values. NULL values should be handled properly when you generate reports out of tables. In this article, we will check how to handle NULL values using Vertica NVL and NVL2 functions with some examples to demonstrate the same. Vertica NVL Function The NVL…