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

    <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 report.stat_date = #{today} THEN report.cost END), 0.00) AS 'todayCost',
                        IFNULL(SUM(CASE WHEN report.stat_date = #{yesterday} AND report.stat_hour &lt;= #{hour} THEN report.cost END), 0.00) AS 'yesterdayCost',
                        IFNULL(SUM(CASE WHEN report.stat_date = #{today} THEN report.cost END), 0.00) AS 'thisWeekTodayCost',
                        IFNULL(SUM(CASE WHEN report.stat_date = #{lastEndDate} AND report.stat_hour &lt;= #{hour} THEN report.cost END), 0.00) AS 'lastWeek2'
                 FROM application.adq_advertiser_report_hourly report
                 WHERE report.stat_date IN (#{today}, #{yesterday}, #{lastEndDate})
             ) hourly
                 CROSS JOIN (
            SELECT IFNULL(SUM(CASE WHEN stat_date >= #{monday} AND stat_date &lt; #{today} THEN cost END), 0.00) AS 'thisWeekDailyCost',
                   CASE
                       WHEN #{lastStartDate} - #{lastEndDate} = 0 THEN 0.00
                       ELSE IFNULL(SUM(CASE
                                           WHEN stat_date >= #{lastStartDate}
                                               AND stat_date &lt;= DATE_FORMAT(DATE_SUB(#{lastEndDate}, INTERVAL 1 DAY), '%Y%m%d')
                                               THEN cost
                           END), 0.00)
                       END AS 'lastWeek1'
            FROM application.adq_advertiser_report_daily
            WHERE stat_date >= #{lastStartDate}
              AND stat_date &lt; #{today}
        ) daily
    </select>

    <select id="getTotalChargeHourly" resultType="com.alibaba.fastjson.JSONObject">
        SELECT 
               IFNULL(SUM(CASE WHEN report.stat_date = #{today} THEN report.cost END), 0.00) AS 'todayCost',
               IFNULL(SUM(CASE WHEN report.stat_date = #{yesterday} AND report.stat_hour &lt;= #{hour} THEN report.cost END), 0.00) AS 'yesterdayCost',
               IFNULL(SUM(CASE WHEN report.stat_date = #{today} THEN report.cost END), 0.00) AS 'thisWeekTodayCost',
               IFNULL(SUM(CASE WHEN report.stat_date = #{lastEndDate} AND report.stat_hour &lt;= #{hour} THEN report.cost END), 0.00) AS 'lastWeek2'
        FROM application.adq_advertiser_report_hourly report
        WHERE report.stat_date IN (#{today}, #{yesterday}, #{lastEndDate})
    </select>

    <select id="getTotalChargeDaily" resultType="com.alibaba.fastjson.JSONObject">
        SELECT IFNULL(SUM(CASE WHEN stat_date >= #{monday} AND stat_date &lt; #{today} THEN cost END), 0.00) AS 'thisWeekDailyCost',
               CASE
                   WHEN #{lastStartDate} - #{lastEndDate} = 0 THEN 0.00
                   ELSE IFNULL(SUM(CASE
                                       WHEN stat_date >= #{lastStartDate}
                                           AND stat_date &lt;= DATE_FORMAT(DATE_SUB(#{lastEndDate}, INTERVAL 1 DAY), '%Y%m%d')
                                           THEN cost
                       END), 0.00)
                   END AS 'lastWeek1'
        FROM application.adq_advertiser_report_daily
        WHERE stat_date >= #{lastStartDate}
          AND stat_date &lt; #{today}
    </select>

    <select id="getYesterdayUploadNewMaterialCount" resultType="Long">
        SELECT IFNULL(COUNT(DISTINCT material_id), 0)
        FROM application.adq_material_report_daily_dw
        WHERE stat_date = #{yesterday}
          AND project_id IN (
            SELECT id
            FROM `jeecg-boot`.ctop_project
            WHERE media_id = 16
              AND agent_code IN (1, 2)
        )
    </select>

    <select id="getHourCost" resultType="com.alibaba.fastjson.JSONObject">
        SELECT report.stat_hour AS 'time',
               IFNULL(SUM(report.cost), 0.00) AS 'cost'
        FROM application.adq_advertiser_report_hourly report
        WHERE report.stat_date = #{startTime}
        <if test="projectId != null and projectId != ''">
            AND report.project_id = #{projectId}
        </if>
        GROUP BY report.stat_hour
        ORDER BY report.stat_hour
    </select>

    <select id="getDataCost" resultType="com.alibaba.fastjson.JSONObject">
        SELECT DATE_FORMAT(report.stat_date, '%Y-%m-%d') AS 'time',
               IFNULL(SUM(report.cost), 0.00) AS 'cost'
        FROM application.adq_advertiser_report_hourly report
        WHERE report.stat_date >= #{startTime}
          AND report.stat_date &lt;= #{endTime}
        <if test="projectId != null and projectId != ''">
            AND report.project_id = #{projectId}
        </if>
        GROUP BY report.stat_date
        ORDER BY report.stat_date
    </select>

    <select id="getProjectTodayBase" resultType="com.alibaba.fastjson.JSONObject">
        SELECT report.project_id AS 'projectId',
               MAX(report.advertiser_name) AS 'companyName',
               SUM(report.cost) AS 'cost',
               SUM(report.view_count) AS 'aclick',
               SUM(report.valid_click_count) AS 'bclick',
               COUNT(DISTINCT CASE WHEN report.cost > 0 THEN report.account_id END) AS 'accountNum',
               COUNT(DISTINCT CASE WHEN report.cost > 0 THEN report.user_id END) AS 'operaterNum'
        FROM application.adq_advertiser_report_hourly report
        WHERE report.stat_date = #{startTime}
        <if test="projectId != null and projectId != ''">
            AND report.project_id = #{projectId}
        </if>
        GROUP BY report.project_id
        HAVING SUM(report.cost) > 0
    </select>

    <select id="getProjectMetaByIds" resultType="com.alibaba.fastjson.JSONObject">
        SELECT project.id AS 'projectId',
               project.project_name AS 'projectName',
               project.project_name AS 'productName',
               manager.realname AS 'operater',
               sale.realname AS 'saler'
        FROM `jeecg-boot`.ctop_project project
                 LEFT JOIN `jeecg-boot`.sys_user manager ON project.responsible_id = manager.id
                 LEFT JOIN `jeecg-boot`.sys_user sale ON project.sale_id = sale.id
        WHERE project.id IN
        <foreach collection="projectIds" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
    </select>

    <select id="getProjectMaterialCountByDayAndIds" resultType="com.alibaba.fastjson.JSONObject">
        SELECT material.project_id AS 'projectId',
               COUNT(DISTINCT material.material_id) AS 'num'
        FROM application.adq_material_report_daily_dw material
        WHERE material.stat_date = #{statDate}
          AND material.project_id IN
          <foreach collection="projectIds" item="item" separator="," open="(" close=")">
              #{item}
          </foreach>
        GROUP BY material.project_id
    </select>

    <select id="getProjectPrevHourlyCostByDayAndIds" resultType="com.alibaba.fastjson.JSONObject">
        SELECT report.project_id AS 'projectId',
               SUM(report.cost) AS 'cost'
        FROM application.adq_advertiser_report_hourly report
        WHERE report.stat_date = #{statDate}
          AND report.stat_hour &lt;= #{hour}
          AND report.project_id IN
          <foreach collection="projectIds" item="item" separator="," open="(" close=")">
              #{item}
          </foreach>
        GROUP BY report.project_id
    </select>

    <select id="getProjectAccountOperatorCountByDayAndIds" resultType="com.alibaba.fastjson.JSONObject">
        SELECT daily.project_id AS 'projectId',
               COUNT(DISTINCT daily.account_id) AS 'accountNum',
               COUNT(DISTINCT daily.user_id) AS 'operaterNum'
        FROM application.adq_advertiser_report_daily daily
        WHERE daily.stat_date = #{statDate}
          AND daily.project_id IN
          <foreach collection="projectIds" item="item" separator="," open="(" close=")">
              #{item}
          </foreach>
        GROUP BY daily.project_id
        HAVING SUM(daily.cost) > 0
    </select>

    <select id="getProjectAccountOperatorCountByCurrentHourlyAndIds" resultType="com.alibaba.fastjson.JSONObject">
        SELECT report.project_id AS 'projectId',
               COUNT(DISTINCT report.account_id) AS 'accountNum',
               COUNT(DISTINCT report.user_id) AS 'operaterNum'
        FROM application.adq_advertiser_report_hourly report
        WHERE report.stat_date = #{statDate}
          AND report.stat_hour &lt;= #{hour}
          AND report.cost > 0
          AND report.project_id IN
          <foreach collection="projectIds" item="item" separator="," open="(" close=")">
              #{item}
          </foreach>
        GROUP BY report.project_id
    </select>

    <select id="getProjectToDayReport" resultType="com.alibaba.fastjson.JSONObject">
        WITH active_projects AS (
            SELECT t1.project_id AS projectId,
                   MAX(t1.advertiser_name) AS companyName,
                   SUM(t1.cost) AS cost,
                   SUM(t1.view_count) AS aclick,
                   SUM(t1.valid_click_count) AS bclick
            FROM application.adq_advertiser_report_hourly t1
            WHERE t1.stat_date = #{startTime}
            <if test='userIds!=null and userIds.size()>0'>
                AND t1.user_id IN
                <foreach collection="userIds" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test="projectId != null and projectId != ''">
                AND t1.project_id = #{projectId}
            </if>
            GROUP BY t1.project_id
            HAVING SUM(t1.cost) > 0
        )
        SELECT *
        FROM (
                 SELECT ap.projectId AS 'projectId',
                        t3.project_name AS 'projectName',
                        t3.project_name AS 'productName',
                        ap.companyName AS 'companyName',
                        t5.realname AS 'saler',
                        t4.realname AS 'operater',
                        ap.cost AS 'cost',
                        ap.aclick AS 'aclick',
                        ap.bclick AS 'bclick',
                        NULL AS 'num',
                        CASE
                            WHEN IFNULL(t7.costs, 0) = 0 THEN '-'
                            ELSE CONCAT(ROUND((ap.cost - t7.costs) / t7.costs * 100, 2), '%')
                        END AS 'rio',
                        CASE
                            WHEN IFNULL(t7.costs, 0) = 0 THEN NULL
                            ELSE ROUND((ap.cost - t7.costs) / t7.costs * 100, 2)
                        END AS 'rioValue',
                        '-' AS 'ssAd',
                        '-' AS 'sxhAd',
                        '-' AS 'lmAd',
                        '-' AS 'xxlAd',
                        '-' AS 'jlAd',
                        '-' AS 'kpAd',
                        IFNULL(t9.accountNum, 0) AS 'accountNum',
                        IFNULL(t9.operaterNum, 0) AS 'operaterNum',
                        NULL AS 'materialUploadMediaNum'
                 FROM active_projects ap
                          LEFT JOIN `jeecg-boot`.ctop_project t3 ON ap.projectId = 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 report.project_id,
                            SUM(report.cost) AS 'costs'
                     FROM application.adq_advertiser_report_hourly report
                              INNER JOIN active_projects active ON active.projectId = report.project_id
                     WHERE report.stat_date = DATE_FORMAT(DATE_SUB(#{startTime}, INTERVAL 1 DAY), '%Y%m%d')
                       AND report.stat_hour &lt;= #{hour}
                     <if test='userIds!=null and userIds.size()>0'>
                         AND report.user_id IN
                         <foreach collection="userIds" item="item" separator="," open="(" close=")">
                             #{item}
                         </foreach>
                     </if>
                     GROUP BY report.project_id
                 ) t7 ON ap.projectId = t7.project_id
                          LEFT JOIN (
                     SELECT report.project_id,
                            COUNT(DISTINCT report.account_id) AS 'accountNum',
                            COUNT(DISTINCT report.user_id) AS 'operaterNum'
                     FROM application.adq_advertiser_report_hourly report
                              INNER JOIN active_projects active ON active.projectId = report.project_id
                     WHERE report.stat_date = #{startTime}
                       AND report.stat_hour &lt;= #{hour}
                       AND report.cost > 0
                     <if test='userIds!=null and userIds.size()>0'>
                         AND report.user_id IN
                         <foreach collection="userIds" item="item" separator="," open="(" close=")">
                             #{item}
                         </foreach>
                     </if>
                     GROUP BY report.project_id
                 ) t9 ON ap.projectId = t9.project_id
             ) t
        <if test="category == 1">
            WHERE rioValue &lt;= -10
        </if>
        ORDER BY ${sortCode} ${order}
    </select>

    <select id="getProjectMoreDaysReport" resultType="com.alibaba.fastjson.JSONObject">
        WITH active_projects AS (
            SELECT t1.project_id AS projectId,
                   MAX(t1.advertiser_name) AS companyName,
                   SUM(t1.cost) AS cost,
                   SUM(t1.view_count) AS aclick,
                   SUM(t1.valid_click_count) AS bclick
            FROM application.adq_advertiser_report_daily t1
            WHERE t1.stat_date >= #{startTime}
              AND t1.stat_date &lt;= #{endTime}
            <if test='userIds!=null and userIds.size()>0'>
                AND t1.user_id IN
                <foreach collection="userIds" item="item" separator="," open="(" close=")">
                    #{item}
                </foreach>
            </if>
            <if test="projectId != null and projectId != ''">
                AND t1.project_id = #{projectId}
            </if>
            GROUP BY t1.project_id
            HAVING SUM(t1.cost) > 0
        )
        SELECT *
        FROM (
                 SELECT ap.projectId AS 'projectId',
                        t3.project_name AS 'projectName',
                        t3.project_name AS 'productName',
                        ap.companyName AS 'companyName',
                        t5.realname AS 'saler',
                        t4.realname AS 'operater',
                        ap.cost AS 'cost',
                        ap.aclick AS 'aclick',
                        ap.bclick AS 'bclick',
                        NULL AS 'num',
                        CASE
                            WHEN IFNULL(t7.costs, 0) = 0 THEN '-'
                            ELSE CONCAT(ROUND((ap.cost - t7.costs) / t7.costs * 100, 2), '%')
                        END AS 'rio',
                        CASE
                            WHEN IFNULL(t7.costs, 0) = 0 THEN NULL
                            ELSE ROUND((ap.cost - t7.costs) / t7.costs * 100, 2)
                        END AS 'rioValue',
                        '-' AS 'ssAd',
                        '-' AS 'sxhAd',
                        '-' AS 'lmAd',
                        '-' AS 'xxlAd',
                        '-' AS 'jlAd',
                        '-' AS 'kpAd',
                        IFNULL(t9.accountNum, 0) AS 'accountNum',
                        IFNULL(t9.operaterNum, 0) AS 'operaterNum',
                        NULL AS 'materialUploadMediaNum'
                 FROM active_projects ap
                          LEFT JOIN `jeecg-boot`.ctop_project t3 ON ap.projectId = 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 daily.project_id,
                            SUM(daily.cost) AS 'costs'
                     FROM application.adq_advertiser_report_daily daily
                              INNER JOIN active_projects active ON active.projectId = daily.project_id
                     WHERE daily.stat_date >= #{lastStart}
                       AND daily.stat_date &lt;= #{lastEnd}
                     <if test='userIds!=null and userIds.size()>0'>
                         AND daily.user_id IN
                         <foreach collection="userIds" item="item" separator="," open="(" close=")">
                             #{item}
                         </foreach>
                     </if>
                     GROUP BY daily.project_id
                 ) t7 ON ap.projectId = t7.project_id
                          LEFT JOIN (
                     SELECT daily.project_id,
                            COUNT(DISTINCT daily.account_id) AS 'accountNum',
                            COUNT(DISTINCT daily.user_id) AS 'operaterNum'
                     FROM application.adq_advertiser_report_daily daily
                              INNER JOIN active_projects active ON active.projectId = daily.project_id
                     WHERE daily.stat_date >= #{startTime}
                       AND daily.stat_date &lt;= #{endTime}
                     <if test='userIds!=null and userIds.size()>0'>
                         AND daily.user_id IN
                         <foreach collection="userIds" item="item" separator="," open="(" close=")">
                             #{item}
                         </foreach>
                     </if>
                     GROUP BY daily.project_id
                     HAVING SUM(daily.cost) > 0
                 ) t9 ON ap.projectId = t9.project_id
             ) t
        <if test="category == 1">
            WHERE rioValue &lt;= -10
        </if>
        ORDER BY ${sortCode} ${order}
    </select>

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

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

    <select id="getUnitNoByHours" resultType="com.alibaba.fastjson.JSONObject">
        SELECT HOUR(create_time) AS 'time',
               COUNT(DISTINCT material_id) AS 'count'
        FROM application.adq_material_report_daily_dw
        WHERE stat_date = #{startTime}
        GROUP BY HOUR(create_time)
        ORDER BY HOUR(create_time)
    </select>

    <select id="getUnitNoByDays" resultType="com.alibaba.fastjson.JSONObject">
        SELECT stat_date AS 'time',
               COUNT(DISTINCT material_id) AS 'count'
        FROM application.adq_material_report_daily_dw
        WHERE stat_date >= #{startTime}
          AND stat_date &lt;= #{endTime}
        GROUP BY stat_date
        ORDER BY stat_date
    </select>

    <select id="getAdqOperateInfoHourly" resultType="com.alibaba.fastjson.JSONObject">
        SELECT COUNT(DISTINCT t.account_id) AS 'accountNum',
               COUNT(DISTINCT t.project_id) AS 'projectNums',
               SUM(t.cost) AS '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',
               '-' AS 'newUnitNum',
               '-' AS 'newCampaignNum',
               NULL AS 'projectNum'
        FROM application.adq_advertiser_report_hourly t
                 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.adq_advertiser_report_hourly d
            WHERE d.cost > 0
              AND d.stat_hour &lt;= #{hour}
              AND d.stat_date >= #{lastTimeStart}
              AND d.stat_date &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
        WHERE t.cost > 0
          AND t.stat_hour &lt;= #{hour}
          AND t.stat_date >= #{startDate}
          AND t.stat_date &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="getAdqOperateInfo" resultType="com.alibaba.fastjson.JSONObject">
        SELECT COUNT(DISTINCT t.account_id) AS 'accountNum',
               COUNT(DISTINCT t.project_id) AS 'projectNums',
               SUM(t.cost) AS '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',
               '-' AS 'newUnitNum',
               '-' AS 'newCampaignNum',
               NULL AS 'projectNum'
        FROM application.adq_advertiser_report_daily t
                 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.adq_advertiser_report_daily d
            WHERE d.cost > 0
              AND d.stat_date >= #{lastTimeStart}
              AND d.stat_date &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
        WHERE t.cost > 0
          AND t.stat_date >= #{startDate}
          AND t.stat_date &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="getAdqOperateGroupInfoHourly" 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',
               NULL AS 'unitNum',
               NULL AS 'projectNum'
        FROM (
                 SELECT COUNT(DISTINCT d.account_id) AS 'accountNum',
                        COUNT(DISTINCT d.project_id) AS 'projectNum',
                        SUM(d.cost) AS 'totalCost',
                        d.user_id
                 FROM application.adq_advertiser_report_hourly d
                 WHERE d.cost > 0
                   AND d.stat_hour &lt;= #{hour}
                   AND d.stat_date >= #{startDate}
                   AND d.stat_date &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) AS 'lastCost'
            FROM application.adq_advertiser_report_hourly d
            WHERE d.cost > 0
              AND d.stat_hour &lt;= #{hour}
              AND d.stat_date >= #{lastTimeStart}
              AND d.stat_date &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
    </select>

    <select id="getAdqOperateGroupInfo" 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',
               NULL AS 'unitNum',
               NULL AS 'projectNum'
        FROM (
                 SELECT COUNT(DISTINCT d.account_id) AS 'accountNum',
                        COUNT(DISTINCT d.project_id) AS 'projectNum',
                        SUM(d.cost) AS 'totalCost',
                        d.user_id
                 FROM application.adq_advertiser_report_daily d
                 WHERE d.cost > 0
                   AND d.stat_date >= #{startDate}
                   AND d.stat_date &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) AS 'lastCost'
            FROM application.adq_advertiser_report_daily d
            WHERE d.cost > 0
              AND d.stat_date >= #{lastTimeStart}
              AND d.stat_date &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
    </select>

    <sql id="adqOperateGroupRelationTableHourly">
        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.adq_advertiser_report_hourly report
                     WHERE report.stat_date >= #{startDate}
                       AND report.stat_date &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.adq_advertiser_report_hourly report
                 WHERE report.user_id IN
                       <foreach collection="managerIds" item="item" separator="," open="(" close=")">
                           #{item}
                       </foreach>
                   AND report.stat_date >= #{startDate}
                   AND report.stat_date &lt;= #{endDate}
                   AND report.cost > 0
                 GROUP BY report.user_id
             ) t
        GROUP BY t.managerId, t.userId
    </sql>

    <sql id="adqOperateGroupRelationTableDaily">
        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.adq_advertiser_report_daily report
                     WHERE report.stat_date >= #{startDate}
                       AND report.stat_date &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.adq_advertiser_report_daily report
                 WHERE report.user_id IN
                       <foreach collection="managerIds" item="item" separator="," open="(" close=")">
                           #{item}
                       </foreach>
                   AND report.stat_date >= #{startDate}
                   AND report.stat_date &lt;= #{endDate}
                   AND report.cost > 0
                 GROUP BY report.user_id
             ) t
        GROUP BY t.managerId, t.userId
    </sql>

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

    <sql id="adqOperateGroupUsersTableDaily">
        SELECT DISTINCT rel.userId
        FROM (
                 <include refid="adqOperateGroupRelationTableDaily"/>
             ) 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.adq_advertiser_report_hourly d
        WHERE d.cost > 0
          AND d.stat_hour &lt;= #{hour}
          AND d.stat_date >= #{startDate}
          AND d.stat_date &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.adq_advertiser_report_daily d
        WHERE d.cost > 0
          AND d.stat_date >= #{startDate}
          AND d.stat_date &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.adq_advertiser_report_hourly d
        WHERE d.cost > 0
          AND d.stat_hour &lt;= #{hour}
          AND d.stat_date >= #{startDate}
          AND d.stat_date &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.adq_advertiser_report_daily d
        WHERE d.cost > 0
          AND d.stat_date >= #{startDate}
          AND d.stat_date &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="getOperateGroupHourlyMetricsByUsers" resultType="com.alibaba.fastjson.JSONObject">
        SELECT d.user_id AS 'userId',
               COUNT(DISTINCT CASE
                                  WHEN d.stat_date >= #{currentStartDate}
                                      AND d.stat_date &lt;= #{currentEndDate}
                                      AND d.stat_hour &lt;= #{hour}
                                      AND d.cost > 0
                                      THEN d.account_id
                   END) AS 'accountNum',
               COUNT(DISTINCT CASE
                                  WHEN d.stat_date >= #{currentStartDate}
                                      AND d.stat_date &lt;= #{currentEndDate}
                                      AND d.stat_hour &lt;= #{hour}
                                      AND d.cost > 0
                                      THEN d.project_id
                   END) AS 'projectNum',
               IFNULL(SUM(CASE
                              WHEN d.stat_date >= #{currentStartDate}
                                  AND d.stat_date &lt;= #{currentEndDate}
                                  AND d.stat_hour &lt;= #{hour}
                                  AND d.cost > 0
                                  THEN d.cost
                          END), 0) AS 'totalCost',
               IFNULL(SUM(CASE
                              WHEN d.stat_date >= #{previousStartDate}
                                  AND d.stat_date &lt;= #{previousEndDate}
                                  AND d.stat_hour &lt;= #{hour}
                                  AND d.cost > 0
                                  THEN d.cost
                          END), 0) AS 'lastCost'
        FROM application.adq_advertiser_report_hourly d
        WHERE d.user_id IN
        <foreach collection="userIds" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
          AND d.stat_date >= #{previousStartDate}
          AND d.stat_date &lt;= #{currentEndDate}
        GROUP BY d.user_id
    </select>

    <select id="getOperateGroupDailyMetricsByUsers" resultType="com.alibaba.fastjson.JSONObject">
        SELECT d.user_id AS 'userId',
               COUNT(DISTINCT CASE
                                  WHEN d.stat_date >= #{currentStartDate}
                                      AND d.stat_date &lt;= #{currentEndDate}
                                      AND d.cost > 0
                                      THEN d.account_id
                   END) AS 'accountNum',
               COUNT(DISTINCT CASE
                                  WHEN d.stat_date >= #{currentStartDate}
                                      AND d.stat_date &lt;= #{currentEndDate}
                                      AND d.cost > 0
                                      THEN d.project_id
                   END) AS 'projectNum',
               IFNULL(SUM(CASE
                              WHEN d.stat_date >= #{currentStartDate}
                                  AND d.stat_date &lt;= #{currentEndDate}
                                  AND d.cost > 0
                                  THEN d.cost
                          END), 0) AS 'totalCost',
               IFNULL(SUM(CASE
                              WHEN d.stat_date >= #{previousStartDate}
                                  AND d.stat_date &lt;= #{previousEndDate}
                                  AND d.cost > 0
                                  THEN d.cost
                          END), 0) AS 'lastCost'
        FROM application.adq_advertiser_report_daily d
        WHERE d.user_id IN
        <foreach collection="userIds" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
          AND d.stat_date >= #{previousStartDate}
          AND d.stat_date &lt;= #{currentEndDate}
        GROUP BY d.user_id
    </select>

    <select id="getAdqOperateGroupInfoHourlyBatch" resultType="com.alibaba.fastjson.JSONObject">
        SELECT rel.managerId AS 'userId',
               COUNT(DISTINCT rel.userId) AS 'operationNum',
               IFNULL(SUM(metrics.accountNum), 0) AS 'accountNum',
               IFNULL(SUM(metrics.projectNum), 0) AS 'projectNums',
               IFNULL(SUM(metrics.totalCost), 0) AS 'totalCost',
               IFNULL(SUM(metrics.lastCost), 0) AS 'lastCost',
               CASE
                   WHEN IFNULL(SUM(metrics.lastCost), 0) = 0 THEN '0%'
                   ELSE CONCAT(CAST(ROUND((IFNULL(SUM(metrics.totalCost), 0) - SUM(metrics.lastCost)) / SUM(metrics.lastCost) * 100, 2) AS CHAR), '%')
                   END AS 'yearOnYearCost',
               NULL AS 'unitNum',
               NULL AS 'projectNum'
        FROM (
                 <include refid="adqOperateGroupRelationTableHourly"/>
             ) rel
                 LEFT JOIN (
            SELECT d.user_id,
                   COUNT(DISTINCT CASE
                                      WHEN d.stat_date >= #{startDate}
                                          AND d.stat_date &lt;= #{endDate}
                                          AND d.stat_hour &lt;= #{hour}
                                          AND d.cost > 0
                                          THEN d.account_id
                       END) AS 'accountNum',
                   COUNT(DISTINCT CASE
                                      WHEN d.stat_date >= #{startDate}
                                          AND d.stat_date &lt;= #{endDate}
                                          AND d.stat_hour &lt;= #{hour}
                                          AND d.cost > 0
                                          THEN d.project_id
                       END) AS 'projectNum',
                   IFNULL(SUM(CASE
                                  WHEN d.stat_date >= #{startDate}
                                      AND d.stat_date &lt;= #{endDate}
                                      AND d.stat_hour &lt;= #{hour}
                                      AND d.cost > 0
                                      THEN d.cost
                              END), 0) AS 'totalCost',
                   IFNULL(SUM(CASE
                                  WHEN d.stat_date >= #{lastTimeStart}
                                      AND d.stat_date &lt;= #{lastTimeEnd}
                                      AND d.stat_hour &lt;= #{hour}
                                      AND d.cost > 0
                                      THEN d.cost
                              END), 0) AS 'lastCost'
            FROM application.adq_advertiser_report_hourly d
                     INNER JOIN (
                <include refid="adqOperateGroupUsersTableHourly"/>
            ) users ON users.userId = d.user_id
            WHERE d.stat_date >= #{lastTimeStart}
              AND d.stat_date &lt;= #{endDate}
            GROUP BY d.user_id
        ) metrics ON rel.userId = metrics.user_id
        GROUP BY rel.managerId
        ORDER BY IFNULL(SUM(metrics.totalCost), 0) DESC
    </select>

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

</mapper>
