Materialized views are a great tool for aggregating data in CH since they are automatically updated on insert from the original table. I recommend you to take a look and try it out, maybe it'll go down to single digit milliseconds!
And there are 2 kinds of those: the other is refreshable materialized views, which run on schedule, can have dependencies between them, thus can implement quite complex data transformation pipelines.
ansgri|4 months ago