How to Simulate SIGN Function

Puzzle of the day:

How to simulate the SIGN function in Oracle SQL by only using CEIL, FLOOR, and ABS Oracle SQL functions along with arithmetic operators? No PL/SQL.

Solution:

SIGN(x)=CEIL(x/(1+ABS(x)))+FLOOR(x/(1+ABS(x)))

In SQL, we can demonstrate it as follows:

WITH r AS (
SELECT dbms_random.VALUE(-999,999) rnd
FROM dual
CONNECT BY LEVEL<=10
UNION ALL
SELECT 0
FROM dual
)
SELECT rnd, SIGN(rnd), CEIL(rnd/(1+ABS(rnd)))+FLOOR(rnd/(1+ABS(rnd))) "MySign"
FROM r

Result:

       RND  SIGN(RND)     MySign
---------- ---------- ----------
  -519.606         -1         -1
-657.62692         -1         -1
414.625079          1          1
736.175183          1          1
268.689074          1          1
-647.12649         -1         -1
338.192233          1          1
784.780876          1          1
-529.69184         -1         -1
-596.56803         -1         -1
         0          0          0

As you can see, “MySign” column perfectly matches SIGN column.

Comment:

WITH clause is needed to generate 10 random values in the range of -999 .. +999. “0” value is added to demonstrate a special case as it is unlikely that zero will be randomly generated.

 

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Puzzle of the Week Challenge – Solutions to the 2nd Puzzle

Last week we presented the 2nd puzzle of our contest, Puzzle of the Week. Today we publish correct answers for that puzzle:
Thanks to all who accepted the challenge!

Dish washing schedule puzzle:

jigsaw-puzzle-piece Four roommate students, Anna, Betty, Carla, and Daniela decided to make a “Dish washing schedule”. Every day one of the girls should do all the dishes. The challenge is to make a schedule for the next month that will spread the responsibilities among the girls as evenly as possible. At the same time the schedule should be completely random.

Solution #1: Traditional approach for mimicking pivoted report.

WITH x AS (
SELECT FLOOR((LEVEL-1)/4) id, 
       LEVEL AS d,  
       RANK()OVER(PARTITION BY FLOOR((LEVEL-1)/4) ORDER BY DBMS_RANDOM.VALUE) rk
FROM dual 
CONNECT BY LEVEL<=32
)
SELECT MAX(CASE WHEN MOD(rk,4)=1 THEN D END) AS "Anna",
       MAX(CASE WHEN MOD(rk,4)=2 THEN D END) AS "Betty",
       MAX(CASE WHEN MOD(rk,4)=3 THEN d END) AS "Carla",
       MAX(CASE WHEN MOD(rk,4)=0 THEN D END) AS "Daniela"
FROM x
WHERE d<=TO_CHAR(LAST_DAY(SYSDATE),'DD')
GROUP BY id
ORDER BY id

Sample output #1:

      Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
         2          1          4          3
         6          5          8          7
        11          9         10         12
        14         15         16         13
        20         19         18         17
        24         21         22         23
        28         27         26         25
        31                    30         29

Sample output #2 (after re-running the same query):

      Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
         3          4          2          1
         7          5          6          8
        12          9         11         10
        16         14         13         15
        20         19         18         17
        23         24         21         22
        25         27         26         28
                   31         29         30

Solution #2: Using Recursive WITH clause for range generation:

WITH x(d) AS (
  SELECT 1 AS d
  FROM dual
  UNION ALL
  SELECT d+1
  FROM x
  WHERE d<32
), y AS (
SELECT FLOOR((d-1)/4) id, 
       CASE WHEN d<=TO_CHAR(LAST_DAY(SYSDATE),'DD') THEN d END d, 
       RANK()OVER(PARTITION BY FLOOR((d-1)/4) ORDER BY DBMS_RANDOM.VALUE) rk
FROM x
)
SELECT MAX(CASE WHEN MOD(rk,4)=1 THEN D END) AS "Anna",
       MAX(CASE WHEN MOD(rk,4)=2 THEN D END) AS "Betty",
       MAX(CASE WHEN MOD(rk,4)=3 THEN d END) AS "Carla",
       MAX(CASE WHEN MOD(rk,4)=0 THEN D END) AS "Daniela"
FROM y
WHERE d<=TO_CHAR(LAST_DAY(SYSDATE),'DD')
GROUP BY id
ORDER BY id

Sample output #1:

      Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
         1          4          2          3
         8          7          6          5
        11         10          9         12
        15         14         13         16
        18         19         20         17
        24         22         21         23
        25         27         28         26
        29                    30         31

Sample output #2 (after re-running the same query):

      Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
         1          4          3          2
         5          6          7          8
        11         12         10          9
        13         15         16         14
        20         19         17         18
        22         23         24         21
        28         26         27         25
                   29         30         31

Solution #3: Using PIVOT clause:

SELECT "1" AS "Anna","2" AS "Betty", "3" AS "Carla", "4" AS "Daniela"  
FROM (
SELECT FLOOR((LEVEL-1)/4) id, 
       CASE WHEN LEVEL<=TO_CHAR(LAST_DAY(SYSDATE),'DD') THEN LEVEL END AS d,  
       RANK()OVER(PARTITION BY FLOOR((LEVEL-1)/4) ORDER BY DBMS_RANDOM.VALUE) rk
FROM dual 
CONNECT BY LEVEL<=4*CEIL(31/4)
)
PIVOT 
(
  MAX(d)
  FOR rk IN (1,2,3,4)
)
ORDER BY id;

Sample output #1:

      Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
         4          1          3          2
         7          6          8          5
        10          9         11         12
        16         14         13         15
        20         19         17         18
        23         22         21         24
        26         25         27         28
        31                    29         30

Sample output #2 (after re-running the same query):

      Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
         4          1          3          2
         8          7          5          6
        11         10         12          9
        16         13         14         15
        20         19         17         18
        21         22         23         24
        28         26         27         25
        31         29         30

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.