sql算法总结

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,