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-- 16CREATE VIEW battery_view AS 17SELECT 18 all_ts.ts as ts, 19 current_avg_ua, 20 capacity_percent, 21 charge_uah, 22 current_ua 23FROM ( 24 SELECT distinct(ts) AS ts 25 FROM counter c 26 JOIN counter_track t on c.track_id = t.id 27 WHERE name LIKE 'batt.%' 28) AS all_ts 29LEFT JOIN ( 30 SELECT ts, value AS current_avg_ua 31 FROM counter c 32 JOIN counter_track t on c.track_id = t.id 33 WHERE name='batt.current.avg_ua' 34) USING(ts) 35LEFT JOIN ( 36 SELECT ts, value AS capacity_percent 37 FROM counter c 38 JOIN counter_track t on c.track_id = t.id 39 WHERE name='batt.capacity_pct' 40) USING(ts) 41LEFT JOIN ( 42 SELECT ts, value AS charge_uah 43 FROM counter c 44 JOIN counter_track t on c.track_id = t.id 45 WHERE name='batt.charge_uah' 46) USING(ts) 47LEFT JOIN ( 48 SELECT ts, value AS current_ua 49 FROM counter c 50 JOIN counter_track t on c.track_id = t.id 51 WHERE name='batt.current_ua' 52) USING(ts) 53ORDER BY ts; 54 55DROP TABLE IF EXISTS android_batt_wakelocks_merged; 56CREATE TABLE android_batt_wakelocks_merged AS 57SELECT 58 MIN(ts) AS ts, 59 MAX(ts_end) AS ts_end 60FROM ( 61 SELECT 62 *, 63 SUM(new_group) OVER (ORDER BY ts) AS group_id 64 FROM ( 65 SELECT 66 ts, 67 ts + dur AS ts_end, 68 -- There is a new group if there was a gap before this wakelock. 69 -- i.e. the max end timestamp of all preceding wakelocks is before 70 -- the start timestamp of this one. 71 -- The null check is for the first row which is always a new group. 72 IFNULL( 73 MAX(ts + dur) OVER ( 74 ORDER BY ts 75 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING 76 ) < ts, 77 true 78 ) AS new_group 79 FROM slice 80 WHERE slice.name LIKE 'WakeLock %' AND dur != -1 81 ) 82) 83GROUP BY group_id; 84 85CREATE TABLE suspend_slice_ AS 86SELECT 87 ts, 88 dur 89FROM ( 90 SELECT 91 ts, 92 ts - lag(ts) OVER w AS lag_dur, 93 lead(ts) OVER w - ts AS dur, 94 start, 95 event, 96 lag(start) OVER w AS lag_start, 97 lag(event) OVER w AS lag_event, 98 lag(start, 2) OVER w AS lag_2_start, 99 lag(event, 2) OVER w AS lag_2_event, 100 lead(start) OVER w AS lead_start, 101 lead(event) OVER w AS lead_event, 102 lead(start, 2) OVER w AS lead_2_start, 103 lead(event, 2) OVER w AS lead_2_event 104 FROM ( 105 SELECT 106 ts, 107 EXTRACT_ARG(arg_set_id, 'start') AS start, 108 EXTRACT_ARG(arg_set_id, 'val') AS event 109 FROM raw 110 WHERE name = 'suspend_resume' 111 ) 112 WINDOW w AS (ORDER BY ts) 113) 114-- We want to find the start and end events with action='timekeeping_freeze'. 115-- Unfortunately b/70292203 leads to the action string being lost. 116-- In practice, these events often show up in a sequence like the following: 117-- start = 1, event = 1 [string would have been 'machine_suspend'] 118-- start = 1, event = (any) [string would have been 'timekeeping_freeze'] * 119-- 120-- (sleep happens here) 121-- 122-- start = 0, event = (any) [string would have been 'timekeeping_freeze'] 123-- start = 0, event = 1 [string would have been 'machine_suspend'] 124-- 125-- So we look for this pattern of start and event, anchored on the event marked 126-- with "*". 127WHERE ( 128 lag_start = 1 AND lag_event = 1 129 AND start = 1 130 AND lead_start = 0 131 AND lead_2_start = 0 AND lead_2_event = 1 132) 133-- Or in newer kernels we seem to have a very different pattern. We can take 134-- advantage of that fact that we get several events with identical timestamp 135-- just before sleeping (normally this never happens): 136-- gap = 0, start = 1, event = 3 137-- gap = 0, start = 0, event = 3 138-- gap = 0, start = 1, event = 0 139-- 140-- (sleep happens here) 141-- 142-- gap = (any), start = 0, event = 0 143 144OR ( 145 lag_dur = 0 146 AND lead_start = 0 AND lead_event = 0 147 AND start = 1 AND event = 0 148 AND lag_start = 0 AND lag_event = 3 149 AND lag_2_start = 1 AND lag_2_event = 3 150); 151 152SELECT RUN_METRIC('android/global_counter_span_view.sql', 153 'table_name', 'screen_state', 154 'counter_name', 'ScreenState'); 155 156CREATE VIRTUAL TABLE screen_state_span_with_suspend 157 USING span_join(screen_state_span, suspend_slice_); 158 159CREATE VIEW android_batt_event AS 160SELECT 161 ts, 162 dur, 163 'Suspended' AS slice_name, 164 'Suspend / resume' AS track_name, 165 'slice' AS track_type 166FROM suspend_slice_; 167 168CREATE VIEW android_batt_output AS 169SELECT AndroidBatteryMetric( 170 'battery_counters', ( 171 SELECT RepeatedField( 172 AndroidBatteryMetric_BatteryCounters( 173 'timestamp_ns', ts, 174 'charge_counter_uah', charge_uah, 175 'capacity_percent', capacity_percent, 176 'current_ua', current_ua, 177 'current_avg_ua', current_avg_ua 178 ) 179 ) 180 FROM battery_view 181 ), 182 'battery_aggregates', ( 183 SELECT NULL_IF_EMPTY(AndroidBatteryMetric_BatteryAggregates( 184 'total_screen_off_ns', 185 SUM(CASE WHEN state = 1.0 AND tbl = 'total' THEN dur ELSE 0 END), 186 'total_screen_on_ns', 187 SUM(CASE WHEN state = 2.0 AND tbl = 'total' THEN dur ELSE 0 END), 188 'total_screen_doze_ns', 189 SUM(CASE WHEN state = 3.0 AND tbl = 'total' THEN dur ELSE 0 END), 190 'sleep_ns', 191 (SELECT SUM(dur) FROM suspend_slice_), 192 'sleep_screen_off_ns', 193 SUM(CASE WHEN state = 1.0 AND tbl = 'sleep' THEN dur ELSE 0 END), 194 'sleep_screen_on_ns', 195 SUM(CASE WHEN state = 2.0 AND tbl = 'sleep' THEN dur ELSE 0 END), 196 'sleep_screen_doze_ns', 197 SUM(CASE WHEN state = 3.0 AND tbl = 'sleep' THEN dur ELSE 0 END), 198 'total_wakelock_ns', 199 (SELECT SUM(ts_end - ts) FROM android_batt_wakelocks_merged) 200 )) 201 FROM ( 202 SELECT dur, screen_state_val AS state, 'total' AS tbl 203 FROM screen_state_span 204 UNION ALL 205 SELECT dur, screen_state_val AS state, 'sleep' AS tbl 206 FROM screen_state_span_with_suspend 207 ) 208 ) 209); 210