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('android.network_packets'); 17 18-- Creates a view of aggregated network activity. It is common among networking 19-- to have the interface active for some time after network use. For example, in 20-- mobile networking, it is common to have the cellular interface active for 10 21-- or more seconds after the last packet was sent or received. This view takes 22-- raw packet timing and aggregates it into something that approximates the 23-- activity of the underlying interface. 24-- 25-- @arg view_name The name of the output view. 26-- @arg group_by Expression to group by (set to 'null' for no grouping). 27-- @arg filter Expression on `android_network_packets` to filter by. 28-- @arg idle_ns The amount of time before considering the network idle. 29-- @arg quant_ns Quantization value, to group rows before the heavy 30-- part of the query. This should be smaller than idle_ns. 31-- 32-- @column group_by The group_by columns are all present in the output. 33-- @column ts The timestamp indicating the start of the segment. 34-- @column dur The duration of the current segment. 35-- @column packet_count The total number of packets in this segment. 36-- @column packet_length The total number of bytes for packets in this segment. 37CREATE VIEW {{view_name}} AS 38WITH quantized AS ( 39 SELECT 40 {{group_by}}, 41 MIN(ts) AS ts, 42 MAX(ts+dur)-MIN(ts) AS dur, 43 SUM(packet_count) AS packet_count, 44 SUM(packet_length) AS packet_length 45 FROM android_network_packets 46 WHERE {{filter}} 47 GROUP BY CAST(ts / {{quant_ns}} AS INT64), {{group_by}} 48), 49with_last AS ( 50 SELECT 51 *, 52 LAG(ts) OVER ( 53 PARTITION BY {{group_by}} 54 ORDER BY ts 55 ) AS last_ts 56 FROM quantized 57), 58with_group AS ( 59 SELECT 60 *, 61 COUNT(IIF(ts-last_ts>{{idle_ns}}, 1, null)) OVER ( 62 PARTITION BY {{group_by}} 63 ORDER BY ts 64 ) AS group_id 65 FROM with_last 66) 67SELECT 68 {{group_by}}, 69 MIN(ts) AS ts, 70 MAX(ts+dur)-MIN(ts)+{{idle_ns}} AS dur, 71 SUM(packet_count) AS packet_count, 72 SUM(packet_length) AS packet_length 73FROM with_group 74GROUP BY group_id, {{group_by}} 75