Using WITH ROLLUP to Efficiently Calculate SQL Percentages

Query optimization is an essential part of working with big data—otherwise, you’ll run into issues with long-running queries, potential timeouts, and additional compute costs. Some optimizations will make your queries more efficient, while others can help you get more data with fewer passes. In this post, you’ll learn how to use ROLLUP to calculate the percentage of each item in a data set in a single pass. When using ROLLUP in Hydrolix, which is built to handle log data at scale, you get up to 5x better performance than using a CROSS JOIN to get the same data.

If you’re already familiar with how to use ROLLUP, you can jump right to Calculate the Percentage of Each Item Using ROLLUP. 

Understanding ROLLUP Queries

ROLLUP queries are useful for performing multiple aggregations in a single query. This can make it easier to see different relationships in data, and when it comes to working with big datasets, it’s much more efficient to compute the results of a single query that performs multiple aggregations instead of executing queries for each aggregation.

ROLLUP (or WITH ROLLUP, depending on the syntax of the query) is a query modifier that needs to be combined with a GROUP BY clause. The best way to show how it works is with hands-on examples. You can use Clickhouse Fiddle to try these examples.

First, create a temporary table with the following command. Note that the query examples mostly use plain SQL but that the Nullable(String) type is specific to Clickhouse.

CREATE TEMPORARY TABLE ContentTable (contentId Nullable(String), country Nullable(String));
INSERT INTO ContentTable VALUES ('Video_1', 'usa'), ('Video_2', 'usa'), ('Video_2', 'uk'), ('Video_2', 'fr'), ('Video_3', 'uk'), ('Video_3', 'uk');

This creates a table with two columns (contentId and country). There are three pieces of content (Video_1, Video_2, and Video_3) and three countries (usa, uk, and fr). A table like this could represent the number of views of each video in each country, a valuable metric for enterprises to see how each piece of content is performing.

Here’s a basic GROUP BY query for both columns that returns the total count of each asset by country:

SELECT
  contentId, country,
  count() AS content_count
  FROM ContentTable
  GROUP BY contentId, country

Here’s what the query returns.

Video_2	fr	1
Video_1	usa	1
Video_2	usa	1
Video_3	uk	2
Video_2	uk	1

This GROUP BY query returns one “level” of aggregation—a grouping of each count by contentId and country. As a result, Video_2 has three separate counts, one for each country.

What if you wanted to also aggregate by contentId so you could see the total count of each piece of content? You could do a separate GROUP BY query, but there are several disadvantages to that approach. It’s inefficient to make multiple queries, especially when working with large datasets. In the case of Hydrolix, where tables can hold billions or even potentially trillions of rows, it’s better to make fewer queries, especially if the aggregations are being automated to reports or dashboards.

Using ROLLUP isn’t just more efficient—it can also help you see the relationships between different groupings. And you can even use a ROLLUP query to perform a more complex aggregation like percentile in a single pass, as we’ll cover in the next section.

Let’s add WITH ROLLUP to the previous query as shown in the next example:

SELECT
  contentId, country,
  count() AS content_count
  FROM ContentTable
  GROUP BY contentId, country
WITH ROLLUP

This query returns the following:

Video_2	fr	1
Video_1	usa	1
Video_2	usa	1
Video_3	uk	2
Video_2	uk	1
Video_2	\N	3
Video_1	\N	1
Video_3	\N	2
\N	\N	6

If you look closely, you’ll see there are two levels of groupings. The first five entries show groupings by contentId and country (for example, there is 1 instance of Video_2 in fr), while the next three entries show groupings just by video. These groupings don’t have a country field (it’s represented by /N, or NULL). The final entry is the total number of pieces of content. (This final entry will come in handy when it comes to calculating percentile using ROLLUP.)

Note that the aggregation levels are determined by the order in GROUP BY. So for example, a ROLLUP with GROUP BY(contentId, country, contentCreator) would first aggregate at the level of all three attributes grouped together, then aggregate by contentId and country, and finally just by contentId. That’s three levels of aggregation in a single query. So ROLLUP can help with generating more complex reports and aggregations, all while keeping queries more efficient.

