1 2 Previous Next 29 Replies Latest reply on Nov 10, 2015 8:30 AM by Pleiadian Go to original post
      • 15. Re: 'Happy numbers' logic in SQL
        Manik

        11 seconds for 1000 range is not a bad solution at all.. Thanks Blu..

        I would just keep this thread open till tomorrow to see if any of our friends would solve it in their own style..

        Thanks for your time..

         

        Cheers,

        Manik.

        • 16. Re: 'Happy numbers' logic in SQL
          Stew Ashton
          with data as (
            select level n from dual connect by level <= 1000
          )
          , recurse(n, pwrsum) as (
            select * from data, table(cast(multiset(
              select sum(power(substr(n,level,1),2))
              from dual
              connect by level <= length(n)
            ) as sys.odcinumberlist))
            union all
            select n, column_value from recurse o, table(cast(multiset(
              select sum(power(substr(pwrsum,level,1),2)) newsum
              from dual
              connect by level <= length(pwrsum)
            ) as sys.odcinumberlist))
            where length(o.pwrsum) > 1
          )
          select n from recurse where pwrsum = 1
          order by 1;
          
          Elapsed: 00:00:00.084
          
          
          

           

          N
          1
          7
          10
          13
          19
          23
          28
          31
          32
          44
          49
          68
          70
          79
          82
          86
          91
          94
          97
          100
          103
          109
          129
          130
          133
          139
          167
          176
          188
          190
          192
          193
          203
          208
          219
          226
          230
          236
          239
          262
          263
          280
          291
          293
          301
          302
          310
          313
          319
          320
          326
          329
          331
          338
          356
          362
          365
          367
          368
          376
          379
          383
          386
          391
          392
          397
          404
          409
          440
          446
          464
          469
          478
          487
          490
          496
          536
          556
          563
          565
          566
          608
          617
          622
          623
          632
          635
          637
          638
          644
          649
          653
          655
          656
          665
          671
          673
          680
          683
          694
          700
          709
          716
          736
          739
          748
          761
          763
          784
          790
          793
          802
          806
          818
          820
          833
          836
          847
          860
          863
          874
          881
          888
          899
          901
          904
          907
          910
          912
          913
          921
          923
          931
          932
          937
          940
          946
          964
          970
          973
          989
          998
          1000
          1 person found this helpful
          • 17. Re: 'Happy numbers' logic in SQL
            Manik

            E X C E L L E N T !!!

             

            Nice one sir! 

             

            Cheers,

            Manik.

            • 18. Re: 'Happy numbers' logic in SQL
              BluShadow

              Let's not forget someone doing the MODEL clause 

              • 19. Re: 'Happy numbers' logic in SQL
                Manik

                Yes true..

                 

                Cheers,

                Manik.

                • 20. Re: 'Happy numbers' logic in SQL
                  chris227

                  BluShadow wrote:

                   

                  Let's not forget someone doing the MODEL clause 

                  Not from my me

                   

                  I ended up with nearly the same code as stew came up with, but without seeing the way to make the sum() "be alllowed" in the recursive branch.

                  Stew cut the gordian knot most brilliant, nothing to add from my side.

                  • 21. Re: Re: 'Happy numbers' logic in SQL
                    Stew Ashton

                    Thanks for the kind words.

                     

                    And for anyone reading who has 12c,

                     

                    table(cast(multiset(

                     

                    can be replaced by

                     

                    lateral(
                    • 22. Re: Re: Re: 'Happy numbers' logic in SQL
                      Scott Swank

                      Very nice indeed.

                       

                      I'll only add the minor simplification of moving the summation to a scalar sub-query.

                       

                      WITH data
                           AS (    SELECT LEVEL n
                                     FROM DUAL
                               CONNECT BY LEVEL <= 1000),
                           recurse (n, pwrsum)
                           AS (SELECT n,
                                      (    SELECT SUM (POWER (SUBSTR (n, LEVEL, 1), 2))
                                             FROM DUAL
                                       CONNECT BY LEVEL <= LENGTH (n))
                                         AS pwrsum
                                 FROM data
                               UNION ALL
                               SELECT n,
                                      (    SELECT SUM (POWER (SUBSTR (pwrsum, LEVEL, 1), 2))
                                             FROM DUAL
                                       CONNECT BY LEVEL <= LENGTH (pwrsum))
                                 FROM recurse
                                WHERE pwrsum >= 10)
                        SELECT n
                          FROM recurse
                         WHERE pwrsum = 1
                      ORDER BY 1;
                      
                      1 person found this helpful
                      • 23. Re: 'Happy numbers' logic in SQL
                        Stew Ashton

                        Scott Swank wrote:

                         

                        I'll only add the minor simplification of moving the summation to a scalar sub-query.

                        You are absolutely right, the subquery returns one scalar value so a scalar subquery is both simpler and more appropriate. Good catch.

                         

                        Your "WHERE pwrsum >= 10" is also an improvement.

                         

                        Best regards, Stew

                        • 24. Re: Re: Re: Re: 'Happy numbers' logic in SQL
                          chris227

                          Scott Swank wrote:

                          I'll only add the minor simplification of moving the summation to a scalar sub-query.

                          Awesome. Yes that's it.

                           

                          Thanks to you this my code now works too

                          with nums as (

                          select level s from dual connect by level < 50

                          )

                          ,happy( p, s ) as (

                            select s p, s from nums

                            union all

                            select

                              (select sum(power(substr(p, level, 1), 2)) p

                              from dual connect by level <= length(p))

                            , s from happy

                          )

                          cycle p set c to '1' default '0'

                          select s

                          from happy

                          where c = '1' and p = 1

                          order by s

                           

                          S
                          1
                          7
                          10
                          13
                          19
                          23

                          28

                          ...

                          @scott: Brilliant idea, i just stucked in the connect by not working in a join in the recursive branch.

                          I like it so much because it's so simple.

                          (And furthermore there might the benefit of scalar subquery caching)

                          1 person found this helpful
                          • 25. Re: 'Happy numbers' logic in SQL
                            Scott Swank

                            And your length(n) is a nice improvement over my ceil(log(10, n+1)). I was being a bit too mathy in my approach.

                            • 26. Re: 'Happy numbers' logic in SQL
                              Pleiadian

                              Can't resist, although there are much more elegant solutions already posted...

                               

                              This tests a specific number, which can be specified in the "start with" part. If you take large numbers, you might have to increase the "level <= 1000" in the first query. This determines the maximum number where the sum/square of the digits is calculated.

                               

                              with

                                s as ( select level n from dual connect by level <= 1000 ) -- generate all numbers up to max value

                              , c as ( select n, to_number(substr(n,rn,1)) d from s        -- split each number into individual digits

                                       cross join (select rownum rn from ( select max(length(n)) ml from s) connect by level < ml )

                                       where substr(n,rn,1) is not null )

                              , n as ( select n, sum(d*d) n2 from c group by n )           -- square and sum the digits

                               

                              select connect_by_root(n)   initial_value

                              ,      n                    current_value

                              ,      n2                   square_summed

                              ,      decode(n2,1,'happy') happiness

                              from   n

                              start with n.n = 133 -- enter number to investigate happiness here

                              connect by nocycle prior n2 = n

                               

                              In this case, 133 returns:

                               

                              INITIAL_VALUECURRENT_VALUESQUARE_SUMMEDHAPPINESS
                              13313319
                              1331982
                              1338268
                              13368100
                              1331001happy
                              1 person found this helpful
                              • 27. Re: Re: 'Happy numbers' logic in SQL
                                Pleiadian

                                And a slight modification to list all happy numbers (up to 1000) as stated in Maniks requirements:

                                 

                                with

                                  s as ( select level n from dual connect by level <= 1000 ) -- generate all numbers up to max value

                                , c as ( select n, to_number(substr(n,rn,1)) d from s        -- split each number into individual digits

                                         cross join (select rownum rn from ( select max(length(n)) ml from s) connect by level < ml )

                                         where substr(n,rn,1) is not null )

                                , n as ( select n, sum(d*d) n2 from c group by n )           -- square and sum the digits

                                 

                                select connect_by_root(n) happy_number

                                from   n

                                where  n2 = 1

                                start with n in ( select * from s )

                                connect by nocycle prior n2 = n

                                1 person found this helpful
                                • 28. Re: 'Happy numbers' logic in SQL
                                  chris227

                                  Coming back to this on 12c i finally found the way the subquery works as an inline view also (The point i stucked in on 11g)

                                   

                                  with nums as (

                                  select level s from dual connect by level < 50

                                  )

                                  ,happy( p, s ) as (

                                    select s p, s from nums

                                    union all

                                    select pp

                                    , s from happy

                                  , lateral (

                                  select sum(power(substr(p, level, 1), 2)) pp

                                  from dual connect by level <= length(p))

                                  )

                                  cycle p set c to '1' default '0'

                                  select s

                                  from happy

                                  where c = '1' and p = 1

                                  order by s

                                   

                                  S
                                  1
                                  7
                                  10
                                  13
                                  19
                                  23

                                  28

                                  ...

                                  • 29. Re: 'Happy numbers' logic in SQL
                                    Pleiadian

                                    I didn't like the ugly cross join to break up the numbers into their individual digits. This is a lot cleaner and easier to read:

                                    with

                                      numbers as ( select level n  -- all numbers up to max value

                                                   from  dual

                                                   connect by level <= 1000 )

                                    , digits  as ( select n      -- list of digits for all numbers

                                                   ,      substr(n,level,1) digit

                                                   from  numbers

                                                   connect by prior n = trunc(n/10) )

                                    , ishappy as ( select n      -- list of digits squared and summed

                                                   ,      sum(digit*digit) n2

                                                   from  digits

                                                   group by n )

                                    select connect_by_root(n) happy_number

                                    from   ishappy

                                    where  n2 = 1

                                    connect by nocycle prior n2 = n

                                     

                                    I'll try to let it go now

                                    1 person found this helpful
                                    1 2 Previous Next