Redshift AVG Function: Syntax, Usage, and Examples 101

Redshift is a cloud data warehouse solution provided by Amazon. It is used by tens of thousands of customers from all over the world to store and analyze huge volumes of data, running up to exabytes. It uses AWS-designed hardware to give the best performance despite the size of the data. 

Redshift organizes data into tables, which are intersections between rows and columns. When using Redshift, you will want to get summaries from your data. This will help you to understand your data better and draw meaningful insights that can be helpful in decision-making. One of the ways to achieve this is by getting the average of the data stored in a table column.

The good thing is that with Redshift, you don’t have to calculate the average value manually, that is, by summing up all the values in that column and dividing the sum by the total number of items. Instead, Redshift provides the AVG function that you can call and give you the result quickly. In this article, we will be discussing the Redshift AVG function in detail. 

Table of Contents

• What is Amazon Redshift?
  • Key Features of Amazon Redshift
• What is the Redshift AVG Function?
  • Syntax
  • Supported Data Types
  • Examples
• Conclusion

Prerequisites

This is what you need for this article:

• An AWS account.

What is Amazon Redshift?

Amazon Redshift is essentially a storage system that allows companies to store petabytes of data across easily accessible “Clusters” that you can query in parallel. Every Amazon Redshift Data Warehouse is fully managed which means that the administrative tasks like maintenance backups, configuration, and security are completely automated. 

Amazon Redshift is primarily designed to work with Big Data and is easily scalable due to its modular node design. It also allows users to gain more granular insight into datasets, owing to the ability of Amazon Redshift Clusters to be further divided into slices. Amazon Redshift’s multi-layered architecture allows multiple queries to be processed simultaneously thus cutting down on waiting times. Apart from these, there are a few more benefits of Amazon Redshift that are covered in the following section.

Key Features of Amazon Redshift

• Enhanced Scalability: Amazon Redshift is known for providing consistently fast performance, even in the face of thousands of concurrent queries. Amazon Redshift Concurrency Scaling supports nearly unlimited concurrent queries and users. By leveraging Redshift’s managed storage, capacity is added to support workloads of up to 8 PB of compressed data. Scaling is just a simple API call, or a few clicks in the console away.    
• Easy Management: Amazon Redshift automates oft-repeated maintenance tasks so that you can focus on gathering actionable insights from your data. It is fairly simple to set up and operate. A new Data Warehouse can be deployed with just a few clicks in the AWS console. Key administrative tasks like backup and replication are automated. Data in Amazon Redshift is automatically backed up to Amazon S3. Amazon Redshift can replicate your snapshots to Amazon S3 asynchronously in a different region for disaster recovery. The Automatic Table Optimization selects the best distribution keys and sort method to enhance the performance efficacy for the cluster’s workload. Amazon Redshift also gives you the flexibility to work with queries in the console, or Business Intelligence tools, libraries, and SQL client tools.    
• Robust Security: Amazon Redshift is known for providing robust data security features at no extra cost. Amazon Redshift allows you to configure firewall rules to take control of network access to a specific Data Warehouse Cluster. Amazon Redshift also specializes in granular column and row-level security controls that ensure that users can only view data with the right type of access. Apart from these, Amazon Redshift also delivers on its promise of reliability and compliance through tokenization, end-to-end encryption, network isolation, and auditing.  
• Data Lake and AWS Integrated: Amazon Redshift allows you to work with data in various open formats that can easily integrate with the AWS ecosystem. Amazon Redshift makes it exceptionally easy to query and write data to your Data Lake in open formats such as JSON, ORC, CSV, Avro to name a few. The federated query capability allows you to query live data across multiple Aurora PostgreSQL and Amazon RDS databases to get enhanced visibility into the business operations. This is carried out without the need for any undesired data movement. The AWS Analytics ecosystem allows you to handle end-to-end analytics workflows without any hiccups. You can also bring in data from various applications like Google Analytics, Facebook Ads, Salesforce to an Amazon Redshift Data Warehouse in a streamlined manner.     
• Flexible Performance: Amazon Redshift distinguishes itself by offering swift, industry-leading performance with a keen focus on flexibility. This is made possible through result caching, materialized views, efficient storage, RA3 instances, and high-performance query processing to name a few. Result Caching is used to deliver sub-second response times for repeat queries. Business Intelligence tools, dashboards, visualizations leveraging repeat queries experience a significant performance boost. At the time of execution, Amazon Redshift looks through the cache to see if there is a cached result for repeat queries. Amazon Redshift also uses sophisticated algorithms to classify and predict the incoming queries based on their run times and resource requirements to manage concurrency and performance dynamically. This helps users prioritize business-critical workloads.   

What is the Redshift AVG Function?

The Redshift avg function calculates the average or the arithmetic mean of the values passed in an input expression. This function is only applicable to numeric values. It also ignores NULL values. 

