1-- 2-- Copyright 2023 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 16INCLUDE PERFETTO MODULE deprecated.v42.common.counters; 17INCLUDE PERFETTO MODULE deprecated.v42.common.timestamps; 18 19CREATE PERFETTO FUNCTION _number_generator(upper_limit INT) 20RETURNS TABLE(num INT) AS 21WITH nums AS 22 (SELECT 1 num UNION SELECT num + 1 23 from NUMS 24 WHERE num < $upper_limit) 25SELECT num FROM nums; 26 27CREATE PERFETTO FUNCTION _earliest_timestamp_for_counter_track( 28 -- Id of a counter track with a counter. 29 counter_track_id INT) 30-- Timestamp of first counter value. Null if doesn't exist. 31RETURNS LONG AS 32SELECT MIN(ts) FROM counter WHERE counter.track_id = $counter_track_id; 33 34-- COUNTER_WITH_DUR_FOR_TRACK but in a specified time. 35-- Does calculation over the table ends - creates an artificial counter value at 36-- the start if needed and chops the duration of the last timestamps in range. 37CREATE PERFETTO FUNCTION _counter_for_time_range( 38 -- Id of track counter track. 39 counter_track_id INT, 40 -- Timestamp of the timerange start. 41 -- Can be earlier than the first counter value. 42 start_ts LONG, 43 -- Timestamp of the timerange end. 44 end_ts LONG) 45RETURNS TABLE( 46 -- Timestamp of the counter value. 47 ts LONG, 48 -- Duration of the counter value. 49 dur LONG, 50 -- Counter value. 51 value DOUBLE, 52 -- If of the counter track. 53 track_id INT, 54 -- Name of the counter track. 55 track_name STRING, 56 -- Counter track set id. 57 track_arg_set_id INT, 58 -- Counter arg set id. 59 arg_set_id INT 60) AS 61SELECT 62 IIF(ts < $start_ts, $start_ts, ts) AS ts, 63 IIF( 64 ts < $start_ts, 65 dur - ($start_ts - ts), 66 IIF(ts + dur > $end_ts, $end_ts - ts, dur)) AS dur, 67 value, 68 track_id, 69 track_name, 70 track_arg_set_id, 71 arg_set_id 72FROM counter_with_dur_for_track($counter_track_id) 73WHERE TRUE 74 AND ts + dur >= $start_ts 75 AND ts < $end_ts 76ORDER BY ts ASC; 77 78-- 79-- Get durations for percentile 80-- 81 82-- All percentiles (range 1-100) for counter track ID in a given time range. 83-- 84-- Percentiles are calculated by: 85-- 1. Dividing the sum of duration in time range for each value in the counter 86-- by duration of the counter in range. This gives us `percentile_for)value` (DOUBLE). 87-- 2. Fetching each percentile by taking floor of each `percentile_for_value`, grouping by 88-- resulting `percentile` and MIN from value for each grouping. As we are rounding down, 89-- taking MIN assures most reliable data. 90-- 3. Filling the possible gaps in percentiles by getting the minimal value from higher 91-- percentiles for each gap. 92CREATE PERFETTO FUNCTION counter_percentiles_for_time_range( 93 -- Id of the counter track. 94 counter_track_id INT, 95 -- Timestamp of start of time range. 96 start_ts LONG, 97 -- Timestamp of end of time range. 98 end_ts LONG) 99RETURNS TABLE( 100 -- All of the numbers from 1 to 100. 101 percentile INT, 102 -- Value for the percentile. 103 value DOUBLE 104) AS 105WITH percentiles_for_value AS ( 106 SELECT 107 value, 108 (CAST(SUM(dur) OVER(ORDER BY value ASC) AS DOUBLE) / 109 ($end_ts - MAX($start_ts, _earliest_timestamp_for_counter_track($counter_track_id)))) * 100 110 AS percentile_for_value 111 FROM _COUNTER_FOR_TIME_RANGE($counter_track_id, $start_ts, $end_ts) 112 ORDER BY value ASC 113), 114with_gaps AS ( 115 SELECT 116 CAST(percentile_for_value AS INT) AS percentile, 117 MIN(value) AS value 118 FROM percentiles_for_value 119 GROUP BY percentile 120 ORDER BY percentile ASC) 121SELECT 122 num AS percentile, 123 IFNULL(value, MIN(value) OVER (ORDER BY percentile DESC)) AS value 124FROM _NUMBER_GENERATOR(100) AS nums 125LEFT JOIN with_gaps ON with_gaps.percentile = nums.num 126ORDER BY percentile DESC; 127 128-- All percentiles (range 1-100) for counter track ID. 129CREATE PERFETTO FUNCTION counter_percentiles_for_track( 130 -- Id of the counter track. 131 counter_track_id INT) 132RETURNS TABLE( 133 -- All of the numbers from 1 to 100. 134 percentile INT, 135 -- Value for the percentile. 136 value DOUBLE 137) AS 138SELECT * 139FROM counter_percentiles_for_time_range( 140 $counter_track_id, trace_start(), trace_end()); 141 142-- Value for specific percentile (range 1-100) for counter track ID in time range. 143CREATE PERFETTO FUNCTION counter_track_percentile_for_time( 144 -- Id of the counter track. 145 counter_track_id INT, 146 -- Any of the numbers from 1 to 100. 147 percentile INT, 148 -- Timestamp of start of time range. 149 start_ts LONG, 150 -- Timestamp of end of time range. 151 end_ts LONG) 152-- Value for the percentile. 153RETURNS DOUBLE AS 154SELECT value 155FROM counter_percentiles_for_time_range($counter_track_id, $start_ts, $end_ts) 156WHERE percentile = $percentile; 157 158-- Value for specific percentile (range 1-100) for counter track ID. 159CREATE PERFETTO FUNCTION counter_track_percentile( 160 -- Id of the counter track. 161 counter_track_id INT, 162 -- Any of the numbers from 1 to 100. 163 percentile INT) 164-- Value for the percentile. 165RETURNS DOUBLE AS 166SELECT counter_track_percentile_for_time($counter_track_id, 167 $percentile, 168 trace_start(), 169 trace_end()); 170