Calculating aggregation functions for each element of an array

[m.fri...@immowelt.de]

Hi there.

We have previously stored HyperLogLog sketches in SQL Server, as 256 columns of tinyint type. These sketches have a nice property that you can aggregate them, by calculating max of each byte. 

After converting them to Clickhouse in a single column of type Array(Int8), we have discovered that an aggregation using

select array(
max(sketch[1]),max(sketch[2]),max(sketch[3]),max(sketch[4]),max(sketch[5]),max(sketch[6]),max(sketch[7]),max(sketch[8]),max(sketch[9]),max(sketch[10]),max(sketch[11]),max(sketch[12]),max(sketch[13]),max(sketch[14]),max(sketch[15]),max(sketch[16]),max(sketch[17]),max(sketch[18]),max(sketch[19]),max(sketch[20]),max(sketch[21]),max(sketch[22]),max(sketch[23]),max(sketch[24]),max(sketch[25]),max(sketch[26]),max(sketch[27]),max(sketch[28]),max(sketch[29]),max(sketch[30]),max(sketch[31]),max(sketch[32]),max(sketch[33]),max(sketch[34]),max(sketch[35]),max(sketch[36]),max(sketch[37]),max(sketch[38]),max(sketch[39]),max(sketch[40]),max(sketch[41]),max(sketch[42]),max(sketch[43]),max(sketch[44]),max(sketch[45]),max(sketch[46]),max(sketch[47]),max(sketch[48]),max(sketch[49]),max(sketch[50]),max(sketch[51]),max(sketch[52]),max(sketch[53]),max(sketch[54]),max(sketch[55]),max(sketch[56]),max(sketch[57]),max(sketch[58]),max(sketch[59]),max(sketch[60]),max(sketch[61]),max(sketch[62]),max(sketch[63]),max(sketch[64]),max(sketch[65]),max(sketch[66]),max(sketch[67]),max(sketch[68]),max(sketch[69]),max(sketch[70]),max(sketch[71]),max(sketch[72]),max(sketch[73]),max(sketch[74]),max(sketch[75]),max(sketch[76]),max(sketch[77]),max(sketch[78]),max(sketch[79]),max(sketch[80]),max(sketch[81]),max(sketch[82]),max(sketch[83]),max(sketch[84]),max(sketch[85]),max(sketch[86]),max(sketch[87]),max(sketch[88]),max(sketch[89]),max(sketch[90]),max(sketch[91]),max(sketch[92]),max(sketch[93]),max(sketch[94]),max(sketch[95]),max(sketch[96]),max(sketch[97]),max(sketch[98]),max(sketch[99]),max(sketch[100]),max(sketch[101]),max(sketch[102]),max(sketch[103]),max(sketch[104]),max(sketch[105]),max(sketch[106]),max(sketch[107]),max(sketch[108]),max(sketch[109]),max(sketch[110]),max(sketch[111]),max(sketch[112]),max(sketch[113]),max(sketch[114]),max(sketch[115]),max(sketch[116]),max(sketch[117]),max(sketch[118]),max(sketch[119]),max(sketch[120]),max(sketch[121]),max(sketch[122]),max(sketch[123]),max(sketch[124]),max(sketch[125]),max(sketch[126]),max(sketch[127]),max(sketch[128]),max(sketch[129]),max(sketch[130]),max(sketch[131]),max(sketch[132]),max(sketch[133]),max(sketch[134]),max(sketch[135]),max(sketch[136]),max(sketch[137]),max(sketch[138]),max(sketch[139]),max(sketch[140]),max(sketch[141]),max(sketch[142]),max(sketch[143]),max(sketch[144]),max(sketch[145]),max(sketch[146]),max(sketch[147]),max(sketch[148]),max(sketch[149]),max(sketch[150]),max(sketch[151]),max(sketch[152]),max(sketch[153]),max(sketch[154]),max(sketch[155]),max(sketch[156]),max(sketch[157]),max(sketch[158]),max(sketch[159]),max(sketch[160]),max(sketch[161]),max(sketch[162]),max(sketch[163]),max(sketch[164]),max(sketch[165]),max(sketch[166]),max(sketch[167]),max(sketch[168]),max(sketch[169]),max(sketch[170]),max(sketch[171]),max(sketch[172]),max(sketch[173]),max(sketch[174]),max(sketch[175]),max(sketch[176]),max(sketch[177]),max(sketch[178]),max(sketch[179]),max(sketch[180]),max(sketch[181]),max(sketch[182]),max(sketch[183]),max(sketch[184]),max(sketch[185]),max(sketch[186]),max(sketch[187]),max(sketch[188]),max(sketch[189]),max(sketch[190]),max(sketch[191]),max(sketch[192]),max(sketch[193]),max(sketch[194]),max(sketch[195]),max(sketch[196]),max(sketch[197]),max(sketch[198]),max(sketch[199]),max(sketch[200]),max(sketch[201]),max(sketch[202]),max(sketch[203]),max(sketch[204]),max(sketch[205]),max(sketch[206]),max(sketch[207]),max(sketch[208]),max(sketch[209]),max(sketch[210]),max(sketch[211]),max(sketch[212]),max(sketch[213]),max(sketch[214]),max(sketch[215]),max(sketch[216]),max(sketch[217]),max(sketch[218]),max(sketch[219]),max(sketch[220]),max(sketch[221]),max(sketch[222]),max(sketch[223]),max(sketch[224]),max(sketch[225]),max(sketch[226]),max(sketch[227]),max(sketch[228]),max(sketch[229]),max(sketch[230]),max(sketch[231]),max(sketch[232]),max(sketch[233]),max(sketch[234]),max(sketch[235]),max(sketch[236]),max(sketch[237]),max(sketch[238]),max(sketch[239]),max(sketch[240]),max(sketch[241]),max(sketch[242]),max(sketch[243]),max(sketch[244]),max(sketch[245]),max(sketch[246]),max(sketch[247]),max(sketch[248]),max(sketch[249]),max(sketch[250]),max(sketch[251]),max(sketch[252]),max(sketch[253]),max(sketch[254]),max(sketch[255]),max(sketch[256])
) as final_sketch

took quite a long time and we had to look for a solution.

One possibility was to use the built-in uniqState and uniqMerge functions, which perform better, but also require big changes in the data format.

Therefore, we have tried to implement a new kind of function suffix in Clickhouse: aggForEach, where agg is any aggregation function. These functions apply nested aggreation functions for each element of the array separately and produce an array as a result, so the previous query can be rewritten like this:

select maxForEach(sketch)

We have achieved some performance speed up, 40% on our very small and specific data set - statistically not significant, I think in real use it could bring below 10% or so.

Some other interesting uses of this feature could be "select countForEach(arr)" for arrays of different sizes or "select groupArrayForEach(arr)" producing a transponded 2D array.

As we don't have any current C++ experience (we have used this language 15 years ago last time), the implementation is very experimental.

Is somebody interested in looking at it, making a code review and bringing it to a production grade quality?

Best Regards, Maxim

[Alex Zatelepin]

Yes, we are interested! Please send a pull request.

[man...@gmail.com]

We really need that function, and we definitely will help you with code review.