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.timestamps'); 17 18-- Timestamp of first counter value in a counter. 19-- 20-- @arg counter_track_id INT Id of a counter track with a counter. 21-- @ret LONG Timestamp of first counter value. Null if doesn't exist. 22SELECT CREATE_FUNCTION( 23 'EARLIEST_TIMESTAMP_FOR_COUNTER_TRACK(counter_track_id INT)', 24 'LONG', 25 'SELECT MIN(ts) FROM counter WHERE counter.track_id = $counter_track_id;' 26); 27 28 29-- Counter values with details of counter track with calculated duration of each counter value. 30-- Duration is calculated as time from counter to the next counter. 31-- 32-- @arg counter_track_id INT Id of track counter track. 33-- @column ts Timestamp of the counter value. 34-- @column dur Duration of the counter value. 35-- @column value Counter value. 36-- @column track_id If of the counter track. 37-- @column track_name Name of the counter track. 38-- @column track_arg_set_id Counter track set id. 39-- @column arg_set_id Counter arg set id. 40SELECT CREATE_VIEW_FUNCTION( 41 'COUNTER_WITH_DUR_FOR_TRACK(counter_track_id INT)', 42 ' 43 ts LONG, 44 dur LONG, 45 value DOUBLE, 46 track_id INT, 47 track_name STRING, 48 track_arg_set_id INT, 49 arg_set_id INT 50 ', 51 ' 52 SELECT 53 ts, 54 LEAD(ts, 1, TRACE_END()) OVER(ORDER BY ts) - ts AS dur, 55 value, 56 track.id AS track_id, 57 track.name AS track_name, 58 track.source_arg_set_id AS track_arg_set_id, 59 counter.arg_set_id AS arg_set_id 60 FROM counter 61 JOIN counter_track track ON track.id = counter.track_id 62 WHERE track.id = $counter_track_id 63 ' 64); 65 66-- COUNTER_WITH_DUR_FOR_TRACK but in a specified time. 67-- Does calculation over the table ends - creates an artificial counter value at 68-- the start if needed and chops the duration of the last timestamps in range. 69-- 70-- @arg counter_track_id INT Id of track counter track. 71-- @arg start_ts LONG Timestamp of the timerange start. 72-- Can be earlier than the first counter value. 73-- @arg end_ts LONG Timestamp of the timerange end. 74-- @column ts Timestamp of the counter value. 75-- @column dur Duration of the counter value. 76-- @column value Counter value. 77-- @column track_id If of the counter track. 78-- @column track_name Name of the counter track. 79-- @column track_arg_set_id Counter track set id. 80-- @column arg_set_id Counter arg set id. 81SELECT CREATE_VIEW_FUNCTION( 82 'COUNTER_FOR_TIME_RANGE(counter_track_id INT, start_ts LONG, end_ts LONG)', 83 ' 84 ts LONG, 85 dur LONG, 86 value DOUBLE, 87 track_id INT, 88 track_name STRING, 89 track_arg_set_id INT, 90 arg_set_id INT 91 ', 92 ' 93 SELECT 94 IIF(ts < $start_ts, $start_ts, ts) AS ts, 95 IIF( 96 ts < $start_ts, 97 dur - ($start_ts - ts), 98 IIF(ts + dur > $end_ts, $end_ts - ts, dur)) AS dur, 99 value, 100 track_id, 101 track_name, 102 track_arg_set_id, 103 arg_set_id 104 FROM COUNTER_WITH_DUR_FOR_TRACK($counter_track_id) 105 WHERE TRUE 106 AND ts + dur >= $start_ts 107 AND ts < $end_ts 108 ORDER BY ts ASC; 109' 110);