<?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(click)          click,
               max(stat_hour)      maxHour
        FROM ctop_bytedance_report_advertiser_hourly
        WHERE stat_datetime = #{statDate}
    </select>

    <select id="queryTodaySumByHour" resultType="com.alibaba.fastjson.JSONObject">
        SELECT ROUND(sum(cost), 2) cost,
               sum(show_num)       showNum,
               sum(click)          click
        FROM ctop_bytedance_report_advertiser_hourly
        WHERE stat_hour &lt;= #{hour}
    </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,
               CASE
                   WHEN sum(click)>=0 and sum(show_num)>0 THEN
                       round(sum(click)/sum(show_num)*100,2)
                   ELSE 0
                   END AS  'clickRate',
               CASE
                   WHEN sum(cost)>=0 and sum(show_num)>0 THEN
                       round(sum(cost)/sum(show_num)*1000,2)
                   ELSE
                       0
                   END AS  'cpm',
               CASE
                   WHEN sum(click)>=0 and sum(cost)>0 THEN
                       round(sum(cost)/sum(click),2)
                   ELSE
                       0
                   END AS  'cp'
        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} '00:00:00'
          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,
               CASE
                   WHEN sum(click)>=0 and sum(show_num)>0 THEN ROUND(sum(click) / sum(show_num) * 100,2)
                   else '0' end AS   'clickRate',
               CASE
                   WHEN (sum(cost) / sum(show_num)) IS NULL THEN
                       0
                   ELSE
                       ROUND(
                               (sum(cost) / sum(show_num)) * 1000,
                               2
                           )
                   END AS                             'cpm',
               CASE
                   WHEN (sum(click) / sum(cost)) IS NULL THEN
                       0
                   ELSE
                       ROUND((sum(cost) / sum(click)), 2)
                   END AS                             'cp',
               DATE_FORMAT(stat_datetime, '%Y-%m-%d') statDate
        FROM ctop_bytedance_report_advertiser_daily
        WHERE stat_datetime &lt;= #{endDate} '00:00:00'
          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,
               CASE
                   WHEN (sum(click) / sum(show_num)) IS NULL THEN
                       0
                   ELSE


                                       ROUND(
                                               (sum(click) / sum(show_num)) * 100,
                                               2
                                           )
                   END AS                          'clickRate',
               CASE
                   WHEN (sum(cost) / sum(show_num)) IS NULL THEN
                       0
                   ELSE
                       ROUND(
                               (sum(cost) / sum(show_num)) * 1000,
                               2
                           )
                   END AS                          'cpm',
               CASE
                   WHEN (sum(click) / sum(cost)) IS NULL THEN
                       0
                   ELSE
                       ROUND((sum(cost) / sum(click)), 2)
                   END AS                          'cp',
               DATE_FORMAT(stat_datetime, '%Y-%m') statDate
        FROM ctop_bytedance_report_advertiser_daily
        WHERE stat_datetime &lt;= #{endDate} '00:00:00'
          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,
               CASE
                   WHEN (sum(click) / sum(show_num)) IS NULL THEN
                       0
                   ELSE


                                       ROUND(
                                               (sum(click) / sum(show_num)) * 100,
                                               2
                                           )

                   END AS                                                  'clickRate',
               CASE
                   WHEN (sum(cost) / sum(show_num)) IS NULL THEN
                       0
                   ELSE
                       ROUND(
                               (sum(cost) / sum(show_num)) * 1000,
                               2
                           )
                   END AS                                                  'cpm',
               CASE
                   WHEN (sum(click) / sum(cost)) IS NULL THEN
                       0
                   ELSE
                       ROUND((sum(cost) / sum(click)), 2)
                   END AS                                                  'cp',
               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} '00:00:00'
          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} '00:00:00'
          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} '00:00:00'
          AND stat_datetime &gt;= #{startDate}
        GROUP BY t1.advertiser_id
        ORDER BY sum(t1.show_num) DESC
        limit #{rank}
    </select>

</mapper>
