all subsets of lists in KDB+

Question

I am new to KDB. I have a table in following format:

id        date  name  order
34  2020.01.20  John     10
23  2020.01.20  John    -20
21  2020.01.20  John     30
43  2020.01.20  John   -400
44  2020.01.20   Dan  -6483  
22  2020.01.20   Dan   8796

The sample table can be created as follows:

t:([]id:(34, 23, 21, 43, 44, 22); date:(2020.01.20; 2020.01.20; 2020.01.20; 2020.01.20; 2020.01.20; 2020.01.20); name:(`John`John`John`John`Dan`Dan); order:(10, -20, 30, -400, -6483, 8796));

I want all the subsets of orders possible for any given date and name in the below format. Now the order column below is sum of order value for id and all the ids.

id        date  name  order    ids
34  2020.01.20  John     10     0n
34  2020.01.20  John    -10     23
34  2020.01.20  John     40     21
34  2020.01.20  John   -390     43
34  2020.01.20  John     20     23, 21
34  2020.01.20  John   -360     21, 43
34  2020.01.20  John   -380     23, 21, 43
23  2020.01.20  John    -20     0n
23  2020.01.20  John    -10     34
23  2020.01.20  John     10     21
23  2020.01.20  John   -420     43
23  2020.01.20  John     20     34, 21
23  2020.01.20  John   -390     21, 43
23  2020.01.20  John   -380     34, 21, 43
21  2020.01.20  John     30     0n
21  2020.01.20  John     40     34
21  2020.01.20  John     10     23
21  2020.01.20  John   -370     43
21  2020.01.20  John     20     34, 23
21  2020.01.20  John     20     23, 43
21  2020.01.20  John   -380     34, 23, 43
43  2020.01.20  John   -400     0n
43  2020.01.20  John   -390     34
43  2020.01.20  John   -420     23
43  2020.01.20  John   -370     21
43  2020.01.20  John   -410     34, 23
43  2020.01.20  John   -390     23, 21
43  2020.01.20  John   -380     34, 23, 21
44  2020.01.20   Dan  -6483     0n
44  2020.01.20   Dan   2313     22
22  2020.01.20   Dan   8796     0n
22  2020.01.20   Dan   2313     44

Answer 1

This gets you part of the way, though this approach has an extra combination that you seem to exclude (you can exclude these afterwards if need be):

q)comb:{$[type b:(count[a:x except y]-1)(01b cross)/01b;(`long$();a);a where each b]};
q)update sum each order from ungroup ungroup select id,order:(order,/:'order i?comb[i]each i),ids:id i?comb[i]each i by date,name from t
date       name id order ids
---------------------------------
2020.01.20 Dan  44 -6483 `long$()
2020.01.20 Dan  44 2313  ,22
2020.01.20 Dan  22 8796  `long$()
2020.01.20 Dan  22 2313  ,44
2020.01.20 John 34 10    `long$()
2020.01.20 John 34 -390  ,43
2020.01.20 John 34 40    ,21
2020.01.20 John 34 -360  21 43
2020.01.20 John 34 -10   ,23
2020.01.20 John 34 -410  23 43
2020.01.20 John 34 20    23 21
2020.01.20 John 34 -380  23 21 43
2020.01.20 John 23 -20   `long$()
2020.01.20 John 23 -420  ,43
...

Answer 2

Not sure if this is the most optimal solution, but below snippet will do what you are looking for:

orderMap: (!) . t`id`order;  
subsets: ungroup
  update ids: {x where each (count[x]-1){x cross 01b}/01b} each ids from 
  select ids: id by name from t;
t: ej[`name;t;subsets];
t: delete from t where id in' ids;
t: update order: order + sum each orderMap@/:ids from  t;
t

For consistency, ids column is created as list of integer lists and empty array `long$() is used instead of 0n

More detaily:

1. orderMap: (!) . t`id`order gives id-to-order mapping. Here I use assume that ids are unique.
2. subsets is table of names and id subsets assigned to the name. {(count[x]-1){x cross 01b}/01b} returns "include" flags which help forming subsets, e.g. 0000b, 1000b, 0100b, .... Could be done more efficiently with integers binary representation.
3. ej[`name;t;subsets] - joins original table with id subsets by name.
4. delete from t where id in' ids deletes rows where id is included into ids subset.
5. update order: order + sum each orderMap@/:ids from t sums up order of id and ids' subset by using orderMap