|
Page 5 of 12
The Power of the Sign Function -- A Mathematical Explanation
It may come as a shock, but the problems in the last section, and much more, can be solved using the sign function. This is just the simple function in which sign(-200)=-1,.. sign(-1)=-1, sign(0)=0, sign(1)=1,.. sign(300)=1. So if the number is > 0 a 1 is returned. Zero is the only number that returns zero. All negative numbers return -1. Again, this simple function does not exist in sqlite, but you can easily create it, permanently. The next section will focus on the creation of this function, but here, the mathematical properties are explained.
The sign function can define the absolute value function abs() as the value of a number times its sign, or sign(x)*(x), abbreviated sign(x)(x). Here is a more detailed look at this function:
sign(x)(x) = abs(x)
Example, assume x=3
sign(3)(3) = abs(3)
1*3 = 3
Example, assume x=-3
sign(-3)(-3) = abs(-3)
-1*-3 = 3
Example, assume x=0
sign(0)(0) = abs(0)
0*0 = 0
Comparisons can be made with the sign function between two variables x and y. For instance, if sign(x-y) is 1, then, x is greater than y.
sign(x-y) is equal to 1 if x > y
sign(x-y) is equal to 0 if x = y
sign(x-y) is equal to -1 if x < y
Now look closely at the three statements below. The sign function starts to resemble an IF statement; a 1 is returned if and only if x = y. Thoroughly understanding the statements below is important, as the rest of the discussion quickly builds from these examples.
IF ( X==Y ) return 1; ELSE return 0;
can be expressed as follows:
1 - abs(sign(x-y)) is equal to 0 if x > y
1 - abs(sign(x-y)) is equal to 1 if x = y
1 - abs(sign(x-y)) is equal to 0 if x < y
It is possible to return a 1 if and only if x < y, otherwise return a zero.
IF ( X < Y ) return 1; ELSE return 0;
can be expressed as follows:
1-sign(1+sign(x-y)) is equal to 0 if x > y
1-sign(1+sign(x-y)) is equal to 0 if x = y
1-sign(1+sign(x-y)) is equal to 1 if x < y
The last example is known as the delta for x
Delta[x=y] = 1 - abs(sign(x-y))
Delta[x!=y] = abs(sign(x-y))
Delta[x
Delta[x<=y] = sign(1-sign(x-y))
Delta[x>y] = 1-sign(1-sign(x-y))
Delta[x>=y] = sign(1+sign(x-y))
Delta[z=x AND z=y] = sign( Delta[z=x]*Delta[z=y] )
Delta[z=x OR z=y] = sign( Delta[z=x]+Delta[z=y] )
Delta[z>x AND zx]*Delta[z
... more can be defined ... but you get the idea
To summarize the following if statement, note the introduction of a third variable, z:
if( x==y )
return z;
else
return 0;
The above expression, in Delta notation, is the following:
z*Delta[x=y]
Here is an interesting example:
create table logic (value int);
insert into logic (value) values (1);
insert into logic (value) values (0);
insert into logic (value) values (-1);
First, take the Cartesian product to show all possible combinations of x and y.
sqlite> .header on
sqlite> .mode column
sqlite> select x.value,y.value from logic x, logic y;
x.value y.value
---------- ----------
1 1
1 0
1 -1
0 1
0 0
0 -1
-1 1
-1 0
-1 -1
After the sign function is created (which we will do in the next section), using the above table, we could examine Delta[x!=y] as follows;
sqlite> .header on
sqlite> .mode column
sqlite> select x.value,y.value,abs(sign(x.value-y.value)) from logic x, logic y;
x.value y.value abs(sign(x.value-y.value))
---------- ---------- --------------------------
1 1 0
1 0 1
1 -1 1
0 1 1
0 0 0
0 -1 1
-1 1 1
-1 0 1
-1 -1 0
Note that every time x is not equal to y, abs(sign(x.value-y.value)) returns a 1. After the sign function is created, these example will run. This is extremely powerful. To show that we have created a condition statement without using the where or group by statements, consider the following example. z.value will only be displayed in the right hand column when x.value != y.value.
sqlite> select x.value,y.value,z.value,
z.value*abs(sign(x.value-y.value))
from logic x, logic y, logic z;
x.value y.value z.value z.value*abs(sign(x.value-y.value))
---------- ---------- ---------- ----------------------------------
1 1 1 0
1 1 0 0
1 1 -1 0
1 0 1 1
1 0 0 0
1 0 -1 -1
1 -1 1 1
1 -1 0 0
1 -1 -1 -1
0 1 1 1
0 1 0 0
0 1 -1 -1
0 0 1 0
0 0 0 0
0 0 -1 0
0 -1 1 1
0 -1 0 0
0 -1 -1 -1
-1 1 1 1
-1 1 0 0
-1 1 -1 -1
-1 0 1 1
-1 0 0 0
-1 0 -1 -1
-1 -1 1 0
-1 -1 0 0
-1 -1 -1 0
|
