Aggregation function that respects the order of data about clickhouse HOT 7 CLOSED

This can be done in several ways.

1. groupArray -> arraySort -> arrayReduce

SELECT arrayReduce('functionA', arraySort(groupArray(x))) FROM R_table; 

2. materialized CTE #61086

WITH t AS MATERIALIZED (SELECT x FROM R_table ORDER BY x) SELECT functionA(x) FROM t;

from clickhouse.

@Alex-Cheng which aggregate function? https://fiddle.clickhouse.com/88a7eba5-01f5-41d9-9577-4865ca007def

Modern optimizer removes excessive order by if result of a query does not depend on it.
You may use limit to overcome optimizer.

select functionA(x) from (select `x` from `R_table` order by `x` 
   limit toUInt64(-1)   ------<<<<<<<<---
) settings max_threads=1

from clickhouse.

CH is not designed to do this, and you will face multiple problems and changes in the future because of this:

• CH is multithreaded by design, without any order guarantees. You can use max_threads=1
• CH does not guarantee in which order it will read the data.
• CH does not guarantee the size of the batches. It's usually max_block_size, but it's also not guaranteed.
• CH also does not guarantee in which order the aggregation states will be merged.

You can try the suggested workarounds, but they might stop working at any time.

The only similar thing to what you are describing are window functions.

from clickhouse.

CH is not designed to do this, and you will face multiple problems and changes in the future because of this:

• CH is multithreaded by design, without any order guarantees. You can use max_threads=1
• CH does not guarantee in which order it will read the data.
• CH does not guarantee the size of the batches. It's usually max_block_size, but it's also not guaranteed.
• CH also does not guarantee in which order the aggregation states will be merged.

You can try the suggested workarounds, but they might stop working at any time.

The only similar thing to what you are describing are window functions.

Yes, the approaches you suggests were used for resolving the problem, e.g. max_threads=1 and max_block_size=<very big number>. However as same as you mentioned, it stops working after upgrading to 24.3. I understood that the aggregation functions should not depend on data order. However my aggregation algorithm requires data odering for my special requirement. I am wondering if there is same requirement that aggregation depends on data ordering?

Is my requirement too special?

Maybe I should transform the aggregation function into a regular function that accepts Array as inputs, just as mentioned by @canhld94 .

from clickhouse.

The approach suggested by @den-crane (thank you, it is very skillful workaround) is working and the solution requires the least workload. It could be a temporary solution for a while, and finally I need to think out of an alternative algorithm that does not depend on the order of input data.

from clickhouse.

@Alex-Cheng check the code of groupArray.
I think there is (or was) some trait which allows to mark a function as dependent of data order.
Earlier groupArray 100% respected the order of input blocks. And even now it's mentioned in the doc: https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/grouparray
I think this trait forbids the optimizer to change a plan (remove orderbys) or something like this.

from clickhouse.

Earlier groupArray 100% respected the order of input blocks.

With important exception of GROUP BY in external memory AFAIK

from clickhouse.