Aggregate values into an array for a group of rows in Redshift using ARRAY_AGG()

Amazon Redshift, a robust data warehousing solution, offers an indispensable tool for this purpose – the ARRAY_AGG() function. In this in-depth article, we will explore the ARRAY_AGG() function, covering its Data Definition Language (DDL) and Data Manipulation Language (DML) usage, with practical examples. Additionally, we will delve into real-world use cases to demonstrate its significance.

Let’s create a sample table called freshers_in_webstats to illustrate the usage of the ARRAY_AGG() function.

CREATE TABLE freshers_in_webstats (
    date DATE,
    pageviews INT,
    visitors INT
);

Our table includes three columns: date, pageviews, and visitors, which represent the date of the statistics, the number of pageviews, and the number of visitors, respectively.

Let’s insert some sample data into our freshers_in_webstats table for demonstration:

INSERT INTO freshers_in_webstats (date, pageviews, visitors)
VALUES
    ('2023-09-01', 1000, 500),
    ('2023-09-02', 1200, 600),
    ('2023-09-03', 1500, 750),
    ('2023-09-04', 800, 400),
    ('2023-09-05', 1300, 650);

With this data, we now have a dataset that spans five days of web statistics.

Understanding ARRAY_AGG()

The ARRAY_AGG() function is used to aggregate values from multiple rows into an array. It is particularly helpful when you need to collect multiple related values into a single array for analysis or reporting. The basic syntax of ARRAY_AGG() is as follows:

ARRAY_AGG(column_name) OVER (PARTITION BY partition_column ORDER BY ordering_column)

column_name: The column whose values you want to aggregate into an array.
PARTITION BY: An optional clause that divides the result set into partitions.
ORDER BY: An optional clause that specifies the order of values in the resulting array.

Real-World use case: Aggregating user actions

Let’s explore a real-world use case where the ARRAY_AGG() function can provide valuable insights. Suppose you are analyzing user actions on a website and want to aggregate the actions taken by each user on different dates.

Here’s how you can use the ARRAY_AGG() function to accomplish this:

SELECT
    date,
    visitors,
    ARRAY_AGG(pageviews) AS pageviews_history
FROM
    freshers_in_webstats
GROUP BY
    date, visitors
ORDER BY
    date;

In this SQL query:

We select the date and visitors columns.
We apply the ARRAY_AGG() function to aggregate the pageviews values into an array called pageviews_history.
We group the results by date and visitors to collect user actions for each date and user.
We order the results by date for clarity.

Result :

    date       | visitors | pageviews_history
--------------+----------+-------------------
  2023-09-01  |   500    | {1000}
  2023-09-02  |   600    | {1200}
  2023-09-03  |   750    | {1500}
  2023-09-04  |   400    | {800}
  2023-09-05  |   650    | {1300}

The pageviews_history column now contains arrays of pageviews for each user on different dates. This information can be used to analyze user engagement trends over time and identify patterns.

Applications of ARRAY_AGG()

The ARRAY_AGG() function has several practical applications:

Data Summarization: It allows you to summarize and collect related data points into arrays for better analysis and reporting.
User Behavior Analysis: In e-commerce or online platforms, it can help analyze user actions and behavior over time.
Data Transformation: It’s useful for reshaping data into a more manageable format for downstream processes.

Read more on Redshift
Read more on Hive
Read more on Snowflake