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:
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:
Week | Growth | Est Growth | Growth minus Est | ABS(Growth – Est) |
---|---|---|---|---|
1 | 110 | 130 | -20 | 20 |
2 | 127 | 124 | 3 | 3 |
3 | 111 | 112 | -1 | 1 |
4 | 115 | 113 | 2 | 2 |
Total: | -16 | 26 |
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?
Car | Perf Rating | Est Perf Rating | (Perf – Est) Inaccuracy | ABS(Perf – Est) Inaccuracy |
---|---|---|---|---|
Tesla 3 | 90 | 85 | 5 | 5 |
Taycan | 80 | 90 | -10 | 10 |
Prius | 70 | 70 | 0 | 0 |
Leaf | 60 | 65 | -5 | 5 |
Total Inaccuracy: | -10 | 20 |
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.
Name | Sales | Reported | Inaccurate Count (Actual-Reported) | Accurate Lies: ABS(Actual-Reported) |
---|---|---|---|---|
Bob | 91000 | 79000 | 12000 | 12000 |
Tina | 97000 | 97000 | 0 | 0 |
Fred | 90000 | 91000 | -1000 | 1000 |
Tina | 82000 | 92000 | -10000 | 10000 |
Tot Lying | 1000 | 23000 |
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:
Input | Returned | Type |
---|---|---|
2 | 2 | Integer |
-2 | 2 | Integer |
2.5 | 2.5 | Numeric |
-2.5 | 2.5 | Numeric |
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