CQL Arithmetic Operators

CQL Arithmetic Operators are now supported in Cassandra 4.0! In this walkthrough, we will show you some examples of how you can use the now supported CQL arithmetic operators.

We recommend going through this walkthrough in Gitpod as Gitpod will have everything we need for this walkthrough. Hit the button below to get started!

Otherwise, the walkthrough can be followed via Docker!

1. Cassandra

1.1 - Run Cassandra

We can run Docker on Gitpod, but as mentioned above, you can run this demo using your local Docker as well.

1.1.1 - Start Cassandra.

docker run --name cassandra -p 9042:9042 -d cassandra:latest

If running on Gitpod, remember to make the 9042 port public when the dialog shows up on the bottom right-hand corner

1.1.2 - Copy CQL File to Container

docker cp /workspace/example-CQL-arithmetic-operators/stats.cql cassandra:/

1.1.3 - Run CQL File

docker exec -it cassandra cqlsh -f /stats.cql

1.2 - Start CQLSH.

docker exec -it cassandra cqlsh 

2. Addition Operator

At the time of creating this repo, the 2022 MLB season has just started up. Nowadays, data is heavily ingrained into professional sports, but baseball especially with Sabermetrics (list of just offensive statistics)

2.1 - Calculate OPS

select season, slg, obp, slg + obp as "ops" from demo.jeter_world_series_stats ;

3. Subtraction Operator

3.1 - Calculate At Bats

select season, ab, tpa - bb - ibb - sac - hbp as "calc_ab" from demo.jeter_world_series_stats ;

4. Division Operator

4.1 - Calculate OBP

select season, obp, (h + bb + ibb + hbp) / (ab + bb + ibb + hbp + sac) as "calc_obp" from demo.jeter_world_series_stats where season = 2000; 

Hmm, something doesn't look right... Because h, bb, ibb, hbp, ab, and sac are int types according to the schema, they are doing integer arithemetic. Because we want to generate a decimal value, we need to CAST them to be something else like FLOAT for example.

4.2 - Calculate OBP with CAST

select obp, (CAST(h as FLOAT) + CAST(bb as FLOAT) + CAST(ibb as FLOAT) + CAST(hbp as FLOAT)) / (CAST(ab as FLOAT) + CAST(bb as FLOAT) + CAST(ibb as FLOAT) + CAST(hbp as FLOAT) + CAST(sac as FLOAT)) as "calc_obp" from demo.jeter_world_series_stats where season = 2000; 

5. Multiplication Operator

5.1 - Calculate ERA

select season, era, (er * 9) / ip as "calc_era" from demo.mariano_world_series_stats where season > 1997 and season <2001 ALLOW FILTERING;

6. Arithmetic Operator and Aggregrate Fuction

6.1 - Calculate Multiple Ratios using SUM and CAST

select SUM(ab) as "total_at_bats", SUM(r) as "total_runs", SUM(rbi) as "total_rbis", SUM(so) as "total_strike_outs", SUM(cast(rbi as FLOAT))/SUM(cast(ab as FLOAT)) as "ab_rbi_ratio",  SUM(cast(so as FLOAT))/SUM(cast(ab as FLOAT)) as "ab_so_ratio" from demo.jeter_world_series_stats;