1create view freq_view as 2 select 3 ts, 4 lead(ts) OVER (PARTITION BY name, ref ORDER BY ts) - ts as dur, 5 ref as cpu, 6 name as freq_name, 7 value as freq_value 8 from counters 9 where name = 'cpufreq' 10 and ref_type = 'cpu'; 11 12create view idle_view 13 as select 14 ts, 15 lead(ts) OVER (PARTITION BY name, ref ORDER BY ts) - ts as dur, 16 ref as cpu, 17 name as idle_name, 18 value as idle_value 19 from counters 20 where name = 'cpuidle' 21 and ref_type = 'cpu'; 22 23create virtual table freq_idle 24 using span_join(freq_view PARTITIONED cpu, idle_view PARTITIONED cpu) 25 26create virtual table window_freq_idle using window; 27 28create virtual table span_freq_idle 29 using span_join(freq_idle PARTITIONED cpu, window_freq_idle) 30 31update window_freq_idle 32 set 33 window_start=(select min(ts) from sched), 34 window_dur=(select max(ts) - min(ts) from sched), 35 quantum=1000000 36 where rowid = 0 37 38create view counter_view 39 as select 40 ts, 41 dur, 42 quantum_ts, 43 cpu, 44 case idle_value 45 when 4294967295 then "freq" 46 else "idle" 47 end as name, 48 case idle_value 49 when 4294967295 then freq_value 50 else idle_value 51 end as value 52 from span_freq_idle 53 54select cpu, name, value, sum(dur) from counter_view group by cpu, name, value 55