|
Am I right? list all the possible, and cal the avg_len?
SQL> SELECT s, length(s) lens, AVG(length(s)) over() avg_len
2 FROM (SELECT DISTINCT substr(a.s, 1, (least(instr(s, '2'), instr(s, '4')))) s
3 FROM (SELECT DISTINCT *
4 FROM (SELECT n, REPLACE(sys_connect_by_path(n, ','), ',') s
5 FROM (SELECT LEVEL n FROM dual CONNECT BY LEVEL <= 4)
6 WHERE LEVEL = 4
7 CONNECT BY nocycle LEVEL <= 4
8 AND n <> PRIOR n)) a);
S LENS AVG_LEN
-------------------------------------------------------------------------------- ---------- ----------
12 2 2.2
312 3 2.2
32 2 2.2
14 2 2.2
314 3 2.2
134 3 2.2
132 3 2.2
2 1 2.2
34 2 2.2
4 1 2.2
10 rows selected
SQL> |
|