示例 QoE 数据库查询
上一次修改主题: 2011-08-01
本节包含用户体验质量 (QoE) 数据库的示例查询。
下面的示例用于获取所有音频流的抖动和丢包平均值。
select avg(cast(JitterInterArrival as bigint)) as JitterAvg, avg(PacketLossRate) as PacketLossRateAvg from AudioStream
下面的示例用于查找使用 Meeting Console 的会议总数。
select avg(ConversationalMOS)
from Session s
inner join MediaLine m
on s.ConferenceDateTime = m.ConferenceDateTime
and s.SessionSeq = m.SessionSeq
and m.MediaLineLabel = 0 -- audio media line
inner join UserAgent uaCaller
on s.CallerUserAgent = uaCaller.UserAgentKey
and uaCaller.UAType = 4 - Lync
inner join UserAgent uaCallee
on s.CalleeUserAgent = uaCallee.UserAgentKey
and uaCallee.UAType = 4 -- Lync
下面的示例用于获取每个捕获设备的 ConversstionalMOS、SendingMOS 和 ListendingMOS。
select t.DeviceName as Device, count(*) as SampleNum, avg(ConversationalMOS) as ConversationalMOS, avg(SendListenMOS) SendingMOS, avg(RecvListenMOS) as ListendingMOS
from
(
select d.DeviceName, m.ConferenceDateTime, m.SessionSeq, a.StreamID, m.ConversationalMOS,a.SendListenMOS, a.RecvListenMOS
from MediaLine m
inner join AudioStream a
on m.ConferenceDateTime = a.ConferenceDateTime
and m.SessionSeq = a.SessionSeq
and m.MediaLineLabel = 0
inner join Device d
on m.CallerCaptureDev = d.DeviceKey
and d.DeviceType = 1
union
select d.DeviceName, m.ConferenceDateTime, m.SessionSeq, a.StreamID, m.ConversationalMOS,a.SendListenMOS, a.RecvListenMOS
from MediaLine m
inner join AudioStream a
on m.ConferenceDateTime = a.ConferenceDateTime
and m.SessionSeq = a.SessionSeq
and m.MediaLineLabel = 0
inner join Device d
on m.CalleeCaptureDev = d.DeviceKey
and d.DeviceType = 1
)as t
group by t.DeviceName
order by SampleNum desc