ScyllaDB window function-like query

Question

I am using ScyllaDB open-source version 4.4.

I am trying to figure out how to write a query which I would have done with a window function or a UNION set operator if this was a traditional relational database with a full SQL.

A simplified table schema:

CREATE TABLE mykeyspace.mytable ( 
    name               text   ,
    timestamp_utc_nanoseconds    bigint   ,
    value               bigint   ,
    PRIMARY KEY( (name),timestamp_utc_nanoseconds )
 ) WITH CLUSTERING ORDER BY (timestamp_utc_nanoseconds DESC);

My query needs to calculate and return 6 values, each of them is an average of "value" over one of the previous minutes.

In pseudo-code:

SELECT AVG(value) AS one_min_avg_6_min_ago FROM mykeyspace.mytable WHERE name = 'some_name' AND timestamp_utc_nanoseconds >= [*6 minutes ago*] AND timestamp_utc_nanoseconds < [*5 minutes ago*];
SELECT AVG(value) AS one_min_avg_5_min_ago FROM mykeyspace.mytable WHERE name = 'some_name' AND timestamp_utc_nanoseconds >= [*5 minutes ago*] AND timestamp_utc_nanoseconds < [*4 minutes ago*];
SELECT AVG(value) AS one_min_avg_4_min_ago FROM mykeyspace.mytable WHERE name = 'some_name' AND timestamp_utc_nanoseconds >= [*4 minutes ago*] AND timestamp_utc_nanoseconds < [*3 minutes ago*];
SELECT AVG(value) AS one_min_avg_3_min_ago FROM mykeyspace.mytable WHERE name = 'some_name' AND timestamp_utc_nanoseconds >= [*3 minutes ago*] AND timestamp_utc_nanoseconds < [*2 minutes ago*];
SELECT AVG(value) AS one_min_avg_2_min_ago FROM mykeyspace.mytable WHERE name = 'some_name' AND timestamp_utc_nanoseconds >= [*2 minutes ago*] AND timestamp_utc_nanoseconds < [*1 minutes ago*];
SELECT AVG(value) AS one_min_avg_1_min_ago FROM mykeyspace.mytable WHERE name = 'some_name' AND timestamp_utc_nanoseconds >= [*1 minute ago*];

My client-side is C# .NET 5. I can easily do pretty much anything on the client side. But the latency in this case is going to be a big problem.

My question is: How can I combine these 6 queries into one result set on the server side (not on the client app side)?

Answer 1

Ideally you would use a UDA - User Defined Aggregate function, however support for these is not yet complete. In the meantime, you can execute these 6 queries in parallel which might even be preferable.