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

    <select id="queryTodaySumReportBy" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ROUND(sum(charge),2) cost,
        sum(`show`) photoShow,
        sum(photo_click) photoClick,
        sum(aClick) as aClick,
        max(`hour`) maxHour
        FROM application.kuaishou_account_report_hourly_dw
        WHERE
        stat_date = DATE_FORMAT(#{statDate},'%Y%m%d')
        AND account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

    <select id="queryTodaySumByHourGroupAccount" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        t1.account_id as accountId,
        t1.account_name as authName,
        IFNULL(key_action,0) as 'keyAction',
        IFNULL(key_action_cost,0.00) as 'keyActionCost',
        ${filed}
        FROM
        application.kuaishou_account_report_hourly_dw t1
        WHERE t1.stat_date = DATE_FORMAT(#{statDate},'%Y%m%d')
        and t1.`hour` &lt;= #{hour}
        AND t1.account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        group by t1.account_id
        order by ${target} ${order}
    </select>

    <select id="queryTodaySumByHour" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ROUND(sum(charge),2) cost,
        sum(`show`) photoShow,
        sum(photo_click) photoClick,
        sum(aClick) as aClick
        FROM application.kuaishou_account_report_hourly_dw
        WHERE
        stat_date = DATE_FORMAT(#{statDate},'%Y%m%d')
        AND `hour` &lt;= #{hour}
        AND account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

    <select id="querySumByHour" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        IFNULL(key_action,0) as 'keyAction',
        IFNULL(key_action_cost,0.00) as 'keyActionCost',
        ${filed}
        FROM
        application.kuaishou_account_report_hourly_dw t1
        WHERE
        stat_date = DATE_FORMAT(#{statDate},'%Y%m%d')
        and `hour` &lt;= #{hour}
        AND account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

    <select id="queryTodayDetailReportBy" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ROUND(sum(charge),2) cost,
        sum(`show`) photoShow,
        sum(photo_click) photoClick,
        sum(aClick) as aClick,
        `hour` as statHour
        FROM application.kuaishou_account_report_hourly_dw
        WHERE
        stat_date = DATE_FORMAT(#{statDate},'%Y%m%d')
        AND account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        GROUP BY
        `hour`
        ORDER BY
        `hour` ASC
    </select>

    <select id="queryAllSumByStartEndDate" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        IFNULL(key_action,0) as 'keyAction',
        IFNULL(key_action_cost,0.00) as 'keyActionCost',
        ${filed}
        FROM application.kuaishou_account_report_daily_dw t1
        WHERE  t1.stat_date &lt;= DATE_FORMAT(#{endDate},'%Y%m%d')
        AND t1.stat_date &gt;= DATE_FORMAT(#{startDate},'%Y%m%d')
        AND t1.account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

    <select id="querySumByStartEndDate" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ROUND(sum(charge),2) cost,
        sum(`show`) photoShow,
        sum(photo_click) photoClick,
        sum(aClick) as aClick
        FROM
        application.kuaishou_account_report_daily_dw
        WHERE stat_date &lt;= DATE_FORMAT(#{endDate},'%Y%m%d')
        AND stat_date &gt;= DATE_FORMAT( #{startDate},'%Y%m%d')
        AND account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

    <select id="querySumByDateGroupAccount" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        DATE_FORMAT(t1.stat_date,'%Y-%m-%d') as statDate,
        t1.account_id as accountId,
        t1.account_name as authName,
        IFNULL(key_action,0) as 'keyAction',
        IFNULL(key_action_cost,0.00) as 'keyActionCost',
        ${filed}
        FROM
        application.kuaishou_account_report_daily_dw  t1
        WHERE
        t1.stat_date &lt;= DATE_FORMAT(#{endDate},'%Y%m%d')
        AND t1.stat_date &gt;= DATE_FORMAT(#{startDate},'%Y%m%d')
        AND t1.account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        group by t1.account_id
        order by ${target} ${order}
    </select>

    <select id="querySumByDateGroupAccountAndDate" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        IFNULL(key_action,0) as 'keyAction',
        IFNULL(key_action_cost,0.00) as 'keyActionCost',
        DATE_FORMAT(t1.stat_date,'%Y-%m-%d') as statDate,
        t1.account_id as accountId,
        t1.account_name as authName,
        ${filed}
        FROM
        application.kuaishou_account_report_daily_dw  t1
        WHERE
        t1.stat_date &lt;= DATE_FORMAT(#{endDate},'%Y%m%d')
        AND t1.stat_date &gt;= DATE_FORMAT(#{startDate},'%Y%m%d')
        AND t1.account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        group by t1.stat_date
        order by ${target} ${order}
    </select>

    <select id="queryDetailByStartEndDate" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
        ROUND(sum(charge),2) cost,
        sum(`show`) photoShow,
        sum(photo_click) photoClick,
        sum(aClick) as aClick,
        DATE_FORMAT(stat_date,'%Y-%m-%d') as statDate
        FROM
        application.kuaishou_account_report_daily_dw
        WHERE stat_date &lt;= DATE_FORMAT(#{endDate},'%Y%m%d')
        AND stat_date &gt;= DATE_FORMAT(#{startDate},'%Y%m%d')
        AND account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        group by stat_date
        order by stat_date
    </select>

    <select id="queryDetailGroupMonthByDate" resultType="com.alibaba.fastjson.JSONObject">

        SELECT
        ROUND(sum(charge),2) cost,
        sum(`show`) photoShow,
        sum(photo_click) photoClick,
        sum(aClick) as aClick,
        DATE_FORMAT(stat_date, '%Y-%m') statDate
        FROM
        application.kuaishou_account_report_daily_dw
        WHERE stat_date &lt;=  DATE_FORMAT(#{endDate},'%Y%m%d')
        AND stat_date &gt;= DATE_FORMAT(#{startDate},'%Y%m%d')
        AND account_id in
        <foreach item="item" index="index" collection="accountIds"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
        group by DATE_FORMAT(stat_date, '%Y-%m')
        order by stat_date
    </select>

    <select id="queryDetailGroupDayByMonth" resultType="com.alibaba.fastjson.JSONObject">
        SELECT
            stat_date as statDate,
            SUBSTR(stat_date FROM 9 FOR 2) day
        FROM ctop_kuaishou_report_daily_account
        WHERE stat_date like '${month}%'
        group by DATE_FORMAT(stat_date, '%Y-%m-%d')
        order by stat_date asc
    </select>

</mapper>
