<?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.crawler.modules.pangolin.mapper.PangolinReportAppHourMapper">
    <select id="getComparedDate" resultType="com.alibaba.fastjson.JSONObject">
        select
        <if test="compareTime1!=null">
            sum(if(date = '${compareTime1}',num,0)) as '${compareTime1}',
        </if>
        <if test="compareTime2!=null">
            sum(if(date = '${compareTime2}',num,0)) as '${compareTime2}',
        </if>
            sum(if(date = '${today}',num,0)) as 'today',
            sum(if(date = '${yesterday}',num,0)) as 'yesterday',
            sum(if(date = '${sevenDaysAgo}',num,0)) as 'sevendaysAgo',
            hour
       from (select num,date,hour
from ctop_pangolin_report_app_hour appHourActive
         left join ctop_pangolin_app app on app.id = appHourActive.app_id
         left join ctop_bind_account_login login on login.account_name = app.account_name
where appHourActive.type = #{type}
and date =  #{today} and hour = #{hour}
<if test="appId!=null">
    and app.id = #{appId}
</if>
<if test="userId!=null">
    and login.user_id = #{userId}
</if>
group by appHourActive.date, appHourActive.hour
union all select num,date,hour
from ctop_pangolin_report_app_hour appHourActive
         left join ctop_pangolin_app app on app.id = appHourActive.app_id
         left join ctop_bind_account_login login on login.account_name = app.account_name
where appHourActive.type = #{type}
        and date =  #{yesterday} and hour = #{hour}
        <if test="appId!=null">
            and app.id = #{appId}
        </if>
        <if test="userId!=null">
            and login.user_id = #{userId}
        </if>
        group by appHourActive.date, appHourActive.hour
union all select num,date,hour
from ctop_pangolin_report_app_hour appHourActive
         left join ctop_pangolin_app app on app.id = appHourActive.app_id
         left join ctop_bind_account_login login on login.account_name = app.account_name
where appHourActive.type = #{type}
        and date =  #{sevenDaysAgo} and hour = #{hour}
        <if test="appId!=null">
            and app.id = #{appId}
        </if>
        <if test="userId!=null">
            and login.user_id = #{userId}
        </if>
        group by appHourActive.date, appHourActive.hour
<if test="compareTime1!=null">
    union all
    select num,date,hour
    from ctop_pangolin_report_app_hour appHourActive
    left join ctop_pangolin_app app on app.id = appHourActive.app_id
    left join ctop_bind_account_login login on login.account_name = app.account_name
    where appHourActive.type = #{type}
    and date =  #{compareTime1} and hour = #{hour}
    <if test="appId!=null">
        and app.id = #{appId}
    </if>
    <if test="userId!=null">
        and login.user_id = #{userId}
    </if>
    group by appHourActive.date, appHourActive.hour
</if>
        <if test="compareTime2!=null">
            union all
            select num,date,hour
            from ctop_pangolin_report_app_hour appHourActive
            left join ctop_pangolin_app app on app.id = appHourActive.app_id
            left join ctop_bind_account_login login on login.account_name = app.account_name
            where appHourActive.type = #{type}
            and date =  #{compareTime2} and hour = #{hour}
            <if test="appId!=null">
                and app.id = #{appId}
            </if>
            <if test="userId!=null">
                and login.user_id = #{userId}
            </if>
            group by appHourActive.date, appHourActive.hour
        </if>
        )a group by hour
    </select>
</mapper>
