set send_logs_level='trace';

Aggregator: Merging aggregated data
Current memory usage (for query): 6.00 GiB.
так секунд 30
Current memory usage (for query): 17.00 GiB.

v21.3.2.5-lts

select
    raw.id,
    raw.similar_ids,
    raw.agg_from_id,
    sum(raw2.agg) as sim_ids_agg
from
    -- flattening of similar_ids
    (
        select
            id,
            similar_ids,
            arrayJoin(similar_ids) as flattened,
            agg as agg_from_id
        from
            -- table t
        (
                      select 1 as id, [2,3] as similar_ids, 3 as agg
            union all select 2 as id, [1,3] as similar_ids, 4 as agg
            union all select 3 as id, [1,2] as similar_ids, 5 as agg
            union all select 4 as id, [1,2, 3] as similar_ids, 10 as agg
            )
    ) as raw join
    -- copy of table t
    (
                      select 1 as id, [2,3] as similar_ids, 3 as agg
            union all select 2 as id, [1,3] as similar_ids, 4 as agg
            union all select 3 as id, [1,2] as similar_ids, 5 as agg
            union all select 4 as id, [1,2, 3] as similar_ids, 10 as agg
    ) as raw2 on raw.flattened = raw2.id
group by
    raw.id,
    raw.similar_ids,
    raw.agg_from_id