Average / Total sum over group

total_over_group.md

create table A (pk_col Int64, col1 Float64, col4 Float64, day Date) Engine=Memory
as select * from values( 
            (1, 3, 4, today()), (1, 1, 4, today()), (1, 2, 0, today()-1), 
            (2, 3, 4, today()), (2, 3, 0, today()-1) );

Window function

select pk_col, day, avgMerge(avgS) over (partition by pk_col) * c res
from  (select pk_col, day,
             avgState( col1 ) avgS ,  
             countIf(col4 = 0) c
      from A
      group by pk_col, day)
order by pk_col, day;

┌─pk_col─┬────────day─┬─res─┐
│      1 │ 2023-03-16 │   2 │
│      1 │ 2023-03-17 │   0 │
│      2 │ 2023-03-16 │   3 │
│      2 │ 2023-03-17 │   0 │
└────────┴────────────┴─────┘

Arrays / groupArray

select pk_col, (arrayJoin(ga) as g).2 day, g.1 * avg_ res
from (
  select pk_col, avgMerge(avgS) avg_, groupArray( (c, day) ) ga
  from  (select pk_col, day,
                avgState( col1 ) avgS ,  
                countIf(col4 = 0) c
         from A
         group by pk_col, day)
  group by pk_col)
order by pk_col, day;

┌─pk_col─┬────────day─┬─res─┐
│      1 │ 2023-03-16 │   2 │
│      1 │ 2023-03-17 │   0 │
│      2 │ 2023-03-16 │   3 │
│      2 │ 2023-03-17 │   0 │
└────────┴────────────┴─────┘

Arrays / sumMap

select pk_col, (arrayJoin(c)as g).1 day, g.2 * avg_ res
from (
      select pk_col, avg(col1) avg_,
        arrayReduce('sumMap',
          [cast(groupArray((day, col4 = 0)), 'Map(Date, Int64)')]) c
       from A
       group by pk_col)
order by pk_col, day; 

┌─pk_col─┬────────day─┬─res─┐
│      1 │ 2023-03-16 │   2 │
│      1 │ 2023-03-17 │   0 │
│      2 │ 2023-03-16 │   3 │
│      2 │ 2023-03-17 │   0 │
└────────┴────────────┴─────┘

Join

select pk_col, day, c * avg_ res
from 
   (select pk_col, day, countIf(col4 = 0) c
    from A group by pk_col, day) t
   join 
    (select pk_col, avg(col1) avg_ from A group by pk_col) t2
    using pk_col
order by pk_col, day;

┌─pk_col─┬────────day─┬─res─┐
│      1 │ 2023-03-16 │   2 │
│      1 │ 2023-03-17 │   0 │
│      2 │ 2023-03-16 │   3 │
│      2 │ 2023-03-17 │   0 │
└────────┴────────────┴─────┘