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,
            usedspace,
            totalspace,
            c.time AS time,
            b2.groupid AS groupid,
            c.citycode AS citycode,
            c.provincecode AS provincecode,
            c.cityname AS cityname,
            c.provincename AS provincename
        FROM
            $statCdnDetailDay$ c,
        (SELECT
            day,
            nodeid,
            nodename,
            MAX(time) AS lasttime,
            groupid,
            citycode,
            provincecode,
            cityname,
            provincename
        FROM
            (SELECT
                day,
                nodeid,
                nodename,
                time,
                floor(unix_timestamp(time)/(15 * 60)) AS groupid,
                citycode,
                provincecode,
                cityname,
                provincename
            FROM
                $statCdnDetailDay$
            WHERE p_date = '$statCdnDetailDay.p_date$' AND p_provincecode = $statCdnDetailDay.p_provincecode$) b1 
            GROUP BY day,nodeid,nodename,groupid,citycode,cityname,provincecode,provincename) b2 
            WHERE c.day = b2.day AND c.nodeid = b2.nodeid AND c.nodename = b2.nodename AND c.time = b2.lasttime AND c.citycode = b2.citycode AND c.cityname = b2.cityname AND c.provincecode = b2.provincecode AND c.provincename = b2.provincename


4.一张表里的根据同一指标不同值打分（同比放大 ）serviceexp_subject_omni_10000_connect_qoe_district_day获得，由star_level_sec及对应qoe，计算公司为：30%*QoE（高星）+30%*QoE（中星）+20%*QoE（低星）+20%*QoE（其他）  (同比放大)
SELECT provincecode,
                 citycode,
                 districtcode,
                SUM(connecttimes) AS connecttimes,
                 CAST(SUM(CASE WHEN star_level_sec = #Star_Level_Sec_Threshold_3# THEN IF(ISNULL(qoe),0,#connect_qoe_Threshold_3#)*(NVL(qoe,0)) 
                               WHEN star_level_sec = #Star_Level_Sec_Threshold_2# THEN IF(ISNULL(qoe),0,#connect_qoe_Threshold_2#)*(NVL(qoe,0)) 
                               WHEN star_level_sec = #Star_Level_Sec_Threshold_1# THEN IF(ISNULL(qoe),0,#connect_qoe_Threshold_1#)*(NVL(qoe,0)) 
                               WHEN star_level_sec = #Star_Level_Sec_Threshold_0# THEN IF(ISNULL(qoe),0,#connect_qoe_Threshold_0#)*(NVL(qoe,0)) END) / 
                      SUM(CASE WHEN star_level_sec = #Star_Level_Sec_Threshold_3# THEN IF(ISNULL(qoe),0,#connect_qoe_Threshold_3#) 
                               WHEN star_level_sec = #Star_Level_Sec_Threshold_2# THEN IF(ISNULL(qoe),0,#connect_qoe_Threshold_2#) 
                               WHEN star_level_sec = #Star_Level_Sec_Threshold_1# THEN IF(ISNULL(qoe),0,#connect_qoe_Threshold_1#) 
                               WHEN star_level_sec = #Star_Level_Sec_Threshold_0# THEN IF(ISNULL(qoe),0,#connect_qoe_Threshold_0#) END) 
                    AS DECIMAL(8,0)) AS connect_qoe 
          FROM $connectqoeDistrictDay$ 
          WHERE p_provincecode = $connectqoeDistrictDay.p_provincecode$ 
            AND p_date = '$connectqoeDistrictDay.p_date$' GROUP BY provincecode,citycode,districtcode


5.同比放大
实体渠道服务时长qoe=客户等待时长得分*50%+业务办理时长得分*30%+工单竣工时长得分*20%
NVL(c.day,d.day) AS day,
        NVL(c.serviceid,d.service_id) AS serviceid,
        waitqoe AS waitqoe,
        IF(ISNULL(waitqoe),0,0.5) AS waitqoeweight,
        acceptqoe AS acceptqoe,
        IF(ISNULL(acceptqoe),0,0.3) AS acceptqoeweight,
        completeqoe AS completeqoe,
        IF(ISNULL(completeqoe),0,0.2) AS completeqoeweight


    CAST( CASE WHEN ISNULL(waitqoe) AND ISNULL(acceptqoe) AND ISNULL(completeqoe) THEN NULL ELSE (      
       waitqoe*(waitqoeweight/(waitqoeweight+acceptqoeweight+completeqoeweight))+
            acceptqoe*(acceptqoeweight/(waitqoeweight+acceptqoeweight+completeqoeweight))
            +completeqoe*(completeqoeweight/(waitqoeweight+acceptqoeweight+completeqoeweight)) ) END

            AS DECIMAL(8,0)) AS serviceqoe,

6.正则表达式的使用（REGEXP  ）

select  serviceid  from serviceexp_subject_user_info_day  where (serviceid  REGEXP  '^ip[0-9]{11}$')=1

7.把不相关的查询连接起来

/*参数格式：*/(function() {    return function(request, script) {        var result = [];        var unit = request.param.timeUnit;        var time=request.param.time;        var userId=request.param.userId;        timeCondition = "day = '" + time + "'";         //服务时长得分QoE得分        var selectCondition01 = " serviceqoe ";        //客户等待时长        var selectCondition02 = " waittime ";        //业务办理时长        var selectCondition03 = " accepttime ";        //工单竣工时长        var selectCondition04 = " completetime ";        //QoE得分,办理错单数          var selectCondition05 = " auditqoe,errornum ";        //QoE得分,满意度单数,客户不满意度单数,客户投诉单数        var selectCondition06 = "perceptionqoe,satisfactionnum,notsatisfactionnum,complaintnum ";        selectCondition=" cast(serviceqoe as decimal(8,0)),waittime,cast(60*accepttime as decimal(8,2)),completetime,cast(auditqoe as decimal(8,0)),cast(errornum as decimal(8,0)),cast(perceptionqoe as decimal(8,0)),cast(satisfactionnum as decimal(8,0)),cast(notsatisfactionnum as decimal(8,0)),cast(complaintnum as decimal(8,0))";        tableName01 = " serviceexp_subject_user_omni_service_qoe_day ";        tableName02 = " serviceexp_subject_omni_paidui_detail_day ";        tableName03 = " serviceexp_subject_omni_accept_paidui_wait_day ";        tableName04 = " serviceexp_subject_omni_accept_detail_day ";        tableName05 = " serviceexp_subject_user_omni_quality_qoe_day ";        tableName06 = " serviceexp_subject_user_omni_perception_qoe_day ";                   var check1="lower(serviceid)";        var check2="lower(phone)";        var check3="lower(service_id)";        var whereCondition1 =" AND "+check1+"=lower('" + userId+"') ";        var whereCondition2 =" AND ( "+check2+"=lower('" + userId+"') or (phone is null and lower(phonebycardid) like '%"+userId+"%' )) ";        var whereCondition3 =" AND "+check3+"=lower('" + userId+"') ";               var  sql01 = "select row_number() over(order by "+selectCondition01+") as t1," + selectCondition01 + " from " + tableName01 + " where " + timeCondition+whereCondition1;          var sql02 = "select row_number() over(order by "+selectCondition02+") as t1," + selectCondition02 + " from " + tableName02 + " where " + timeCondition+whereCondition2;              var  sql03 = "select row_number() over(order by "+selectCondition03+") as t1," + selectCondition03 + " from " + tableName03 + " where " + timeCondition+whereCondition3;              var sql04 = "select row_number() over(order by "+selectCondition04+") as t1," + selectCondition04 + " from " + tableName04 + " where " + timeCondition+whereCondition3;               var  sql05 = "select row_number() over(order by "+selectCondition05+") as t1," + selectCondition05 + " from " + tableName05 + " where " + timeCondition+whereCondition1;                var  sql06 = "select row_number() over(order by "+selectCondition06+") as t1," + selectCondition06 + " from " + tableName06 + " where " + timeCondition+whereCondition1;               var sql12 = " select  if( a.t1 is null,b.t1,a.t1) as t1 ,"+selectCondition01+ ","+selectCondition02+" from ("+sql01+") a full join ( "+sql02+") b on a.t1=b.t1 ";               var  sql13 = " select if( c.t1 is null,d.t1,c.t1) as t1,"+selectCondition01+","+selectCondition02+","+selectCondition03+" from ("+sql12+") c full join ( "+sql03+") d on c.t1=d.t1 ";               var sql14 = " select if( e.t1 is null,f.t1,e.t1) as t1,"+selectCondition01+","+selectCondition02+","+selectCondition03+","+selectCondition04+" from ("+sql13+") e full join ( "+sql04+") f on e.t1=f.t1 ";                var sql15 = " select if( m.t1 is null,n.t1,m.t1) as t1,"+selectCondition01+","+selectCondition02+","+selectCondition03+","+selectCondition04+","+selectCondition05+" from ("+sql14+") m full join ( "+sql05+") n on m.t1=n.t1 ";           var sql16 = " select "+selectCondition+" from ("+sql15+") g full join ( "+sql06+") h on g.t1=h.t1 ";                result = matrix(sql16);               log("=====================================result",result.data);       return result.data;    }})();