To calculate the average of data values manually, one has to calculate their sum and divide it by the total number of values. This can take a lengthy time, or even be impossible when dealing with huge volumes of records, running up to millions. The manual calculation of the average for values is also prone to errors. 

The Redshift avg function saves you from the above. You only have to invoke the function and it will return the correct results to you. 

Next, we will be discussing the Redshift avg function syntax:

Syntax

The Redshift avg function takes the following syntax:

AVG ( [DISTINCT | ALL] expression )

The above parameters are described below:

• Expression: This is the target expression or table column on which the avg function is to be applied. 
• DISTINCT | ALL: When the Redshift AVG function is used with the DISTINCT clause, it will eliminate all the duplicate values from the expression before it can compute the average. If the function is used with the ALL clause, all duplicate values will be retained during the calculation of the average. Note that the function uses ALL as the default. This means that since this clause is optional if you don’t specify it when calling the avg function, ALL will be used by default. 

Supported Data Types

The Redshift avg function supports different types of numeric data types. These include SMALLINT, BIGINT, INTEGER, DECIMAL, NUMERIC, REAL, and DOUBLE PRECISION. 

The avg function supports the following return types:

• BIGINT for integer-type arguments.
• DOUBLE PRECISION for floating-point arguments.

By default, the Redshift avg function uses a precision of 19 for a DECIMAL or 64-bit NUMERIC argument. For a result with a 128-bit DECIMAL or NUMERIC argument, the function uses a precision of  38. Both the argument and the result take the same precision. 

Examples

Let us use the Redshift SALES table to demonstrate how the Redshift avg function works. To know the average quantity sold per transaction, we can run the following query:

select avg(qtysold) from sales;

In the above query, we have called the Redshift avg function and passed an argument to it. The argument, in this case, is a column of the SALES table, that is, the qtysold column. The function will calculate and return the average of the values stored in that column. 

We can also calculate the average amount each customer spends when purchasing from the store. In this case, we should consider the pricepaid column as shown below:

select avg(pricepaid) from sales;

The query will return the average amount that a customer spends in the shop. We simply invoked the Redshift avg function and passed the name of the column to it as the argument. 

Let us find the average price that was paid and group it by month in descending order:

select avg(pricepaid) as average_price, month 
from sales, date
where sales.dateid = date.dateid
group by month
order by average_price desc;

The query should return the average price for each month. 

We can also use the LISTING table to demonstrate how the avg function works. The following query demonstrates how to know the average total price that has been listed for all the listings:

select avg(numtickets*priceperticket) as average_total_price from listing;

We can also use the Redshift avg function to calculate the moving average. This is a very important metric as it helps you to track the average value over some time. For example, the average number of sales over the past 10 days. The moving average gives a better trend than plotting the daily numbers. 

However, Redshift doesn’t come with a specific function for calculating the moving average, but we can use its avg function for this. 

Let’s create a new table to demonstrate this:

create table sales (order_date date, amount int);

Let us insert some rows into the table:

insert into sales values ('2022-01-01',22), 
('2022-01-02', 24), ('2022-01-03', 17), ('2022-01-04', 32),
('2022-01-05', 20), ('2022-01-10', 18), ('2022-01-06', 26), 
('2022-01-07', 13), ('2022-01-08', 28), ('2022-01-09', 24);

Suppose we need to know the moving average for the past 5 days. We can achieve this using the Redshift window functions. The following query shows how to calculate the moving average over the past 5 days:

SELECT a.order_date,a.amount, 
       AVG(a.amount)
            OVER(ORDER BY a.order_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS average_sales
FROM sales a ;

You can also round the results when calculating the moving average. You only have to use the ROUND function as shown below:

SELECT a.order_date, a.amount, 
       round(AVG(a.amount)
            OVER(ORDER BY a.order_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW), 2) AS average_sales
FROM sales a;

Note that in the above two queries, we have combined the Redshift avg function with the OVER window function to calculate the average value for the specified window. 

We have also used the ORDER BY clause to ensure that the rows are sorted chronologically. 

That is how the Redshift avg function works. 

Conclusion

In this article, you’ve learned that Redshift is a cloud data warehouse solution provided by Amazon. It helps users to store and analyze huge volumes of data, running up to exabytes. This article also elicits the Redshift avg function that calculates the average or the arithmetic mean of the values passed to it as arguments. The avg function can only be applied on numeric values and it ignores NULLs. 

The avg function can be used together with DISTINCT and ALL clauses. When used with the DISTINCT clause, it eliminates all duplicate values when calculating the average. When used with the ALL clause, duplicate values will be considered in the calculation of the average. By default, it uses the ALL clause.

Extracting complex data from a diverse set of data sources to carry out an insightful analysis can be challenging, and this is where Hevo saves the day! Hevo offers a faster way to move data from Databases or SaaS applications into your Data Warehouses like Amazon Redshift to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.

VISIT OUR WEBSITE TO EXPLORE HEVO

Want to take Hevo for a spin?

SIGN UP and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.