I got this source table
| id | A | B |
|---|---|---|
| aaa | 0 | 1 |
| aaa | 0 | 1 |
| aaa | 1 | 0 |
| bbb | 0 | 1 |
I need to get this table
| id | A | B | C |
|---|---|---|---|
| aaa | 1 | 2 | 1 |
| bbb | 0 | 1 | 0 |
where C is calculated based in rule (A > 0 and B >= 2). So to calculate C I need to aggregated all data for an id.
I tried merge aggregations in materialized views and inner select queries, but if I got row in past with A=1 and row in a present with B=1 I got still 0 in C for id. Whats the right way to do it?
Simply
select id, sum(A) A, sum(B) B, (A > 0 and B >= 2) C
from test
group by id
order by id;
https://fiddle.clickhouse.com/0a843cc8-1f06-4288-a634-fb840c35c31d
You cannot calculate (A > 0 and B >= 2) in MatView because A & B are aggregated results after GROUPBY, you can calculate it only in the final SELECT to MatView.
Or you need to use Refreshable materialized view