sku in basket

gistfile1.txt

Denny Crane [Starship Captain at Altinity (NB,Canada)], [Dec 15, 2021 at 1:00:24 PM]:
create table x (order String, sku String) Engine = Memory;
insert into x values ('order1', 'banana') ('order1', 'bread')
insert into x values ('order2', 'banana') ('order2', 'bread')
insert into x values ('order3', 'banana') 
insert into x values ('order4', 'banana')  ('order4', 'milk') 
insert into x values ('order5', 'break')  ('order5', 'milk') 

select arrayJoin([g, g]), arrayElement(g.1, 1)
from 
   (select order, sumMap([sku], [toUInt64(1)]) g from x group by order)
group by sku   

select sku, arrayReverseSort( j -> j.2, arrayFilter( i -> i.1 <> sku, arrayZip(g__.1, g__.2))) x from (
select sku, sumMap(g_) g__ from (
select arrayJoin(g.1) sku, (flatten([g.1, g.1]), flatten([g.2, g.2]),) g_ from (
select order, sumMap([sku], [toUInt64(1)]) g from x group by order)
)
group by sku)

┌─sku────┬─x──────────────────────────┐
│ milk   │ [('banana',2),('break',2)] │
│ bread  │ [('banana',4)]             │
│ break  │ [('milk',2)]               │
│ banana │ [('bread',4),('milk',2)]   │
└────────┴────────────────────────────┘


кол-во умножено на 2, это легко починить джойня с 0, а не 1, но мне лень

Denny Crane [Starship Captain at Altinity (NB,Canada)], [Dec 15, 2021 at 1:01:02 PM (Dec 15, 2021 at 1:01:06 PM)]:
arrayJoin позволяет сделать декартово п.

а хотя 


select sku, arrayReverseSort( j -> j.2, arrayFilter( i -> i.1 <> sku, arrayZip(g__.1, g__.2))) x from (
select sku, sumMap(g_) g__ from (
select arrayJoin(g.1) sku, (flatten([g.1, g.1]), flatten([g.2, arrayMap(z -> 0, g.2)]),) g_ from (
select order, sumMap([sku], [toUInt64(1)]) g from x group by order)
)
group by sku)

┌─sku────┬─x──────────────────────────┐
│ milk   │ [('banana',1),('break',1)] │
│ bread  │ [('banana',2)]             │
│ break  │ [('milk',1)]               │
│ banana │ [('bread',2),('milk',1)]   │
└────────┴────────────────────────────┘