Skip to content

Instantly share code, notes, and snippets.

@emakarov
Created April 3, 2019 15:56
Show Gist options
  • Save emakarov/d781de2edf93163c106dac027c6e12d7 to your computer and use it in GitHub Desktop.
Save emakarov/d781de2edf93163c106dac027c6e12d7 to your computer and use it in GitHub Desktop.
clickhouse user cpu calculation
create temporary table cputime (dt Date, ts Int8, cpu Int8, user_id Int8);
insert into cputime values
('2019-01-01', 1, 0, 1), ('2019-01-01', 2, 1, 1), ('2019-01-01', 3, 1, 1),
('2019-01-01', 4, 0, 1), ('2019-01-01', 5, 2, 1), ('2019-01-01', 6, 3, 1),
('2019-01-01', 1, 0, 2), ('2019-01-01', 2, 4, 2), ('2019-01-01', 3, 5, 2),
('2019-01-01', 4, 0, 2), ('2019-01-01', 5, 8, 2), ('2019-01-01', 6, 15, 2),
('2019-01-01', 7, 5, 2), ('2019-01-01', 8, 8, 2), ('2019-01-01', 9, 8, 2);
select
user_id,
groupArray(cpu) as arr_cpu,
arrayDifference(arr_cpu) as arr_cpu_diff,
arraySum(
arrayFilter(
x -> x>0,
arr_cpu_diff
)
) cpu_incorrect,
arrayMap(
(x, y) -> if(y >= 0, y, x),
arr_cpu,
arr_cpu_diff
) as vals,
arraySum(vals) as cpu_correct
from
(
select
ts, cpu, user_id
from cputime
order by ts
)
group by user_id;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment