Abs in Cockroach

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:

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

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:

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:

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?

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

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.

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

Use of abs()

ABS syntax

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:

Cockroach ABS Shortcut

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

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.