Finally, there are two ways to add ROLLUP to queries. In addition to the WITH ROLLUP syntax used in the previous examples, you can also do GROUP BY ROLLUP(...) as well.

Calculate the Percentage of Each Item Using WITH ROLLUP

Now let’s jump right into the specific use case of this post—calculating the percentage of each item using ROLLUP. Let’s take a look at an example that returns the percentage of each item in a data set. This use case is typical for a company that wants to better understand customer behavior. Specifically, a media company might want to break down streaming content usage by percentage. This is a simple example using the following dataset of contentIds:

For a dashboard, it might be helpful to have not just the total number of downloads for each video but also the overall percentage of downloads for each video. Having a percentage value can better help you compare the popularity of each item.

To try out the query, you’ll first need a temporary table that includes sample data. You can easily create a temporary table and test the queries in this post using Clickhouse Fiddle—just like with the previous examples.

To follow along, create a temporary table with the following data:

CREATE TEMPORARY TABLE ContentTable (contentId Nullable(String));
INSERT INTO ContentTable VALUES ('Video_1'), ('Video_2'), ('Video_2'), ('Video_2'), ('Video_3'), ('Video_3');

Real world datasets will be much larger, but this is all you’ll need for the example.

SELECT
  contentId,
  count() AS content_count
  FROM ContentTable
  GROUP BY contentId
  WITH ROLLUP

This returns the following output:

In contrast, a simple count query without ROLLUP would return the following:

WITH ROLLUP adds an additional row with the sum of the content_count. Note it has a NULL value for its contentId, which makes sense because it’s an aggregation of all the contentIds and doesn’t have its own ID. This NULL value is important for the full query—more on that in a moment.

Sometimes a simple sum is all you need, but what if you want to calculate the percentage for each item in a result set? For instance, you might want to know what percentage of users watched video_1 versus video_2. Fortunately, all of the data for calculating the percentage of each item is already included in the WITH ROLLUP query. Each contentId has its own content_count and there’s an additional row with a NULL contentId and a content_count that has the total sum of all the items. That means you can make use of the NULL value to do the following calculation in a query:

ROUND(content_count*100 / (SELECT content_count FROM CountPerContent WHERE content IS NULL), 2) as pct_total

You simply need to divide the content_count value of each contentId by the content_count of the row that has a NULL contentId (the sum), and multiply that by 100, giving you the percentage for each item.

However, there is one gotcha that needs to be accounted for: ensuring that the only NULL contentId is for the row that WITH ROLLUP generates. Fortunately, SQL provides COALESCE() to handle NULL values.

Let’s take a look at the full common table expression, which uses both COALESCE() and a ROLLUP, to get both the count of each item as well as its overall percentage in a single pass.

WITH CountPerContent AS (
    SELECT
        COALESCE(contentId, '__missing__')::Nullable(String) as content,
        count() as content_count
    FROM ContentTable
    GROUP BY content
    WITH ROLLUP
)
SELECT
    content,
    content_count,
    ROUND(content_count*100 / (SELECT content_count FROM CountPerContent WHERE content IS NULL), 2) as pct_total
FROM CountPerContent WHERE content IS NOT NULL

The first part of the common table expression uses COALESCE() to change any NULL contentId values in the temporary result set to __missing__. This ensures that the row that includes the sum will be the only NULL contentId value.

Here’s the output of the query:

Note that this query doesn’t include the aggregated row from WITH ROLLUP. You can easily add the aggregated value which includes the sum (and a pct_total of 100) by changing FROM CountPerContent WHERE content IS NOT NULL to FROM CountPerContent.

Advanced Scenarios for Queries Using ROLLUP

Queries using ROLLUP modifiers can provide multiple levels of aggregation in a single pass. That makes it useful for a wide range of use cases such as building reports and aggregate tables. Let’s break down its role in advanced use cases.

Integration with Modern Data Architectures

