1 DEFINE{query_name 'throughput_session';
3 max_lfta_disorder '1'; max_hfta_disorder '1';
4 comment 'throughput experienced by UE session 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( endian_swap_ui(UINT(endTimeStamp)) - endian_swap_ui(UINT(startTimeStamp))), 1) as active_throughput,
12 sum(usageCountDL) / UMAX( max( endian_swap_ui(UINT(endTimeStamp))) - min(endian_swap_ui(UINT(startTimeStamp))), 1) as average_throughput,
14 min( usageCountDL / UMAX( endian_swap_ui(UINT(endTimeStamp)) - endian_swap_ui(UINT(startTimeStamp)), 1) ) as min_throughput,
16 max( usageCountDL / UMAX( endian_swap_ui(UINT(endTimeStamp)) - endian_swap_ui(UINT(startTimeStamp)), 1) ) as max_throughput
17 from RATDATAUSAGE.rat_data_usage
18 group by e_RAB_ID, id_SgNB_UE_X2AP_ID as UE_ID, gnb_id as GNB_ID,
19 timestamp_ms/$window as TB
22 DEFINE{query_name 'prelim_throughput_gtp_teid';
24 max_lfta_disorder '1'; max_hfta_disorder '1';
25 comment 'pre-aggregation to get per-teid throughput';
28 select TB, GNB_UE_ID, GNB_ID, e_RAB_ID,
29 sum(usageCountDL) as sum_usageCountDL,
30 sum( endian_swap_ui(UINT(endTimeStamp)) - endian_swap_ui(UINT(startTimeStamp))) as sum_duration,
31 max( endian_swap_ui(UINT(endTimeStamp))) as max_end_ts,
32 min(endian_swap_ui(UINT(startTimeStamp))) as min_start_ts,
33 min( usageCountDL / UMAX( endian_swap_ui(UINT(endTimeStamp)) - endian_swap_ui(UINT(startTimeStamp)), 1) ) as min_throughput,
34 max( usageCountDL / UMAX( endian_swap_ui(UINT(endTimeStamp)) - endian_swap_ui(UINT(startTimeStamp)), 1) ) as max_throughput
35 from RATDATAUSAGE.rat_data_usage
36 group by id_SgNB_UE_X2AP_ID as GNB_UE_ID, gnb_id as GNB_ID, e_RAB_ID, timestamp_ms/$window as TB
39 DEFINE{query_name 'throughput_session_gtp_teid_join';
40 max_lfta_disorder '1'; max_hfta_disorder '1';
41 comment 'Join throughput_session with gnb_ueid_teid_map to get the gtp_teid, a followup query will reaggregate';
44 select T.TB, T.GNB_UE_ID, T.GNB_ID, T.e_RAB_ID, M.gTP_TEID,
45 T.sum_usageCountDL, T.sum_duration, T.max_end_ts, T.min_start_ts,
46 T.min_throughput, max_throughput
47 INNER_JOIN from prelim_throughput_gtp_teid T, gnb_ueid_teid_map M
48 Where T.GNB_ID=M.GNB_ID and T.GNB_UE_ID=M.id_SgNB_UE_X2AP_ID and T.TB=M.TB
51 DEFINE{query_name 'throughput_gtp_teid';
53 max_lfta_disorder '1'; max_hfta_disorder '1';
54 comment 'throughput experienced by UE, as determined by the gtp_teid, over a measurement interval. *Active* throughput is throughput while actively downloading, *average* averages bytes transfered over the measurement interval';
57 select ($window*(TB+1))/1000 as TS, gTP_TEID, GNB_ID,
58 $window/1000.0 as measurementInterval,
59 sum(sum_usageCountDL) / UMAX( sum(sum_duration), 1) as active_throughput,
60 sum(sum_usageCountDL) / UMAX( max(max_end_ts) - min(min_start_ts), 1) as average_throughput,
61 min( min_throughput ) as min_throughput,
62 max( max_throughput ) as max_throughput
63 from throughput_session_gtp_teid_join
64 group by GNB_ID, gTP_TEID, TB
67 DEFINE{query_name 'throughput_session_pci_join';
68 max_lfta_disorder '1'; max_hfta_disorder '1';
69 comment 'Join throughput_session with gnb_ueid_teid_map to get the gtp_teid, a followup query will reaggregate';
72 select T.TB, T.GNB_UE_ID, T.GNB_ID, T.e_RAB_ID, M.physCellId,
73 T.sum_usageCountDL, T.sum_duration, T.max_end_ts, T.min_start_ts,
74 T.min_throughput, max_throughput
75 INNER_JOIN from prelim_throughput_gtp_teid T, gnb_ueid_cellid_map M
76 Where T.GNB_ID=M.GNB_ID and T.GNB_UE_ID=M.id_SgNB_UE_X2AP_ID and T.TB=M.TB
79 DEFINE{query_name 'throughput_pci';
81 max_lfta_disorder '1'; max_hfta_disorder '1';
82 comment 'throughput experienced by UE, as determined by the gtp_teid, over a measurement interval. *Active* throughput is throughput while actively downloading, *average* averages bytes transfered over the measurement interval';
85 select ($window*(TB+1))/1000 as TS, physCellId, GNB_ID,
86 $window/1000.0 as measurementInterval,
87 sum(sum_usageCountDL) / UMAX( sum(sum_duration), 1) as active_throughput,
88 sum(sum_usageCountDL) / UMAX( max(max_end_ts) - min(min_start_ts), 1) as average_throughput,
89 min( min_throughput ) as min_throughput,
90 max( max_throughput ) as max_throughput
91 from throughput_session_pci_join
92 group by GNB_ID, physCellId, TB
96 DEFINE{query_name 'throughput_gtp_teid_bearer';
98 max_lfta_disorder '1'; max_hfta_disorder '1';
99 comment 'throughput experienced by UE, as determined by the gtp_teid, for a bearer (eRAB_ID) over a measurement interval. *Active* throughput is throughput while actively downloading, *average* averages bytes transfered over the measurement interval';
101 PARAM{ window uint; }
102 select ($window*(TB+1))/1000 as TS, gTP_TEID, GNB_ID, e_RAB_ID,
103 $window/1000.0 as measurementInterval,
104 sum(sum_usageCountDL) / UMAX( sum(sum_duration), 1) as active_throughput,
105 sum(sum_usageCountDL) / UMAX( max(max_end_ts) - min(min_start_ts), 1) as average_throughput,
106 min( min_throughput ) as min_throughput,
107 max( max_throughput ) as max_throughput
108 from throughput_session_gtp_teid_join
109 group by GNB_ID, gTP_TEID, e_RAB_ID, TB
112 // -----------------------------------------
114 DEFINE{query_name 'add_req_event';
115 max_lfta_disorder '1'; max_hfta_disorder '1';
116 comment 'addition_request event, for merging into qci-arp map';
119 Select timestamp_ms, id_MeNB_UE_X2AP_ID, LLONG(0) as id_SgNB_UE_X2AP_ID, gnb_id,
120 qCI, priorityLevel as ARP, 0 as event_type
121 from SGNB_ADDITION_REQ.sgnb_addreq_for_ue_bearers R
124 DEFINE{query_name 'add_req_ack_event';
125 max_lfta_disorder '1'; max_hfta_disorder '1';
126 comment 'addition_request_acknowledge event, for merging into qci-arp map';
129 Select timestamp_ms, id_MeNB_UE_X2AP_ID, id_SgNB_UE_X2AP_ID, gnb_id,
130 LLONG(0) as qCI, LLONG(0) as ARP, 1 as event_type
131 From SGNB_ADDITION_REQ_ACK.eRABs_acked_for_admit_for_ue A
134 DEFINE{query_name 'add_req_events';
135 max_lfta_disorder '1'; max_hfta_disorder '1';
136 comment 'addition_request / acknowledge events';
139 Merge R.timestamp_ms : A.timestamp_ms
140 From add_req_event R, add_req_ack_event A
143 DEFINE{query_name 'gnb_ueid_qci_arp_map';
144 max_lfta_disorder '1'; max_hfta_disorder '1';
145 comment 'Output the last known map from (gnb, gnb_ueid) to (qci, arp)';
148 Select TB, GNB_ID, id_MeNB_UE_X2AP_ID, LAST(id_SgNB_UE_X2AP_ID) as id_SgNB_UE_X2AP_ID,
149 FIRST(qCI) as qCI, FIRST(ARP) as ARP
151 GROUP BY timestamp_ms / $window as TB, id_MeNB_UE_X2AP_ID, gnb_id as GNB_ID
152 Having LAST(event_type) = 1
153 CLOSING_WHEN ((TB+1)*$window-LAST(timestamp_ms))/1000.0 >= 3600
157 -- DEFINE{query_name 'gueid_to_qciarp_join';
158 -- max_lfta_disorder '1'; max_hfta_disorder '1';
159 -- comment 'get gnb_ueid to qci,arp mapping, gnb_ueid is in sgnb addition request acknowledge, qci,arp is in sgnb addition request';
161 -- PARAM{ window uint;}
162 -- Select A.timestamp_ms/$window as TB,
163 -- non_temporal(A.timestamp_ms) as timestamp_ms,
164 -- R.id_MeNB_UE_X2AP_ID, A.id_SgNB_UE_X2AP_ID, A.gnb_id,
165 -- R.qCI, R.priorityLevel as ARP
166 -- INNER_JOIN from SGNB_ADDITION_REQ.sgnb_addreq_for_ue_bearers R,
167 -- SGNB_ADDITION_REQ_ACK.eRABs_acked_for_admit_for_ue A
168 -- Where A.timestamp_ms/$window = R.timestamp_ms/$window and
169 -- A.gnb_id = R.gnb_id and A.id_MeNB_UE_X2AP_ID = R.id_MeNB_UE_X2AP_ID
170 -- -- AND R.PCI = A.PCI
173 -- DEFINE{query_name 'gnb_ueid_qci_arp_map';
174 -- max_lfta_disorder '1'; max_hfta_disorder '1';
175 -- comment 'Output the last known map from (gnb, gnb_ueid) to (qci, arp)';
177 -- PARAM{ window uint;}
178 -- Select TB, GNB_ID, id_SgNB_UE_X2AP_ID, LAST(qCI) as qCI, LAST(ARP) as ARP
179 -- from gueid_to_qciarp_join
180 -- --from SGNB_ADDITION_REQ_ACK.eRABs_acked_for_admit_for_ue
181 -- group by TB, gnb_id as GNB_ID, id_SgNB_UE_X2AP_ID
182 -- CLOSING_WHEN ((TB+1)*$window-LAST(timestamp_ms))/1000.0 >= 3600
185 DEFINE{query_name 'throughput_session_userclass_join';
186 max_lfta_disorder '1'; max_hfta_disorder '1';
187 comment 'Join throughput_session with gnb_ueid_teid_map to get the gtp_tied, a followup query will reaggregate';
189 PARAM{ window uint; }
190 select T.TB, T.GNB_UE_ID, T.GNB_ID, M.qCI, M.ARP, M.id_SgNB_UE_X2AP_ID,
191 EQ(qCI, 9)*EQ(M.ARP, 15) + 2*EQ(qCI, 9)*EQ(M.ARP, 14) as class,
192 T.sum_usageCountDL, T.sum_duration, T.max_end_ts, T.min_start_ts,
193 T.min_throughput, max_throughput
194 INNER_JOIN from prelim_throughput_gtp_teid T, gnb_ueid_qci_arp_map M
195 Where T.GNB_ID=M.GNB_ID and T.GNB_UE_ID=M.id_SgNB_UE_X2AP_ID
199 DEFINE{query_name 'throughput_userclass';
201 max_lfta_disorder '1'; max_hfta_disorder '1';
202 comment 'throughput experienced by UE, rolled up into user classes, over a measurement interval. Class A (qci=9, arp=15) is class=1 and Class B (qci=8, arp=15) is class=2. *Active* throughput is throughput while actively downloading, *average* averages bytes transfered over the measurement interval';
204 PARAM{ window uint; }
205 select ($window*(TB+1))/1000 as TS, GNB_ID, class as CLASS,
206 $window/1000.0 as measurementInterval,
207 sum(sum_usageCountDL) / UMAX( sum(sum_duration), 1) as active_throughput,
208 sum(sum_usageCountDL) / UMAX( max(max_end_ts) - min(min_start_ts), 1) as average_throughput,
209 min( min_throughput ) as min_throughput,
210 max( max_throughput ) as max_throughput
211 from throughput_session_userclass_join
213 group by TB, GNB_ID, class
216 DEFINE{query_name 'throughput_group_pci_join';
217 max_lfta_disorder '1'; max_hfta_disorder '1';
218 comment 'Join throughput_session with gnb_ueid_teid_map to get the gtp_teid, a followup query will reaggregate';
220 PARAM{ window uint; }
221 select T.TB, T.GNB_UE_ID, T.GNB_ID, T.qCI, T.ARP, M.physCellId,
222 T.sum_usageCountDL, T.sum_duration, T.max_end_ts, T.min_start_ts,
223 T.min_throughput, max_throughput
224 INNER_JOIN from throughput_session_userclass_join T, gnb_ueid_cellid_map M
225 Where T.GNB_ID=M.GNB_ID and T.GNB_UE_ID=M.id_SgNB_UE_X2AP_ID and T.TB=M.TB
228 DEFINE{query_name 'throughput_userclass_pci';
230 max_lfta_disorder '1'; max_hfta_disorder '1';
231 comment 'throughput experienced by UE, rolled up into user classes, over a measurement interval. Class A (qci=9, arp=15) is class=1 and Class B (qci=8, arp=15) is class=2. *Active* throughput is throughput while actively downloading, *average* averages bytes transfered over the measurement interval';
233 PARAM{ window uint; }
234 select ($window*(TB+1))/1000 as TS, GNB_ID, physCellId, qCI, ARP,
235 $window/1000.0 as measurementInterval,
236 sum(sum_usageCountDL) / UMAX( sum(sum_duration), 1) as active_throughput,
237 sum(sum_usageCountDL) / UMAX( max(max_end_ts) - min(min_start_ts), 1) as average_throughput,
238 min( min_throughput ) as min_throughput,
239 max( max_throughput ) as max_throughput
240 from throughput_group_pci_join
241 group by TB, GNB_ID, physCellId, qCI, ARP
245 DEFINE{query_name 'throughput_rollup';
247 max_lfta_disorder '1'; max_hfta_disorder '1';
248 comment 'statistics on the per-UE throughput';
250 PARAM{ window uint; }
251 select TS, e_RAB_ID, GNB_ID,
252 $window/1000.0 as measurementInterval,
253 count(*) as count_ues,
254 quantile_of( UINT(average_throughput), .05) as average_throughput_percentile_05,
255 quantile_of( UINT(average_throughput), .50) as average_throughput_percentile_50,
256 avg( average_throughput) as average_average_throughput,
257 quantile_of( UINT(average_throughput), .95) as average_throughput_percentile_95,
258 quantile_of( UINT(active_throughput), .05) as active_throughput_percentile_05,
259 quantile_of( UINT(active_throughput), .50) as active_throughput_percentile_50,
260 avg( active_throughput) as average_active_throughput,
261 quantile_of( UINT(active_throughput), .95) as active_throughput_percentile_95
262 from throughput_session
263 group by TS, e_RAB_ID, GNB_ID
266 DEFINE{query_name 'throughput_gnb';
267 max_lfta_disorder '1'; max_hfta_disorder '1';
268 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';
270 PARAM{ window uint; }
271 select ($window*(TB+1))/1000 as TS, e_RAB_ID, GNB_ID,
272 $window/1000.0 as measurementInterval,
274 sum(usageCountDL) / UMAX( sum( endian_swap_ui(UINT(endTimeStamp)) - endian_swap_ui(UINT(startTimeStamp))), 1) as active_throughput,
276 sum(usageCountDL) / UMAX( max( endian_swap_ui(UINT(endTimeStamp))) - min(endian_swap_ui(UINT(startTimeStamp))), 1) as average_throughput,
278 min( usageCountDL / UMAX( endian_swap_ui(UINT(endTimeStamp)) - endian_swap_ui(UINT(startTimeStamp)), 1) ) as min_throughput,
280 max( usageCountDL / UMAX( endian_swap_ui(UINT(endTimeStamp)) - endian_swap_ui(UINT(startTimeStamp)), 1) ) as max_throughput,
282 quantile_of( UINT( (usageCountDL) / UMAX( endian_swap_ui(UINT(endTimeStamp)) - endian_swap_ui(UINT(startTimeStamp)), 1)), .05) as active_throughput_percentile_05,
284 quantile_of( UINT( (usageCountDL) / UMAX( endian_swap_ui(UINT(endTimeStamp)) - endian_swap_ui(UINT(startTimeStamp)), 1)), .95) as active_throughput_percentile_95
286 from RATDATAUSAGE.rat_data_usage
287 group by e_RAB_ID, gnb_id as GNB_ID, timestamp_ms/$window as TB