<?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.kuaishou.modules.report.mapper.EtlKuaishouReportAccountHourlyMapper">

    <insert id="replaceBatch">
        replace into ctop_etl_kuaishou_report_account_hourly(
        stat_date,
        stat_hour,
        account_id,
        charge,
        photo_show,
        photo_click,
        aclick,
        bclick,
        photo_share,
        photo_comment,
        photo_like,
        follow,
        cancel_follow,
        report,
        block,
        negative,
        download_started,
        download_completed,
        activation,
        submit,
        event_pay_first_day,
        event_pay_purchase_amount_first_day,
        event_pay,
        event_pay_purchase_amount,
        event_register,
        event_jin_jian_app,
        event_credit_grant_app,
        event_order_paid,
        event_order_paid_purchase_amount,
        event_jin_jian_landing_page,
        event_credit_grant_landing_page,
        event_next_day_stay,
        form_count,
        play_3s_count
        )
        values
        <foreach collection="list" item="appInfo" separator=",">
            (
            #{appInfo.statDate},
            #{appInfo.statHour},
            #{appInfo.accountId},
            #{appInfo.charge},
            #{appInfo.photoShow},
            #{appInfo.photoClick},
            #{appInfo.aclick},
            #{appInfo.bclick},
            #{appInfo.photoShare},
            #{appInfo.photoComment},
            #{appInfo.photoLike},
            #{appInfo.follow},
            #{appInfo.cancelFollow},
            #{appInfo.report},
            #{appInfo.block},
            #{appInfo.negative},
            #{appInfo.downloadStarted},
            #{appInfo.downloadCompleted},
            #{appInfo.activation},
            #{appInfo.submit},
            #{appInfo.eventPayFirstDay},
            #{appInfo.eventPayPurchaseAmountFirstDay},
            #{appInfo.eventPay},
            #{appInfo.eventPayPurchaseAmount},
            #{appInfo.eventRegister},
            #{appInfo.eventJinJianApp},
            #{appInfo.eventCreditGrantApp},
            #{appInfo.eventOrderPaid},
            #{appInfo.eventOrderPaidPurchaseAmount},
            #{appInfo.eventJinJianLandingPage},
            #{appInfo.eventCreditGrantLandingPage},
            #{appInfo.eventNextDayStay},
            #{appInfo.formCount},
            #{appInfo.play3sCount}
            )
        </foreach>
    </insert>

    <select id="getOnedayAggregateByHour"
            resultType="cn.com.ctop.kuaishou.modules.report.entity.EtlKuaishouReportAccountHourly">
        SELECT *,
               IFNULL(ROUND(charge / activation, 2), 0) as 'activationCost',
               IFNULL(ROUND(charge / aclick * 1000, 2), 0) as 'impression1kCost',
               IFNULL(ROUND(charge / photo_click, 2), 0) as 'photoClickCost',
               IFNULL(ROUND(charge / bclick, 2), 0) as 'actionCost',
               IFNULL(ROUND(play_3s_count / aclick * 100, 2), 0) as 'play3sRatio',
               IFNULL(ROUND(charge / download_started, 2), 0) as 'downloadStartedCost',
               IFNULL(ROUND(charge / download_completed, 2), 0) as 'downloadCompletedCost',
               IFNULL(ROUND(charge / form_count, 2), 0) as 'formCost',
               IFNULL(ROUND(charge / event_pay_first_day, 2), 0) as 'eventPayFirstDayCost',
               IFNULL(ROUND(charge / event_pay, 2), 0) as 'eventPayCost',
               IFNULL(ROUND(charge / event_register, 2), 0) as 'eventRegisterCost',
               IFNULL(ROUND(charge / event_next_day_stay, 2), 0) as 'eventNextDayStayCost',
               IFNULL(ROUND(charge / event_jin_jian_app, 2), 0) as 'eventJinJianAppCost',
               IFNULL(ROUND(charge / event_credit_grant_app, 2), 0) as 'eventCreditGrantAppCost',
               IFNULL(ROUND(charge / event_jin_jian_landing_page, 2), 0) as 'eventJinJianLandingPageCost',
               IFNULL(ROUND(charge / event_credit_grant_landing_page, 2), 0) as 'eventCreditGrantLandingPageCost',
               IFNULL(ROUND(charge / submit, 2), 0) as 'submitCost',
               IFNULL(ROUND(event_pay_purchase_amount_first_day / charge * 100, 2), 0) as 'eventPayFirstDayRoi',
               IFNULL(ROUND(event_pay_purchase_amount / charge * 100, 2), 0) as 'eventPayRoi',
               IFNULL(ROUND(bclick / aclick * 100, 2), 0) as 'actionRatio',
               IFNULL(ROUND(photo_click / photo_show * 100, 2), 0) as 'photoClickRatio',
               IFNULL(ROUND(download_started / bclick * 100, 2), 0) as 'downloadStartedRatio',
               IFNULL(ROUND(download_completed / download_started * 100, 2), 0) as 'downloadCompletedRatio',
               IFNULL(ROUND(activation / download_completed * 100, 2), 0) as 'activationRatio',
               IFNULL(ROUND(activation / bclick * 100, 2), 0) as 'clickOnActivationRatio',
               IFNULL(ROUND(event_register / activation * 100, 2), 0) as 'eventRegisterRatio',
               IFNULL(ROUND(event_next_day_stay / activation * 100, 2), 0) as 'eventNextDayStayRatio',
               IFNULL(ROUND(event_credit_grant_app / event_jin_jian_app * 100, 2), 0) as 'eventCreditGrantAppRatio',
               IFNULL(ROUND(form_count / bclick * 100, 2), 0) as 'formActionRatio',
               IFNULL(ROUND(submit / bclick * 100, 2), 0) as 'submitRatio'
        FROM ctop_etl_kuaishou_report_account_hourly
        WHERE account_id = #{accountId}
          AND stat_date = #{startTime}
    </select>

    <select id="getOnedayEtlReportList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT stat_date as 'statDate',
        <if test="type == 'alone'">
            IFNULL(${value},0) as 'value',
        </if>
        <if test="type == 'more'">
            <if test="value == 'activationCost'">
                IFNULL(ROUND(charge / activation, 2), 0) as 'value',
            </if>
            <if test="value == 'impression1kCost'">
                IFNULL(ROUND(charge / aclick * 1000, 2), 0) as 'value',
            </if>
            <if test="value == 'photoClickCost'">
                IFNULL(ROUND(charge / photo_click, 2), 0) as 'value',
            </if>
            <if test="value == 'actionCost'">
                IFNULL(ROUND(charge / bclick, 2), 0) as 'value',
            </if>
            <if test="value == 'play3sCount'">
                IFNULL(ROUND(play_3s_ratio * aclick, 2), 0) as 'value',
            </if>
            <if test="value == 'downloadStartedCost'">
                IFNULL(ROUND(charge / download_started, 2), 0) as 'value',
            </if>
            <if test="value == 'downloadCompletedCost'">
                IFNULL(ROUND(charge / download_completed, 2), 0) as 'value',
            </if>
            <if test="value == 'formCost'">
                IFNULL(ROUND(charge / form_count, 2), 0) as 'value',
            </if>
            <if test="value == 'eventPayFirstDayCost'">
                IFNULL(ROUND(charge / event_pay_first_day, 2), 0) as 'value',
            </if>
            <if test="value == 'eventPayCost'">
                IFNULL(ROUND(charge / event_pay, 2), 0) as 'value',
            </if>
            <if test="value == 'eventRegisterCost'">
                IFNULL(ROUND(charge / event_register, 2), 0) as 'value',
            </if>
            <if test="value == 'eventNextDayStayCost'">
                IFNULL(ROUND(charge / event_next_day_stay, 2), 0) as 'value',
            </if>
            <if test="value == 'eventJinJianAppCost'">
                IFNULL(ROUND(charge / event_jin_jian_app, 2), 0) as 'value',
            </if>
            <if test="value == 'eventCreditGrantAppCost'">
                IFNULL(ROUND(charge / event_credit_grant_app, 2), 0) as 'value',
            </if>
            <if test="value == 'eventJinJianLandingPageCost'">
                IFNULL(ROUND(charge / event_jin_jian_landing_page, 2), 0) as 'value',
            </if>
            <if test="value == 'eventCreditGrantLandingPageCost'">
                IFNULL(ROUND(charge / event_credit_grant_landing_page, 2), 0) as 'value',
            </if>
            <if test="value == 'submitCost'">
                IFNULL(ROUND(charge / submit, 2), 0) as 'value',
            </if>
            <if test="value == 'eventPayFirstDayRoi'">
                IFNULL(ROUND(event_pay_purchase_amount_first_day / charge * 100, 2), 0) as 'value',
            </if>
            <if test="value == 'eventPayRoi'">
                IFNULL(ROUND(event_pay_purchase_amount / charge * 100, 2), 0) as 'value',
            </if>
            <if test="value == 'actionRatio'">
                IFNULL(ROUND(bclick / aclick * 100, 2), 0) as 'value',
            </if>
            <if test="value == 'photoClickRatio'">
                IFNULL(ROUND(photo_click / photo_show * 100, 2), 0) as 'value',
            </if>
            <if test="value == 'downloadStartedRatio'">
                IFNULL(ROUND(download_started / bclick * 100, 2), 0) as 'value',
            </if>
            <if test="value == 'downloadCompletedRatio'">
                IFNULL(ROUND(download_completed / download_started * 100, 2), 0) as 'value',
            </if>
            <if test="value == 'activationRatio'">
                IFNULL(ROUND(activation / download_completed * 100, 2), 0) as 'value',
            </if>
            <if test="value == 'eventRegisterRatio'">
                IFNULL(ROUND(event_register / activation * 100, 2), 0) as 'value',
            </if>
            <if test="value == 'eventNextDayStayRatio'">
                IFNULL(ROUND(event_next_day_stay / activation * 100, 2), 0) as 'value',
            </if>
            <if test="value == 'eventCreditGrantAppRatio'">
                IFNULL(ROUND(event_credit_grant_app / event_jin_jian_app * 100, 2), 0) as 'value',
            </if>
            <if test="value == 'formActionRatio'">
                IFNULL(ROUND(form_count / bclick * 100, 2), 0) as 'value',
            </if>
            <if test="value == 'submitRatio'">
                IFNULL(ROUND(submit / bclick * 100, 2), 0) as 'value',
            </if>
        </if>
        stat_hour as 'statHour'
        FROM ctop_kuaishou_report_hourly_account
        WHERE account_id = #{accountId}
        AND stat_date = #{startTime}
        GROUP BY stat_hour
        order by stat_hour
    </select>

    <select id="getNewlyUpdateTime" resultType="java.lang.String">
        select DATE_FORMAT(update_time, '%Y-%m-%d %H:%i:%s')
        FROM ctop_etl_kuaishou_report_account_hourly
        ORDER BY update_time desc LIMIT 1
    </select>

    <select id="accountPageListTotal" resultType="java.lang.Integer">
        select IFNULL(SUM(1), 0)
        from (
                 SELECT t2.account_id
                 FROM ctop_etl_kuaishou_report_account_hourly t1
                          LEFT JOIN ctop_user_allocation t2 ON t1.account_id = t2.account_id
                 where stat_date = #{startTime}
                   AND t2.user_id = #{userId}
                 GROUP BY t2.account_id
             ) t
    </select>

    <select id="accountPageList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ${filed},
        IFNULL(CONCAT(CAST(ROUND((SUM(charge)-t3.y_charge)/t3.y_charge*100,3)as CHAR),'%'),'0%') as 'grewRatio',
        IFNULL(CONCAT(CAST(ROUND((SUM(charge)-t4.week_charge)/t4.week_charge*100,3)as CHAR),'%'),'0%') as
        'weekGrewRatio',
        t1.account_id 'accountId',
        t2.auth_name as 'authName',
        IFNULL(ROUND(SUM(play_3s_count) / SUM(aclick) * 100, 2), 0) as 'play3sRatio',
        (select realname FROM sys_user where id = #{userId}) as 'realname',
        IFNULL(t6.balance,0) as 'totalBalance',
        IFNULL(t6.day_budget,0) as 'budget'
        FROM ctop_etl_kuaishou_report_account_hourly t1
        LEFT JOIN ctop_user_allocation t2 ON t1.account_id = t2.account_id
        LEFT JOIN(
        SELECT i1.account_id,SUM(charge) as 'y_charge' FROM ctop_kuaishou_report_hourly_account i1
        LEFT JOIN ctop_user_allocation i2 ON i1.account_id = i2.account_id
        where stat_date = DATE_SUB(#{startTime},INTERVAL 1 DAY)
        AND stat_hour &lt;= #{hours}
        AND i2.user_id = #{userId}
        GROUP BY i1.account_id
        ) t3 ON t2.account_id = t3.account_id
        LEFT JOIN(
        SELECT i1.account_id,SUM(charge) as 'week_charge' FROM ctop_kuaishou_report_hourly_account i1
        LEFT JOIN ctop_user_allocation i2 ON i1.account_id = i2.account_id
        where stat_date = DATE_SUB(#{startTime},INTERVAL 7 DAY)
        AND stat_hour &lt;= #{hours}
        AND i2.user_id = #{userId}
        GROUP BY i1.account_id
        ) t4 ON t2.account_id = t4.account_id
        LEFT JOIN (
        select account_id,balance,day_budget from ctop_kuaishou_account_balance_budget
        where state =1
        ) t6 ON t6.account_id = t2.account_id
        where stat_date = #{startTime}
        AND t2.user_id = #{userId}
        GROUP BY t2.account_id
        <if test="sortCode != '' and sortCode != null ">
            ORDER BY ${sortCode}
        </if>
        ${sortType}
    </select>

    <select id="leaderAccountPageListTotal" resultType="java.lang.Integer">
        select IFNULL(SUM(1),0)
        from (
        SELECT
        t2.account_id
        FROM ctop_etl_kuaishou_report_account_hourly t1
        LEFT JOIN ctop_user_allocation t2 ON t1.account_id = t2.account_id
        where stat_date = #{startTime}
        <if test="userIds != null">
            AND t2.user_id IN
            <foreach item="id" index="index" collection="userIds"
                     open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
        <if test="projectId != null">
            AND t2.project_id = #{projectId}
        </if>
        GROUP BY t2.account_id
        ) t
    </select>

    <select id="leaderAccountPageList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ${filed},
        IFNULL(CONCAT(CAST(ROUND((SUM(charge)-t3.y_charge)/t3.y_charge*100,3)as CHAR),'%'),'0%') as 'grewRatio',
        IFNULL(CONCAT(CAST(ROUND((SUM(charge)-t4.week_charge)/t4.week_charge*100,3)as CHAR),'%'),'0%') as
        'weekGrewRatio',
        t1.account_id 'accountId',
        t2.auth_name as 'authName',
        IFNULL(ROUND(SUM(play_3s_count) / SUM(aclick) * 100, 2), 0) as 'play3sRatio',
        t5.realname,
        IFNULL(t6.balance,0) as 'totalBalance',
        IFNULL(t6.day_budget,0) as 'budget'
        FROM ctop_etl_kuaishou_report_account_hourly t1
        LEFT JOIN ctop_user_allocation t2 ON t1.account_id = t2.account_id
        LEFT JOIN(
        SELECT i1.account_id,SUM(charge) as 'y_charge' FROM ctop_kuaishou_report_hourly_account i1
        LEFT JOIN ctop_user_allocation i2 ON i1.account_id = i2.account_id
        where stat_date = DATE_SUB(#{startTime},INTERVAL 1 DAY)
        AND stat_hour &lt;= #{hours}
        <if test="userIds != null">
            AND i2.user_id IN
            <foreach item="id" index="index" collection="userIds"
                     open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
        <if test="projectId != null">
            AND i2.project_id = #{projectId}
        </if>
        GROUP BY i1.account_id
        ) t3 ON t2.account_id = t3.account_id
        LEFT JOIN(
        SELECT i1.account_id,SUM(charge) as 'week_charge' FROM ctop_kuaishou_report_hourly_account i1
        LEFT JOIN ctop_user_allocation i2 ON i1.account_id = i2.account_id
        where stat_date = DATE_SUB(#{startTime},INTERVAL 7 DAY)
        AND stat_hour &lt;= #{hours}
        <if test="userIds != null">
            AND i2.user_id IN
            <foreach item="id" index="index" collection="userIds"
                     open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
        <if test="projectId != null">
            AND i2.project_id = #{projectId}
        </if>
        GROUP BY i1.account_id
        ) t4 ON t2.account_id = t4.account_id
        LEFT JOIN (
        SELECT
        id,realname
        FROM
        sys_user
        WHERE 1=1
        <if test="userIds != null">
            and id IN
            <foreach item="id" index="index" collection="userIds"
                     open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
        ) t5 ON t2.user_id = t5.id
        LEFT JOIN (
        select account_id,balance,day_budget from ctop_kuaishou_account_balance_budget
        where state =1
        ) t6 ON t6.account_id = t2.account_id
        where stat_date = #{startTime}
        <if test="userIds != null">
            AND t2.user_id IN
            <foreach item="id" index="index" collection="userIds"
                     open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
        <if test="projectId != null">
            AND t2.project_id = #{projectId}
        </if>
        GROUP BY t2.account_id
        <if test="sortCode != '' and sortCode != null ">
            ORDER BY ${sortCode}
        </if>
        ${sortType}
    </select>

    <select id="queryEveryHourlyCharge" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        IFNULL(stat_hour,'') 'time',
        IFNULL(SUM(charge),0) 'value'
        FROM ctop_kuaishou_report_hourly_account t1
        LEFT JOIN ctop_user_allocation t2 ON t1.account_id = t2.account_id
        where stat_date = #{startTime}
        AND t2.user_id IN
        <foreach item="id" index="index" collection="userIds"
                 open="(" separator="," close=")">
            #{id}
        </foreach>
        GROUP BY stat_hour
        ORDER BY stat_hour
    </select>

    <select id="operatingStaffDataListTotal" resultType="java.lang.Integer">
        SELECT IFNULL(SUM(1),0)
        FROM (
        SELECT 1
        FROM ctop_etl_kuaishou_report_account_hourly t1
        LEFT JOIN ctop_user_allocation t2 ON t1.account_id = t2.account_id
        WHERE stat_date = #{startTime}
        AND t2.user_id IN
        <foreach item="id" index="index" collection="userIds"
                 open="(" separator="," close=")">
            #{id}
        </foreach>
        GROUP BY t2.user_id
        ) t
    </select>

    <select id="operatingStaffDataList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        IFNULL(t2.user_id,'') 'userId',
        IFNULL(t9.realname,'') 'realName',
        IFNULL(t3.accountCount,0) 'accountCount',
        IFNULL(SUM(t1.charge),0) 'charge',
        IFNULL(t4.weekCharge,0) 'weekCharge',
        concat(CAST(IFNULL(ROUND((t4.weekCharge-t5.lweekCharge)/t5.lweekCharge*100,2), 0)as char),'%')as 'grew',
        IFNULL(t6.effectiveCount,0) 'effectiveCount',
        IFNULL(t7.campaignCount,0) 'campaignCount',
        IFNULL(t8.unitCount,0) 'unitCount'
        FROM
        ctop_etl_kuaishou_report_account_hourly t1
        LEFT JOIN ctop_user_allocation t2 ON t1.account_id = t2.account_id
        LEFT JOIN(
        SELECT user_id,COUNT(1) 'accountCount' FROM ctop_user_allocation i1
        INNER JOIN ctop_etl_kuaishou_report_account_hourly  i2 ON i1.account_id = i2.account_id
        AND i2.stat_date = #{startTime}
        WHERE user_id IN
        <foreach item="id" index="index" collection="userIds"
                 open="(" separator="," close=")">
            #{id}
        </foreach>
        GROUP BY user_id
        ) t3 ON t2.user_id = t3.user_id
        LEFT JOIN (
        SELECT
        SUM(charge) 'weekCharge',
        tt2.user_id
        FROM
        ctop_etl_kuaishou_report_account_daily tt1
        LEFT JOIN ctop_user_allocation tt2 ON tt1.account_id = tt2.account_id
        WHERE tt1.stat_date &lt;=#{today}
        and tt1.stat_date &gt;=#{weekStartTime}
        AND tt2.user_id IN
        <foreach item="id" index="index" collection="userIds"
                 open="(" separator="," close=")">
            #{id}
        </foreach>
        GROUP BY tt2.user_id
        ) t4 ON t2.user_id = t4.user_id
        LEFT JOIN (
        SELECT
        SUM(charge) 'lweekCharge',
        tt2.user_id
        FROM
        ctop_etl_kuaishou_report_account_daily tt1
        LEFT JOIN ctop_user_allocation tt2 ON tt1.account_id = tt2.account_id
        WHERE tt1.stat_date &lt;=#{lweekEndTime}
        and tt1.stat_date &gt;=#{lweekStartTime}
        AND tt2.user_id IN
        <foreach item="id" index="index" collection="userIds"
                 open="(" separator="," close=")">
            #{id}
        </foreach>
        GROUP BY tt2.user_id
        ) t5 ON t2.user_id = t5.user_id
        LEFT JOIN (
        SELECT
        SUM(effective_count) 'effectiveCount',
        tt2.user_id
        FROM
        ctop_etl_kuaishou_account_material_overview tt1
        LEFT JOIN ctop_user_allocation tt2 ON tt1.account_id = tt2.account_id
        WHERE tt1.state_date = #{yesterday}
        AND tt2.user_id IN
        <foreach item="id" index="index" collection="userIds"
                 open="(" separator="," close=")">
            #{id}
        </foreach>
        GROUP BY tt2.user_id
        ) t6 ON t2.user_id = t6.user_id
        LEFT JOIN (
        SELECT
        SUM(new_count) 'campaignCount',
        tt2.user_id
        FROM
        ctop_etl_kuaishou_newly tt1
        LEFT JOIN ctop_user_allocation tt2 ON tt1.account_id = tt2.account_id
        WHERE tt1.stat_date = #{yesterday}
        AND tt1.new_type = 'campaign'
        AND tt2.user_id IN
        <foreach item="id" index="index" collection="userIds"
                 open="(" separator="," close=")">
            #{id}
        </foreach>
        GROUP BY tt2.user_id
        ) t7 ON t2.user_id = t7.user_id
        LEFT JOIN (
        SELECT
        SUM(new_count) 'unitCount',
        tt2.user_id
        FROM
        ctop_etl_kuaishou_newly tt1
        LEFT JOIN ctop_user_allocation tt2 ON tt1.account_id = tt2.account_id
        WHERE tt1.stat_date = #{yesterday}
        AND tt1.new_type = 'unit'
        AND tt2.user_id IN
        <foreach item="id" index="index" collection="userIds"
                 open="(" separator="," close=")">
            #{id}
        </foreach>
        GROUP BY tt2.user_id
        ) t8 ON t2.user_id = t8.user_id
        LEFT JOIN (
        SELECT
        id,realname
        FROM
        sys_user
        WHERE id IN
        <foreach item="id" index="index" collection="userIds"
                 open="(" separator="," close=")">
            #{id}
        </foreach>
        ) t9 ON t2.user_id = t9.id
        WHERE t1.stat_date = #{startTime}
        AND t2.user_id IN
        <foreach item="id" index="index" collection="userIds"
                 open="(" separator="," close=")">
            #{id}
        </foreach>
        GROUP BY t2.user_id
        <if test="sortCode != '' and sortCode != null ">
            ORDER BY ${sortCode}
        </if>
        ${sortType}
    </select>

    <select id="projectPageList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ${filed},
        IFNULL(CONCAT(CAST(ROUND((SUM(charge)-t5.y_charge)/t5.y_charge*100,3)as CHAR),'%'),'0%') as 'grewRatio',
        IFNULL(CONCAT(CAST(ROUND((SUM(charge)-t6.week_charge)/t6.week_charge*100,3)as CHAR),'%'),'0%') as
        'weekGrewRatio',
        t2.project_id 'projectId',
        t3.project_name as 'projectName',
        IFNULL(ROUND(SUM(play_3s_count) / SUM(aclick) * 100, 2), 0) as 'play3sRatio',
        (
        SELECT count(1) FROM ctop_etl_kuaishou_report_account_hourly i1
        LEFT JOIN ctop_user_allocation i2 ON i1.account_id = i2.account_id
        where project_id = t3.id
        AND i1.stat_date = #{startTime}) as 'accountCount',
        IFNULL(t4.userCount,0) as 'userCount'
        FROM ctop_etl_kuaishou_report_account_hourly t1
        LEFT JOIN ctop_user_allocation t2 ON t1.account_id = t2.account_id
        INNER JOIN ctop_project t3 ON t2.project_id = t3.id
        LEFT JOIN (
        SELECT project_id,COUNT(1) 'userCount'
        FROM (
        SELECT DISTINCT project_id,user_id
        FROM ctop_user_allocation
        ) t
        GROUP BY project_id
        ) t4 ON t4.project_id = t3.id
        LEFT JOIN(
        SELECT i1.account_id,SUM(charge) as 'y_charge' FROM ctop_kuaishou_report_hourly_account i1
        LEFT JOIN ctop_user_allocation i2 ON i1.account_id = i2.account_id
        where stat_date = DATE_SUB(#{startTime},INTERVAL 1 DAY)
        AND stat_hour &lt;= #{hours}
        GROUP BY i1.account_id
        ) t5 ON t2.account_id = t5.account_id
        LEFT JOIN(
        SELECT i1.account_id,SUM(charge) as 'week_charge' FROM ctop_kuaishou_report_hourly_account i1
        LEFT JOIN ctop_user_allocation i2 ON i1.account_id = i2.account_id
        where stat_date = DATE_SUB(#{startTime},INTERVAL 7 DAY)
        AND stat_hour &lt;= #{hours}
        GROUP BY i1.account_id
        ) t6 ON t2.account_id = t6.account_id
        where
        stat_date = #{startTime}
        and
        t3.responsible_id in
        <foreach item="id" index="index" collection="userIds"
                 open="(" separator="," close=")">
            #{id}
        </foreach>
        GROUP BY t3.id
        HAVING SUM(charge)>0
        <if test="sortCode != '' and sortCode != null ">
            ORDER BY ${sortCode}
            ${sortType}
        </if>

    </select>

    <select id="projectPageListTotal" resultType="java.lang.Integer">
        select IFNULL(SUM(1),0) from
        (
        SELECT
        t3.id
        FROM ctop_etl_kuaishou_report_account_hourly t1
        LEFT JOIN ctop_user_allocation t2 ON t1.account_id = t2.account_id
        INNER JOIN ctop_project t3 ON t2.project_id = t3.id
        where stat_date = #{startTime}
        and
        t3.responsible_id in
        <foreach item="id" index="index" collection="userIds"
                 open="(" separator="," close=")">
            #{id}
        </foreach>
        GROUP BY t3.id
        HAVING SUM(charge)>0
        )t
    </select>


</mapper>