Cassandra Aggregating & Grouping Data

Cassandra provides a powerful set of tools for aggregating and grouping data, allowing you to perform complex analytics on large datasets. In this tutorial, we’ll go over how to use Cassandra’s built-in aggregation functions and grouping features to query your data.

Aggregation Functions

Cassandra provides a number of built-in aggregation functions for performing calculations on your data. Here are some of the most commonly used functions:

• COUNT: Returns the number of rows in a result set.
• SUM: Returns the sum of a set of values.
• AVG: Returns the average of a set of values.
• MIN: Returns the smallest value in a set.
• MAX: Returns the largest value in a set.

Let’s say we have a keyspace called “my_keyspace” with a table called “employees”, and we want to calculate the average salary of all employees. Here’s the CQL query we would use:

SELECT AVG(salary) FROM my_keyspace.employees;

This would return a single row with the average salary across all employees.

Grouping Data

In addition to aggregation functions, Cassandra also provides grouping features to allow you to group data by one or more columns. This can be useful for performing more complex analytics, such as calculating the average salary by department.

To group data in Cassandra, we use the GROUP BY clause in our query. Let’s say we have a “departments” column in our “employees” table, and we want to calculate the average salary for each department. Here’s the CQL query we would use:

SELECT department, AVG(salary) FROM my_keyspace.employees GROUP BY department;

This would return a result set with one row for each department, along with the average salary for that department.

Filtering Grouped Data

Sometimes we want to filter our results based on the results of an aggregation. For example, let’s say we want to find all departments where the average salary is greater than $100,000. Here’s the CQL query we would use:

SELECT department, AVG(salary) FROM my_keyspace.employees GROUP BY department HAVING AVG(salary) > 100000;

This query uses the HAVING clause to filter our results based on the average salary of each department. The result set would only include departments with an average salary greater than $100,000.

Cassandra’s aggregation and grouping features are powerful tools for performing complex analytics on large datasets. By using the built-in aggregation functions and grouping clauses, you can quickly and easily calculate statistics and group data based on one or more columns.