create or replace type to_k_means as object (id number, x number, y number, cid number, cx number, cy number)
/
create or replace type tt_k_means as table of to_k_means
/
create or replace function f_k_means(p in tt_k_means) return tt_k_means is
  result     tt_k_means;
  tmp        tt_k_means;
  l_clusters number :=  2 ;
  l_cnt      number;
begin

  select to_k_means(id,
                    x,
                    y,
                    cid,
                    avg(x) over(partition by cid),
                    avg(y) over(partition by cid)) bulk collect
    into result
    from (select M.id,
                 M.x,
                 M.y,
                 c.cid,
                 row_number() over(partition by m.id order by sqrt((m.x - c.x) * (m.x - c.x) + (m.y - c.y) * (m.y - c.y))) rn
            from table(p) M,
                 (select id cid, x, y from table(p) where id <= l_clusters) c)
   where rn =  1 ;

  while true loop
    select to_k_means(id,
                      x,
                      y,
                      cid,
                      avg(x) over(partition by cid),
                      avg(y) over(partition by cid)) bulk collect
      into tmp
      from (select id,
                   x,
                   y,
                   t.cid,
                   row_number() over(partition by id order by sqrt((x - t.cx) * (x - t.cx) + (y - t.cy) * (y - t.cy))) rn
              from table(result),
                   (select distinct cid, cx, cy from table(result)) t)
     where rn =  1 ;
  
    select count(*)
      into l_cnt
      from table(tmp) t1, table(result) t2
     where t1.id = t2.id
       and t1.cid = t2.cid;
  
    if l_cnt = result.count then
      exit;
    else
      result := tmp;
    end if;
  end loop;

  return result;

end f_k_means;
/

SQL> create or replace type to_cluster force as object (cluster_id number, probability binary_double)
  2  /

Type created.

SQL> create or replace type tt_cluster as varray (2147483647) of to_cluster
  2  /

Type created.

SQL> set lines 150 pages 150
SQL> set long 1000
SQL> column id format 99
SQL> column x format 99
SQL> column y format 99
SQL> column c format 99
SQL> column probability format 0.999999999
SQL> column distance format 999.999999
SQL> column cluster_details format a65
SQL> column cluster_set format a70
SQL> with t as
  2  (
  3  select 1 id, 1 x, 2 y from dual
  4  union all select 2 id, 2 x, 1 y from dual
  5  union all select 3 id, 4 x, 3 y from dual
  6  union all select 4 id, 5 x, 4 y from dual
  7  )
  8  select id,
  9         x,
 10         y,
 11         cluster_id(into 2 using x, y) over() c,
 12         cluster_probability(into 2 using x, y) over() probability,
 13         cluster_distance(into 2 using x, y) over() distance,
 14         cast(cluster_set(into 2 using x, y) over() as tt_cluster) cluster_set,
 15         cluster_details(into 2 using x, y) over() cluster_details
 16    from t;

 ID   X   Y   C  PROBABILITY    DISTANCE CLUSTER_SET(CLUSTER_ID, PROBABILITY)
--- --- --- --- ------------ ----------- ----------------------------------------------------------------------
CLUSTER_DETAILS
-----------------------------------------------------------------
  1   1   2   3  0.999841564    9.140625 TT_CLUSTER(TO_CLUSTER(3, 9.998E-001), TO_CLUSTER(2, 1.584E-004))
<Details algorithm="K-Means Clustering" cluster="3">
<Attribute name="X" actualValue="1" weight=".119" rank="1"/>
<Attribute name="Y" actualValue="2" weight=".001" rank="2"/>
</Details>

  2   2   1   3  0.999915189    9.765625 TT_CLUSTER(TO_CLUSTER(3, 9.999E-001), TO_CLUSTER(2, 8.481E-005))
<Details algorithm="K-Means Clustering" cluster="3">
<Attribute name="Y" actualValue="1" weight=".033" rank="1"/>
<Attribute name="X" actualValue="2" weight=".002" rank="2"/>
</Details>

  3   4   3   2  0.995390428    5.765625 TT_CLUSTER(TO_CLUSTER(2, 9.954E-001), TO_CLUSTER(3, 4.61E-003))
<Details algorithm="K-Means Clustering" cluster="2">
<Attribute name="X" actualValue="4" weight=".115" rank="1"/>
<Attribute name="Y" actualValue="3" weight=".028" rank="2"/>
</Details>

  4   5   4   2  0.999997098   13.140625 TT_CLUSTER(TO_CLUSTER(2, 1.0E+000), TO_CLUSTER(3, 2.902E-006))
<Details algorithm="K-Means Clustering" cluster="2">
<Attribute name="X" actualValue="5" weight=".002" rank="1"/>
<Attribute name="Y" actualValue="4" weight=".001" rank="2"/>
</Details>

with
tree(id, parent, value) as
(
select 1, null, 100
union all select 2, 1, 1
union all select 0, 1, 1
union all select 3, 2, 11
union all select 4, 2, 1
union all select 5, 2, 111
union all select 22, 0, 222
)
, r(lvl, id, parent, sm) as
(
select 1, id, parent, sum(value) over ()
  from tree
 where parent is null
union all
select lvl+1, t.id, t.parent, sum(value) over ()
  from r
  join tree t on t.parent = r.id
)
select *
from r;

lvl         id          parent      sm
----------- ----------- ----------- -----------
1           1           NULL        100
2           2           1           2
2           0           1           2
3           22          0           222
3           3           2           123
3           4           2           123
3           5           2           123

(7 rows affected)

postgres=# with recursive
postgres-# tree(id, parent, value) as
postgres-# (
postgres(# select 1, null, 100
postgres(# union all select 2, 1, 1
postgres(# union all select 0, 1, 1
postgres(# union all select 3, 2, 11
postgres(# union all select 4, 2, 1
postgres(# union all select 5, 2, 111
postgres(# union all select 22, 0, 222
postgres(# )
postgres-# , r(lvl, id, parent, sm) as
postgres-# (
postgres(# select 1, id, parent, sum(value) over ()
postgres(#   from tree
postgres(#  where parent is null
postgres(# union all
postgres(# select lvl+1, t.id, t.parent, sum(value) over ()
postgres(#   from r
postgres(#   join tree t on t.parent = r.id
postgres(# )
postgres-# select *
postgres-# from r;
 lvl | id | parent | sm
-----+----+--------+-----
   1 |  1 |        | 100
   2 |  0 |      1 |   2
   2 |  2 |      1 |   2
   3 | 22 |      0 | 345
   3 |  5 |      2 | 345
   3 |  4 |      2 | 345
   3 |  3 |      2 | 345
(7 rows)

SQL> with
  2  tree(id, parent, value) as
  3  (
  4  select 1, null, 100 from dual
  5  union all select 2, 1, 1 from dual
  6  union all select 0, 1, 1 from dual
  7  union all select 3, 2, 11 from dual
  8  union all select 4, 2, 1 from dual
  9  union all select 5, 2, 111 from dual
 10  union all select 22, 0, 222 from dual
 11  )
 12  , r(lvl, id, parent, sm) as
 13  (
 14  select 1, id, parent, sum(value) over ()
 15    from tree
 16   where parent is null
 17  union all
 18  select lvl+1, t.id, t.parent, sum(value) over ()
 19    from r
 20    join tree t on t.parent = r.id
 21  )
 22  select *
 23  from r;

       LVL         ID     PARENT         SM
---------- ---------- ---------- ----------
         1          1                   100
         2          2          1          2
         2          0          1          2
         3          3          2        345
         3          4          2        345
         3          5          2        345
         3         22          0        345

7 rows selected.