I know I can do
raze table_list
But that require every single table in the table list with the same columns.
For my use case, I can addept adding columns with null values, if that column doesn’t exist in all tables.In that case, I can do
(uj) over table_list
But this line of code is very slow. nowhere as fast as raze.
Is there an efficient way of merging all tables, even if they don’t all have the same columns?
You’ll never get as far as raze with conforming tables but using global append (if you can accept the creation of a global) can be an improvement on uj over:
/conforming tabs
n:1000000;
tabs:{flipcol1col2col3col4col5col6!(n?100i;n?100j;n?.z.D;n?10f;n?.z.P;n?5h)}each til 10
q)\ts raze tabs
82 570426736
/non-conforming tabs
nctabs:{flip(6?`4)!(n?100i;n?100j;n?.z.D;n?10f;n?.z.P;n?5h)}each til 10
q)\ts a:(uj/)nctabs
5560 11729374832
/global append
q)\ts {t::(uj/)0#'x;{t,:x}each x}nctabs
3146 356520224
q)a~t
1b
Terry
for some high cost of memory you can also do
q)\ts {t::(uj/)0#'x;{t,:x}each x}nctabs
1470 356520192
q)\ts {raze((uj/)0#'x)uj/:x}nctabs
577 8912912032
Attila
Another solution: construct list of all table columns with their null values, update every table’s missing columns with appropriate nulls, reorder each table by union of column list, and flatten:
q)n:1000000;
q)tabs:{flipcol1col2col3col4col5col6!(n?100i;n?100j;n?.z.D;n?10f;n?.z.P;n?5h)}each til 10
q)\ts raze tabs
222 570428928
q)nctabs:{flip(6?`4)!(n?100i;n?100j;n?.z.D;n?10f;n?.z.P;n?5h)}each til 10
q)\ts a:(uj/)nctabs
8477 11729374832
// global append
q)\ts {t::(uj/)0#'x;{t,:x}each x}nctabs
3354 6060774704
// table update and raze
q)\ts b:raze nctabs {i:where not y[1] in cols x;y[1] xcols ![x;();0b;y[1][i]!y[0][i]]}: (raze {first each (0#x) cols x} each nctabs;raze cols each nctabs)
2817 8912913616
// uj empty tables, join orig and raze
q)\ts c:{raze((uj/)0#'x)uj/:x}nctabs
2180 8912912176
q)(a~b) and (b~c) and (c~t)
1b