1 2 Previous Next 28 Replies Latest reply: Jun 14, 2008 9:54 AM by 486393 RSS

    SQL puzzle :)

    598210
      I want to find all possible X up to the most highest number possbile with a single SQL.

      X - Y = T + Z

      where

      X as a positive number,
      Y is the reverse of X,
      T is the sum of each number which X has
      Z is the product of each number which X has

      like in this example;

      63 - 36 = 9 + 18

      - Which is the most efficient way of producing for example numbers between 1 and 1000000000000, CONNECT BY from DUAL?
      - Is there an alternative way to reverse a number other than undocumented REVERSE function to_number(reverse(to_char(anumber)) this way there will be three function calls for each number,
      - What is the best way to break a number into pieces to sum or make product in SQL.

      Thank you very much :)
        • 1. Re: SQL puzzle :)
          486393
          Is calling user defined PL/SQL functions allowed or not?
          • 2. Re: SQL puzzle :)
            598210
            wateenmooiedag the problem will be the performance most probably, but if it can not be handled within already SQL suppllied functionality then yes of course :)

            By the way I will be using an 10.2 instance.
            • 3. Re: SQL puzzle :)
              561093
              SQL> create or replace function sum_prod(p_val in number) return number is
                2   l_val Varchar2(500) := p_val;
                3   l_sum number := 0;
                4   l_n1  number;
                5   l_n2  number;
                6   l_product number := 1;
                7   l_first number;
                8  begin
                9    for i in 1..length(p_val)
              10    loop
              11      l_first := substr(l_val, 1, 1);
              12      l_sum := l_sum + l_first;
              13      l_product := l_product * l_first;
              14      l_val := substr(l_val, 2);
              15    end loop;
              16    return(l_sum + l_product);
              17  end;
              18  /

              Function created.

              SQL> select rno from ( select rownum rno
                2                      from dual
                3                    connect by level <= 100)
                4  where rno - reverse(to_char(rno)) = sum_prod(rno);

                RNO
              -----
                 63

              SQL> select rno from ( select rownum rno
                2                      from dual
                3                    connect by level <= 1000)
                4  where rno - reverse(to_char(rno)) = sum_prod(rno);

                RNO
              -----
                 63
                726

              SQL> select rno from ( select rownum rno
                2                      from dual
                3                    connect by level <= 10000)
                4  where rno - reverse(to_char(rno)) = sum_prod(rno);

                RNO
              -----
                 63
                726
              8937
              I think SQL experts can write it more elegantly.
              • 4. Re: SQL puzzle :)
                598210
                Citrus thank you for your time, but let me rephrase my concerns below, if I use 1000000000000 with this method even on a strong machine it takes half a day to finish.

                - Which is the most efficient way of producing for example numbers between 1 and 1000000000000, CONNECT BY from DUAL?
                - Is there an alternative way to reverse a number other than undocumented REVERSE function to_number(reverse(to_char(anumber)) this way there will be three function calls for each number,
                - What is the best way to break a number into pieces to sum or make product in SQL.
                • 5. Re: SQL puzzle :)
                  Nicolas.Gasparotto
                  Hi,

                  The brute force won't help too much in your case.
                  You may want to exclude at least the number where the last digit is higher than the first.
                  And still, with such big number, I'm not sure SQL and PL/SQL will be very efficient.
                  Lastly, even if that takes half ad day to finish, I'm sure it's not a scheduled job, after running it once, it's finish.

                  Anyway, here below my first try on my laptop :
                  SQL> create or replace type nb_obj as object (n1 number, n2 number, n3 number, n4 number);
                    2  /

                  Type created.

                  Elapsed: 00:00:00.00
                  SQL>
                  SQL> create or replace type nb_list as table of nb_obj;
                    2  /

                  Type created.

                  Elapsed: 00:00:00.04
                  SQL>
                  SQL> create or replace function f_maths (p_max number) return nb_list pipelined is
                    2      x number:=0;
                    3      y number;
                    4      t number;
                    5      z number;
                    6
                    7  begin
                    8      loop
                    9          --X is a positive number
                  10          x := x+1;
                  11          if    substr(x,-1,1) > substr(x,1,1)
                  12          then  x := x+11-substr(x,-1,1);
                  13          end if;
                  14          exit when x > p_max;
                  15
                  16          y := null;
                  17          t := 0;
                  18          z := 1;
                  19          for j in 1..length(x) loop
                  20              --Y is the reverse of X
                  21              y:=y||to_char(substr(x,-j,1));
                  22              --T is the sum of each number which X has
                  23              t:=t+substr(x,-j,1);
                  24              --Z is the product of each number which X has
                  25              z:=z*substr(x,-j,1);
                  26              exit when y > substr(x,1,j);
                  27          end loop;
                  28          --X - Y = T + Z
                  29          if x - y = t + z then
                  30              pipe row (nb_obj(x,y,t,z));
                  31          end if;
                  32      end loop;
                  33  end;
                  34  /

                  Function created.

                  Elapsed: 00:00:00.04
                  SQL> show err
                  No errors.
                  SQL> select *
                    2  from   table(f_maths(10000));

                          N1         N2         N3         N4
                  ---------- ---------- ---------- ----------
                          63         36          9         18
                         726        627         15         84
                        8937       7398         27       1512

                  Elapsed: 00:00:00.06
                  SQL>
                  SQL> select *
                    2  from   table(f_maths(100000));

                          N1         N2         N3         N4
                  ---------- ---------- ---------- ----------
                          63         36          9         18
                         726        627         15         84
                        8937       7398         27       1512

                  Elapsed: 00:00:00.60
                  SQL>
                  SQL> select *
                    2  from   table(f_maths(1000000));

                          N1         N2         N3         N4
                  ---------- ---------- ---------- ----------
                          63         36          9         18
                         726        627         15         84
                        8937       7398         27       1512

                  Elapsed: 00:00:07.01
                  SQL>
                  SQL> select *
                    2  from   table(f_maths(10000000));

                          N1         N2         N3         N4
                  ---------- ---------- ---------- ----------
                          63         36          9         18
                         726        627         15         84
                        8937       7398         27       1512

                  Elapsed: 00:01:21.81
                  SQL>
                  SQL> select *
                    2  from   table(f_maths(100000000));

                          N1         N2         N3         N4
                  ---------- ---------- ---------- ----------
                          63         36          9         18
                         726        627         15         84
                        8937       7398         27       1512

                  Elapsed: 00:15:36.81
                  Nicolas.
                  • 6. Re: SQL puzzle :)
                    598210
                    N. Gasparotto thank you for your responce.

                    Related to just using SQL functions I opened another thread here -
                    How to break a number into pieces and get the sum or product of the numbers

                    But I guess since there are several function calls those solutions do not help both in performance and easy undertanding compared to a user defined pl/sql like yours. This was an interesting experience :)
                    • 7. Re: SQL puzzle :)
                      561093
                      I replaced "SUM_PROD" function with wateenmooiedag's query:

                      Re: How to break a number into pieces and get the sum or product of the num
                      SQL> select rno from ( select rownum rno
                        2                          from dual
                        3                        connect by level <= 1000)
                        4      where rno - reverse(to_char(rno)) =
                        5                           (select to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select '
                        6                                   ||regexp_replace(rno,'(.)','\1+') || '0'||'
                        7                                      s from dual')),'/ROWSET/ROW/S'))
                        8                          +        to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select '
                        9                                   ||regexp_replace(rno,'(.)','\1*') || '1'||'
                      10                                   p from dual')),'/ROWSET/ROW/P'))
                      11                              from dual);

                             RNO
                      ----------
                              63
                             726

                      SQL>
                      • 8. Re: SQL puzzle :)
                        Nicolas.Gasparotto
                        YEah, XML and REGEXP are slow down. I'm pretty sure you can find some more rules to add in your function to exclude more and more numbers.

                        Nicolas.
                        • 9. Re: SQL puzzle :)
                          Nicolas.Gasparotto
                          Just a new try, ~3 minutes less for hundred million...
                          SQL> drop type nb_list;

                          Type dropped.

                          Elapsed: 00:00:00.11
                          SQL> create or replace type nb_obj as object (n1 number, n2 number, n3 number, n4 number);
                            2  /

                          Type created.

                          Elapsed: 00:00:00.01
                          SQL>
                          SQL> create or replace type nb_list as table of nb_obj;
                            2  /

                          Type created.

                          Elapsed: 00:00:00.15
                          SQL>
                          SQL> create or replace function f_maths (p_min number,p_max number) return nb_list pipelined is
                            2      x number:=p_min-1;
                            3      y number;
                            4      t number;
                            5      z number;
                            6      x1 number;
                            7      x2 number;
                            8  begin
                            9      loop
                          10          --X is a positive number
                          11          x := x+1;
                          12          x1 := substr(x,1,floor(length(x)/2));
                          13          x2 := substr(x,-floor(length(x)/2));
                          14          --dbms_output.put_line(x||' '||x1||' '||x2);
                          15          if    x1 < x2 or length(x2) < length(x1)
                          16          then  x := ceil(x/power(10,length(x2)))*power(10,length(x2));
                          17          end if;
                          18          exit when x > p_max;
                          19
                          20          y := null;
                          21          t := 0;
                          22          z := 1;
                          23          for j in 1..length(x) loop
                          24              --Y is the reverse of X
                          25              y:=y||to_char(substr(x,-j,1));
                          26              exit when y > substr(x,1,j);
                          27              --T is the sum of each number which X has
                          28              t:=t+substr(x,-j,1);
                          29              --Z is the product of each number which X has
                          30              z:=z*substr(x,-j,1);
                          31          end loop;
                          32          --X - Y = T + Z
                          33          if x - y = t + z then
                          34              pipe row (nb_obj(x,y,t,z));
                          35          end if;
                          36      end loop;
                          37  end;
                          38  /

                          Function created.

                          Elapsed: 00:00:00.04
                          SQL> show err
                          No errors.
                          SQL> select *
                            2  from   table(f_maths(1,100000000));

                                  N1         N2         N3         N4
                          ---------- ---------- ---------- ----------
                                  63         36          9         18
                                 726        627         15         84
                                8937       7398         27       1512

                          Elapsed: 00:12:43.79
                          SQL>
                          Nicolas.
                          • 10. Re: SQL puzzle :)
                            MichaelS
                            Probaly just interesting from an academical point of view. Performancewise it is just not acceptable:
                            SQL> select * from
                              2   xmltable('declare function local:reverse($a)
                              3             {
                              4               if (string-length($a) != 0) then
                              5                concat(substring($a,string-length($a),1), local:reverse(substring($a,1,string-length($a)-1)))
                              6               else ()
                              7             }; (: eof :)
                              8             declare function local:sum($a)
                              9             {
                             10               if (string-length($a) != 0) then
                             11                 xs:integer(substring($a,1,1)) + xs:integer(local:sum(substring($a,2)))
                             12               else (0)
                             13             }; (: eof :)
                             14             declare function local:prod($a)
                             15             {
                             16               if (string-length($a) != 0) then
                             17                 xs:integer(substring($a,1,1)) * xs:integer(local:prod(substring($a,2)))
                             18               else (1)
                             19             }; (: eof :)
                             20             for $i in 1 to 10000
                             21              where $i - local:reverse(xs:string($i)) = local:sum(xs:string($i)) + local:prod(xs:string($i))
                             22                    return $i' columns x integer path '.')
                             23  /
                            
                                     X
                            ----------
                                    63
                                   726
                                  8937
                            
                            Abgelaufen: 00:01:24.51
                            • 11. Re: SQL puzzle :)
                              Nicolas.Gasparotto
                              Abgelaufen: 00:01:24.51
                              Around 1400 times slower than function defined earlier above, who said SQL was always faster than PL/SQL ?
                              ;-)

                              Nicolas.
                              • 12. Re: SQL puzzle :)
                                MichaelS
                                Even without xml no faster than Nicolas ;)
                                SQL> select * from (select level x from dual connect by level <= 10000)
                                  2         connect by nocycle level <= length (x) and prior x = x - 1
                                  3   group by x
                                  4  having x - reverse(to_char(x)) = sum(substr(x,level,1)) + round(exp(sum(ln(decode(substr(x,level,1),0,1,substr(x,level,1))))))
                                  5   order by x
                                  6  /
                                
                                         X
                                ----------
                                        63
                                       726
                                      8937
                                
                                Abgelaufen: 00:00:02.29
                                • 13. Re: SQL puzzle :)
                                  NicloeiW
                                  dats great michaels,


                                  you beat nic this time though,

                                  can i have link from where i can learn more about xml with sql and they way u used it,
                                  • 14. Re: SQL puzzle :)
                                    Nicolas.Gasparotto
                                    you beat nic this time though,
                                    No, mine was less than 1 second for 10,000...
                                    However, really nice sql and obviously shorter.

                                    Nicolas.
                                    1 2 Previous Next