<?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="com.ruixuan.report.mapper.KuaishouLiveAccountReportMapper">

    <select id="operateHomePageHead" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t1.*,
        IFNULL(ROUND((t1.costTotal - t2.costTotal) / t2.costTotal * 100, 2), 0) as 'costTotalRoi',
        IFNULL(ROUND((t1.selfOperationCostTotal - t2.selfOperationCostTotal) / t2.selfOperationCostTotal * 100, 2), 0)
        as 'selfOperationCostTotalRoi',
        IFNULL(ROUND(t1.selfOperationCostTotal / t1.costTotal * 100, 2), 0) as 'roi'
        FROM (
        SELECT
        SUM(cost_total) AS 'costTotal',
        COUNT(DISTINCT CASE WHEN t1.cost_total > 0 THEN t1.account_id ELSE 0 END) AS 'accountNum',
        SUM(CASE WHEN t1.account_type = 1 THEN cost_total ELSE 0 END) AS 'selfOperationCostTotal'
        FROM kuaishou_live_account_report_hourly_dw t1
        LEFT JOIN kuaishou_account t2 ON t1.account_id = t2.account_id
        WHERE report_date = #{today}
        AND hour &lt;= #{hour}
        <if test="userList != null and userList.size() > 0 ">
            AND user_id IN
            <foreach collection="userList" item="userId" open="(" separator="," close=")">
                #{userId}
            </foreach>
        </if>
        ) t1
        LEFT JOIN
        (
        SELECT
        SUM(cost_total) AS 'costTotal',
        SUM(CASE WHEN t1.account_type = 1 THEN cost_total ELSE 0 END) AS 'selfOperationCostTotal'
        FROM kuaishou_live_account_report_hourly_dw t1
        WHERE report_date = #{yesterday}
        AND hour &lt;= #{hour}
        <if test="userList != null and userList.size() > 0 ">
            AND user_id IN
            <foreach collection="userList" item="userId" open="(" separator="," close=")">
                #{userId}
            </foreach>
        </if>
        ) t2 ON 1 = 1

    </select>

    <select id="getCustomersNum" resultType="Integer">
        SELECT
        COUNT(DISTINCT advertiser_id)
        FROM
        kuaishou_live_account_report_hourly_dw t1
        LEFT JOIN kuaishou_account t2 ON t1.account_id = t2.account_id
        WHERE cost_total > 0
        <if test="userList != null and userList.size() > 0 ">
            AND user_id IN
            <foreach collection="userList" item="userId" open="(" separator="," close=")">
                #{userId}
            </foreach>
        </if>
        AND report_date = #{today}
        AND hour &lt;= #{hour}
    </select>

    <select id="getProjectCostListByHourly" resultType="com.alibaba.fastjson.JSONObject">
        SELECT * FROM (
        SELECT t1.*,
        IFNULL(ROUND((t1.costTotal - t2.costTotal) / t2.costTotal * 100, 2), 0) as 'costTotalRoi',
        IFNULL(ROUND((t1.roi - t2.roi) / t2.roi * 100, 2), 0) as 'rRoi'
        FROM (
        SELECT t1.project_id as 'projectId',
        t2.project_name as 'projectName',
        t2.operator_id as 'operatorId',
        t2.operator_name as 'operatorName',
        SUM(cost_total) as 'costTotal',
        IFNULL(ROUND(SUM(t1.t0_gmv) / SUM(cost_total), 2), 0) as 'roi'
        FROM kuaishou_live_account_report_hourly_dw t1
        LEFT JOIN `kuaishou_project` t2 ON t1.project_id = t2.id
        WHERE report_date = #{today}
        AND hour &lt;= #{hour}
        AND t1.account_type = 1
        <if test="projectIds != null and projectIds.size() > 0 ">
            AND t1.project_id IN
            <foreach collection="projectIds" item="projectId" open="(" separator="," close=")">
                #{projectId}
            </foreach>
        </if>
        GROUP BY t1.project_id
        HAVING SUM(cost_total)>0
        ) t1
        LEFT JOIN
        (
        SELECT
        t1.project_id as 'projectId',
        SUM(cost_total) as 'costTotal',
        IFNULL(ROUND(SUM(t1.t0_gmv) / SUM(cost_total), 2), 0) as 'roi'
        FROM kuaishou_live_account_report_hourly_dw t1
        WHERE report_date = #{yesterday}
        AND hour &lt;= #{hour}
        AND account_type = 1
        <if test="projectIds != null and projectIds.size() > 0 ">
            AND t1.project_id IN
            <foreach collection="projectIds" item="projectId" open="(" separator="," close=")">
                #{projectId}
            </foreach>
        </if>
        GROUP BY t1.project_id
        ) t2 ON t1.projectId = t2.projectId
        ) t ORDER BY costTotal desc
    </select>
    <select id="getProjectCostListByDaily" resultType="com.alibaba.fastjson.JSONObject">
        SELECT *
        FROM (
        SELECT t1.*,
        IFNULL(ROUND((t1.costTotal - t2.costTotal) / t2.costTotal * 100, 2), 0) as 'costTotalRoi',
        IFNULL(ROUND((t1.roi - t2.roi) / t2.roi * 100, 2), 0) as 'rRoi'
        FROM (
        SELECT t1.project_id as 'projectId',
        t2.project_name as 'projectName',
        t2.operator_id as 'operatorId',
        t2.operator_name as 'operatorName',
        SUM(cost_total) as 'costTotal',
        IFNULL(ROUND(SUM(t1.t0_gmv) / SUM(cost_total), 2), 0) as 'roi'
        FROM kuaishou_live_account_report_daily_dw t1
        LEFT JOIN `kuaishou_project` t2 ON t1.project_id = t2.id
        WHERE report_date >= #{startDate}
        AND report_date &lt;= #{endDate}
        AND t1.account_type = 1
        <if test="projectIds != null and projectIds.size() > 0 ">
            AND t1.project_id IN
            <foreach collection="projectIds" item="projectId" open="(" separator="," close=")">
                #{projectId}
            </foreach>
        </if>
        GROUP BY t1.project_id
        HAVING SUM(cost_total)>0
        ) t1
        LEFT JOIN
        (
        SELECT
        project_id as 'projectId',
        SUM(cost_total) as 'costTotal',
        IFNULL(ROUND(SUM(t0_gmv) / SUM(cost_total), 2), 0) as 'roi'
        FROM kuaishou_live_account_report_daily_dw
        WHERE report_date >=#{lastStart}
        AND report_date &lt;=#{lastEnd}
        AND account_type = 1
        <if test="projectIds != null and projectIds.size() > 0 ">
            AND project_id IN
            <foreach collection="projectIds" item="projectId" open="(" separator="," close=")">
                #{projectId}
            </foreach>
        </if>
        GROUP BY project_id
        ) t2 ON t1.projectId = t2.projectId
        ) t
        ORDER BY costTotal desc
    </select>


    <select id="getOperationsGroupCostByHourly" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        IFNULL(t1.costTotal,0) as 'costTotal',
        IFNULL(t1.perCost,0) as 'perCost',
        IFNULL(ROUND((t1.costTotal - t2.costTotal) / t2.costTotal * 100, 2), 0) as 'roi',
        IFNULL(t2.costTotal,0) as 'yesCostTotal'
        FROM (
        SELECT
        SUM(cost_total) as 'costTotal',
        IFNULL(ROUND(SUM(cost_total) / ${operateNum}/${days}, 2), 0) as 'perCost'
        FROM kuaishou_live_account_report_hourly_dw
        WHERE report_date = #{today}
        AND hour &lt;= #{hour}
        AND account_type = 1
        <if test="userList != null and userList.size() > 0 ">
            AND user_id IN
            <foreach collection="userList" item="userId" open="(" separator="," close=")">
                #{userId}
            </foreach>
        </if>
        ) t1
        LEFT JOIN
        (
        SELECT
        SUM(cost_total) as 'costTotal'
        FROM kuaishou_live_account_report_hourly_dw
        WHERE report_date = #{yesterday}
        AND hour &lt;= #{hour}
        AND account_type = 1
        <if test="userList != null and userList.size() > 0 ">
            AND user_id IN
            <foreach collection="userList" item="userId" open="(" separator="," close=")">
                #{userId}
            </foreach>
        </if>
        ) t2 ON 1 = 1
    </select>
    <select id="getOperationsGroupCostByDaily" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        IFNULL(t1.costTotal,0) as 'costTotal',
        IFNULL(t1.perCost,0) as 'perCost',
        IFNULL(ROUND((t1.costTotal - t2.costTotal) / t2.costTotal * 100, 2), 0) as 'roi',
        IFNULL(t2.costTotal,0) as 'yesCostTotal'
        FROM (
        SELECT
        SUM(cost_total) as 'costTotal',
        IFNULL(ROUND(SUM(cost_total) / ${operateNum}/${days}, 2), 0) as 'perCost'
        FROM kuaishou_live_account_report_daily_dw
        WHERE report_date >= #{startDate}
        AND report_date &lt;= #{endDate}
        AND account_type = 1
        <if test="userList != null and userList.size() > 0 ">
            AND user_id IN
            <foreach collection="userList" item="userId" open="(" separator="," close=")">
                #{userId}
            </foreach>
        </if>
        ) t1
        LEFT JOIN
        (
        SELECT
        SUM(cost_total) as 'costTotal'
        FROM kuaishou_live_account_report_daily_dw
        WHERE report_date >=#{lastStart}
        AND report_date &lt;=#{lastEnd}
        AND account_type = 1
        <if test="userList != null and userList.size() > 0 ">
            AND user_id IN
            <foreach collection="userList" item="userId" open="(" separator="," close=")">
                #{userId}
            </foreach>
        </if>
        ) t2 ON 1 = 1
    </select>


    <select id="getOperateCostListByHourly" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t1.*,
        IFNULL( ROUND(( t1.costTotal - t2.costTotal )/ t2.costTotal * 100,2), 0) AS 'costTotalRoi',
        IFNULL(ROUND(t1.costTotal / t1.accountNum/${days}, 2), 0) as 'accountCost'
        FROM
        (
        SELECT
        t2.operator_id AS 'userId',
        t2.operator_name AS 'userName',
        SUM(cost_total) AS 'costTotal',
        count( DISTINCT t1.account_id ) AS 'accountNum',
        count( DISTINCT t1.project_id) AS 'projectNum'
        FROM
        kuaishou_live_account_report_hourly_dw t1
        LEFT JOIN kuaishou_account t2 ON t1.account_id = t2.account_id
        WHERE report_date = #{today}
        AND hour &lt;= #{hour}
        AND t1.cost_total >0
        AND t2.account_type = 1
        <if test="userList != null and userList.size() > 0 ">
            AND t1.user_id IN
            <foreach collection="userList" item="userId" open="(" separator="," close=")">
                #{userId}
            </foreach>
        </if>
        GROUP BY
        t1.user_id
        ORDER BY costTotal desc
        ) t1
        LEFT JOIN (
        SELECT
        user_id AS 'userId',
        SUM(cost_total) AS 'costTotal'
        FROM
        kuaishou_live_account_report_hourly_dw
        WHERE report_date = #{yesterday}
        AND hour &lt;= #{hour}
        AND account_type = 1
        <if test="userList != null and userList.size() > 0 ">
            AND user_id IN
            <foreach collection="userList" item="userId" open="(" separator="," close=")">
                #{userId}
            </foreach>
        </if>
        GROUP BY
        user_id
        ) t2 ON t1.userId = t2.userId
    </select>
    <select id="getOperateCostListByDaily" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t1.*,
        IFNULL( ROUND(( t1.costTotal - t2.costTotal )/ t2.costTotal * 100,2), 0) AS 'costTotalRoi',
        IFNULL(ROUND(t1.costTotal / t1.accountNum/${days}, 2), 0) as 'accountCost'
        FROM
        (
        SELECT
        t2.operator_id AS 'userId',
        t2.operator_name AS 'userName',
        SUM(cost_total) AS 'costTotal',
        count( DISTINCT t1.account_id ) AS 'accountNum',
        count( DISTINCT t1.project_id) AS 'projectNum'
        FROM
        kuaishou_live_account_report_daily_dw t1
        LEFT JOIN kuaishou_account t2 ON t1.account_id = t2.account_id
        WHERE report_date >= #{startDate}
        AND report_date &lt;= #{endDate}
        AND t1.cost_total >0
        AND t2.account_type = 1
        <if test="userList != null and userList.size() > 0 ">
            AND user_id IN
            <foreach collection="userList" item="userId" open="(" separator="," close=")">
                #{userId}
            </foreach>
        </if>
        GROUP BY
        t1.user_id
        ORDER BY costTotal desc
        ) t1
        LEFT JOIN (
        SELECT
        user_id AS 'userId',
        SUM(cost_total) AS 'costTotal'
        FROM
        kuaishou_live_account_report_daily_dw
        WHERE report_date >=#{lastStart}
        AND report_date &lt;=#{lastEnd}
        AND account_type = 1
        <if test="userList != null and userList.size() > 0 ">
            AND user_id IN
            <foreach collection="userList" item="userId" open="(" separator="," close=")">
                #{userId}
            </foreach>
        </if>
        GROUP BY
        user_id
        ) t2 ON t1.userId = t2.userId
    </select>

    <select id="getOperateCostTotalByHourly" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t1.*,
        IFNULL( ROUND(( t1.costTotal - t2.costTotal )/ t2.costTotal * 100,2), 0) AS 'costTotalRoi',
        IFNULL(ROUND(t1.costTotal / t1.accountNum/${days}, 2), 0) as 'accountCost'
        FROM
        (
        SELECT
        '汇总' AS 'userName',
        SUM(cost_total) AS 'costTotal',
        count( DISTINCT t1.account_id ) AS 'accountNum',
        count( DISTINCT t1.project_id) AS 'projectNum'
        FROM
        kuaishou_live_account_report_hourly_dw t1
        LEFT JOIN kuaishou_account t2 ON t1.account_id = t2.account_id
        WHERE report_date = #{today}
        AND hour &lt;= #{hour}
        AND t2.account_type = 1
        AND t1.cost_total >0
        <if test="userList != null and userList.size() > 0 ">
            AND t1.user_id IN
            <foreach collection="userList" item="userId" open="(" separator="," close=")">
                #{userId}
            </foreach>
        </if>
        ) t1
        LEFT JOIN (
        SELECT
        SUM(cost_total) AS 'costTotal'
        FROM
        kuaishou_live_account_report_hourly_dw
        WHERE report_date = #{yesterday}
        AND hour &lt;= #{hour}
        AND account_type = 1
        <if test="userList != null and userList.size() > 0 ">
            AND user_id IN
            <foreach collection="userList" item="userId" open="(" separator="," close=")">
                #{userId}
            </foreach>
        </if>
        ) t2 ON 1=1
    </select>
    <select id="getOperateCostTotalByDaily" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t1.*,
        IFNULL(ROUND((t1.costTotal - t2.costTotal)/ t2.costTotal * 100,2), 0) AS 'costTotalRoi',
        IFNULL(ROUND(t1.costTotal / t1.accountNum/${days}, 2), 0) as 'accountCost'
        FROM
        (
        SELECT
        '汇总' AS 'userName',
        SUM(cost_total) AS 'costTotal',
        count( DISTINCT t1.account_id ) AS 'accountNum',
        count( DISTINCT t1.project_id) AS 'projectNum'
        FROM
        kuaishou_live_account_report_daily_dw t1
        LEFT JOIN kuaishou_account t2 ON t1.account_id = t2.account_id
        WHERE report_date >= #{startDate}
        AND report_date &lt;= #{endDate}
        AND t2.account_type = 1
        AND t1.cost_total >0
        <if test="userList != null and userList.size() > 0 ">
            AND user_id IN
            <foreach collection="userList" item="userId" open="(" separator="," close=")">
                #{userId}
            </foreach>
        </if>
        ) t1
        LEFT JOIN (
        SELECT
        SUM(cost_total) AS 'costTotal'
        FROM
        kuaishou_live_account_report_daily_dw
        WHERE report_date >=#{lastStart}
        AND report_date &lt;=#{lastEnd}
        AND account_type = 1
        <if test="userList != null and userList.size() > 0 ">
            AND user_id IN
            <foreach collection="userList" item="userId" open="(" separator="," close=")">
                #{userId}
            </foreach>
        </if>
        ) t2 ON 1=1
    </select>


    <select id="getOperateCostTrendChartListByHourly" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        hour as 'time',
        SUM(cost_total) AS 'costTotal'
        FROM
        kuaishou_live_account_report_hourly_dw
        WHERE account_type = 1
        AND report_date = #{today}
        <if test="userList != null and userList.size() > 0 ">
            AND user_id IN
            <foreach collection="userList" item="userId" open="(" separator="," close=")">
                #{userId}
            </foreach>
        </if>
        GROUP BY hour
        ORDER BY hour
    </select>
    <select id="getOperateCostTrendChartListByDaily" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        date_format(report_date, '%Y-%m-%d') as 'time',
        SUM(cost_total) AS 'costTotal'
        FROM
        kuaishou_live_account_report_daily_dw
        WHERE account_type = 1
        AND report_date >= #{startDate}
        AND report_date &lt;= #{endDate}
        <if test="userList != null and userList.size() > 0 ">
            AND user_id IN
            <foreach collection="userList" item="userId" open="(" separator="," close=")">
                #{userId}
            </foreach>
        </if>
        GROUP BY report_date
        ORDER BY report_date
    </select>


    <select id="getStatisticalReportHeadByHourly" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ROUND(t1.costTotal,2)AS'costTotal',
        ROUND(t1.t0Gmv,2)AS't0Gmv',
        ROUND(t1.t0Roi,2)AS't0Roi',
        ROUND(t1.viewCost,2)AS'viewCost',
        ROUND(t1.orderCost,2)AS'orderCost',
        t1.upFans,
        IFNULL(ROUND((t1.costTotal - t2.costTotal)/ t2.costTotal * 100,2), 0) AS 'costTotalRoi',
        IFNULL(ROUND((t1.t0Gmv - t2.t0Gmv)/ t2.t0Gmv * 100,2), 0) AS 't0GmvRoi',
        IFNULL(ROUND((t1.t0Roi - t2.t0Roi)/ t2.t0Roi * 100,2), 0) AS 't0RoiRoi',
        IFNULL(ROUND((t1.viewCost - t2.viewCost)/ t2.viewCost * 100,2), 0) AS 'viewCostRoi',
        IFNULL(ROUND((t1.orderCost - t2.orderCost)/ t2.orderCost * 100,2), 0) AS 'orderCostRoi',
        IFNULL(ROUND((t1.upFans - t2.upFans)/ t2.upFans * 100,2), 0) AS 'upFansRoi'
        FROM
        (
        SELECT
        IFNULL(SUM(cost_total),0)AS'costTotal',
        IFNULL(SUM(t0_gmv),0)AS't0Gmv',
        IFNULL(SUM(t0_gmv)/SUM(cost_total),0)AS't0Roi',
        IFNULL(SUM(cost_total)/SUM(standard_live_played_started),0)AS'viewCost',
        IFNULL(SUM(merchant_reco_fans),0)AS'upFans',
        IFNULL(SUM(cost_total)/SUM(t0_order_cnt),0)AS'orderCost'
        FROM
        kuaishou_live_account_report_hourly_dw
        WHERE report_date = #{today}
        AND hour &lt;= #{hour}
        AND account_type = 1
        <if test="accountIdList != null and accountIdList.size() > 0 ">
            AND account_id in
            <foreach collection="accountIdList" item="accountId" open="(" separator="," close=")">
                #{accountId}
            </foreach>
        </if>
        )t1
        LEFT JOIN
        (
        SELECT
        IFNULL(SUM(cost_total),0)AS'costTotal',
        IFNULL(SUM(t0_gmv),0)AS't0Gmv',
        IFNULL(SUM(t0_gmv)/SUM(cost_total),0)AS't0Roi',
        IFNULL(SUM(cost_total)/SUM(standard_live_played_started),0)AS'viewCost',
        IFNULL(SUM(merchant_reco_fans),0)AS'upFans',
        IFNULL(SUM(cost_total)/SUM(t0_order_cnt),0)AS'orderCost'
        FROM
        kuaishou_live_account_report_hourly_dw
        WHERE report_date = #{yesterday}
        AND hour &lt;= #{hour}
        AND account_type = 1
        <if test="accountIdList != null and accountIdList.size() > 0 ">
            AND account_id in
            <foreach collection="accountIdList" item="accountId" open="(" separator="," close=")">
                #{accountId}
            </foreach>
        </if>
        )t2 ON 1=1
    </select>
    <select id="getStatisticalReportHeadByDaily" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ROUND(t1.costTotal,2)AS'costTotal',
        ROUND(t1.t0Gmv,2)AS't0Gmv',
        ROUND(t1.t0Roi,2)AS't0Roi',
        ROUND(t1.viewCost,2)AS'viewCost',
        ROUND(t1.orderCost,2)AS'orderCost',
        t1.upFans,
        IFNULL(ROUND((t1.costTotal - t2.costTotal)/ t2.costTotal * 100,2), 0) AS 'costTotalRoi',
        IFNULL(ROUND((t1.t0Gmv - t2.t0Gmv)/ t2.t0Gmv * 100,2), 0) AS 't0GmvRoi',
        IFNULL(ROUND((t1.t0Roi - t2.t0Roi)/ t2.t0Roi * 100,2), 0) AS 't0RoiRoi',
        IFNULL(ROUND((t1.viewCost - t2.viewCost)/ t2.viewCost * 100,2), 0) AS 'viewCostRoi',
        IFNULL(ROUND((t1.orderCost - t2.orderCost)/ t2.orderCost * 100,2), 0) AS 'orderCostRoi',
        IFNULL(ROUND((t1.upFans - t2.upFans)/ t2.upFans * 100,2), 0) AS 'upFansRoi'
        FROM
        (
        SELECT
        IFNULL(SUM(cost_total),0)AS'costTotal',
        IFNULL(SUM(t0_gmv),0)AS't0Gmv',
        IFNULL(SUM(t0_gmv)/SUM(cost_total),0)AS't0Roi',
        IFNULL(SUM(cost_total)/SUM(standard_live_played_started),0)AS'viewCost',
        IFNULL(SUM(merchant_reco_fans),0)AS'upFans',
        IFNULL(SUM(cost_total)/SUM(t0_order_cnt),0)AS'orderCost'
        FROM
        kuaishou_live_account_report_daily_dw
        WHERE report_date >= #{startDate}
        AND report_date &lt;= #{endDate}
        AND account_type = 1
        <if test="accountIdList != null and accountIdList.size() > 0 ">
            AND account_id in
            <foreach collection="accountIdList" item="accountId" open="(" separator="," close=")">
                #{accountId}
            </foreach>
        </if>
        )t1
        LEFT JOIN
        (
        SELECT
        IFNULL(SUM(cost_total),0)AS'costTotal',
        IFNULL(SUM(t0_gmv),0)AS't0Gmv',
        IFNULL(SUM(t0_gmv)/SUM(cost_total),0)AS't0Roi',
        IFNULL(SUM(cost_total)/SUM(standard_live_played_started),0)AS'viewCost',
        IFNULL(SUM(merchant_reco_fans),0)AS'upFans',
        IFNULL(SUM(cost_total)/SUM(t0_order_cnt),0)AS'orderCost'
        FROM
        kuaishou_live_account_report_daily_dw
        WHERE report_date >= #{startDate}
        AND report_date &lt;= #{endDate}
        AND account_type = 1
        <if test="accountIdList != null and accountIdList.size() > 0 ">
            AND account_id in
            <foreach collection="accountIdList" item="accountId" open="(" separator="," close=")">
                #{accountId}
            </foreach>
        </if>
        )t2 ON 1=1
    </select>


    <select id="getvisitorStatisticsTrendChartListByHourly" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        hour as 'time',
        SUM(cost_total) AS 'costTotal'
        FROM
        kuaishou_live_account_report_hourly_dw
        WHERE account_type = 1
        AND report_date = #{today}
        <if test="accountIdList != null and accountIdList.size() > 0 ">
            AND account_id in
            <foreach collection="accountIdList" item="accountId" open="(" separator="," close=")">
                #{accountId}
            </foreach>
        </if>
        GROUP BY hour
        ORDER BY hour
    </select>
    <select id="getvisitorStatisticsTrendChartListByDaily" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        date_format(report_date, '%Y-%m-%d') as 'time',
        SUM(cost_total) AS 'costTotal'
        FROM
        kuaishou_live_account_report_daily_dw
        WHERE account_type = 1
        AND report_date >= #{startDate}
        AND report_date &lt;= #{endDate}
        <if test="accountIdList != null and accountIdList.size() > 0 ">
            AND account_id in
            <foreach collection="accountIdList" item="accountId" open="(" separator="," close=")">
                #{accountId}
            </foreach>
        </if>
        GROUP BY report_date
        ORDER BY report_date
    </select>


    <select id="getNodeAccountsCostByHourly" resultType="com.alibaba.fastjson.JSONObject">
        SELECT * FROM (
            (
        SELECT
        2 as 'orders',
        t1.hour as 'time',
        t1.account_id as 'accountId',
        t1.account_name as 'accountName',
        SUM(t1.cost_total) AS 'costTotal',
        IFNULL(CONCAT(CAST(ROUND((SUM(t1.cost_total)-t2.costTotal)/t2.costTotal * 100,2) AS CHAR) ,'%'),'-') as 'roi'
        FROM
        kuaishou_live_account_report_hourly_dw t1
        LEFT JOIN (
        SELECT
        account_id ,
        account_name ,
        SUM(cost_total) AS 'costTotal'
        FROM
        kuaishou_live_account_report_hourly_dw
        WHERE account_type = 1
        AND report_date = DATE_FORMAT(DATE_SUB(#{date}, INTERVAL 1 DAY ),'%Y%m%d')
        AND hour = #{hour}
        <if test="accountIdList != null and accountIdList.size() > 0 ">
            AND account_id in
            <foreach collection="accountIdList" item="accountId" open="(" separator="," close=")">
                #{accountId}
            </foreach>
        </if>
        GROUP BY account_id
        ORDER BY account_id
        ) t2 ON t1.account_id = t2.account_id

        WHERE t1.account_type = 1
        AND t1.report_date = #{date}
        AND t1.hour = #{hour}
        <if test="accountIdList != null and accountIdList.size() > 0 ">
            AND t1.account_id in
            <foreach collection="accountIdList" item="accountId" open="(" separator="," close=")">
                #{accountId}
            </foreach>
        </if>
        GROUP BY t1.account_id
        HAVING SUM(t1.cost_total) >0
        ORDER BY t1.account_id
            )
        UNION ALL
        (
        SELECT
        1 as 'orders',
        hour as 'time',
        '汇总' as 'accountId',
        '汇总' as 'accountName',
        SUM(t1.cost_total) AS 'costTotal',
        IFNULL(CONCAT(CAST(ROUND((SUM(t1.cost_total)-t2.costTotal)/t2.costTotal * 100,2) AS CHAR) ,'%'),'-') as 'roi'
        FROM
        kuaishou_live_account_report_hourly_dw t1
        LEFT JOIN (
        SELECT
        account_id ,
        account_name ,
        SUM(cost_total) AS 'costTotal'
        FROM
        kuaishou_live_account_report_hourly_dw
        WHERE account_type = 1
        AND report_date = DATE_FORMAT(DATE_SUB(#{date}, INTERVAL 1 DAY ),'%Y%m%d')
        AND hour = #{hour}
        <if test="accountIdList != null and accountIdList.size() > 0 ">
            AND account_id in
            <foreach collection="accountIdList" item="accountId" open="(" separator="," close=")">
                #{accountId}
            </foreach>
        </if>
        ) t2 ON 1=1
        WHERE t1.account_type = 1
        AND t1.report_date = #{date}
        AND t1.hour = #{hour}
        <if test="accountIdList != null and accountIdList.size() > 0 ">
            AND t1.account_id in
            <foreach collection="accountIdList" item="accountId" open="(" separator="," close=")">
                #{accountId}
            </foreach>
        </if>
         )
        ) t ORDER BY orders,costTotal DESC
    </select>
    <select id="getNodeAccountsCostByDaily" resultType="com.alibaba.fastjson.JSONObject">
        SELECT * FROM (
        (
        SELECT
        2 as 'orders',
        date_format(report_date, '%Y-%m-%d') as 'time',
        t1.account_id as 'accountId',
        t1.account_name as 'accountName',
        SUM(t1.cost_total) AS 'costTotal',
        IFNULL(CONCAT(CAST(ROUND((SUM(t1.cost_total)-t2.costTotal)/t2.costTotal * 100,2) AS CHAR) ,'%'),'-') as 'roi'
        FROM
        kuaishou_live_account_report_daily_dw t1
        LEFT JOIN (
        SELECT
        account_id ,
        account_name ,
        SUM(cost_total) AS 'costTotal'
        FROM
        kuaishou_live_account_report_daily_dw
        WHERE account_type = 1
        AND report_date = DATE_FORMAT(DATE_SUB(#{date}, INTERVAL 1 DAY ),'%Y%m%d')
        <if test="accountIdList != null and accountIdList.size() > 0 ">
            AND account_id in
            <foreach collection="accountIdList" item="accountId" open="(" separator="," close=")">
                #{accountId}
            </foreach>
        </if>
        GROUP BY account_id
        ORDER BY account_id
        ) t2 ON t1.account_id = t2.account_id
        WHERE t1.account_type = 1
        AND t1.report_date = #{date}
        <if test="accountIdList != null and accountIdList.size() > 0 ">
            AND t1.account_id in
            <foreach collection="accountIdList" item="accountId" open="(" separator="," close=")">
                #{accountId}
            </foreach>
        </if>
        GROUP BY t1.account_id
        HAVING SUM(t1.cost_total) >0
        ORDER BY t1.account_id
        )
        UNION ALL
        (
        SELECT
        1 as 'orders',
        date_format(report_date, '%Y-%m-%d') as 'time',
        '汇总' as 'accountId',
        '汇总' as 'accountName',
        SUM(t1.cost_total) AS 'costTotal',
        IFNULL(CONCAT(CAST(ROUND((SUM(t1.cost_total)-t2.costTotal)/t2.costTotal * 100,2) AS CHAR) ,'%'),'-') as 'roi'
        FROM
        kuaishou_live_account_report_daily_dw t1
        LEFT JOIN (
        SELECT
        account_id ,
        account_name ,
        SUM(cost_total) AS 'costTotal'
        FROM
        kuaishou_live_account_report_daily_dw
        WHERE account_type = 1
        AND report_date = DATE_FORMAT(DATE_SUB(#{date}, INTERVAL 1 DAY ),'%Y%m%d')
        <if test="accountIdList != null and accountIdList.size() > 0 ">
            AND account_id in
            <foreach collection="accountIdList" item="accountId" open="(" separator="," close=")">
                #{accountId}
            </foreach>
        </if>
        ) t2 ON 1=1
        WHERE t1.account_type = 1
        AND t1.report_date = #{date}
        <if test="accountIdList != null and accountIdList.size() > 0 ">
            AND t1.account_id in
            <foreach collection="accountIdList" item="accountId" open="(" separator="," close=")">
                #{accountId}
            </foreach>
        </if>
        )
        ) t ORDER BY orders,costTotal DESC
    </select>


    <select id="getAccountDataReportByHourly" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        date_format(report_date, '%Y-%m-%d') as 'date',
        CONCAT(hour,':00-',(hour+1),':00') as 'time',
        hour,
        ${filedAll}
        FROM
        kuaishou_live_account_report_hourly_dw t1
        WHERE account_type = 1
        AND report_date = #{today}
        <if test="accountIdList != null and accountIdList.size() > 0 ">
            AND account_id in
            <foreach collection="accountIdList" item="accountId" open="(" separator="," close=")">
                #{accountId}
            </foreach>
        </if>
        GROUP BY hour
        ORDER BY hour
    </select>
    <select id="getAccountDataReportByDaily" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        date_format(report_date, '%Y-%m-%d') as 'time',
        ${filedAll}
        FROM
        kuaishou_live_account_report_daily_dw t1
        WHERE account_type = 1
        AND report_date >= #{startDate}
        AND report_date &lt;= #{endDate}
        <if test="accountIdList != null and accountIdList.size() > 0 ">
            AND account_id in
            <foreach collection="accountIdList" item="accountId" open="(" separator="," close=")">
                #{accountId}
            </foreach>
        </if>
        GROUP BY report_date
        ORDER BY report_date
    </select>


    <insert id="replaceKuaishouFields">
        replace
        into kuaishou_field_definition(
        user_id,
        json,
        column_type
        )values(
        #{request.userId},
        #{request.json},
        #{request.columnType}
        )
    </insert>
    <select id="getKuaishouFieldsByUserId" resultType="com.alibaba.fastjson.JSONObject">
        SELECT user_id as 'userId',
               json,
               column_type as 'columnType'
        FROM kuaishou_field_definition
        WHERE user_id = #{userId}
          AND column_type = #{columnType} Limit 1
    </select>
</mapper>