<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.com.ctop.kuaishou.modules.ai.mapper.IAiKuaiShouReportAnalyzeMapper">

    <select id="queryStrategyByAccountId" resultType="com.alibaba.fastjson.JSONObject">
        SELECT id,
               strategy_name
        FROM ctop_kuaishou_strategy
        WHERE account_id = #{accountId}
    </select>

    <select id="queryTargetByStrategyId" resultType="com.alibaba.fastjson.JSONObject">
        SELECT t4.id,
               t4.target_type,
               t4.target_content
        FROM ctop_kuaishou_strategy t1
                 LEFT JOIN ctop_ai_kuaishou_strategy_middle t2 on t1.id = t2.strategy_id
                 LEFT JOIN ctop_ai_kuaishou_strategy_target_union t3 on t2.id = t3.strategy_middle_id
                 LEFT JOIN ctop_ai_kuaishou_strategy_target_base t4 on t3.strategy_target_id = t4.id
        where t1.id = #{strategyId}
    </select>

    <select id="queryMaterialByStrategyId" resultType="com.alibaba.fastjson.JSONObject">
        SELECT DISTINCT t2.code,
                        t2.material_name,
                        t2.cover_url
        FROM ctop_ai_kuaishou_strategy_map_creative t1
                 LEFT JOIN ctop_material_info t2 ON t1.video_signature = t2.CODE
        WHERE t1.strategy_id = #{strategyId}
            and t2.code is not null
        GROUP BY t2.code
    </select>

    <select id="queryTargetReportBy" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        r.target_type as targetType,
        r.target_content as targetContent,
        IFNULL(sum(t.charge),0) as cost,
        IFNULL(sum(t.photo_show),0) as photoShow,
        IFNULL(sum(t.photo_click),0) as photoClick,
        CASE WHEN sum(t.photo_click) / sum(t.photo_show) IS NULL THEN 0 ELSE CONCAT( ROUND((sum(t.photo_click) /
        sum(t.photo_show)) * 100,2),'%') END AS clickRate,
        IFNULL(sum(t.aclick),0) as aclick,
        IFNULL(sum(t.bclick),0) as bclick,
        CASE WHEN sum(t.bclick) / sum(t.aclick) IS NULL THEN 0 ELSE CONCAT( ROUND((sum(t.bclick) / sum(t.aclick)) *
        100,2),'%') END AS bClickRate,
        CASE WHEN sum(t.charge) / sum(t.photo_show) IS NULL THEN 0 ELSE ROUND((sum(t.charge) / sum(t.photo_show)) *
        1000, 2 ) END AS cpm ,
        CASE WHEN sum(t.charge) / sum(t.photo_click) IS NULL THEN 0 ELSE ROUND((sum(t.charge) / sum(t.photo_click)), 2)
        END AS cpc ,
        IFNULL(sum(t.activation),0) as active,
        CASE WHEN sum(t.charge) / sum(t.activation) IS NULL THEN 0 ELSE sum(t.charge) / sum(t.activation) end AS
        activeCost ,
        CASE WHEN sum(t.activation) / sum(t.download_completed) IS NULL THEN 0 ELSE concat(round(sum(t.activation) /
        sum(t.download_completed)*100,2),'%') end as activeRate ,
        IFNULL(sum(t.event_register),0) as register,
        CASE WHEN sum(t.charge) / sum(t.event_register) IS NULL THEN 0 ELSE round(sum(t.charge)/sum(t.event_register),3)
        end as activeRegisterCost ,
        CASE WHEN sum(t.event_register) / sum(t.activation) IS NULL THEN 0 ELSE concat(round(sum(t.event_register) /
        sum(t.activation)*100,2),'%') end as activeRegisterRate ,
        IFNULL(sum(t.event_next_day_stay),0) as nextDayOpen,
        CASE WHEN sum(t.event_next_day_stay) / sum(t.activation) IS NULL THEN 0 ELSE
        concat(round(sum(t.event_next_day_stay) / sum(t.activation)*100,2),'%') end as nextDayOpenRate
        FROM
        ctop_kuaishou_report_daily_group t LEFT JOIN (SELECT
        unit_id,
        t3.target_type,
        t3.target_content
        FROM
        ctop_ai_kuaishou_strategy_map_creative t1
        LEFT JOIN ctop_ai_kuaishou_strategy_target_union t2 on t1.strategy_target_union_id=t2.id
        LEFT JOIN ctop_ai_kuaishou_strategy_target_base t3 on t2.strategy_target_id=t3.id
        WHERE
        t1.strategy_id= #{strategyId}
        and t1.video_signature in
        <foreach item="item" index="index" collection="codes"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        GROUP BY t1.unit_id,t3.id) r on t.unit_id=r.unit_id
        where account_id=#{accountId}
        <if test="startDate != null">
            AND t.stat_date >=#{startDate}
        </if>
        <if test="endDate != null">
            AND t.stat_date &lt;=#{endDate}
        </if>
        GROUP BY t.unit_id
    </select>

    <select id="queryMaterialReportBy" resultType="com.alibaba.fastjson.JSONObject">
        SELECT r.creativeCount,
        r.scenes,
        t.signature as code,
        t.cover_url as coverUrl,
        IFNULL(sum(t.charge),0) as cost,
        IFNULL(sum(t.photo_show),0) as photoShow,
        IFNULL(sum(t.photo_click),0) as photoClick,
        CASE WHEN sum(t.photo_click) / sum(t.photo_show) IS NULL THEN 0 ELSE CONCAT( ROUND((sum(t.photo_click) /
        sum(t.photo_show)) * 100,2),'%') END AS clickRate,
        IFNULL(sum(t.aclick),0) as aclick,
        IFNULL(sum(t.bclick),0) as bclick,
        CASE WHEN sum(t.bclick) / sum(t.aclick) IS NULL THEN 0 ELSE CONCAT( ROUND((sum(t.bclick) / sum(t.aclick)) *
        100,2),'%') END AS bClickRate,
        CASE WHEN sum(t.charge) / sum(t.photo_show) IS NULL THEN 0 ELSE ROUND((sum(t.charge) / sum(t.photo_show)) *
        1000, 2 ) END AS cpm ,
        CASE WHEN sum(t.charge) / sum(t.photo_click) IS NULL THEN 0 ELSE ROUND((sum(t.charge) / sum(t.photo_click)), 2)
        END AS cpc ,
        IFNULL(sum(t.activation),0) as active,
        CASE WHEN sum(t.charge) / sum(t.activation) IS NULL THEN 0 ELSE sum(t.charge) / sum(t.activation) end AS
        activeCost ,
        CASE WHEN sum(t.activation) / sum(t.download_completed) IS NULL THEN 0 ELSE concat(round(sum(t.activation) /
        sum(t.download_completed)*100,2),'%') end as activeRate ,
        IFNULL(sum(t.event_register),0) as register,
        CASE WHEN sum(t.charge) / sum(t.event_register) IS NULL THEN 0 ELSE round(sum(t.charge)/sum(t.event_register),3)
        end as activeRegisterCost ,
        CASE WHEN sum(t.event_register) / sum(t.activation) IS NULL THEN 0 ELSE concat(round(sum(t.event_register) /
        sum(t.activation)*100,2),'%') end as activeRegisterRate ,
        IFNULL(sum(t.event_next_day_stay),0) as nextDayOpen,
        CASE WHEN sum(t.event_next_day_stay) / sum(t.activation) IS NULL THEN 0 ELSE
        concat(round(sum(t.event_next_day_stay) / sum(t.activation)*100,2),'%') end as nextDayOpenRate
        FROM
        ctop_kuaishou_report_daily_material t LEFT JOIN (SELECT
        t1.video_signature as code,
        COUNT(t1.video_signature) as creativeCount,
        t4.scenes
        FROM
        ctop_ai_kuaishou_strategy_map_creative t1
        LEFT JOIN ctop_ai_kuaishou_strategy_target_union t2 on t1.strategy_target_union_id=t2.id
        LEFT JOIN ctop_ai_kuaishou_strategy_target_base t3 on t2.strategy_target_id=t3.id
        LEFT JOIN ctop_kuaishou_strategy t4 on t1.strategy_id=t4.id
        WHERE
        t1.strategy_id= #{strategyId}
        and t3.id in
        <foreach item="item" index="index" collection="targetIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        GROUP BY t1.video_signature) r on t.signature=r.`code`
        where account_id=#{accountId}
        <if test="startDate != null">
            AND t.stat_date >=#{startDate}
        </if>
        <if test="endDate != null">
            AND t.stat_date &lt;=#{endDate}
        </if>
        GROUP BY t.signature
    </select>

    <select id="queryAgeChartByTarget" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            t.age_segment as ageSegment,
        IFNULL(sum(t.charge),0) as cost,
        IFNULL(sum(t.photo_show),0) as photoShow,
        IFNULL(sum(t.photo_click),0) as photoClick,
        CASE WHEN sum(t.photo_click) / sum(t.photo_show) IS NULL THEN 0 ELSE CONCAT( ROUND((sum(t.photo_click) /
        sum(t.photo_show)) * 100,2),'%') END AS clickRate,
        IFNULL(sum(t.aclick),0) as aclick,
        IFNULL(sum(t.bclick),0) as bclick,
        CASE WHEN sum(t.bclick) / sum(t.aclick) IS NULL THEN 0 ELSE CONCAT( ROUND((sum(t.bclick) / sum(t.aclick)) *
        100,2),'%') END AS bClickRate,
        CASE WHEN sum(t.charge) / sum(t.photo_show) IS NULL THEN 0 ELSE ROUND((sum(t.charge) / sum(t.photo_show)) *
        1000, 2 ) END AS cpm ,
        CASE WHEN sum(t.charge) / sum(t.photo_click) IS NULL THEN 0 ELSE ROUND((sum(t.charge) / sum(t.photo_click)), 2)
        END AS cpc ,
        IFNULL(sum(t.activation),0) as active,
        CASE WHEN sum(t.charge) / sum(t.activation) IS NULL THEN 0 ELSE sum(t.charge) / sum(t.activation) end AS
        activeCost ,
        CASE WHEN sum(t.activation) / sum(t.download_completed) IS NULL THEN 0 ELSE concat(round(sum(t.activation) /
        sum(t.download_completed)*100,2),'%') end as activeRate ,
        IFNULL(sum(t.event_register),0) as register,
        CASE WHEN sum(t.charge) / sum(t.event_register) IS NULL THEN 0 ELSE round(sum(t.charge)/sum(t.event_register),3)
        end as activeRegisterCost ,
        CASE WHEN sum(t.event_register) / sum(t.activation) IS NULL THEN 0 ELSE concat(round(sum(t.event_register) /
        sum(t.activation)*100,2),'%') end as activeRegisterRate ,
        IFNULL(sum(t.event_next_day_stay),0) as nextDayOpen,
        CASE WHEN sum(t.event_next_day_stay) / sum(t.activation) IS NULL THEN 0 ELSE
        concat(round(sum(t.event_next_day_stay) / sum(t.activation)*100,2),'%') end as nextDayOpenRate
        FROM
            ctop_kuaishou_audience_report_daily_age t
        WHERE
                unit_id IN (
                SELECT
                    t1.unit_id
                FROM
                    ctop_ai_kuaishou_strategy_map_creative t1
                        LEFT JOIN ctop_ai_kuaishou_strategy_target_union t2 ON t1.strategy_target_union_id = t2.id
                        LEFT JOIN ctop_ai_kuaishou_strategy_target_base t3 ON t2.strategy_target_id = t3.id
                        LEFT JOIN ctop_kuaishou_strategy t4 ON t1.strategy_id = t4.id
                WHERE
                    t1.strategy_id = #{strategyId}
                  AND t3.id in
        <foreach item="item" index="index" collection="targetIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
                GROUP BY
                    t1.unit_id
            )
        GROUP BY
            t.age_segment
    </select>

    <select id="queryAgeChartByMaterial" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            t.age_segment as ageSegment,
        IFNULL(sum(t.charge),0) as cost,
        IFNULL(sum(t.photo_show),0) as photoShow,
        IFNULL(sum(t.photo_click),0) as photoClick,
        CASE WHEN sum(t.photo_click) / sum(t.photo_show) IS NULL THEN 0 ELSE CONCAT( ROUND((sum(t.photo_click) /
        sum(t.photo_show)) * 100,2),'%') END AS clickRate,
        IFNULL(sum(t.aclick),0) as aclick,
        IFNULL(sum(t.bclick),0) as bclick,
        CASE WHEN sum(t.bclick) / sum(t.aclick) IS NULL THEN 0 ELSE CONCAT( ROUND((sum(t.bclick) / sum(t.aclick)) *
        100,2),'%') END AS bClickRate,
        CASE WHEN sum(t.charge) / sum(t.photo_show) IS NULL THEN 0 ELSE ROUND((sum(t.charge) / sum(t.photo_show)) *
        1000, 2 ) END AS cpm ,
        CASE WHEN sum(t.charge) / sum(t.photo_click) IS NULL THEN 0 ELSE ROUND((sum(t.charge) / sum(t.photo_click)), 2)
        END AS cpc ,
        IFNULL(sum(t.activation),0) as active,
        CASE WHEN sum(t.charge) / sum(t.activation) IS NULL THEN 0 ELSE sum(t.charge) / sum(t.activation) end AS
        activeCost ,
        CASE WHEN sum(t.activation) / sum(t.download_completed) IS NULL THEN 0 ELSE concat(round(sum(t.activation) /
        sum(t.download_completed)*100,2),'%') end as activeRate ,
        IFNULL(sum(t.event_register),0) as register,
        CASE WHEN sum(t.charge) / sum(t.event_register) IS NULL THEN 0 ELSE round(sum(t.charge)/sum(t.event_register),3)
        end as activeRegisterCost ,
        CASE WHEN sum(t.event_register) / sum(t.activation) IS NULL THEN 0 ELSE concat(round(sum(t.event_register) /
        sum(t.activation)*100,2),'%') end as activeRegisterRate ,
        IFNULL(sum(t.event_next_day_stay),0) as nextDayOpen,
        CASE WHEN sum(t.event_next_day_stay) / sum(t.activation) IS NULL THEN 0 ELSE
        concat(round(sum(t.event_next_day_stay) / sum(t.activation)*100,2),'%') end as nextDayOpenRate
        FROM
            ctop_kuaishou_audience_report_daily_age t
        WHERE
                unit_id IN (
                SELECT
                    unit_id
                FROM
                    ctop_ai_kuaishou_strategy_map_creative
                WHERE
                    strategy_id = #{strategyId}
                  AND video_signature in
        <foreach item="item" index="index" collection="codes"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
                GROUP BY
                    unit_id
            )
        GROUP BY
            t.age_segment
    </select>

    <select id="queryGenderChartByTarget" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t.gender,
        IFNULL(sum(t.charge),0) as cost,
        IFNULL(sum(t.photo_show),0) as photoShow,
        IFNULL(sum(t.photo_click),0) as photoClick,
        CASE WHEN sum(t.photo_click) / sum(t.photo_show) IS NULL THEN 0 ELSE CONCAT( ROUND((sum(t.photo_click) /
        sum(t.photo_show)) * 100,2),'%') END AS clickRate,
        IFNULL(sum(t.aclick),0) as aclick,
        IFNULL(sum(t.bclick),0) as bclick,
        CASE WHEN sum(t.bclick) / sum(t.aclick) IS NULL THEN 0 ELSE CONCAT( ROUND((sum(t.bclick) / sum(t.aclick)) *
        100,2),'%') END AS bClickRate,
        CASE WHEN sum(t.charge) / sum(t.photo_show) IS NULL THEN 0 ELSE ROUND((sum(t.charge) / sum(t.photo_show)) *
        1000, 2 ) END AS cpm ,
        CASE WHEN sum(t.charge) / sum(t.photo_click) IS NULL THEN 0 ELSE ROUND((sum(t.charge) / sum(t.photo_click)), 2)
        END AS cpc ,
        IFNULL(sum(t.activation),0) as active,
        CASE WHEN sum(t.charge) / sum(t.activation) IS NULL THEN 0 ELSE sum(t.charge) / sum(t.activation) end AS
        activeCost ,
        CASE WHEN sum(t.activation) / sum(t.download_completed) IS NULL THEN 0 ELSE concat(round(sum(t.activation) /
        sum(t.download_completed)*100,2),'%') end as activeRate ,
        IFNULL(sum(t.event_register),0) as register,
        CASE WHEN sum(t.charge) / sum(t.event_register) IS NULL THEN 0 ELSE round(sum(t.charge)/sum(t.event_register),3)
        end as activeRegisterCost ,
        CASE WHEN sum(t.event_register) / sum(t.activation) IS NULL THEN 0 ELSE concat(round(sum(t.event_register) /
        sum(t.activation)*100,2),'%') end as activeRegisterRate ,
        IFNULL(sum(t.event_next_day_stay),0) as nextDayOpen,
        CASE WHEN sum(t.event_next_day_stay) / sum(t.activation) IS NULL THEN 0 ELSE
        concat(round(sum(t.event_next_day_stay) / sum(t.activation)*100,2),'%') end as nextDayOpenRate
        FROM
        ctop_kuaishou_audience_report_daily_gender t
        WHERE
        unit_id IN (
        SELECT
        t1.unit_id
        FROM
        ctop_ai_kuaishou_strategy_map_creative t1
        LEFT JOIN ctop_ai_kuaishou_strategy_target_union t2 ON t1.strategy_target_union_id = t2.id
        LEFT JOIN ctop_ai_kuaishou_strategy_target_base t3 ON t2.strategy_target_id = t3.id
        LEFT JOIN ctop_kuaishou_strategy t4 ON t1.strategy_id = t4.id
        WHERE
        t1.strategy_id = #{strategyId}
        AND t3.id in
        <foreach item="item" index="index" collection="targetIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        GROUP BY
        t1.unit_id
        )
        GROUP BY
        t.gender
    </select>

    <select id="queryGenderChartByMaterial" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t.gender,
        IFNULL(sum(t.charge),0) as cost,
        IFNULL(sum(t.photo_show),0) as photoShow,
        IFNULL(sum(t.photo_click),0) as photoClick,
        CASE WHEN sum(t.photo_click) / sum(t.photo_show) IS NULL THEN 0 ELSE CONCAT( ROUND((sum(t.photo_click) /
        sum(t.photo_show)) * 100,2),'%') END AS clickRate,
        IFNULL(sum(t.aclick),0) as aclick,
        IFNULL(sum(t.bclick),0) as bclick,
        CASE WHEN sum(t.bclick) / sum(t.aclick) IS NULL THEN 0 ELSE CONCAT( ROUND((sum(t.bclick) / sum(t.aclick)) *
        100,2),'%') END AS bClickRate,
        CASE WHEN sum(t.charge) / sum(t.photo_show) IS NULL THEN 0 ELSE ROUND((sum(t.charge) / sum(t.photo_show)) *
        1000, 2 ) END AS cpm ,
        CASE WHEN sum(t.charge) / sum(t.photo_click) IS NULL THEN 0 ELSE ROUND((sum(t.charge) / sum(t.photo_click)), 2)
        END AS cpc ,
        IFNULL(sum(t.activation),0) as active,
        CASE WHEN sum(t.charge) / sum(t.activation) IS NULL THEN 0 ELSE sum(t.charge) / sum(t.activation) end AS
        activeCost ,
        CASE WHEN sum(t.activation) / sum(t.download_completed) IS NULL THEN 0 ELSE concat(round(sum(t.activation) /
        sum(t.download_completed)*100,2),'%') end as activeRate ,
        IFNULL(sum(t.event_register),0) as register,
        CASE WHEN sum(t.charge) / sum(t.event_register) IS NULL THEN 0 ELSE round(sum(t.charge)/sum(t.event_register),3)
        end as activeRegisterCost ,
        CASE WHEN sum(t.event_register) / sum(t.activation) IS NULL THEN 0 ELSE concat(round(sum(t.event_register) /
        sum(t.activation)*100,2),'%') end as activeRegisterRate ,
        IFNULL(sum(t.event_next_day_stay),0) as nextDayOpen,
        CASE WHEN sum(t.event_next_day_stay) / sum(t.activation) IS NULL THEN 0 ELSE
        concat(round(sum(t.event_next_day_stay) / sum(t.activation)*100,2),'%') end as nextDayOpenRate
        FROM
        ctop_kuaishou_audience_report_daily_gender t
        WHERE
        unit_id IN (
        SELECT
        unit_id
        FROM
        ctop_ai_kuaishou_strategy_map_creative
        WHERE
        strategy_id = #{strategyId}
        AND video_signature in
        <foreach item="item" index="index" collection="codes"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        GROUP BY
        unit_id
        )
        GROUP BY
        t.gender
    </select>

    <select id="queryRegionChartByTarget" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t.province,
        IFNULL(sum(t.charge),0) as cost,
        IFNULL(sum(t.photo_show),0) as photoShow,
        IFNULL(sum(t.photo_click),0) as photoClick,
        CASE WHEN sum(t.photo_click) / sum(t.photo_show) IS NULL THEN 0 ELSE CONCAT( ROUND((sum(t.photo_click) /
        sum(t.photo_show)) * 100,2),'%') END AS clickRate,
        IFNULL(sum(t.aclick),0) as aclick,
        IFNULL(sum(t.bclick),0) as bclick,
        CASE WHEN sum(t.bclick) / sum(t.aclick) IS NULL THEN 0 ELSE CONCAT( ROUND((sum(t.bclick) / sum(t.aclick)) *
        100,2),'%') END AS bClickRate,
        CASE WHEN sum(t.charge) / sum(t.photo_show) IS NULL THEN 0 ELSE ROUND((sum(t.charge) / sum(t.photo_show)) *
        1000, 2 ) END AS cpm ,
        CASE WHEN sum(t.charge) / sum(t.photo_click) IS NULL THEN 0 ELSE ROUND((sum(t.charge) / sum(t.photo_click)), 2)
        END AS cpc ,
        IFNULL(sum(t.activation),0) as active,
        CASE WHEN sum(t.charge) / sum(t.activation) IS NULL THEN 0 ELSE sum(t.charge) / sum(t.activation) end AS
        activeCost ,
        CASE WHEN sum(t.activation) / sum(t.download_completed) IS NULL THEN 0 ELSE concat(round(sum(t.activation) /
        sum(t.download_completed)*100,2),'%') end as activeRate ,
        IFNULL(sum(t.event_register),0) as register,
        CASE WHEN sum(t.charge) / sum(t.event_register) IS NULL THEN 0 ELSE round(sum(t.charge)/sum(t.event_register),3)
        end as activeRegisterCost ,
        CASE WHEN sum(t.event_register) / sum(t.activation) IS NULL THEN 0 ELSE concat(round(sum(t.event_register) /
        sum(t.activation)*100,2),'%') end as activeRegisterRate ,
        IFNULL(sum(t.event_next_day_stay),0) as nextDayOpen,
        CASE WHEN sum(t.event_next_day_stay) / sum(t.activation) IS NULL THEN 0 ELSE
        concat(round(sum(t.event_next_day_stay) / sum(t.activation)*100,2),'%') end as nextDayOpenRate
        FROM
        ctop_kuaishou_audience_report_daily_province t
        WHERE
        unit_id IN (
        SELECT
        t1.unit_id
        FROM
        ctop_ai_kuaishou_strategy_map_creative t1
        LEFT JOIN ctop_ai_kuaishou_strategy_target_union t2 ON t1.strategy_target_union_id = t2.id
        LEFT JOIN ctop_ai_kuaishou_strategy_target_base t3 ON t2.strategy_target_id = t3.id
        LEFT JOIN ctop_kuaishou_strategy t4 ON t1.strategy_id = t4.id
        WHERE
        t1.strategy_id = #{strategyId}
        AND t3.id in
        <foreach item="item" index="index" collection="targetIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        GROUP BY
        t1.unit_id
        )
        GROUP BY
        t.province
    </select>

    <select id="queryRegionChartByMaterial" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t.province,
        IFNULL(sum(t.charge),0) as cost,
        IFNULL(sum(t.photo_show),0) as photoShow,
        IFNULL(sum(t.photo_click),0) as photoClick,
        CASE WHEN sum(t.photo_click) / sum(t.photo_show) IS NULL THEN 0 ELSE CONCAT( ROUND((sum(t.photo_click) /
        sum(t.photo_show)) * 100,2),'%') END AS clickRate,
        IFNULL(sum(t.aclick),0) as aclick,
        IFNULL(sum(t.bclick),0) as bclick,
        CASE WHEN sum(t.bclick) / sum(t.aclick) IS NULL THEN 0 ELSE CONCAT( ROUND((sum(t.bclick) / sum(t.aclick)) *
        100,2),'%') END AS bClickRate,
        CASE WHEN sum(t.charge) / sum(t.photo_show) IS NULL THEN 0 ELSE ROUND((sum(t.charge) / sum(t.photo_show)) *
        1000, 2 ) END AS cpm ,
        CASE WHEN sum(t.charge) / sum(t.photo_click) IS NULL THEN 0 ELSE ROUND((sum(t.charge) / sum(t.photo_click)), 2)
        END AS cpc ,
        IFNULL(sum(t.activation),0) as active,
        CASE WHEN sum(t.charge) / sum(t.activation) IS NULL THEN 0 ELSE sum(t.charge) / sum(t.activation) end AS
        activeCost ,
        CASE WHEN sum(t.activation) / sum(t.download_completed) IS NULL THEN 0 ELSE concat(round(sum(t.activation) /
        sum(t.download_completed)*100,2),'%') end as activeRate ,
        IFNULL(sum(t.event_register),0) as register,
        CASE WHEN sum(t.charge) / sum(t.event_register) IS NULL THEN 0 ELSE round(sum(t.charge)/sum(t.event_register),3)
        end as activeRegisterCost ,
        CASE WHEN sum(t.event_register) / sum(t.activation) IS NULL THEN 0 ELSE concat(round(sum(t.event_register) /
        sum(t.activation)*100,2),'%') end as activeRegisterRate ,
        IFNULL(sum(t.event_next_day_stay),0) as nextDayOpen,
        CASE WHEN sum(t.event_next_day_stay) / sum(t.activation) IS NULL THEN 0 ELSE
        concat(round(sum(t.event_next_day_stay) / sum(t.activation)*100,2),'%') end as nextDayOpenRate
        FROM
        ctop_kuaishou_audience_report_daily_province t
        WHERE
        unit_id IN (
        SELECT
        unit_id
        FROM
        ctop_ai_kuaishou_strategy_map_creative
        WHERE
        strategy_id = #{strategyId}
        AND video_signature in
        <foreach item="item" index="index" collection="codes"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        GROUP BY
        unit_id
        )
        GROUP BY
        t.province
    </select>

</mapper>
