<?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="org.jeecg.ctop.material.mapper.KuaishouOperationRecordMapper">


<!-- 查询快手组 时报 消耗前10 -->
    <select id="getKuaishouUnitReportHourly" resultType="org.jeecg.ctop.material.entity.KuaishouUnitReportHourlyPojo">
        SELECT
            unit_id,
            unit_name,
            sum( aclick ) AS aClick,
            sum( bclick ) AS bClick,
            sum( charge ) AS cost,
            sum( `show` ) AS photoShow,
            sum( photo_click ) AS photoClick ,
            CONCAT( ROUND( sum( photo_click ) / sum( `show` ) * 100, 2 ), '%' ) AS clickRate,
            CONCAT( ROUND( sum( bclick ) / sum( aclick ) * 100, 2 ), '%' ) AS bClickRate,
            ROUND( sum( charge ) / sum( `show` ) * 1000, 2 ) AS cpm,
            ROUND( sum( charge ) / sum( photo_click ) * 1000, 2 ) AS cpc,
            ROUND( sum( charge ) / sum( bclick ) * 1000, 2 ) AS cpb,
            sum( download_started ) AS downloadStart,
            CONCAT( ROUND( sum( download_started ) / sum( bclick ) * 100, 2 ), '%' ) AS downloadStartRate,
            ROUND( sum( charge ) / sum( download_started ) * 100, 2 ) AS downloadStartCost,

            CONCAT(ROUND( sum( event_pay_first_day ) / sum( activation ) * 100, 2 ), '%' ) AS eventNewUserPayRatio,
            sum( download_completed ) AS downloadFinish,
            CONCAT( ROUND( sum( download_completed ) / sum( download_started ) * 100, 2 ), '%' ) AS downloadFinishRate,

            ROUND( sum( charge ) / sum( download_completed ) * 100, 2 ) AS downloadFinishCost,
            sum( activation ) AS active,
            ROUND( sum( charge ) / sum( activation ) * 100, 2 ) AS activeCost,
            CONCAT( ROUND( sum( activation ) / sum( download_completed ) * 100, 2 ), '%' ) AS activeRate,
            sum( event_pay_first_day ) AS firstDayPay,
            sum( event_pay_purchase_amount_first_day ) AS firstDayPayAmount,
            ROUND( sum( charge ) / sum( event_pay_first_day ) * 100, 2 ) AS firstDayPayCost,
            ROUND( sum( event_pay_purchase_amount_first_day ) / sum( charge ) * 100, 2 ) AS firstDayPayROI,
            sum( event_pay ) AS eventPay,
            sum( event_pay_purchase_amount ) AS eventPayPurchaseAmount,
            ROUND( sum( charge ) / sum( event_pay ) * 100, 2 ) AS eventPayCost,
            ROUND( sum( event_pay_purchase_amount ) / sum( charge ) * 100, 2 ) AS ROI,
            sum( event_register ) AS register,
            ROUND( sum( charge ) / sum( event_register ) * 100, 2 ) AS activeRegisterCost,
            CONCAT( ROUND( sum( event_register ) / sum( activation ) * 100, 2 ), '%' ) AS activeRegisterRate,
            sum( event_next_day_stay ) AS nextDayOpen,
            ROUND( sum( charge ) / sum( event_next_day_stay ) * 100, 2 ) AS nextDayOpenCost,
            CONCAT(ROUND( sum( charge ) / sum( form_count ) * 100, 2 ), '%' ) AS nextDayOpenRate,
            sum( event_jin_jian_app ) AS eventJinJianApp,
            ROUND( sum( charge ) / sum( event_jin_jian_app ) * 100, 2 ) AS eventJinJianAppCost,
            sum( event_credit_grant_app ) AS eventCreditGrantApp,
            ROUND( sum( charge ) / sum( event_credit_grant_app ) * 100, 2 ) AS eventCreditGrantAppCost,
            CONCAT( ROUND( sum( event_credit_grant_app ) / sum( event_jin_jian_app ) * 100, 2 ), '%' ) AS eventCreditGrantAppRate,
            ROUND( sum( charge ) / sum( aclick ) * 100, 2 )  AS play3sRate,
            sum( key_action ) AS keyAction,
            ROUND( sum( charge ) / sum( aclick ) * 100, 2 ) AS keyActionCost,
            sum( event_app_invoked ) AS eventAppInvoked,
            ROUND( sum( charge ) / sum( bclick ) * 100, 2 ) AS eventAppInvokedCost,
            CONCAT( ROUND( sum( event_app_invoked ) / sum( bclick ) * 100, 2 ), '%' ) AS eventAppInvokedRatio,
            sum( form_count ) AS formCount,
            CONCAT( ROUND( sum( form_cost ) / sum( bclick ) * 100, 2 ), '%' ) AS formCountRate,
            ROUND( sum( charge ) / sum( event_jin_jian_app ) * 100, 2 ) AS formCountCost,
            sum( event_jin_jian_landing_page ) AS eventJinJianLandingPage,
            ROUND( sum( charge ) / sum( event_jin_jian_landing_page ) * 100, 2 ) AS eventJinJianLandingPageCost,
            sum( event_credit_grant_landing_page ) AS eventCreditGrantLandingPage,
            ROUND( sum( charge ) / sum( event_credit_grant_landing_page_ratio ) * 100, 2 ) AS eventCreditGrantLandingPageCost,
            CONCAT( ROUND( sum( event_credit_grant_landing_page ) / sum( event_jin_jian_landing_page ) * 100, 2 ), '%' ) AS eventCreditGrantLandingPageRate,
            sum( event_button_click ) AS submit,
            ROUND( sum( charge ) / sum( event_button_click ) * 100, 2 ) AS submitCost,
            CONCAT( ROUND( sum( event_button_click ) / sum( bclick ) * 100, 2 ), '%' ) AS submitRate
        FROM
            application.kuaishou_unit_report_hourly_dw
        WHERE
         stat_date >= #{startTime}
        AND stat_date &lt;= #{endTime}
        AND account_id = #{advertiserId}
        GROUP BY unit_id
        ORDER BY sum( charge ) DESC
        LIMIT 10
    </select>



    <!-- 查询快手组 日报 消耗前10  -->
    <select id="getKuaishouUnitReportDaily" resultType="org.jeecg.ctop.material.entity.KuaishouUnitReportHourlyPojo">
        SELECT
            unit_id,
            unit_name,
            sum( aclick ) AS aClick,
            sum( bclick ) AS bClick,
            sum( charge ) AS cost,
            sum( `show` ) AS photoShow,
            sum( photo_click ) AS photoClick ,
            CONCAT( ROUND( sum( photo_click ) / sum( `show` ) * 100, 2 ), '%' ) AS clickRate,
            CONCAT( ROUND( sum( bclick ) / sum( aclick ) * 100, 2 ), '%' ) AS bClickRate,
            ROUND( sum( charge ) / sum( `show` ) * 1000, 2 ) AS cpm,
            ROUND( sum( charge ) / sum( photo_click ) * 1000, 2 ) AS cpc,
            ROUND( sum( charge ) / sum( bclick ) * 1000, 2 ) AS cpb,
            sum( download_started ) AS downloadStart,
            CONCAT( ROUND( sum( download_started ) / sum( bclick ) * 100, 2 ), '%' ) AS downloadStartRate,
            ROUND( sum( charge ) / sum( download_started ) * 100, 2 ) AS downloadStartCost,

            CONCAT(ROUND( sum( event_pay_first_day ) / sum( activation ) * 100, 2 ), '%' ) AS eventNewUserPayRatio,
            sum( download_completed ) AS downloadFinish,
            CONCAT( ROUND( sum( download_completed ) / sum( download_started ) * 100, 2 ), '%' ) AS downloadFinishRate,

            ROUND( sum( charge ) / sum( download_completed ) * 100, 2 ) AS downloadFinishCost,
            sum( activation ) AS active,
            ROUND( sum( charge ) / sum( activation ) * 100, 2 ) AS activeCost,
            CONCAT( ROUND( sum( activation ) / sum( download_completed ) * 100, 2 ), '%' ) AS activeRate,
            sum( event_pay_first_day ) AS firstDayPay,
            sum( event_pay_purchase_amount_first_day ) AS firstDayPayAmount,
            ROUND( sum( charge ) / sum( event_pay_first_day ) * 100, 2 ) AS firstDayPayCost,
            ROUND( sum( event_pay_purchase_amount_first_day ) / sum( charge ) * 100, 2 ) AS firstDayPayROI,
            sum( event_pay ) AS eventPay,
            sum( event_pay_purchase_amount ) AS eventPayPurchaseAmount,
            ROUND( sum( charge ) / sum( event_pay ) * 100, 2 ) AS eventPayCost,
            ROUND( sum( event_pay_purchase_amount ) / sum( charge ) * 100, 2 ) AS ROI,
            sum( event_register ) AS register,
            ROUND( sum( charge ) / sum( event_register ) * 100, 2 ) AS activeRegisterCost,
            CONCAT( ROUND( sum( event_register ) / sum( activation ) * 100, 2 ), '%' ) AS activeRegisterRate,
            sum( event_next_day_stay ) AS nextDayOpen,
            ROUND( sum( charge ) / sum( event_next_day_stay ) * 100, 2 ) AS nextDayOpenCost,
            CONCAT(ROUND( sum( charge ) / sum( form_count ) * 100, 2 ), '%' ) AS nextDayOpenRate,
            sum( event_jin_jian_app ) AS eventJinJianApp,
            ROUND( sum( charge ) / sum( event_jin_jian_app ) * 100, 2 ) AS eventJinJianAppCost,
            sum( event_credit_grant_app ) AS eventCreditGrantApp,
            ROUND( sum( charge ) / sum( event_credit_grant_app ) * 100, 2 ) AS eventCreditGrantAppCost,
            CONCAT( ROUND( sum( event_credit_grant_app ) / sum( event_jin_jian_app ) * 100, 2 ), '%' ) AS eventCreditGrantAppRate,
            ROUND( sum( charge ) / sum( aclick ) * 100, 2 )  AS play3sRate,
            sum( key_action ) AS keyAction,
            ROUND( sum( charge ) / sum( aclick ) * 100, 2 ) AS keyActionCost,
            sum( event_app_invoked ) AS eventAppInvoked,
            ROUND( sum( charge ) / sum( bclick ) * 100, 2 ) AS eventAppInvokedCost,
            CONCAT( ROUND( sum( event_app_invoked ) / sum( bclick ) * 100, 2 ), '%' ) AS eventAppInvokedRatio,
            sum( form_count ) AS formCount,
            CONCAT( ROUND( sum( form_cost ) / sum( bclick ) * 100, 2 ), '%' ) AS formCountRate,
            ROUND( sum( charge ) / sum( event_jin_jian_app ) * 100, 2 ) AS formCountCost,
            sum( event_jin_jian_landing_page ) AS eventJinJianLandingPage,
            ROUND( sum( charge ) / sum( event_jin_jian_landing_page ) * 100, 2 ) AS eventJinJianLandingPageCost,
            sum( event_credit_grant_landing_page ) AS eventCreditGrantLandingPage,
            ROUND( sum( charge ) / sum( event_credit_grant_landing_page_ratio ) * 100, 2 ) AS eventCreditGrantLandingPageCost,
            CONCAT( ROUND( sum( event_credit_grant_landing_page ) / sum( event_jin_jian_landing_page ) * 100, 2 ), '%' ) AS eventCreditGrantLandingPageRate,
            sum( event_button_click ) AS submit,
            ROUND( sum( charge ) / sum( event_button_click ) * 100, 2 ) AS submitCost,
            CONCAT( ROUND( sum( event_button_click ) / sum( bclick ) * 100, 2 ), '%' ) AS submitRate
        FROM
            application.kuaishou_unit_report_daily_dw
        WHERE
            stat_date &gt;= #{startTime}
          AND stat_date &lt;= #{endTime}
          AND account_id = #{advertiserId}
        GROUP BY unit_id
        ORDER BY sum( charge ) DESC
            LIMIT 10
    </select>


    <!-- 查看快手 组数据信息  -->
    <select id="getKuaishouOperationUnitReport" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        unit_id AS unitId,
        stat_date AS statDate,
        HOUR AS statHour,
        charge,
        activation,
        ROUND( charge / activation * 100, 2 ) AS activeCost,
        event_register AS eventRegister,
        event_register_cost AS eventRegisterCost,
        event_next_day_stay AS eventNextDayStay,
        event_next_day_stay_cost AS eventNextDayStay,
        event_pay_first_day AS eventPayIfrstDay,
        ROUND( charge / event_pay_first_day * 100, 2 ) AS eventPayIfrstDayCost,
        form_count AS formCount
        FROM
        application.kuaishou_unit_report_hourly_dw
        WHERE
        stat_date= #{statDate}
        AND hour = #{statHour}
        AND account_id = #{accountId}
        AND unit_id = #{unitId}
        ORDER BY
        stat_date,HOUR


    </select>




    <!-- 查看账户操作记录 -->
    <select id="getKuaishouOperationRecordByAdvertiserId" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            advertiser_id,
            object_id,
            object_name,
            operation_type,
            operation_target,
            role_type,
            operation_time,
            content_log,
            create_time
        FROM
            media_api.`kuaishou_tools_advertiser_operation_record_list`
        WHERE
            advertiser_id = #{advertiserId}
            AND operation_type = #{operationType}
            AND object_id = #{objectId}
          AND date_format(operation_time, '%Y%m%d%H') = #{operationTime}


    </select>




</mapper>
