1.过滤掉pcf_timestamp为0点到6点的记录,夜间记录不做分析
case when SUBSTRING(pcf_timestamp,5,1)="-" then pcf_timestamp
else FROM_UNIXTIME(CAST(pcf_timestamp/1000 AS BIGINT)) end as pcf_timestamp,
hour(pcf_timestamp) > 6
2.正则表达式的使用regexp
url中字段头部是igmp字符的表示“直播”,值填为0;url中字段头部是rtsp字符的表示“直播”,值填为1。
示例:
igmp://239.254.200.71:8288
rtsp://192.168.22.70:554
IF(url regexp '^igmp', 0, IF(url regexp '^rtsp', 1, -1)) AS playtype
3.同比放大
iptv_subject_stb_live_city_day.qoe*60% + iptv_subject_stb_vod_city_day.qoe*40%
其中占比可配置,并且当其中某个qoe为null时,则直接取另外一个播放类型的qoe得分( 即同比放大 (a.qoe*#vodqoe_Proportion# +b.qoe*#vodqoe_Proportion#)/(aq+#liveqoe_Proportion#) ) 即 if(a.qoe is null,0,#vodqoe_Proportion#) as qoe_tha if(b.qoe is null,0,#liveqoe_Proportion#) as qoe_thb
SELECT '$subjectCeiCityDay.p_date$' AS day,
((qoe_a*#vodqoe_Proportion# +qoe_b*#liveqoe_Proportion#)/(qoe_tha+qoe_thb)) as cei,
$subjectCeiCityDay.p_provincecode$ AS provincecode,
nvl(Temp_A.citycode,area.citycode) as citycode,
或者
((qoe_a*#vodqoe_Proportion# +qoe_b*#liveqoe_Proportion#)/(qoe_tha+qoe_thb)) as cei,
nvl(a.qoe,0) AS qoe_a,
nvl(b.qoe,0) AS qoe_b,
CASE WHEN a.qoe is null THEN 0 ELSE #vodqoe_Proportion# END AS qoe_tha,
CASE WHEN b.qoe is null THEN 0 ELSE #liveqoe_Proportion# END AS qoe_thb,
4.打分case when
CAST(
CASE WHEN VODAvgDelay >= #vodavgdelay_Threshold1# AND VODAvgDelay <= #vodavgdelay_Threshold2#
THEN 100
WHEN VODAvgDelay > #vodavgdelay_Threshold2# AND VODAvgDelay < #vodavgdelay_Threshold3#
THEN (11400 - 4 * VODAvgDelay) / 90
WHEN VODAvgDelay >= #vodavgdelay_Threshold3#
THEN 30
END
AS DECIMAL(8,0)) AS vodavgdelayscore,
5.按每15钟取出数据
SELECT
c.day AS day,
c.nodeid AS nodeid,
c.nodename AS nodename,
totaluser,
maxbandwidth,
totalbandwidth,
case when SUBSTRING(pcf_timestamp,5,1)="-" then pcf_timestamp
else FROM_UNIXTIME(CAST(pcf_timestamp/1000 AS BIGINT)) end as pcf_timestamp,
hour(pcf_timestamp) > 6
2.正则表达式的使用regexp
url中字段头部是igmp字符的表示“直播”,值填为0;url中字段头部是rtsp字符的表示“直播”,值填为1。
示例:
igmp://239.254.200.71:8288
rtsp://192.168.22.70:554
IF(url regexp '^igmp', 0, IF(url regexp '^rtsp', 1, -1)) AS playtype
3.同比放大
iptv_subject_stb_live_city_day.qoe*60% + iptv_subject_stb_vod_city_day.qoe*40%
其中占比可配置,并且当其中某个qoe为null时,则直接取另外一个播放类型的qoe得分( 即同比放大 (a.qoe*#vodqoe_Proportion# +b.qoe*#vodqoe_Proportion#)/(aq+#liveqoe_Proportion#) ) 即 if(a.qoe is null,0,#vodqoe_Proportion#) as qoe_tha if(b.qoe is null,0,#liveqoe_Proportion#) as qoe_thb
SELECT '$subjectCeiCityDay.p_date$' AS day,
((qoe_a*#vodqoe_Proportion# +qoe_b*#liveqoe_Proportion#)/(qoe_tha+qoe_thb)) as cei,
$subjectCeiCityDay.p_provincecode$ AS provincecode,
nvl(Temp_A.citycode,area.citycode) as citycode,
或者
((qoe_a*#vodqoe_Proportion# +qoe_b*#liveqoe_Proportion#)/(qoe_tha+qoe_thb)) as cei,
nvl(a.qoe,0) AS qoe_a,
nvl(b.qoe,0) AS qoe_b,
CASE WHEN a.qoe is null THEN 0 ELSE #vodqoe_Proportion# END AS qoe_tha,
CASE WHEN b.qoe is null THEN 0 ELSE #liveqoe_Proportion# END AS qoe_thb,
4.打分case when
CAST(
CASE WHEN VODAvgDelay >= #vodavgdelay_Threshold1# AND VODAvgDelay <= #vodavgdelay_Threshold2#
THEN 100
WHEN VODAvgDelay > #vodavgdelay_Threshold2# AND VODAvgDelay < #vodavgdelay_Threshold3#
THEN (11400 - 4 * VODAvgDelay) / 90
WHEN VODAvgDelay >= #vodavgdelay_Threshold3#
THEN 30
END
AS DECIMAL(8,0)) AS vodavgdelayscore,
5.按每15钟取出数据
SELECT
c.day AS day,
c.nodeid AS nodeid,
c.nodename AS nodename,
totaluser,
maxbandwidth,
totalbandwidth,