Search code examples
cassandracql

Cassandra use aggregate function and then order by that aggregate

I have a cassandra database with a table that has the following columns:

  • itemid
  • userid
  • rating

itemid and userid are the primary key. My query looks like this:

SELECT itemid, avg(rating) as avgRating from mytable GROUP BY itemid order by avgRating asc;

I get the following error:

InvalidRequest: Error from server: code=2200 [Invalid query] message="ORDER BY is only supported when the partition key is restricted by an EQ or an IN."

How can I fix this?

I need to order by the average ratings after so I can get the top 10 movies based on their average rating.

Solution

  • Cassandra can only order results by clustering column(s). It cannot order results by an aggregate function.

    There are a couple of options you could look at in order to accomplish this.

    1. Make the query and then re-order the results in your application.

    This option may work if you only expect a limited number of rows to be returned from each query.

    Note that it is recommended that you only use aggregate functions (like avg()) when you know that it will only apply to a limited number of rows. Ideally you should only use them when operating on a single partition (use a WHERE clause to limit to a single partition). If you don't have any limit you may see very slow queries, or query timeouts if Cassandra needs to read a large number of rows in order to calculate the aggregate.

    1. Store a pre-calculated average in the table, or cache it in your application.

    This is the best option if you need calculated averages over a larger data set.

    If you make average_rating a clustering column Cassandra will store the averages for each partition in sorted order. This is very efficient from Cassandra's perspective.

    The downside is that you'll need to calculate the average in your application each time you insert into or update a row, because it will be a primary key column in your Cassandra table.

    One thing you could look into is using a Cassandra trigger to calculate the average for you. This may make life easier for you if you have multiple applications writing to this table, however I am not actually sure if it is possible to modify a primary key column via a custom trigger. I would recommend doing some research & testing if you decide to look at this option. You can read about triggers here.