範例 QoE 資料庫查詢

上次修改主題的時間： 2011-08-01

本節包含經驗品質 (QoE) 資料庫的範例查詢。

使用下列範例取得音訊資料流的抖動和封包遺漏平均值。

select avg(cast(JitterInterArrival as bigint)) as JitterAvg, avg(PacketLossRate) as PacketLossRateAvg from AudioStream

使用下列範例找出用於 Meeting 主控台的會議總數。

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