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 16SELECT IMPORT('common.counters'); 17SELECT IMPORT('common.timestamps'); 18 19SELECT CREATE_VIEW_FUNCTION( 20 'INTERNAL_NUMBER_GENERATOR(to INT)', 21 'num INT', 22 'WITH NUMS AS 23 (SELECT 1 num UNION SELECT num + 1 24 from NUMS 25 WHERE num < $to) 26 SELECT num FROM NUMS;' 27); 28 29-- 30-- Get durations for percentile 31-- 32 33-- All percentiles (range 1-100) for counter track ID in a given time range. 34-- 35-- Percentiles are calculated by: 36-- 1. Dividing the sum of duration in time range for each value in the counter 37-- by duration of the counter in range. This gives us `percentile_for)value` (DOUBLE). 38-- 2. Fetching each percentile by taking floor of each `percentile_for_value`, grouping by 39-- resulting `percentile` and MIN from value for each grouping. As we are rounding down, 40-- taking MIN assures most reliable data. 41-- 3. Filling the possible gaps in percentiles by getting the minimal value from higher 42-- percentiles for each gap. 43-- 44-- @arg counter_track_id INT Id of the counter track. 45-- @arg start_ts LONG Timestamp of start of time range. 46-- @arg end_ts LONG Timestamp of end of time range. 47-- @column percentile All of the numbers from 1 to 100. 48-- @column value Value for the percentile. 49SELECT CREATE_VIEW_FUNCTION( 50 'COUNTER_PERCENTILES_FOR_TIME_RANGE(counter_track_id INT, start_ts LONG, end_ts LONG)', 51 'percentile INT, value DOUBLE', 52 'WITH percentiles_for_value AS ( 53 SELECT 54 value, 55 (CAST(SUM(dur) OVER(ORDER BY value ASC) AS DOUBLE) / 56 ($end_ts - MAX($start_ts, EARLIEST_TIMESTAMP_FOR_COUNTER_TRACK($counter_track_id)))) * 100 57 AS percentile_for_value 58 FROM COUNTER_FOR_TIME_RANGE($counter_track_id, $start_ts, $end_ts) 59 ORDER BY value ASC 60 ), 61 with_gaps AS ( 62 SELECT 63 CAST(percentile_for_value AS INT) AS percentile, 64 MIN(value) AS value 65 FROM percentiles_for_value 66 GROUP BY percentile 67 ORDER BY percentile ASC) 68 SELECT 69 num AS percentile, 70 IFNULL(value, MIN(value) OVER (ORDER BY percentile DESC)) AS value 71 FROM INTERNAL_NUMBER_GENERATOR(100) AS nums 72 LEFT JOIN with_gaps ON with_gaps.percentile = nums.num 73 ORDER BY percentile DESC 74 ' 75); 76 77-- All percentiles (range 1-100) for counter track ID. 78-- 79-- @arg counter_track_id INT Id of the counter track. 80-- @column percentile All of the numbers from 1 to 100. 81-- @column value Value for the percentile. 82SELECT CREATE_VIEW_FUNCTION( 83 'COUNTER_PERCENTILES_FOR_TRACK(counter_track_id INT)', 84 'percentile INT, value DOUBLE', 85 'SELECT * FROM COUNTER_PERCENTILES_FOR_TIME_RANGE($counter_track_id, TRACE_START(), TRACE_END());' 86); 87 88-- Value for specific percentile (range 1-100) for counter track ID in time range. 89-- 90-- @arg counter_track_id INT Id of the counter track. 91-- @arg percentile INT Any of the numbers from 1 to 100. 92-- @arg start_ts LONG Timestamp of start of time range. 93-- @arg end_ts LONG Timestamp of end of time range. 94-- @ret DOUBLE Value for the percentile. 95SELECT CREATE_FUNCTION( 96 'COUNTER_TRACK_PERCENTILE_FOR_TIME(counter_track_id INT, percentile INT, start_ts LONG, end_ts LONG)', 97 'DOUBLE', 98 'SELECT value 99 FROM COUNTER_PERCENTILES_FOR_TIME_RANGE($counter_track_id, $start_ts, $end_ts) 100 WHERE percentile = $percentile;' 101); 102 103-- Value for specific percentile (range 1-100) for counter track ID. 104-- 105-- @arg counter_track_id INT Id of the counter track. 106-- @arg percentile INT Any of the numbers from 1 to 100. 107-- @ret DOUBLE Value for the percentile. 108SELECT CREATE_FUNCTION( 109 'COUNTER_TRACK_PERCENTILE(counter_track_id INT, percentile INT)', 110 'DOUBLE', 111 'SELECT COUNTER_TRACK_PERCENTILE_FOR_TIME($counter_track_id, $percentile, TRACE_START(), TRACE_END());' 112); 113 114