1-- 2-- Copyright 2019 The Android Open Source Project 3-- 4-- Licensed under the Apache License, Version 2.0 (the "License"); 5-- you may not use this file except in compliance with the License. 6-- You may obtain a copy of the License at 7-- 8-- https://www.apache.org/licenses/LICENSE-2.0 9-- 10-- Unless required by applicable law or agreed to in writing, software 11-- distributed under the License is distributed on an "AS IS" BASIS, 12-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13-- See the License for the specific language governing permissions and 14-- limitations under the License. 15-- 16 17-- View of Power Rail counters with ts converted from ns to ms. 18DROP VIEW IF EXISTS power_rails_counters; 19CREATE VIEW power_rails_counters AS 20SELECT value, ts / 1000000 AS ts, name 21FROM counter c 22JOIN counter_track t ON c.track_id = t.id 23WHERE name GLOB 'power.*'; 24 25DROP VIEW IF EXISTS avg_used_powers; 26CREATE VIEW avg_used_powers AS 27SELECT 28 name, 29 avg_used_power, 30 tot_used_power, 31 powrail_start_ts, 32 powrail_end_ts 33FROM ( 34 SELECT 35 name, 36 (LEAD(value) OVER (PARTITION BY name ORDER BY ts) - value) 37 / (LEAD(ts) OVER (PARTITION BY name ORDER BY ts) - ts) AS avg_used_power, 38 (LEAD(value) OVER (PARTITION BY name ORDER BY ts) - value) AS tot_used_power, 39 ts AS powrail_start_ts, 40 (LEAD(ts) OVER (PARTITION BY name ORDER BY ts)) AS powrail_end_ts 41 FROM ( 42 SELECT name, MIN(ts) AS ts, value 43 FROM power_rails_counters 44 GROUP BY name 45 UNION 46 SELECT name, MAX(ts) AS ts, value 47 FROM power_rails_counters 48 GROUP BY name 49 ) 50 ORDER BY name, ts 51) WHERE avg_used_power IS NOT NULL; 52 53DROP VIEW IF EXISTS power_rails_view; 54CREATE VIEW power_rails_view AS 55WITH RECURSIVE name AS (SELECT DISTINCT name FROM power_rails_counters) 56SELECT 57 name, 58 ts, 59 AndroidPowerRails_PowerRails( 60 'name', name, 61 'energy_data', RepeatedField( 62 AndroidPowerRails_EnergyData( 63 'timestamp_ms', ts, 64 'energy_uws', value 65 ) 66 ), 67 'avg_used_power_mw', (SELECT avg_used_power FROM avg_used_powers 68 WHERE avg_used_powers.name = power_rails_counters.name) 69 ) AS power_rails_proto 70FROM power_rails_counters 71GROUP BY name 72ORDER BY ts ASC; 73 74DROP VIEW IF EXISTS android_powrails_output; 75CREATE VIEW android_powrails_output AS 76SELECT AndroidPowerRails( 77 'power_rails', ( 78 SELECT RepeatedField(power_rails_proto) 79 FROM power_rails_view 80 ), 81 'avg_total_used_power_mw', ( 82 SELECT SUM(tot_used_power) / (MAX(powrail_end_ts) - MIN(powrail_start_ts)) FROM avg_used_powers 83 ) 84); 85