ZQuery Template

Query collection for ZDB

Query Tips

• Always use date for filter queries
• Using the site_id filter would be better
• To check query results, filter the date using the past 7 days first

Query Meas and Packet Loss

Example #1

query meas lookup to network_cell to get site_id field, from 7 days before until yesterday with specify site_id

select date, site_id, sum(payload_mbyte) as payload_mbyte
from meas_2g_daily m
    join network_cell n on m.network_cell_id = n.id
where date between date_int(7) and date_int(1)
    and region in ('YOUR REGION HERE')
    and site_id in ('YOUR SITE_ID HERE')
group by date, site_id
order by site_id, date;

Example #2

query meas lookup to network_cell to get site_id field and cluster name, from 7 days before until yesterday with specify cluster

select date, n.site_id, cluster, sum(payload_mbyte) as payload_mbyte
from meas_2g_daily m
    join network_cell n on m.network_cell_id = n.id
    join ref_site r on n.site_id = r.site_id
where date between date_int(7) and date_int(1)
    and r.region in ('YOUR REGION HERE')
    and r.cluster in ('YOUR CLUSTER NAME HERE')
group by date, n.site_id, cluster
order by cluster, n.site_id, date;

Table List

Schema	Table	Description
public	meas_2g_hourly	meas hourly cell
public	meas_4g_hourly	meas hourly cell
public	meas_5g_hourly	meas hourly cell
public	meas_2g_daily	meas daily cell
public	meas_4g_daily	meas daily cell
public	meas_5g_daily	meas daily cell
public	meas_2g_daily_bh_traffic	meas daily busy hour (by traffic) cell
public	meas_4g_daily_bh_activeuser	meas daily busy hour (by active user) cell
public	meas_4g_daily_bh_payload	meas daily busy hour (by payload) cell
public	meas_5g_daily_bh_payload	meas daily busy hour (by payload) cell
public	meas_2g_weekly_bdbh_traffic	meas weekly busy day busy hour (by traffic) cell
public	meas_4g_weekly_bdbh_activeuser	meas weekly busy day busy hour (by active user) cell
public	meas_5g_weekly_bdbh_payload	meas weekly busy day busy hour (by payload) cell
public	pl_2g_hourly	packet loss hourly site
public	pl_4g_15min	packet loss 15 minutes site
public	network_cell	cell reference for meas lookup
public	network_site	site reference for pl lookup
public	ref_site	region reference

Function List

Function	Example	Result	Description
to_week(date)	to_week('2023-12-01')	202348	convert date to week
date_from(yearweek)	date_from('202348')	2023-12-01	start date of a week
date_to(yearweek)	date_to('202348')	2023-12-07	start end of a week
date_int(number)	date_int(2)	2023-12-06	get date with interval from current date
ndp(num, denum)	ndp(2,4)	50.00	num denum calculation with percentage result format
nds(num, denum)	nds(1,3)	0.33	num denum calculation with standard result format

Query Dump

Example #1

Get parameters data from the dump

select dm_site_name(managedelement)        as site_name,
       dm_site_id(managedelement)          as site_id,
       dm_me_id(managedelement)            as me_id,
       dm_longitude(managedelement)        as longitude,
       dm_latitude(managedelement)         as latitude,
       dm_sw_version(managedelement)       as sw_version,

       dp_cell_name(parameters)            as cell_name,
       dp_cell_id(parameters)              as cell_id,
       dp_tac(parameters)                  as tac,
       dp_band(parameters)                 as band,
       dp_freq(parameters)                 as freq,
       dp_longitude(parameters)            as longitude,
       dp_latitude(parameters)             as latitude,

       parameters ->> 'alphaMaxLTE'        as alphaMaxLTE,
       parameters ->> 'case5RecoverThrdDl' as case5RecoverThrdDl
from dump
where server = 'ume9'
  and moc in ('CUEUtranCellFDDLTE', 'CUEUtranCellTDDLTE', 'EUtranCellFDD', 'EUtranCellTDD')
  and dm_site_id(managedelement) in ('YOUR SITE_ID HERE')
limit 100;

Example #2

Get a list of parameters for each MO

select *
from mv_dump_parameters_itbbu
where server = 'ume9'
  and moc in ('CUEUtranCellFDDLTE', 'CUEUtranCellTDDLTE', 'EUtranCellFDD', 'EUtranCellTDD');

Table List

Schema	Table	Description
public	dump	dump data
public	mv_dump_parameters_itbbu	parameters data for itbbu me type
public	mv_dump_parameters_sdr	parameters data for sdr me type
public	mv_dump_parameters_mrnc	parameters data for mrnc me type

Function List for Dump Query

Function with "Managed Element" field argument

Function	Example	Description
dm_site_name(field)	dm_site_name(managedelement)	get site name from managedelement field
dm_site_id(field)	dm_site_id(managedelement)	get site id from managedelement field
dm_me_id(field)	dm_me_id(managedelement)	get me id from managedelement field
dm_longitude(field)	dm_longitude(managedelement)	get longitude from managedelement field
dm_latitude(field)	dm_latitude(managedelement)	get latitude from managedelement field
dm_sw_version(field)	dm_sw_version(managedelement)	get software version from managedelement field

Function with "Parameter" field argument

Function	Example	Description
dp_cell_name(field)	dp_cell_name(parameters)	get cell name from parameters field
dp_cell_id(field)	dp_cell_id(parameters)	get cell id from parameters field
dp_tac(field)	dp_tac(parameters)	get tac from parameters field
dp_band(field)	dp_band(parameters)	get band from parameters field
dp_freq(field)	dp_freq(parameters)	get freq from parameters field
dp_longitude(field)	dp_longitude(parameters)	get longitude from parameters field
dp_latitude(field)	dp_latitude(parameters)	get latitude from parameters field

Thank you