Abs in Cockroach

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In this document, we will explore the use of abs in Cockroach from multiple angles, including:

  • What? What is abs and what does abs() do? This includes a high level view of the ABS function.
  • Why? Why and when do we use this function?
  • How? How do we make use of abs() in our Cockroach database usage? This includes syntax as well as examples of real world uses.

What is ABS?

CockroachDB’s abs() returns the absolute value for a number. The returned value is how distant from zero our number is, no matter what the positive/negative sign is. Another way to put it: ABS does nothing to a positive number and removes the negative sign from a negative number.

Here is an image of using the formula Y = ABS(X) to convert both negative x values and positive x values into positives for Y, which, draws both lines on the top side (positive) of our 2D graph:

Image from Gyazo

Here is another perspective in an SQL statement of how the function works:

1
SELECT ABS(i_Yaxis) FROM myDataTable

The above SQL command will return the following results: | i_Yaxis | Returned | |——–:|———:| | abs(12) | 12 | | abs(6) | 6 | | abs(0) | 0 | | abs(-6) | 6 | |abs(-12) | 12 |

Now that we have a high level (bird’s eye) understanding of the abs function, let’s go a bit deeper and talk about some use cases that might appear in the real world.

Why use the ABS function?

ABS use case

A potential use is when we are comparing numbers of estimated plant growth vs. actual planet growth over time where we don’t necessarily know if subtracting estimated growth from actual growth will give us a positive or negative number during any given week. With the comparison below, we want to know how inaccurate or mismatched the estimates were, so we can give huge quanities of raw broccoli to our favorite botanist. Here is an example:

WeekGrowthEst GrowthGrowth minus EstABS(Growth – Est)
1110130-2020
212712433
3111112-11
411511322
Total:-1626

Note how the column not using the ABS function, “Growth minus Est”, has an error in its total of -16. 26 is the correct total disparity between actual and estimated growth.

That data above (minus total) can be retrieved from Cockroach with an SQL statement like:

1
2
3
SELECT ID, i_week, i_growth_actual, i_growth_estimated, ABS(i_growth_actual - i_growth_estimated)
FROM growth_plants_weekly
ORDER BY i_week

Use case: calculate accuracy

What if we want to calculate the total accuracy or inaccuracy of the performance estimates of a car industry journalist so we can decide whether to give him a raise or fire him?

CarPerf RatingEst Perf Rating(Perf – Est) InaccuracyABS(Perf – Est) Inaccuracy
Tesla 3908555
Taycan8090-1010
Prius707000
Leaf6065-55
Total Inaccuracy:-1020

The above table of data can be pulled out of CockroachDB using an SQL statement like:

1
2
3
SELECT ID, t_car_name, i_perf, i_perf_est, abs(i_perf_actual - i_perf_est)
FROM tblCars
WHERE t_car_type = 'EV'

Of course, the above table would have a column named “t_car_type” to distinguish electric (EV) vehicles from internal combusion engines (ICE).

Use case: count false

At SellingSnowToAlaskans.net, the CEO wants to have a report on all sales personnel, ranked by who stretches the truth most regarding their sales figures. In addition, he wants to see a total of just how much capitol is being misrepresented in their department. A byproduct he may gain from this type of report is that he will attain valuable clues about who may be stealing from the company.

NameSalesReportedInaccurate Count (Actual-Reported)Accurate Lies: ABS(Actual-Reported)
Bob91000790001200012000
Tina970009700000
Fred9000091000-10001000
Tina8200092000-1000010000
Tot Lying100023000

The above represented data (absent the total) would be retrieved out of Cockroach using an SQL statement like the you see here:

1
2
3
SELECT ID, t_name, c_sales, c_reported, c_sales-c_reported AS c_inaccurate, ABS(c_sales_actual - c_sales_reported)
FROM tblEmployees
WHERE ID_dept = 3

Use of abs()

ABS syntax

1
new_number = ABS(a_number)

The “a_number” can be a field or value that Cockroach evaluates as a number. Oh and in terms of data type, the data type you enter determines the data type returned from Cockroach. For example:

InputReturnedType
22Integer
-22Integer
2.52.5Numeric
-2.52.5Numeric

Cockroach ABS Shortcut

The “@” operator functions the same as abs(). See in this next example:

1
SELECT @ -22

Returns 22

Conclusion

In this document we learned how to use ABS in CockroachDB. We also explored some use cases of why and when you may wish to use Cockroach’s ABS() function. Hopefully, you fully understood the pattern in the above use cases of removing negative results from the comparisons so that we could calculate accurate readings of totals. Do you see how – especially in statistical analysis scenarios with Cockroach – ABS can be quite useful.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.