2

I have a situation where I'm performing a calculate over a huge number of rows, and I can really increase the performance if I can eschew a conditional statement.

What I need is for a given positive, zero, or negative integer I want the result 1, 0, -1 respectively.

So if I do col/ABS(col), I will get 1 for a positive number, and -1 for a negative number, but of course if col equals 0 then I'll get an error. I can't get an error.

This seems simple enough, but I can't wrap my ahead around it.

Daniel Gimenez
  • 18,530
  • 3
  • 50
  • 70
  • Hmmm, what's the impact of multiplying the term you already have by `col` ? – High Performance Mark Oct 29 '14 at 16:07
  • Depending on the language, you can say: `if not value return 0; else return value/abs(value)`. – fedorqui Oct 29 '14 at 16:07
  • 1
    `col/max(1, abs(col))`? Ugly but works. (For integers, that is.) – biziclop Oct 29 '14 at 16:08
  • I think the point was to avoid another conditional - both those statements will mean another conditional. – David S. Oct 29 '14 at 16:09
  • other than stripping out zeros how do you expect to know if your value is zero? DivByZero errors are going to happen – T McKeown Oct 29 '14 at 16:10
  • 2
    Some inspiration here, perhaps: http://stackoverflow.com/questions/1610836/branchless-code-that-maps-zero-negative-and-positive-to-0-1-2 – 500 - Internal Server Error Oct 29 '14 at 16:11
  • What operations are available? Some languages allow boolean results of comparisons to be treated as integers (and so you can do arithmetic on terms like `(x < 0)`). – DSM Oct 29 '14 at 16:12
  • Are you really sure the conditional is the bottbottleneck? Can you provide some profiling information backing that up? – templatetypedef Oct 29 '14 at 16:13
  • I was doing this in SSAS tabular, and the query plan is exceeds 10,000 lines. I couldn't use the accepted answer exactly but I did something similar and it reduced the time it took by 40%. – Daniel Gimenez Oct 29 '14 at 16:54
  • Possible duplicate of [Is there a standard sign function (signum, sgn) in C/C++?](https://stackoverflow.com/questions/1903954/is-there-a-standard-sign-function-signum-sgn-in-c-c) – phuclv Jul 08 '18 at 09:25

3 Answers3

6

Assuming either two's complement 32-bit integers, or one's complement with no negative-zero to worry about, then the following works well:

(x>>31) - (-x>>31);

Replace 31 with 63 for 64-bit integers, and so on.

Jon Hanna
  • 110,372
  • 10
  • 146
  • 251
3

col/max(1, abs(col))

Ugly but works. For integers, that is. For floating point values where there's no well-defined smallest positive value, you're stuck unless the language allows you to look into it as a bit sequence, then you can just do the same with the sign flag and the significand.

Whether this helps optimising anything is highly debatable though. It certainly makes things harder to read.

biziclop
  • 48,926
  • 12
  • 77
  • 104
  • Thanks, this works. I'll check you as soon as I can. In my application IF statements get offloaded to a different engine. I don't know if Max will help or not, but this is what I was looking for. – Daniel Gimenez Oct 29 '14 at 16:13
  • 3
    but max is just a wrapper of an if statement... this isn't making much sense to me – T McKeown Oct 29 '14 at 16:15
  • @TMcKeown Yeah, that's why it's a bit suspicious to me. But as OP says, if there are two different engines at work, a fast one that has `max()` hard-coded into it and a slow one that processes `if` statements (something like SQL and PLSQL), and it avoids context-switching, it may just work. – biziclop Oct 29 '14 at 16:16
  • Both `abs` and `max` *could* be coded in a branchless fashion, but then you really need to define such, and I can't think of a branchless `abs` that doesn't use a branchless two-way `sign`, so you're nearly half-way there once you go down that route. – Jon Hanna Oct 29 '14 at 16:40
0

I am doing this in an SSAS tabular model, which doesn't have a MAX function like in @bizclop's answer (which helps in many other applications such as EXCEL).

I ended up doing the following which was inspired by the accepted answer:

ROUND([col] / (ABS([col]) + 1), 0)

This ended up reducing my query time quite significantly (40%) versus IF([col] <> 0, [col]/ABS([col], 0).

Daniel Gimenez
  • 18,530
  • 3
  • 50
  • 70