1 DEFINE{query_name 'throughput_ue';
3 max_lfta_disorder '1'; max_hfta_disorder '1';
4 comment 'throughput experienced by UE over a measurement interval. *Active* throughput is throughput while actively downloading, *average* averages bytes transfered over the measurement interval';
7 select ($window*(TB+1))/1000 as TS, e_RAB_ID, UE_ID, GNB_ID,
8 $window/1000.0 as measurementInterval,
10 (sum(usageCountDL)) / UMAX( sum(
11 UINT(((endTimeStamp & HEX'FF000000') >> 24) | ((endTimeStamp & HEX'00FF0000') >> 8) | ((endTimeStamp & HEX'0000FF00') << 8) | ((endTimeStamp & HEX'000000FF') << 24)) -
12 UINT(((startTimeStamp & HEX'FF000000') >> 24) | ((startTimeStamp & HEX'00FF0000') >> 8) | ((startTimeStamp & HEX'0000FF00') << 8) | ((startTimeStamp & HEX'000000FF') << 24))
13 ), 1) as active_throughput,
15 (sum(usageCountDL)) / UMAX(
16 max( UINT(((endTimeStamp & HEX'FF000000') >> 24) | ((endTimeStamp & HEX'00FF0000') >> 8) | ((endTimeStamp & HEX'0000FF00') << 8) | ((endTimeStamp & HEX'000000FF') << 24)) ) -
17 min( UINT(((startTimeStamp & HEX'FF000000') >> 24) | ((startTimeStamp & HEX'00FF0000') >> 8) | ((startTimeStamp & HEX'0000FF00') << 8) | ((startTimeStamp & HEX'000000FF') << 24))
18 ), $window) as average_throughput,
20 min((usageCountDL) / UMAX(
21 UINT(((endTimeStamp & HEX'FF000000') >> 24) | ((endTimeStamp & HEX'00FF0000') >> 8) | ((endTimeStamp & HEX'0000FF00') << 8) | ((endTimeStamp & HEX'000000FF') << 24)) -
22 UINT(((startTimeStamp & HEX'FF000000') >> 24) | ((startTimeStamp & HEX'00FF0000') >> 8) | ((startTimeStamp & HEX'0000FF00') << 8) | ((startTimeStamp & HEX'000000FF') << 24))
23 , 1) ) as min_throughput,
25 max((usageCountDL) / UMAX(
26 UINT(((endTimeStamp & HEX'FF000000') >> 24) | ((endTimeStamp & HEX'00FF0000') >> 8) | ((endTimeStamp & HEX'0000FF00') << 8) | ((endTimeStamp & HEX'000000FF') << 24)) -
27 UINT(((startTimeStamp & HEX'FF000000') >> 24) | ((startTimeStamp & HEX'00FF0000') >> 8) | ((startTimeStamp & HEX'0000FF00') << 8) | ((startTimeStamp & HEX'000000FF') << 24))
28 , 1) ) as max_throughput
30 from RATDATAUSAGE.rat_data_usage
32 group by e_RAB_ID, id_SgNB_UE_X2AP_ID as UE_ID, gnb_id as GNB_ID, timestamp_ms/$window as TB
35 // transform to switch byte order
36 // (((endTimeStamp & HEX'FF000000') >> 24) | ((endTimeStamp & HEX'00FF0000') >> 8) | ((endTimeStamp & HEX'0000FF00') << 8) | ((endTimeStamp & HEX'000000FF') << 24))
37 // (((startTimeStamp & HEX'FF000000') >> 24) | ((startTimeStamp & HEX'00FF0000') >> 8) | ((startTimeStamp & HEX'0000FF00') << 8) | ((startTimeStamp & HEX'000000FF') << 24))
40 DEFINE{query_name 'throughput_rollup';
42 max_lfta_disorder '1'; max_hfta_disorder '1';
43 comment 'statistics on the per-UE throughput';
46 select TS, e_RAB_ID, GNB_ID,
47 $window/1000.0 as measurementInterval,
48 count(*) as count_ues,
49 quantile_of( UINT(average_throughput), .05) as average_throughput_percentile_05,
50 quantile_of( UINT(average_throughput), .50) as average_throughput_percentile_50,
51 avg( average_throughput) as average_average_throughput,
52 quantile_of( UINT(average_throughput), .95) as average_throughput_percentile_95,
53 quantile_of( UINT(active_throughput), .05) as active_throughput_percentile_05,
54 quantile_of( UINT(active_throughput), .50) as active_throughput_percentile_50,
55 avg( active_throughput) as average_active_throughput,
56 quantile_of( UINT(active_throughput), .95) as active_throughput_percentile_95
58 group by TS, e_RAB_ID, GNB_ID
61 DEFINE{query_name 'throughput_gnb';
62 max_lfta_disorder '1'; max_hfta_disorder '1';
63 comment 'throughput experienced by a GNB over a measurement interval. *Active* throughput is throughput while actively downloading, *average* averages bytes transfered over the measurement interval';
66 select ($window*(TB+1))/1000 as TS, e_RAB_ID, GNB_ID,
67 $window/1000.0 as measurementInterval,
69 (sum(usageCountDL)) / UMAX( sum(
70 UINT(((endTimeStamp & HEX'FF000000') >> 24) | ((endTimeStamp & HEX'00FF0000') >> 8) | ((endTimeStamp & HEX'0000FF00') << 8) | ((endTimeStamp & HEX'000000FF') << 24)) -
71 UINT(((startTimeStamp & HEX'FF000000') >> 24) | ((startTimeStamp & HEX'00FF0000') >> 8) | ((startTimeStamp & HEX'0000FF00') << 8) | ((startTimeStamp & HEX'000000FF') << 24))
72 ), 1) as active_throughput,
74 (sum(usageCountDL)) / UMAX(
75 max( UINT(((endTimeStamp & HEX'FF000000') >> 24) | ((endTimeStamp & HEX'00FF0000') >> 8) | ((endTimeStamp & HEX'0000FF00') << 8) | ((endTimeStamp & HEX'000000FF') << 24)) ) -
76 min( UINT(((startTimeStamp & HEX'FF000000') >> 24) | ((startTimeStamp & HEX'00FF0000') >> 8) | ((startTimeStamp & HEX'0000FF00') << 8) | ((startTimeStamp & HEX'000000FF') << 24))
77 ), $window) as average_throughput,
79 min((usageCountDL) / UMAX(
80 UINT(((endTimeStamp & HEX'FF000000') >> 24) | ((endTimeStamp & HEX'00FF0000') >> 8) | ((endTimeStamp & HEX'0000FF00') << 8) | ((endTimeStamp & HEX'000000FF') << 24)) -
81 UINT(((startTimeStamp & HEX'FF000000') >> 24) | ((startTimeStamp & HEX'00FF0000') >> 8) | ((startTimeStamp & HEX'0000FF00') << 8) | ((startTimeStamp & HEX'000000FF') << 24))
82 , 1) ) as min_throughput,
84 max((usageCountDL) / UMAX(
85 UINT(((endTimeStamp & HEX'FF000000') >> 24) | ((endTimeStamp & HEX'00FF0000') >> 8) | ((endTimeStamp & HEX'0000FF00') << 8) | ((endTimeStamp & HEX'000000FF') << 24)) -
86 UINT(((startTimeStamp & HEX'FF000000') >> 24) | ((startTimeStamp & HEX'00FF0000') >> 8) | ((startTimeStamp & HEX'0000FF00') << 8) | ((startTimeStamp & HEX'000000FF') << 24))
87 , 1) ) as max_throughput,
89 quantile_of( UINT( (usageCountDL) / UMAX(
90 UINT(((endTimeStamp & HEX'FF000000') >> 24) | ((endTimeStamp & HEX'00FF0000') >> 8) | ((endTimeStamp & HEX'0000FF00') << 8) | ((endTimeStamp & HEX'000000FF') << 24)) -
91 UINT(((startTimeStamp & HEX'FF000000') >> 24) | ((startTimeStamp & HEX'00FF0000') >> 8) | ((startTimeStamp & HEX'0000FF00') << 8) | ((startTimeStamp & HEX'000000FF') << 24))
94 quantile_of( UINT( (usageCountDL) / UMAX(
95 UINT(((endTimeStamp & HEX'FF000000') >> 24) | ((endTimeStamp & HEX'00FF0000') >> 8) | ((endTimeStamp & HEX'0000FF00') << 8) | ((endTimeStamp & HEX'000000FF') << 24)) -
96 UINT(((startTimeStamp & HEX'FF000000') >> 24) | ((startTimeStamp & HEX'00FF0000') >> 8) | ((startTimeStamp & HEX'0000FF00') << 8) | ((startTimeStamp & HEX'000000FF') << 24))
99 from RATDATAUSAGE.rat_data_usage
100 //where schemaId=1501
101 group by e_RAB_ID, gnb_id as GNB_ID, timestamp_ms/$window as TB