<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="operatorReportSqlMapper">
	
	<select id="findCountForDeliverEffectAnalysis" parameterType="map" resultType="int">
		select count(date) from ((
		select distinct date 
		from t_rpt_zone  
		<where>
		date &gt;= #{startDate} and #{endDate} &gt;=date
		<if test="zoneId != null">
		and zone_id=#{zoneId}
		</if>
		<if test="operatorId != null">
		and operator_id=#{operatorId}
		</if>
		</where>
		) as a)
	</select>
	
	
	<select id="findDeliverEffectAnalysis" parameterType="map" resultType="com.cloudcross.ssp.model.OperatorReport">
		select 
		zone_id as zoneId,date as reportDate,sum(pv) as pv,sum(uv) as uv,sum(impression) as impression,
		sum(uimpression) as uImpression, sum(uclick) as uClick,
		100*sum(impression)/sum(pv) as fillRate,
        sum(click) as click,100* sum(click)/sum(impression) as clickRate,sum(ecpm) as eCpm,sum(income) as income
		from t_rpt_zone 
		<where>
		date &gt;= #{startDate} and #{endDate} &gt;=date
		<if test="zoneId!=null">
		and zone_id=#{zoneId}
		</if>
		<if test="operatorId != null">
		and operator_id=#{operatorId}
		</if>
		</where>
		group by date
		limit #{pager.offset}, #{pager.limit}
	</select>
	
	<select id="findSum" parameterType="map" resultType="com.cloudcross.ssp.model.OperatorReport">
		select sum(pv) as pv, sum(uv) as uv, sum(impression) as impression, sum(click) as click, sum(income) as income
		from t_rpt_zone where date &gt;= #{startDate} and #{endDate} &gt;=date
		<if test="zoneId!=null">
		and zone_id=#{zoneId}
		</if>
		<if test="operatorId != null">
		and operator_id=#{operatorId}
		</if>
	</select>
	
	
	<select id="findDeliverEffectAnalysis1" parameterType="map" resultType="com.cloudcross.ssp.model.OperatorReport">
		select 
		zone_id as zoneId,date as reportDate,sum(pv) as pv,sum(uv) as uv,sum(impression) as impression,
		sum(uimpression) as uImpression, sum(uclick) as uClick,
		100*sum(impression)/sum(pv) as fillRate,
        sum(click) as click,100* sum(click)/sum(impression) as clickRate,sum(ecpm) as eCpm,sum(income) as income
		from t_rpt_zone 
		<where>
		date &gt;= #{startDate} and #{endDate} &gt;=date
		<if test="zoneId!=null">
		and zone_id=#{zoneId}
		</if>
		<if test="operatorId != null">
		and operator_id=#{operatorId}
		</if>
		</where>
		group by date
	</select>
	
	<select id="countBySize" parameterType="map" resultType="int">
		 select count(height) from ((select distinct width,height from t_rpt_zone_size where 
		  date &gt;= #{startDate} and #{endDate} &gt;=date
		  <if test="operatorId!=null">
		    and operator_id=#{operatorId}
		 </if>
		 ) as a )
	</select>
	
	<select id="findBySize" parameterType="map" resultType="com.cloudcross.ssp.model.OperatorReport">
		select 
	    id,date as reportDate,width ,height,100*sum(pv)/(select sum(pv) from t_rpt_zone_size 
	    where
            date &gt;= #{startDate} and #{endDate} &gt;=date
		  <if test="operatorId!=null">
		    and operator_id=#{operatorId}
		 </if>
		) as viewRate,sum(pv) as pv,sum(uv) as uv,sum(impression) as impression,
		sum(uimpression) as uImpression, sum(uclick) as uClick,
		100*impression/pv as fillRate,sum(click) as click,100*sum(click)/sum(impression) as clickRate,sum(ecpm) as eCpm,sum(income) as income
		from t_rpt_zone_size 
		where
            date &gt;= #{startDate} and #{endDate} &gt;=date
		  <if test="operatorId!=null">
		    and operator_id=#{operatorId}
		 </if>
		group by width,height
		order by impression desc
		limit #{pager.offset}, #{pager.limit}
	</select>
	
	<select id="findBySize1" parameterType="map" resultType="com.cloudcross.ssp.model.OperatorReport">
		select 
	    id,date as reportDate,width ,height,100*sum(pv)/(select sum(pv) from t_rpt_zone_size 
	    where
            date &gt;= #{startDate} and #{endDate} &gt;=date
		  <if test="operatorId!=null">
		    and operator_id=#{operatorId}
		 </if>
		) as viewRate,sum(pv) as pv,sum(uv) as uv,sum(impression) as impression,
		sum(uimpression) as uImpression, sum(uclick) as uClick,
		100*sum(impression)/sum(pv) as fillRate,sum(click) as click,100*sum(click)/sum(impression) as clickRate,sum(ecpm) as eCpm,sum(income) as income
		from t_rpt_zone_size 
		where
            date &gt;= #{startDate} and #{endDate} &gt;=date
		  <if test="operatorId!=null">
		    and operator_id=#{operatorId}
		 </if>
		group by width,height
		order by impression desc
	</select>
	
	
	<select id="countByPosition" parameterType="map" resultType="int">
		select count(position) from ((select distinct position from t_rpt_zone_position where 
		 date &gt;= #{startDate} and #{endDate} &gt;=date
		  <if test="operatorId!=null">
		    and operator_id=#{operatorId}
		 </if>
		 ) as a )
	</select>
	
	<select id="findByPosition" parameterType="map" resultType="com.cloudcross.ssp.model.OperatorReport">
		select 
	    id,date as reportDate,position,100*sum(pv)/(select sum(pv) from t_rpt_zone_position where 
		 date &gt;= #{startDate} and #{endDate} &gt;=date
		  <if test="operatorId!=null">
		    and operator_id=#{operatorId}
		 </if>
		 ) as viewRate,sum(pv) as pv,sum(uv) as uv,sum(impression) as impression ,
		 sum(uimpression) as uImpression, sum(uclick) as uClick,
		 100*sum(impression)/sum(pv) as fillRate,sum(click) as click ,100*sum(click)/sum(impression) as clickRate,sum(eCpm) as eCpm,sum(income) as income 
		from t_rpt_zone_position
		where 
		 date &gt;= #{startDate} and #{endDate} &gt;=date
		  <if test="operatorId!=null">
		    and operator_id=#{operatorId}
		 </if>
		group by position 
		order by impression desc 
      	limit #{pager.offset}, #{pager.limit}
	</select>
	
	<select id="findByPosition1" parameterType="map" resultType="com.cloudcross.ssp.model.OperatorReport">
		select 
	    id,date as reportDate,position,100*sum(pv)/(select sum(pv) from t_rpt_zone_position where 
		 date &gt;= #{startDate} and #{endDate} &gt;=date
		  <if test="operatorId!=null">
		    and operator_id=#{operatorId}
		 </if>
		 ) as viewRate,sum(pv) as pv,sum(uv) as uv,sum(impression) as impression ,
		 sum(uimpression) as uImpression, sum(uclick) as uClick,
		 100*sum(impression)/sum(pv) as fillRate,sum(click) as click ,100*sum(click)/sum(impression) as clickRate,sum(eCpm) as eCpm,sum(income) as income 
		from t_rpt_zone_position
		where 
		 date &gt;= #{startDate} and #{endDate} &gt;=date
		  <if test="operatorId!=null">
		    and operator_id=#{operatorId}
		 </if>
		group by position  
		order by impression desc
	</select>
	
	<select id="countBySystem" parameterType="map" resultType="int">
		  select count(os) from ((select distinct os from t_rpt_zone_os where 
		  date &gt;= #{startDate} and #{endDate} &gt;=date
		  <if test="operatorId!=null">
		    and operator_id=#{operatorId}
		 </if>
		  ) as a)
	</select>
	<select id="findBySystem" parameterType="map" resultType="com.cloudcross.ssp.model.OperatorReport">
		select 
	    os as system,100*(sum(pv)/(select sum(pv) from t_rpt_zone_os
		where date &gt;= #{startDate} and #{endDate} &gt;=date
		<if test="operatorId!=null">
		    and operator_id=#{operatorId}
		</if> 
		   )) as viewRate,
		sum(pv) as pv,sum(uv) as uv,sum(impression) as impression,
		sum(uimpression) as uImpression, sum(uclick) as uClick,
		100*(sum(impression)/sum(pv)) as fillRate,
        sum(click) as click,100*(sum(click)/sum(impression)) as clickRate,sum(ecpm) as eCpm,sum(income) as income
		from t_rpt_zone_os 
		where date &gt;= #{startDate} and #{endDate} &gt;=date
		<if test="operatorId!=null">
		    and operator_id=#{operatorId}
		</if>
		group by os
		order by impression desc
		limit #{pager.offset}, #{pager.limit}
	</select>
	
	
	<select id="findBySystem1" parameterType="map" resultType="com.cloudcross.ssp.model.OperatorReport">
		select 
	    os as system,100*(sum(pv)/(select sum(pv) from t_rpt_zone_os
		where date &gt;= #{startDate} and #{endDate} &gt;=date
		<if test="operatorId!=null">
		    and operator_id=#{operatorId}
		</if> 
		   )) as viewRate,
		sum(pv) as pv,sum(uv) as uv,sum(impression) as impression,
		sum(uimpression) as uImpression, sum(uclick) as uClick,
		100*(sum(impression)/sum(pv)) as fillRate,
        sum(click) as click,100*(sum(click)/sum(impression)) as clickRate,sum(ecpm) as eCpm,sum(income) as income
		from t_rpt_zone_os 
		where date &gt;= #{startDate} and #{endDate} &gt;=date
		<if test="operatorId!=null">
		    and operator_id=#{operatorId}
		</if>
		group by os
		order by impression desc
	</select>
	
	
	<select id="findByDevice" parameterType="map" resultType="com.cloudcross.ssp.model.OperatorReport">
		select 
	    id,date as reportDate,device,100*sum(pv)/
	    (select sum(pv) from t_rpt_zone_device where date &gt;= #{startDate} and #{endDate} &gt;=date
		<if test="operatorId!=null">
		    and operator_id=#{operatorId}
		</if> ) as viewRate,
	    sum(pv) as pv,sum(uv) as uv,sum(impression) as impression ,
	    sum(uimpression) as uImpression, sum(uclick) as uClick,
	    100*sum(impression)/sum(pv) as fillRate,sum(click) as click ,100*sum(click)/sum(impression) as clickRate,sum(eCpm) as eCpm,sum(income) as income
        from t_rpt_zone_device
		where date &gt;= #{startDate} and #{endDate} &gt;=date
		<if test="operatorId!=null">
		    and operator_id=#{operatorId}
		</if> 
		group by device 
		order by impression desc
	</select>
	
	<select id="countByAllAreal" parameterType="map" resultType="int">
		select count(cn) from ((select distinct cn from t_rpt_zone_location a,t_location b 
  		where
		a.location = b.location and date &gt;= #{startDate} and #{endDate} &gt;=date
		<if test="operatorId!=null">
		    and operator_id=#{operatorId}
		</if> ) as t)
	</select>
	
	<select id="findByAllAreal" parameterType="map" resultType="com.cloudcross.ssp.model.OperatorReport">
		  select 
	    a.id,a.date as reportDate,b.cn,b.cn_city,100*sum(a.pv)/(select sum(pv) from t_rpt_zone_location where date &gt;= #{startDate} and #{endDate} &gt;=date
		<if test="operatorId!=null">
		    and operator_id=#{operatorId}
		</if>) as viewRate,
	    sum(a.pv) as pv,sum(a.uv) as uv,sum(a.impression) as impression,
	    sum(uimpression) as uImpression, sum(uclick) as uClick,100*sum(a.impression)/sum(a.pv) as fillRate,sum(a.click) as click,100*sum(a.click)/sum(a.impression) as clickRate,sum(a.eCpm) as eCpm,sum(a.income) as income
		from t_rpt_zone_location a,t_location b
	 	where
		a.location = b.location  and a.date &gt;= #{startDate} and #{endDate} &gt;=a.date
		<if test="operatorId!=null">
		    and operator_id=#{operatorId}
		</if>
		group by b.cn 
		 order by cn
		limit #{pager.offset}, #{pager.limit}
	</select>
	
	<select id="findByAreal" parameterType="map" resultType="com.cloudcross.ssp.model.OperatorReport">
		select 
	    b.cn,b.cn_city,100*sum(a.pv)/(select sum(pv) from t_rpt_zone_location where date &gt;= #{startDate} and #{endDate} &gt;=date
		<if test="operatorId!=null">
		    and operator_id=#{operatorId}
		</if>) as viewRate,
	    sum(a.pv) as pv,sum(a.uv) as uv,sum(a.impression),
	    sum(uimpression) as uImpression, sum(uclick) as uClick,
	    100*sum(a.impression)/sum(a.pv) as fillRate,sum(a.click),100*sum(a.click)/sum(a.impression) as clickRate,sum(a.eCpm) as eCpm,sum(a.income) as income
		from t_rpt_zone_location a,t_location b
		where
		a.location=b.location and b.cn=#{cn} and date &gt;= #{startDate} and #{endDate} &gt;=date
		<if test="operatorId!=null">
		    and operator_id=#{operatorId}
		</if>
       group by b.cn_city
	</select>
	
	<select id="findAllArea" parameterType="map" resultType="com.cloudcross.ssp.model.OperatorReport">
		select 
	    a.id,a.date as reportDate,b.cn,b.cn_city,100*sum(a.pv)/(select sum(pv) from t_rpt_zone_location where date &gt;= #{startDate} and #{endDate} &gt;=date
		<if test="operatorId!=null">
		    and operator_id=#{operatorId}
		</if>) as viewRate,
	    sum(a.pv) as pv,sum(a.uv) as uv,sum(a.impression) as impression,
	    sum(uimpression) as uImpression, sum(uclick) as uClick,
	    100*sum(a.impression)/sum(a.pv) as fillRate,sum(a.click) as click,100*sum(a.click)/sum(a.impression) as clickRate,sum(a.eCpm) as eCpm,sum(a.income) as income
		from t_rpt_zone_location a,t_location b
	 	where
		a.location = b.location  and a.date &gt;= #{startDate} and #{endDate} &gt;=a.date
		<if test="operatorId!=null">
		    and operator_id=#{operatorId}
		</if>
		group by b.cn 
		order by b.cn
	</select>
	
	
	<select id="countByPlace" parameterType="map" resultType="int">
		select count(place_id) from ((select distinct place_id from t_rpt_zone_place where 
		date &gt;= #{startDate} and #{endDate} &gt;=date
		<if test="operatorId!=null">
		    and operator_id=#{operatorId}
		</if>) as a) 
	</select>
	
	<select id="findByPlace" parameterType="map" resultType="com.cloudcross.ssp.model.OperatorReport">
		select t_place.name as place,sum(p.pv) as pv, sum(p.uv) as uv, sum(p.impression) as impression,
		sum(uimpression) as uImpression, sum(uclick) as uClick,
		 sum(p.click) as click, sum(p.ecpm) as eCpm,
    sum(p.income) as income, 100 * sum(p.pv) / (select sum(pv) from t_rpt_zone_place where date &gt;= #{startDate} and #{endDate} &gt;=date
		<if test="operatorId!=null">
		    and operator_id=#{operatorId}
		</if> ) as viewRate, 
    100 * sum(p.impression) / sum(p.pv) as fillRate, 100 * sum(p.click) / sum(p.impression) as clickRate
    from ((select distinct place_id from t_rpt_zone_place where date &gt;= #{startDate} and #{endDate} &gt;=date
		<if test="operatorId!=null">
		    and operator_id=#{operatorId}
		</if> ) as a ), 
    t_rpt_zone_place as p, t_place where a.place_id = p.place_id and a.place_id = t_place.id
    and date &gt;= #{startDate} and #{endDate} &gt;=date
		<if test="operatorId!=null">
		    and operator_id=#{operatorId}
		</if> 
    group by p.place_id
    order by p.impression desc
	limit #{pager.offset}, #{pager.limit}
	</select>
	
	
	<select id="findByPlace1" parameterType="map" resultType="com.cloudcross.ssp.model.OperatorReport">
		select t_place.name as place,sum(p.pv) as pv, sum(p.uv) as uv, sum(p.impression) as impression, 
		sum(uimpression) as uImpression, sum(uclick) as uClick,
		sum(p.click) as click, sum(p.ecpm) as eCpm,
    sum(p.income) as income, 100 * sum(p.pv) / (select sum(pv) from t_rpt_zone_place where date &gt;= #{startDate} and #{endDate} &gt;=date
		<if test="operatorId!=null">
		    and operator_id=#{operatorId}
		</if> ) as viewRate, 
    100 * sum(p.impression) / sum(p.pv) as fillRate, 100 * sum(p.click) / sum(p.impression) as clickRate
    from ((select distinct place_id from t_rpt_zone_place where date &gt;= #{startDate} and #{endDate} &gt;=date
		<if test="operatorId!=null">
		    and operator_id=#{operatorId}
		</if> ) as a ), 
    t_rpt_zone_place as p, t_place where a.place_id = p.place_id and a.place_id = t_place.id
    and date &gt;= #{startDate} and #{endDate} &gt;=date
		<if test="operatorId!=null">
		    and operator_id=#{operatorId}
		</if> 
    group by p.place_id
    order by p.impression desc
	</select>
	
	<select id="countByApmac" parameterType="map" resultType="int">
			select count(apmac) from ((select distinct apmac from t_rpt_zone_ap where 
		 date &gt;= #{startDate} and date &lt;= #{endDate}
		 <if test="operatorId!=null">
		    and operator_id=#{operatorId}
		</if>
		 ) as a) 
	</select>
	
	<select id="findByApmac" parameterType="map" resultType="com.cloudcross.ssp.model.OperatorReport">
		select apmac, sum(pv) as pv, sum(uv) as uv, sum(impression) as impression, 
		sum(uimpression) as uImpression,
		sum(uclick) as uClick,sum(click) as click, sum(ecpm) as eCpm,
    sum(income) as income, 100 * sum(pv) / (select sum(pv) from t_rpt_zone_ap where date &gt;= #{startDate} and date &lt;= #{endDate}
    <if test="operatorId!=null">
		    and operator_id=#{operatorId}
	</if>
    ) as viewRate, 
    100 * sum(impression) / sum(pv) as fillRate, 100 * sum(click) / sum(impression) as clickRate
    from  t_rpt_zone_ap  where 
    date &gt;= #{startDate} and date &lt;= #{endDate}
    <if test="operatorId!=null">
		    and operator_id=#{operatorId}
	</if>
    group by apmac
    order by impression desc
	limit #{pager.offset}, #{pager.limit}
	</select>
	
	<select id="findByApmac1" parameterType="map" resultType="com.cloudcross.ssp.model.OperatorReport">
			select apmac, sum(pv) as pv, sum(uv) as uv, sum(impression) as impression, 
		sum(uimpression) as uImpression,
		sum(uclick) as uClick,sum(click) as click, sum(ecpm) as eCpm,
    sum(income) as income, 100 * sum(pv) / (select sum(pv) from t_rpt_zone_ap where date &gt;= #{startDate} and date &lt;= #{endDate}
    <if test="operatorId!=null">
		    and operator_id=#{operatorId}
	</if>
    ) as viewRate, 
    100 * sum(impression) / sum(pv) as fillRate, 100 * sum(click) / sum(impression) as clickRate
    from  t_rpt_zone_ap  where 
    date &gt;= #{startDate} and date &lt;= #{endDate}
    <if test="operatorId!=null">
		    and operator_id=#{operatorId}
	</if>
    group by apmac
    order by impression desc
	</select>
	
	
	
</mapper>