<?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.BytedanceAccountReportDailyDwMapper">

    <select id="getYesterdayUploadNewMaterialCount" resultType="Long">
        SELECT IFNULL(sum(material_num), 0)
        FROM application.`new_material_num_in_project`
        WHERE stat_date = #{yesterday}
          AND media_id = 1
          and project_id in (select id from `jeecg-boot`.ctop_project where agent_code in (1, 2))
    </select>


    <select id="getTotalCharge" resultType="com.alibaba.fastjson.JSONObject">
        SELECT hourly.todaycost,
               hourly.yesterdaycost,
               daily.thisWeekDailyCost + hourly.thisWeekTodayCost AS 'thisWeekcost',
               daily.lastWeek1 + hourly.lastWeek2 AS 'lastWeekcost'
        FROM (
                 SELECT IFNULL(SUM(CASE WHEN stat_datetime = #{today} THEN cost END), 0.00) AS 'todaycost',
                        IFNULL(SUM(CASE WHEN stat_datetime = #{yesterday} AND hour &lt;= #{hour} THEN cost END), 0.00) AS 'yesterdaycost',
                        IFNULL(SUM(CASE WHEN stat_datetime = #{today} THEN cost END), 0.00) AS 'thisWeekTodayCost',
                        IFNULL(SUM(CASE WHEN stat_datetime = #{lastEndDate} AND hour &lt;= #{hour} THEN cost END), 0.00) AS 'lastWeek2'
                 FROM application.bytedance_advertiser_report_hourly_dw
                 WHERE stat_datetime IN (#{today}, #{yesterday}, #{lastEndDate})
             ) hourly
                 CROSS JOIN
             (
                 SELECT IFNULL(SUM(CASE WHEN stat_datetime >= #{monday} AND stat_datetime &lt; #{today} THEN cost END), 0.00) AS 'thisWeekDailyCost',
                        CASE
                            WHEN #{lastStartDate} - #{lastEndDate} = 0 THEN 0.00
                            ELSE IFNULL(SUM(CASE
                                                WHEN stat_datetime >= #{lastStartDate}
                                                    AND stat_datetime &lt;= DATE_FORMAT(DATE_SUB(#{lastEndDate}, INTERVAL 1 DAY), '%Y%m%d')
                                                    THEN cost
                                END), 0.00)
                            END AS 'lastWeek1'
                 FROM application.bytedance_advertiser_report_daily_dw
                 WHERE stat_datetime >= #{lastStartDate}
                   AND stat_datetime &lt; #{today}
             ) daily
    </select>

    <select id="getHourCost" resultType="com.alibaba.fastjson.JSONObject">
        SELECT hour as 'time', IFNULL(SUM(cost), 0.00) as 'cost'
        FROM application.bytedance_advertiser_report_hourly_dw
        WHERE stat_datetime = #{startTime}

        <if test="projectId != null and projectId != ''">
            AND project_id = #{projectId}
        </if>
        GROUP BY hour
        ORDER BY hour
    </select>

    <select id="getDataCost" resultType="com.alibaba.fastjson.JSONObject">
        SELECT DATE_FORMAT(stat_datetime, '%Y-%m-%d') as 'time', IFNULL(sum(cost), 0.00) as 'cost'
        FROM application.bytedance_advertiser_report_hourly_dw
        WHERE stat_datetime &lt;= #{endTime}
        AND stat_datetime >= #{startTime}

        <if test="projectId != null and projectId != ''">
            AND project_id = #{projectId}
        </if>
        GROUP BY stat_datetime
        ORDER BY stat_datetime
    </select>


    <select id="getProjectToDayReportTotal" resultType="Long">
        SELECT IFNULL(sum(1), 0)
        FROM (
        SELECT 1
        FROM application.bytedance_advertiser_report_hourly_dw
        WHERE stat_datetime = #{startTime}

        <if test="projectId != null and projectId != ''">
            AND project_id = #{projectId}
        </if>
        GROUP BY project_id
        ) t
    </select>


    <select id="getProjectToDayReport" resultType="com.alibaba.fastjson.JSONObject">
        SELECT * FROM (
        SELECT
        t1.project_id as 'projectId',
        t3.project_name AS 'projectName',
        t3.project_name AS 'productName',
        t1.advertiser_name AS 'companyName',
        t5.realname AS 'saler',
        t4.realname AS 'operater',
        sum( cost ) AS 'cost',
        SUM( `show` ) AS 'aclick',
        SUM( click ) AS 'bclick',
        IFNULL( t6.num, 0 ) AS 'num',
        IFNULL(concat(round((sum(cost) - t7.costs) / t7.costs * 100, 2), '%'), '-') as 'rio',
        IFNULL(t8.unitNum,0) as 'unitNum',
        IFNULL(t8.projectNum,0) as 'projectNum',
        '-' AS 'ssAd',
        '-' AS 'sxhAd',
        '-' AS 'lmAd',
        '-' AS 'xxlAd',
        '-' AS 'jlAd',
        '-' AS 'kpAd',
        IFNULL(t9.accountNum,0) as 'accountNum',
        IFNULL(t9.operaterNum,0) as 'operaterNum',
        IFNULL(t13.material_upload_media_num,0) as 'materialUploadMediaNum'
        FROM application.bytedance_advertiser_report_hourly_dw t1
        LEFT JOIN `jeecg-boot`.ctop_project t3 ON t1.project_id = t3.id
        LEFT JOIN `jeecg-boot`.sys_user t4 ON t3.responsible_id = t4.id
        LEFT JOIN `jeecg-boot`.sys_user t5 ON t3.sale_id = t5.id
        LEFT JOIN (
        SELECT project_id,sum(material_num) as 'num' FROM application.new_material_num_in_project
        WHERE stat_date = DATE_FORMAT(DATE_SUB(#{startTime}, INTERVAL 1 DAY ), '%Y%m%d')
        AND media_id = 1
        <if test="projectId != null and projectId != ''">
            AND project_id = #{projectId}
        </if>
        <if test="projectId == null">
            GROUP BY project_id
        </if>
        ) t6 ON t1.project_id = t6.project_id
        LEFT JOIN (
        SELECT project_id, SUM(cost) as 'costs'
        FROM application.bytedance_advertiser_report_hourly_dw
        WHERE stat_datetime = DATE_FORMAT(DATE_SUB(#{startTime}, INTERVAL 1 DAY ), '%Y%m%d')
        AND hour &lt;= #{hour}

        <if test="projectId != null and projectId != ''">
            AND project_id = #{projectId}
        </if>
        <if test="projectId == null">
            GROUP BY project_id
        </if>
        ) t7 ON t1.project_id = t7.project_id
        LEFT JOIN (
        SELECT project_id,
               IFNULL(SUM(new_ad_num),0)+IFNULL(SUM(promotion_num),0) as 'unitNum',
               SUM(new_cpd_project_num) as 'projectNum'
        FROM
        application.app_bytedance_account_base_info_d
        WHERE stat_date =DATE_FORMAT(DATE_SUB(#{startTime}, INTERVAL 1 DAY ), '%Y%m%d')

        GROUP BY project_id
        ) t8 ON t1.project_id = t8.project_id
        LEFT JOIN(
        SELECT project_id,count(account_id)as 'accountNum',count(DISTINCT user_id)as 'operaterNum' FROM (
        SELECT project_id,account_id,user_id FROM application.bytedance_advertiser_report_daily_dw
        WHERE stat_datetime =DATE_FORMAT(DATE_SUB(#{startTime}, INTERVAL 1 DAY ), '%Y%m%d')
        GROUP BY project_id,account_id
        HAVING sum(cost) > 0
        )t
        GROUP BY project_id
        ) t9 ON t1.project_id = t9.project_id
        LEFT JOIN(SELECT project_id , SUM(material_num) as 'material_upload_media_num' FROM
        application.new_material_num_in_project
        where stat_date = #{startTime}
        and media_id=1
        GROUP BY project_id
        ) t13 ON t1.project_id = t13.project_id
        WHERE stat_datetime = #{startTime}

        <if test="projectId != null and projectId != ''">
            AND t1.project_id = #{projectId}
        </if>
        GROUP BY t1.project_id
        HAVING sum(cost) > 0
        ) t
        <if test="category == 1">
            WHERE rio &lt;= -0.1 AND rio !='-'
        </if>
        ORDER BY ${sortCode} ${order}
    </select>


    <select id="getProjectMoreDaysReportTotal" resultType="Long">
        SELECT IFNULL(sum(1), 0)
        FROM (
        SELECT 1
        FROM application.bytedance_advertiser_report_daily_dw
        WHERE stat_datetime >= #{startTime}
        AND stat_datetime &lt;= #{endTime}

        <if test="projectId != null and projectId != ''">
            AND project_id = #{projectId}
        </if>
        GROUP BY project_id
        ) t
    </select>

    <select id="getProjectMoreDaysReport" resultType="com.alibaba.fastjson.JSONObject">
        SELECT * FROM (
        SELECT
        t1.project_id as 'projectId',
        t3.project_name AS 'projectName',
        t3.project_name AS 'productName',
        t1.advertiser_name AS 'companyName',
        t5.realname AS 'saler',
        t4.realname AS 'operater',
        sum( cost ) AS 'cost',
        SUM( `show` ) AS 'aclick',
        SUM( click ) AS 'bclick',
        IFNULL(t6.num, 0 ) AS 'num',
        IFNULL(concat(round((sum(cost) - t7.costs) / t7.costs * 100, 2), '%'), '-') as 'rio',
        IFNULL(t8.unitNum,0) as 'unitNum',
        IFNULL(t8.projectNum,0) as 'projectNum',
        '-' AS 'ssAd',
        '-' AS 'sxhAd',
        '-' AS 'lmAd',
        '-' AS 'xxlAd',
        '-' AS 'jlAd',
        '-' AS 'kpAd',
        IFNULL(t9.accountNum,0) as 'accountNum',
        IFNULL(t9.operaterNum,0) as 'operaterNum',
        IFNULL(t13.material_upload_media_num,0) as 'materialUploadMediaNum'
        FROM application.bytedance_advertiser_report_daily_dw t1

        LEFT JOIN `jeecg-boot`.ctop_project t3 ON t1.project_id = t3.id
        LEFT JOIN `jeecg-boot`.sys_user t4 ON t3.responsible_id = t4.id
        LEFT JOIN `jeecg-boot`.sys_user t5 ON t3.sale_id = t5.id
        LEFT JOIN (
        SELECT project_id,sum(material_num) as 'num' FROM application.new_material_num_in_project
        WHERE stat_date >= #{startTime}
        AND stat_date &lt;= #{endTime}
        AND media_id = 1
        <if test="projectId != null and projectId != ''">
            AND project_id = #{projectId}
        </if>
        <if test="projectId == null">
            GROUP BY project_id
        </if>
        ) t6 ON t1.project_id = t6.project_id
        LEFT JOIN (
        SELECT project_id, SUM(cost) as 'costs'
        FROM application.bytedance_advertiser_report_hourly_dw
        WHERE stat_datetime >= #{lastStart}
        AND stat_datetime &lt;= #{lastEnd}

        <if test="projectId != null and projectId != ''">
            AND project_id = #{projectId}
        </if>
        <if test="projectId == null">
            GROUP BY project_id
        </if>
        ) t7 ON t1.project_id = t7.project_id
        LEFT JOIN (
        SELECT
               project_id,
               IFNULL(SUM(new_ad_num),0)+IFNULL(SUM(promotion_num),0) as 'unitNum',
               SUM(new_cpd_project_num) as 'projectNum'
        FROM
        application.app_bytedance_account_base_info_d
        WHERE stat_date >= #{startTime}
        AND stat_date &lt;= #{endTime}

        GROUP BY project_id
        ) t8 ON t1.project_id = t8.project_id
        LEFT JOIN(
        SELECT project_id,count(account_id)as 'accountNum',count(DISTINCT user_id)as 'operaterNum' FROM (
        SELECT project_id,account_id,user_id FROM application.bytedance_advertiser_report_daily_dw
        WHERE stat_datetime >= #{startTime}
        AND stat_datetime &lt;= #{endTime}
        GROUP BY project_id,account_id
        HAVING sum(cost) > 0
        )t
        GROUP BY project_id
        ) t9 ON t1.project_id = t9.project_id
        LEFT JOIN(SELECT project_id , SUM(material_num) as 'material_upload_media_num' FROM
        application.new_material_num_in_project
        where stat_date >= #{startTime}
        AND stat_date &lt;= #{endTime}
        and media_id=1
        GROUP BY project_id
        ) t13 ON t1.project_id = t13.project_id
        WHERE stat_datetime >= #{startTime}
        AND stat_datetime &lt;= #{endTime}

        <if test="projectId != null and projectId != ''">
            AND t1.project_id = #{projectId}
        </if>
        GROUP BY t1.project_id
        HAVING sum(cost) > 0
        ) t
        ORDER BY ${sortCode} ${order}
    </select>


    <select id="getProjectNoByDays" resultType="com.alibaba.fastjson.JSONObject">
        SELECT stat_datetime as 'time',COUNT(1) as 'count'
        FROM (SELECT stat_datetime, project_id
              FROM application.bytedance_advertiser_report_daily_dw
              WHERE stat_datetime >= #{startTime}
                AND stat_datetime &lt;= #{endTime}
              GROUP BY stat_datetime, project_id
              HAVING sum(cost) >= 10
             ) t
        GROUP BY stat_datetime
        ORDER BY stat_datetime
    </select>

    <select id="getAdvertiserNoByDays" resultType="com.alibaba.fastjson.JSONObject">
        SELECT stat_datetime as 'time',COUNT(1) as 'count'
        FROM (SELECT stat_datetime, advertiser_id
              FROM application.bytedance_advertiser_report_daily_dw
              WHERE stat_datetime >= #{startTime}
                AND stat_datetime &lt;= #{endTime}
              GROUP BY stat_datetime, advertiser_id
              HAVING sum(cost) >= 10
             ) t
        GROUP BY stat_datetime
        ORDER BY stat_datetime
    </select>

    <select id="getUnitNoByHours" resultType="com.alibaba.fastjson.JSONObject">
        SELECT `hour`as 'time'  , SUM(material_upload_media_num) as 'count'
        FROM application.today_material_upload_num
        WHERE type_id = 1
          and stat_date = #{startTime}
          AND dimension in (
            SELECT id
            FROM `jeecg-boot`.ctop_project
            WHERE media_id = 1
        )
        GROUP BY hour
        ORDER BY hour
    </select>

    <select id="getUnitNoByDays" resultType="com.alibaba.fastjson.JSONObject">
        SELECT stat_date as 'time',sum(material_num) as 'count'
        FROM application.new_material_num_in_project
        WHERE stat_date >= #{startTime}
          AND stat_date &lt;= #{endTime}
          AND media_id = 1
        GROUP BY stat_date
        ORDER BY stat_date
    </select>


    <select id="getToutiaoOperateInfoHourly" resultType="com.alibaba.fastjson.JSONObject">
        SELECT COUNT(DISTINCT t.account_id) 'accountNum',
        COUNT(DISTINCT t.project_id) 'projectNums',
        SUM(t.cost) 'totalCost',
        t.user_id as 'userId',
        t2.realname as 'userName',
        IFNULL(t4.projectNum,0) as 'projectNum',
        CONCAT(CAST(IFNULL(ROUND((SUM(t.cost) - t3.lastCost) / t3.lastCost * 100, 2), 0) AS CHAR), '%') AS 'rate',
        '-' as 'newUnitNum',
        '-' as 'newCampaignNum'
        FROM `application`.bytedance_advertiser_report_hourly_dw t
        LEFT JOIN `jeecg-boot`.ctop_user_allocation t1 ON t.account_id = t1.account_id
        LEFT JOIN `jeecg-boot`.sys_user t2 ON t.user_id = t2.id
        LEFT JOIN (
        SELECT SUM(d.cost) as 'lastCost', d.user_id
        FROM `application`.bytedance_advertiser_report_hourly_dw d
        LEFT JOIN `jeecg-boot`.ctop_user_allocation u ON d.account_id = u.account_id
        LEFT JOIN `jeecg-boot`.sys_user suser ON d.user_id = suser.id
        WHERE d.cost > 0
        AND hour &lt;= #{hour}
        AND d.stat_datetime >= #{lastTimeStart}
        AND d.stat_datetime &lt;= #{lastTimeEnd}
        <if test='list!=null and list.size() > 0'>
            AND d.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY d.user_id
        ) t3 ON t.user_id = t3.user_id
        left join (
        SELECT user_id, SUM(new_cpd_project_num) as 'projectNum' FROM
        application.app_bytedance_account_base_info_d
        WHERE stat_date = #{startDate}
        GROUP BY user_id
        ) t4 ON t4.user_id = t.user_id
        WHERE t.cost > 0
        AND hour &lt;= #{hour}
        AND t.stat_datetime >= #{startDate}
        AND t.stat_datetime &lt;= #{endDate}
        <if test='list!=null and list.size()>0'>
            AND t.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY t.user_id
        ORDER BY SUM(t.cost) DESC
    </select>

    <select id="getToutiaoOperateInfo" resultType="com.alibaba.fastjson.JSONObject">
        SELECT COUNT(DISTINCT t.account_id) 'accountNum',
        COUNT(DISTINCT t.project_id) 'projectNums',
        SUM(t.cost) 'totalCost',
        t.user_id as 'userId',
        t2.realname as 'userName',
        CONCAT(CAST(IFNULL(ROUND((SUM(t.cost) - t3.lastCost) / t3.lastCost * 100, 2), 0) AS CHAR), '%') AS 'rate',
        IFNULL(t4.newUnitNum,'-') as 'newUnitNum',
        IFNULL(t4.newCampaignNum,'-') as 'newCampaignNum',
        IFNULL(t4.projectNum,0) as 'projectNum'
        FROM `application`.bytedance_advertiser_report_daily_dw t
        LEFT JOIN `jeecg-boot`.ctop_user_allocation t1 ON t.account_id = t1.account_id
        LEFT JOIN `jeecg-boot`.sys_user t2 ON t.user_id = t2.id
        LEFT JOIN (
        SELECT SUM(d.cost) as 'lastCost', d.user_id
        FROM `application`.bytedance_advertiser_report_daily_dw d
        LEFT JOIN `jeecg-boot`.ctop_user_allocation u ON d.account_id = u.account_id
        LEFT JOIN `jeecg-boot`.sys_user suser ON d.user_id = suser.id
        WHERE d.cost > 0
        AND d.stat_datetime >= #{lastTimeStart}
        AND d.stat_datetime &lt;= #{lastTimeEnd}
        <if test='list!=null and list.size() > 0'>
            AND d.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY d.user_id
        ) t3 ON t.user_id = t3.user_id
        LEFT JOIN (
        SELECT user_id,
        IFNULL(SUM(new_ad_num),0) + IFNULL(SUM(trust_unit_num),0) + IFNULL(SUM(promotion_num),0)  as 'newUnitNum',
        IFNULL(SUM(new_campaign_num),0) as 'newCampaignNum',
        SUM(new_cpd_project_num) as 'projectNum'
        FROM application.app_bytedance_account_base_info_d
        WHERE stat_date >= #{startDate}
        AND stat_date &lt;= #{endDate}
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY user_id
        ) t4 ON t.user_id = t4.user_id
        WHERE t.cost > 0
        AND t.stat_datetime >= #{startDate}
        AND t.stat_datetime &lt;= #{endDate}
        <if test='list!=null and list.size()>0'>
            AND t.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY t.user_id
        ORDER BY SUM(t.cost) DESC
    </select>


    <select id="getToutiaoOperateGroupInfoHourly" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        IFNULL( count( t1.user_id ), 0 ) AS 'operationNum',
        IFNULL( SUM( t1.accountNum ), 0 ) AS 'accountNum',
        IFNULL( SUM( t1.projectNum ), 0 ) AS 'projectNums',
        IFNULL( SUM( t1.totalCost ), 0 ) AS 'totalCost',
        t3.lastCost,
        CONCAT( CAST( IFNULL( ROUND(( SUM( t1.totalCost ) - t3.lastCost )/ t3.lastCost * 100, 2 ), 0 ) AS CHAR ), '%' )
        AS 'yearOnYearCost',
        t4.unitNum,
        t4.projectNum
        FROM
        (
        SELECT
        COUNT( DISTINCT d.account_id ) 'accountNum',
        COUNT( DISTINCT d.project_id ) 'projectNum',
        SUM( d.cost ) 'totalCost' ,
        d.user_id
        FROM
        `application`.bytedance_advertiser_report_hourly_dw d
        LEFT JOIN `jeecg-boot`.ctop_user_allocation u ON d.account_id = u.account_id
        LEFT JOIN `jeecg-boot`.sys_user suser ON d.user_id = suser.id
        WHERE
        d.cost > 0
        AND d.hour &lt;= #{hour}
        AND d.stat_datetime >= #{startDate}
        AND d.stat_datetime &lt;=#{endDate}
        <if test='list!=null and list.size()>0'>
            AND d.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY
        d.user_id
        ) t1
        LEFT JOIN (
        SELECT
        SUM(d.cost) 'lastCost'
        FROM
        `application`.bytedance_advertiser_report_hourly_dw d
        LEFT JOIN `jeecg-boot`.ctop_user_allocation u ON d.account_id = u.account_id
        LEFT JOIN `jeecg-boot`.sys_user suser ON d.user_id = suser.id
        WHERE
        d.cost > 0
        AND d.hour &lt;= #{hour}
        AND d.stat_datetime >= #{lastTimeStart}
        AND d.stat_datetime &lt;= #{lastTimeEnd}
        <if test='list!=null and list.size()>0'>
            AND d.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        ) t3 ON 1 = 1
        LEFT JOIN (
        SELECT IFNULL(SUM(new_ad_num),0) +IFNULL(SUM(promotion_num),0)  as 'unitNum',
        IFNULL(SUM(new_cpd_project_num),0) as 'projectNum'
        FROM application.app_bytedance_account_base_info_d
        WHERE stat_date >= #{startDate}
        AND stat_date &lt;= #{endDate}
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        ) t4 ON 1=1
    </select>

    <select id="getToutiaoOperateGroupInfo" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        IFNULL( count( t1.user_id ), 0 ) AS 'operationNum',
        IFNULL( SUM( t1.accountNum ), 0 ) AS 'accountNum',
        IFNULL( SUM( t1.projectNum ), 0 ) AS 'projectNums',
        IFNULL( SUM( t1.totalCost ), 0 ) AS 'totalCost',
        t3.lastCost,
        CONCAT( CAST( IFNULL( ROUND(( SUM( t1.totalCost ) - t3.lastCost )/ t3.lastCost * 100, 2 ), 0 ) AS CHAR ), '%' )
        AS 'yearOnYearCost',
        t4.unitNum,
        t4.projectNum
        FROM
        (
        SELECT
        COUNT( DISTINCT d.account_id ) 'accountNum',
        COUNT( DISTINCT d.project_id ) 'projectNum',
        SUM( d.cost ) 'totalCost' ,
        d.user_id
        FROM
        `application`.bytedance_advertiser_report_daily_dw d
        LEFT JOIN `jeecg-boot`.ctop_user_allocation u ON d.account_id = u.account_id
        LEFT JOIN `jeecg-boot`.sys_user suser ON d.user_id = suser.id
        WHERE
        d.cost > 0
        AND d.stat_datetime >= #{startDate}
        AND d.stat_datetime &lt;=#{endDate}
        <if test='list!=null and list.size()>0'>
            AND d.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        GROUP BY
        d.user_id
        ) t1
        LEFT JOIN (
        SELECT
        SUM(d.cost) 'lastCost'
        FROM
        `application`.bytedance_advertiser_report_daily_dw d
        LEFT JOIN `jeecg-boot`.ctop_user_allocation u ON d.account_id = u.account_id
        LEFT JOIN `jeecg-boot`.sys_user suser ON d.user_id = suser.id
        WHERE
        d.cost > 0
        AND d.stat_datetime >= #{lastTimeStart}
        AND d.stat_datetime &lt;= #{lastTimeEnd}
        <if test='list!=null and list.size()>0'>
            AND d.user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        ) t3 ON 1 = 1
        LEFT JOIN (
        SELECT IFNULL(SUM(new_ad_num),0) + IFNULL(SUM(promotion_num),0)  as 'unitNum',
        IFNULL(SUM(new_cpd_project_num),0) as 'projectNum'
        FROM application.app_bytedance_account_base_info_d
        WHERE stat_date >= #{startDate}
        AND stat_date &lt;= #{endDate}
        <if test='list!=null and list.size()>0'>
            AND user_id IN
            <foreach collection="list" item="item" separator=","
                     open="(" close=")">
                #{item}
            </foreach>
        </if>
        ) t4 ON 1=1
    </select>

    <sql id="toutiaoOperateGroupRelationTableHourly">
        SELECT t.managerId, t.userId
        FROM (
                 SELECT m.manage_id AS managerId, u.id AS userId
                 FROM `jeecg-boot`.sys_user u
                          LEFT JOIN `application`.app_user_manage m ON u.id = m.user_id
                          LEFT JOIN `jeecg-boot`.sys_user_role ur ON u.id = ur.user_id
                          LEFT JOIN `jeecg-boot`.sys_role r ON ur.role_id = r.id
                          INNER JOIN (
                     SELECT DISTINCT report.user_id
                     FROM application.bytedance_advertiser_report_hourly_dw report
                     WHERE report.stat_datetime >= #{startDate}
                       AND report.stat_datetime &lt;= #{endDate}
                       AND report.cost > 0
                 ) active_users ON active_users.user_id = u.id
                 WHERE m.manage_id IN
                       <foreach collection="managerIds" item="item" separator="," open="(" close=")">
                           #{item}
                       </foreach>
                   AND r.role_name LIKE '%运营%'
                   AND u.STATUS = 1
                   AND u.del_flag = 0
                 GROUP BY m.manage_id, u.id
                 UNION ALL
                 SELECT report.user_id AS managerId, report.user_id AS userId
                 FROM application.bytedance_advertiser_report_hourly_dw report
                 WHERE report.user_id IN
                       <foreach collection="managerIds" item="item" separator="," open="(" close=")">
                           #{item}
                       </foreach>
                   AND report.stat_datetime >= #{startDate}
                   AND report.stat_datetime &lt;= #{endDate}
                   AND report.cost > 0
                 GROUP BY report.user_id
             ) t
        GROUP BY t.managerId, t.userId
    </sql>

    <sql id="toutiaoOperateGroupRelationTableDaily">
        SELECT t.managerId, t.userId
        FROM (
                 SELECT m.manage_id AS managerId, u.id AS userId
                 FROM `jeecg-boot`.sys_user u
                          LEFT JOIN `application`.app_user_manage m ON u.id = m.user_id
                          LEFT JOIN `jeecg-boot`.sys_user_role ur ON u.id = ur.user_id
                          LEFT JOIN `jeecg-boot`.sys_role r ON ur.role_id = r.id
                          INNER JOIN (
                     SELECT DISTINCT report.user_id
                     FROM application.bytedance_advertiser_report_daily_dw report
                     WHERE report.stat_datetime >= #{startDate}
                       AND report.stat_datetime &lt;= #{endDate}
                       AND report.cost > 0
                 ) active_users ON active_users.user_id = u.id
                 WHERE m.manage_id IN
                       <foreach collection="managerIds" item="item" separator="," open="(" close=")">
                           #{item}
                       </foreach>
                   AND r.role_name LIKE '%运营%'
                   AND u.STATUS = 1
                   AND u.del_flag = 0
                 GROUP BY m.manage_id, u.id
                 UNION ALL
                 SELECT report.user_id AS managerId, report.user_id AS userId
                 FROM application.bytedance_advertiser_report_daily_dw report
                 WHERE report.user_id IN
                       <foreach collection="managerIds" item="item" separator="," open="(" close=")">
                           #{item}
                       </foreach>
                   AND report.stat_datetime >= #{startDate}
                   AND report.stat_datetime &lt;= #{endDate}
                   AND report.cost > 0
                 GROUP BY report.user_id
             ) t
        GROUP BY t.managerId, t.userId
    </sql>

    <sql id="toutiaoOperateGroupUsersTableHourly">
        SELECT DISTINCT rel.userId
        FROM (
                 <include refid="toutiaoOperateGroupRelationTableHourly"/>
             ) rel
    </sql>

    <sql id="toutiaoOperateGroupUsersTableDaily">
        SELECT DISTINCT rel.userId
        FROM (
                 <include refid="toutiaoOperateGroupRelationTableDaily"/>
             ) rel
    </sql>

    <select id="getOperateGroupCurrentHourlyByUsers" resultType="com.alibaba.fastjson.JSONObject">
        SELECT d.user_id AS 'userId',
               COUNT(DISTINCT d.account_id) AS 'accountNum',
               COUNT(DISTINCT d.project_id) AS 'projectNum',
               SUM(d.cost) AS 'totalCost'
        FROM application.bytedance_advertiser_report_hourly_dw d
        WHERE d.cost > 0
          AND d.hour &lt;= #{hour}
          AND d.stat_datetime >= #{startDate}
          AND d.stat_datetime &lt;= #{endDate}
          AND d.user_id IN
          <foreach collection="userIds" item="item" separator="," open="(" close=")">
              #{item}
          </foreach>
        GROUP BY d.user_id
    </select>

    <select id="getOperateGroupCurrentByUsers" resultType="com.alibaba.fastjson.JSONObject">
        SELECT d.user_id AS 'userId',
               COUNT(DISTINCT d.account_id) AS 'accountNum',
               COUNT(DISTINCT d.project_id) AS 'projectNum',
               SUM(d.cost) AS 'totalCost'
        FROM application.bytedance_advertiser_report_daily_dw d
        WHERE d.cost > 0
          AND d.stat_datetime >= #{startDate}
          AND d.stat_datetime &lt;= #{endDate}
          AND d.user_id IN
          <foreach collection="userIds" item="item" separator="," open="(" close=")">
              #{item}
          </foreach>
        GROUP BY d.user_id
    </select>

    <select id="getOperateGroupPreviousHourlyByUsers" resultType="com.alibaba.fastjson.JSONObject">
        SELECT d.user_id AS 'userId',
               SUM(d.cost) AS 'lastCost'
        FROM application.bytedance_advertiser_report_hourly_dw d
        WHERE d.cost > 0
          AND d.hour &lt;= #{hour}
          AND d.stat_datetime >= #{startDate}
          AND d.stat_datetime &lt;= #{endDate}
          AND d.user_id IN
          <foreach collection="userIds" item="item" separator="," open="(" close=")">
              #{item}
          </foreach>
        GROUP BY d.user_id
    </select>

    <select id="getOperateGroupPreviousByUsers" resultType="com.alibaba.fastjson.JSONObject">
        SELECT d.user_id AS 'userId',
               SUM(d.cost) AS 'lastCost'
        FROM application.bytedance_advertiser_report_daily_dw d
        WHERE d.cost > 0
          AND d.stat_datetime >= #{startDate}
          AND d.stat_datetime &lt;= #{endDate}
          AND d.user_id IN
          <foreach collection="userIds" item="item" separator="," open="(" close=")">
              #{item}
          </foreach>
        GROUP BY d.user_id
    </select>

    <select id="getOperateGroupBaseByUsers" resultType="com.alibaba.fastjson.JSONObject">
        SELECT base.user_id AS 'userId',
               IFNULL(SUM(base.new_ad_num), 0) + IFNULL(SUM(base.promotion_num), 0) AS 'unitNum',
               IFNULL(SUM(base.new_cpd_project_num), 0) AS 'projectNum'
        FROM application.app_bytedance_account_base_info_d base
        WHERE base.stat_date >= #{startDate}
          AND base.stat_date &lt;= #{endDate}
          AND base.user_id IN
          <foreach collection="userIds" item="item" separator="," open="(" close=")">
              #{item}
          </foreach>
        GROUP BY base.user_id
    </select>

    <select id="getToutiaoOperateGroupInfoHourlyBatch" resultType="com.alibaba.fastjson.JSONObject">
        SELECT rel.managerId AS 'userId',
               COUNT(DISTINCT rel.userId) AS 'operationNum',
               IFNULL(SUM(cur.accountNum), 0) AS 'accountNum',
               IFNULL(SUM(cur.projectNum), 0) AS 'projectNums',
               IFNULL(SUM(cur.totalCost), 0) AS 'totalCost',
               IFNULL(SUM(prev.lastCost), 0) AS 'lastCost',
               CASE
                   WHEN IFNULL(SUM(prev.lastCost), 0) = 0 THEN '0%'
                   ELSE CONCAT(CAST(ROUND((IFNULL(SUM(cur.totalCost), 0) - SUM(prev.lastCost)) / SUM(prev.lastCost) * 100, 2) AS CHAR), '%')
                   END AS 'yearOnYearCost',
               IFNULL(SUM(base.unitNum), 0) AS 'unitNum',
               IFNULL(SUM(base.projectNum), 0) AS 'projectNum'
        FROM (
                 <include refid="toutiaoOperateGroupRelationTableHourly"/>
             ) rel
                 LEFT JOIN (
            SELECT d.user_id,
                   COUNT(DISTINCT d.account_id) AS 'accountNum',
                   COUNT(DISTINCT d.project_id) AS 'projectNum',
                   SUM(d.cost) AS 'totalCost'
            FROM `application`.bytedance_advertiser_report_hourly_dw d
                     INNER JOIN (
                <include refid="toutiaoOperateGroupUsersTableHourly"/>
            ) users ON users.userId = d.user_id
            WHERE d.cost > 0
              AND d.hour &lt;= #{hour}
              AND d.stat_datetime >= #{startDate}
              AND d.stat_datetime &lt;= #{endDate}
            GROUP BY d.user_id
        ) cur ON rel.userId = cur.user_id
                 LEFT JOIN (
            SELECT d.user_id,
                   SUM(d.cost) AS 'lastCost'
            FROM `application`.bytedance_advertiser_report_hourly_dw d
                     INNER JOIN (
                <include refid="toutiaoOperateGroupUsersTableHourly"/>
            ) users ON users.userId = d.user_id
            WHERE d.cost > 0
              AND d.hour &lt;= #{hour}
              AND d.stat_datetime >= #{lastTimeStart}
              AND d.stat_datetime &lt;= #{lastTimeEnd}
            GROUP BY d.user_id
        ) prev ON rel.userId = prev.user_id
                 LEFT JOIN (
            SELECT base.user_id,
                   IFNULL(SUM(base.new_ad_num), 0) + IFNULL(SUM(base.promotion_num), 0) AS 'unitNum',
                   IFNULL(SUM(base.new_cpd_project_num), 0) AS 'projectNum'
            FROM application.app_bytedance_account_base_info_d base
                     INNER JOIN (
                <include refid="toutiaoOperateGroupUsersTableHourly"/>
            ) users ON users.userId = base.user_id
            WHERE base.stat_date >= #{startDate}
              AND base.stat_date &lt;= #{endDate}
            GROUP BY base.user_id
        ) base ON rel.userId = base.user_id
        GROUP BY rel.managerId
        ORDER BY IFNULL(SUM(cur.totalCost), 0) DESC
    </select>

    <select id="getToutiaoOperateGroupInfoBatch" resultType="com.alibaba.fastjson.JSONObject">
        SELECT rel.managerId AS 'userId',
               COUNT(DISTINCT rel.userId) AS 'operationNum',
               IFNULL(SUM(cur.accountNum), 0) AS 'accountNum',
               IFNULL(SUM(cur.projectNum), 0) AS 'projectNums',
               IFNULL(SUM(cur.totalCost), 0) AS 'totalCost',
               IFNULL(SUM(prev.lastCost), 0) AS 'lastCost',
               CASE
                   WHEN IFNULL(SUM(prev.lastCost), 0) = 0 THEN '0%'
                   ELSE CONCAT(CAST(ROUND((IFNULL(SUM(cur.totalCost), 0) - SUM(prev.lastCost)) / SUM(prev.lastCost) * 100, 2) AS CHAR), '%')
                   END AS 'yearOnYearCost',
               IFNULL(SUM(base.unitNum), 0) AS 'unitNum',
               IFNULL(SUM(base.projectNum), 0) AS 'projectNum'
        FROM (
                 <include refid="toutiaoOperateGroupRelationTableDaily"/>
             ) rel
                 LEFT JOIN (
            SELECT d.user_id,
                   COUNT(DISTINCT d.account_id) AS 'accountNum',
                   COUNT(DISTINCT d.project_id) AS 'projectNum',
                   SUM(d.cost) AS 'totalCost'
            FROM `application`.bytedance_advertiser_report_daily_dw d
                     INNER JOIN (
                <include refid="toutiaoOperateGroupUsersTableDaily"/>
            ) users ON users.userId = d.user_id
            WHERE d.cost > 0
              AND d.stat_datetime >= #{startDate}
              AND d.stat_datetime &lt;= #{endDate}
            GROUP BY d.user_id
        ) cur ON rel.userId = cur.user_id
                 LEFT JOIN (
            SELECT d.user_id,
                   SUM(d.cost) AS 'lastCost'
            FROM `application`.bytedance_advertiser_report_daily_dw d
                     INNER JOIN (
                <include refid="toutiaoOperateGroupUsersTableDaily"/>
            ) users ON users.userId = d.user_id
            WHERE d.cost > 0
              AND d.stat_datetime >= #{lastTimeStart}
              AND d.stat_datetime &lt;= #{lastTimeEnd}
            GROUP BY d.user_id
        ) prev ON rel.userId = prev.user_id
                 LEFT JOIN (
            SELECT base.user_id,
                   IFNULL(SUM(base.new_ad_num), 0) + IFNULL(SUM(base.promotion_num), 0) AS 'unitNum',
                   IFNULL(SUM(base.new_cpd_project_num), 0) AS 'projectNum'
            FROM application.app_bytedance_account_base_info_d base
                     INNER JOIN (
                <include refid="toutiaoOperateGroupUsersTableDaily"/>
            ) users ON users.userId = base.user_id
            WHERE base.stat_date >= #{startDate}
              AND base.stat_date &lt;= #{endDate}
            GROUP BY base.user_id
        ) base ON rel.userId = base.user_id
        GROUP BY rel.managerId
        ORDER BY IFNULL(SUM(cur.totalCost), 0) DESC
    </select>

</mapper>
