<?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="cn.com.ctop.toutiao.modules.report.mapper.BytedanceHomepageMapper">


    <select id="queryTodaySumReportBy" resultType="com.alibaba.fastjson.JSONObject">
       SELECT
	     ROUND(sum(cost),2) cost,
	     sum(show_num) showNum,
	     sum(stat_datetime) click,
	     max(stat_hour) maxHour
       FROM
	      ctop_bytedance_report_advertiser_hourly
       WHERE
	      stat_datetime = #{statDate}
      </select>


    <select id="queryTodayDetailReportBy" resultType="com.alibaba.fastjson.JSONObject">
       SELECT
         stat_datetime statDate,
         stat_hour statHour,
	     ROUND(sum(cost),2) cost,
	     sum(show_num) showNum,
	     sum(click) click
	   FROM
	      ctop_bytedance_report_advertiser_hourly
       WHERE
	      stat_datetime = #{statDate}
       GROUP BY
	      stat_hour
       ORDER BY
	      stat_hour ASC

    </select>

    <select id="querySumByStartEndDate" resultType="com.alibaba.fastjson.JSONObject">
       SELECT
         ROUND(sum(cost), 2) cost,
         sum(show_num) showNum,
         sum(click) click
       FROM
         ctop_bytedance_report_advertiser_daily
       WHERE
         stat_datetime &lt;= #{endDate}
       AND
         stat_datetime &gt;= #{startDate}
    </select>

    <select id="queryDetailByStartEndDate" resultType="com.alibaba.fastjson.JSONObject">
       SELECT
         ROUND(sum(cost), 2) cost,
         sum(show_num) showNum,
         sum(click) click,
         DATE_FORMAT(stat_datetime,'%Y-%m-%d') statDate
       FROM
         ctop_bytedance_report_advertiser_daily
       WHERE
            stat_datetime &lt;= #{endDate}
       AND  stat_datetime &gt;= #{startDate}
       group by stat_datetime
       order by stat_datetime asc
    </select>

    <select id="queryDetailGroupMonthByDate" resultType="com.alibaba.fastjson.JSONObject">
       SELECT
         ROUND(sum(cost), 2) cost,
         sum(show_num) showNum,
         sum(click) click,
         DATE_FORMAT(stat_datetime,'%Y-%m') statDate
       FROM
         ctop_bytedance_report_advertiser_daily
       WHERE
         stat_datetime &lt;= #{endDate}
       AND
         stat_datetime &gt;= #{startDate}
      group by DATE_FORMAT(stat_datetime,'%Y-%m')
      order by stat_datetime asc
    </select>

    <select id="queryDetailGroupDayByMonth" resultType="com.alibaba.fastjson.JSONObject">
       SELECT
         ROUND(sum(cost), 2) cost,
         sum(show_num) showNum,
         sum(click) click,
         DATE_FORMAT(stat_datetime,'%Y-%m-%d') statDate,
         SUBSTR(DATE_FORMAT(stat_datetime,'%Y-%m-%d') FROM 9 FOR 2) day
       FROM
         ctop_bytedance_report_advertiser_daily
       WHERE
         stat_datetime like '${month}%'
      group by DATE_FORMAT(stat_datetime,'%Y-%m-%d')
      order by stat_datetime asc
    </select>

    <select id="queryTopCost" resultType="com.alibaba.fastjson.JSONObject">
       SELECT
         t2.auth_name as authName,
         ROUND(sum(t1.cost), 2) cost
       FROM
         ctop_bytedance_report_advertiser_daily t1
       LEFT JOIN
         ctop_user_allocation t2 ON t1.advertiser_id=t2.account_id
       WHERE
         stat_datetime &lt;= #{endDate}
       AND
         stat_datetime &gt;= #{startDate}
       GROUP BY t1.advertiser_id
       ORDER BY sum(t1.cost) DESC
       limit #{rank}
    </select>

    <select id="queryTopClick" resultType="com.alibaba.fastjson.JSONObject">
       SELECT
         t2.auth_name as authName,
         ROUND(sum(t1.click), 2) click
       FROM
         ctop_bytedance_report_advertiser_daily t1
       LEFT JOIN
         ctop_user_allocation t2 ON t1.advertiser_id=t2.account_id
       WHERE
         stat_datetime &lt;= #{endDate}
       AND
         stat_datetime &gt;= #{startDate}
       GROUP BY t1.advertiser_id
       ORDER BY sum(t1.click) DESC
       limit #{rank}
    </select>

    <select id="queryTopShowNum" resultType="com.alibaba.fastjson.JSONObject">
       SELECT
         t2.auth_name as authName,
         ROUND(sum(t1.show_num), 2) showNum
       FROM
         ctop_bytedance_report_advertiser_daily t1
       LEFT JOIN
         ctop_user_allocation t2 ON t1.advertiser_id=t2.account_id
       WHERE
         stat_datetime &lt;= #{endDate}
       AND
         stat_datetime &gt;= #{startDate}
       GROUP BY t1.advertiser_id
       ORDER BY sum(t1.show_num) DESC
       limit #{rank}
    </select>

</mapper>
