
15. Re: 'Happy numbers' logic in SQL
Manik Nov 4, 2015 10:09 AM (in response to BluShadow)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 Nov 4, 2015 10:16 AM (in response to Manik)1 person found this helpfulwith 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 
17. Re: 'Happy numbers' logic in SQL
Manik Nov 4, 2015 10:13 AM (in response to Stew Ashton)E X C E L L E N T !!!
Nice one sir!
Cheers,
Manik.

18. Re: 'Happy numbers' logic in SQL
BluShadow Nov 4, 2015 10:14 AM (in response to Manik)Let's not forget someone doing the MODEL clause

19. Re: 'Happy numbers' logic in SQL
Manik Nov 4, 2015 10:16 AM (in response to BluShadow)Yes true..
Cheers,
Manik.

20. Re: 'Happy numbers' logic in SQL
chris227 Nov 4, 2015 5:51 PM (in response to BluShadow)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 Nov 4, 2015 10:12 PM (in response to chris227)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 Nov 4, 2015 11:24 PM (in response to Stew Ashton)1 person found this helpfulVery nice indeed.
I'll only add the minor simplification of moving the summation to a scalar subquery.
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;

23. Re: 'Happy numbers' logic in SQL
Stew Ashton Nov 5, 2015 1:05 PM (in response to Scott Swank)Scott Swank wrote:
I'll only add the minor simplification of moving the summation to a scalar subquery.
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 Nov 5, 2015 2:59 PM (in response to Scott Swank)1 person found this helpfulScott Swank wrote:
I'll only add the minor simplification of moving the summation to a scalar subquery.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)

25. Re: 'Happy numbers' logic in SQL
Scott Swank Nov 5, 2015 4:32 PM (in response to Stew Ashton)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 Nov 6, 2015 4:00 PM (in response to Manik)1 person found this helpfulCan'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_VALUE CURRENT_VALUE SQUARE_SUMMED HAPPINESS 133 133 19 133 19 82 133 82 68 133 68 100 133 100 1 happy 
27. Re: Re: 'Happy numbers' logic in SQL
Pleiadian Nov 6, 2015 4:07 PM (in response to Pleiadian)1 person found this helpfulAnd 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

28. Re: 'Happy numbers' logic in SQL
chris227 Nov 7, 2015 6:55 PM (in response to Scott Swank)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 Nov 10, 2015 8:30 AM (in response to Manik)1 person found this helpfulI 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