Modern data architectures have grown increasingly large and complex, and stakeholders ranging from operations to data scientists can benefit from the granular level of aggregations that GROUP BY ROLLUP can provide. In the case of operations teams, ROLLUP can help with more efficiently collecting aggregate data for many different kinds of groupings, providing support for use cases like observability and cybersecurity. For these use cases, operations and security teams can benefit from getting a high level view of their systems from many different angles.

Further downstream, data science and business intelligence (BI) teams can use GROUP BY ROLLUP to build in-depth reports. These reports can help uncover patterns and also provide the high-level metrics necessary for conveying data to leadership teams. 

Real-time Analytics Considerations

When it comes to big data, it can be computationally expensive and slower to query the underlying raw data, especially for real-time use cases. Often, the best solution is to use aggregate tables (known as summary tables in Hydrolix), which “roll up” raw data into metrics that are much faster to query. In the case of Hydrolix, summary tables are updated in real time and also account for late-arriving data, making them a highly accurate source for analytics.

Queries that aggregate data, especially from large data sets, can have higher latency and be more compute-intensive. As a result, teams need to be careful about performing too many GROUP BY queries that include aggregations. WITH ROLLUP queries can reduce the amount of queries needed by performing more aggregations in a single pass and revealing relationships between attributes that might otherwise be difficult to uncover. This can be especially helpful for real-time use cases such as root cause analysis and mitigating issues that are impacting end users.

Scaling Considerations

Teams working with big data often must choose their queries carefully. Some queries are especially compute-intensive, especially over large datasets. Queries that perform full-table scans (SELECT *) will be inefficient and costly—especially if they’re performing complex aggregations. ROLLUP queries can make it possible to perform more aggregations in a single pass, but they also need to be used wisely when working with large tables.

Here are several considerations when using ROLLUP queries with big datasets (these considerations generally apply for other kinds of queries as well):

• Add filters to reduce the amount of data that needs to be scanned. SELECT * is a poor choice for large datasets. In the case of log data, timestamps are a common filter. For example, uncovering the root cause of an issue that happened 30 minutes ago may only require an hour worth of data. For solutions that partition data (such as Hydrolix), filters can dramatically reduce the number of partitions that need to be scanned.
• Choose GROUP BY columns carefully. Many high-volume analytics platforms use columnar storage and users can specify which columns to query. This way, only data from the specified columns is retrieved and aggregated. This is especially important for “wide” datasets that can include hundreds or even thousands of columns.
• Use aggregate tables where possible. Sometimes it’s necessary to aggregate raw data on the fly, but if you’re regularly performing the same aggregations (such as for dashboards), then it’s often better to reconsider how your tables are structured. These tables pre-aggregate data and are much smaller than the underlying raw data tables, making them much more efficient (and cost-effective), especially over time.

Best Practices

• Index and partition data to make scanning more efficient. Hydrolix automatically indexes all columns and partitions data by time, but with many platforms (and solutions from scratch), you’ll need to define partition settings and indexes manually.
• Use OLAP columnar storage for analyzing big data. Traditional OLTP (online transactional databases) aren’t the best choice for big data analytics. OLAP (online analytical processing) solutions use columnar storage, advanced compression techniques, and other features to make aggregations more efficient.
• Use aggregate tables for common metrics and aggregations. Aggregate tables pre-aggregate data and are much more efficient to query.
• Reduce the amount of data that queries need to scan. Add filters and specify columns to reduce the total amount of data that needs to be scanned.
• Track overall compute usage. If you’re using a cloud-based platform, track overall compute usage and look for optimization opportunities. Especially for query-intensive use cases (such as ML training), compute costs can rise quickly.

Summary

ROLLUP queries are a powerful way to aggregate data. For big data use cases, it’s important to think carefully about how you use them and to implement best practices to maximize their efficiency. And as this post demonstrates, you can use ROLLUP to calculate the percentage of each item in a data set in a single pass—which is one way to be more efficient! You’ll save time and computational resources—and you’ll also see much better performance than you would with a CROSS JOIN.