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

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..

Cheers,

Manik.

• ###### 16. Re: 'Happy numbers' logic in SQL
```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
• ###### 17. Re: 'Happy numbers' logic in SQL

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

Nice one sir!

Cheers,

Manik.

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

Let's not forget someone doing the MODEL clause

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

Yes true..

Cheers,

Manik.

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

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

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

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;
```
• ###### 23. Re: 'Happy numbers' logic in SQL

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

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)

• ###### 25. Re: 'Happy numbers' logic in SQL

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

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_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

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

connect by nocycle prior n2 = n

• ###### 28. Re: 'Happy numbers' logic in SQL

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

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