package com.zanxiang.game.data.serve.service.impl;

import com.zanxiang.erp.base.ErpServer;
import com.zanxiang.erp.base.rpc.ISysUserRpc;
import com.zanxiang.game.data.serve.pojo.dto.RoleCombatRankingDTO;
import com.zanxiang.game.data.serve.pojo.dto.RoleRechargeRankingDTO;
import com.zanxiang.game.data.serve.pojo.enums.OrderByEnum;
import com.zanxiang.game.data.serve.service.IRoleManageService;
import com.zanxiang.game.data.serve.utils.Page;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.dubbo.config.annotation.DubboReference;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.pager.Pager;
import org.nutz.dao.sql.Criteria;
import org.nutz.dao.sql.Sql;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @author tianhua
 * @time 2023/9/8
 * @Description 角色运营管理逻辑处理
 **/
@Service
@Slf4j
public class RoleManageServiceImpl implements IRoleManageService {

    @Autowired
    private Dao dao;

    @DubboReference(providedBy = ErpServer.SERVER_DUBBO_NAME)
    private ISysUserRpc sysUserRpc;

    /**
     * 角色充值排行榜
     * @param dto RoleRechargeRankingDTO
     * @return Page<List<Map<String, Object>>>
     */
    @Override
    public Page<Map> getRoleRechargeRanking(RoleRechargeRankingDTO dto) {
        //默认查询不合服的数据
        if (dto.getIsMergeServer() == null) {
            dto.setIsMergeServer(Boolean.FALSE);
        }
        //创建查询条件 给主表使用
        Criteria criA = Cnd.cri();
        if (dto.getGameId() != null) {
            criA.where().andEquals("role_reg_game_id", dto.getGameId());
        }
        if (dto.getParentGameId() != null) {
            //角色注册父游戏
            criA.where().andEquals("role_reg_parent_game_id", dto.getParentGameId());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            criA.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (StringUtils.isNotBlank(dto.getRoleId())) {
            criA.where().andEquals("role_id", dto.getRoleId());
        }
        if (StringUtils.isNotBlank(dto.getRoleName())) {
            criA.where().andLike("role_name", dto.getRoleName());
        }
        if (CollectionUtils.isNotEmpty(dto.getServerIds()) && !dto.getIsMergeServer()) {
            //区服列表查询,不合服的区服
            criA.where().andIn("server_id", dto.getServerIds().toArray(String[]::new));
        }
        if (CollectionUtils.isNotEmpty(dto.getServerIds()) && dto.getIsMergeServer()) {
            //合服区服列表查询
            criA.where().andIn("merge_server_id", dto.getServerIds().toArray(String[]::new));
        }
        if (dto.getCreateRoleBeginDate() != null && dto.getCreateRoleEndDate() != null) {
            //角色创建时间
            criA.where().andBetween("DATE(role_create_time)", dto.getCreateRoleBeginDate(), dto.getCreateRoleEndDate());
        }
        if (dto.getVipLevel() != null) {
            criA.where().andEquals("vip_level", dto.getVipLevel());
        }
        if (dto.getIsSendMail() != null) {
            criA.where().andEquals("is_send_mail", dto.getIsSendMail());
        }
        if (dto.getIsChange() != null) {
            //是否转端
            criA.where().andEquals("is_change_game_type", dto.getIsChange());
        }
        if (StringUtils.isNotBlank(dto.getWeChatCompany())) {
            //企业微信号
            criA.where().andLike("add_corp_user_id", dto.getWeChatCompany());
        }
        if (StringUtils.isNotBlank(dto.getWeChat())) {
            //客户微信
            criA.where().andLike("user_wechat", dto.getWeChat());
        }
        if (StringUtils.isNotBlank(dto.getPhone())) {
            //客户手机号
            criA.where().andEquals("user_phone", dto.getPhone());
        }
        if (dto.getGsId() != null) {
            criA.where().andEquals("gs_id", dto.getGsId());
        }
        if (dto.getPitcherId() != null) {
            //投手
            criA.where().andEquals("put_user_id", dto.getPitcherId());
        }
        if (dto.getOperatorId() != null) {
            //运营
            criA.where().andEquals("oper_user_id", dto.getOperatorId());
        }
        if (dto.getCustomerServerId() != null) {
            //客服
            criA.where().andEquals("customer_service_id", dto.getCustomerServerId());
        }
        if (dto.getTotalRechargeMin() != null) {
            //累计充值金额最小值
            criA.where().andGTE("amount", dto.getTotalRechargeMin());
        }
        if (dto.getTotalRechargeMax() != null) {
            //累计充值金额最大值
            criA.where().andLTE("amount", dto.getTotalRechargeMax());
        }
        if (dto.getIsRemoveGame() != null) {
            //是否退游
            criA.where().andEquals("is_remove_game", dto.getIsRemoveGame());
        }
        if (dto.getIsRemoveGameForSystem() != null) {
            //是否退游(系统判定)
            criA.where().andEquals("is_remove_game_for_system", dto.getIsRemoveGameForSystem());
        }
        if (dto.getIsWakeUp() != null) {
            //是否唤醒
            criA.where().andEquals("is_wake_up", dto.getIsWakeUp());
        }
        if (dto.getIsAddCorpWechat() != null) {
            //是否添加企微
            criA.where().andEquals("is_add_corp_wechat", dto.getIsAddCorpWechat());
        }
        //给充值时间查询条件
        Criteria criTodayAmount = Cnd.cri();
        //查询充值成功的
        criTodayAmount.where().andEquals("status", 2);
        if (dto.getRechargeBeginDate() != null && dto.getRechargeEndDate() != null) {
            //充值时间限制
            criTodayAmount.where().andBetween("DATE(pay_time)", dto.getRechargeBeginDate(), dto.getRechargeEndDate());
        }
        //分页对象
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        //查询总记录数
        Sql countSql = Sqls.create(getCountNumSql(criA, criTodayAmount));
        countSql.setCallback(Sqls.callback.integer());
        dao.execute(countSql);
        pager.setRecordCount(countSql.getInt());
        //主表添加排序条件
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isBlank(dto.getSortFiled())) {
            criA.getOrderBy().orderBy("amount", dto.getSortType());
        } else {
            criA.getOrderBy().orderBy(dto.getSortFiled(), dto.getSortType());
        }
        //创建sql
        Sql sql = Sqls.create(getRoleRechargeRankingSql(criA, criTodayAmount));
        sql.setCallback(Sqls.callback.maps());
        sql.setPager(pager);
        dao.execute(sql);
        List<Map> list = sql.getList(Map.class).stream().map(map -> {
            getNameById(map);
            //去除‘null’字符串
            if ("null".equals(map.get("add_corp_user_id"))) {
                map.put("add_corp_user_id", null);
            }
            if ("null".equals(map.get("user_wechat"))) {
                map.put("user_wechat", null);
            }
            if ("null".equals(map.get("remark"))) {
                map.put("remark", null);
            }
            if ("null".equals(map.get("user_phone"))) {
                map.put("user_phone", null);
            }
            if ("null".equals(map.get("country"))) {
                map.put("country", null);
            }
            return map;
        }).collect(Collectors.toList());

        //返回结果
        return new Page<>(list, pager);
    }

    /**
     * 角色战力排行榜
     * @param dto RoleCombatRankingDTO
     * @return Page<List<Map<String, Object>>>
     */
    @Override
    public Page<Map> getRoleCombatRanking(RoleCombatRankingDTO dto) {

        //是否查询合服数据(默认查询合服数据)
        boolean isQueryMerge = true;
        //如果不传值，查看的是原始服数据
        if (dto.getIsMergeServer() == null || Boolean.FALSE.equals(dto.getIsMergeServer())) {
            isQueryMerge = false;
        }
        //是否查询超父游戏数据（默认查询超父游戏数据）
        boolean isSuperParentGame = true;
        if (dto.getParentGameId() != null) {
            isSuperParentGame = false;
        }
        //查询区服相关数据
        if (isQueryMerge) {
            if (isSuperParentGame) {
                //查询合服-超父
                return getMergeSuperParentData(dto);
            } else {
                //查询合服-父游戏
                return getMergeParentData(dto);
            }
        } else {
            if (isSuperParentGame) {
                //查询原始服-超父
                return getOriginSuperParentData(dto);
            } else {
                //查询原始服-父游戏
                return getOriginParentData(dto);
            }
        }
    }

    /**
     * 通过id获取 GS、运营、客服人员、投手名字
     * @param dataMap dataMap
     * @return String
     */
    private Map<String, Object> getNameById(Map<String, Object> dataMap){

        //投手名
        if (dataMap.get("put_user_id") != null) {
            dataMap.put("put_user_name",
                    sysUserRpc.getById(Long.valueOf((String) dataMap.get("put_user_id"))).getData().getNickname());
        }
        //运营人员名
        if (dataMap.get("oper_user_id") != null) {
            dataMap.put("oper_user_name",
                    sysUserRpc.getById((Long) dataMap.get("oper_user_id")).getData().getNickname());
        }
        //GS人员名
        if (dataMap.get("gs_id") != null) {
            dataMap.put("gs_name",
                    sysUserRpc.getById((Long) dataMap.get("gs_id")).getData().getNickname());
        }
        //客服人员名
        if (dataMap.get("customer_service_id") != null) {
            dataMap.put("customer_service_name",
                    sysUserRpc.getById((Long) dataMap.get("customer_service_id")).getData().getNickname());
        }

        return dataMap;
    }

    /**
     * 原始服-父游戏
     * @param dto RoleCombatRankingDTO
     * @return Page<Map>
     */
    private Page<Map> getOriginParentData(RoleCombatRankingDTO dto) {
        //查询条件
        Criteria cri = Cnd.cri();
        if (dto.getParentGameId() != null) {
            //父游戏ID
            cri.where().andEquals("parent_game_id", dto.getParentGameId());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //原始服开服时间
            cri.where().andBetween("dt", dto.getBeginDate(), dto.getEndDate());
        }
        if (CollectionUtils.isNotEmpty(dto.getServerIds())) {
            //原始区服列表
            cri.where().andIn("server_id", dto.getServerIds().toArray(String[]::new));
        }
        if (StringUtils.isNotBlank(dto.getServerName())) {
            //区服名称
            cri.where().andLike("server_name", dto.getServerName());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        //拼接分组条件
        cri.getGroupBy().groupBy("source_system" , "server_id" , "boss_server_id" , "parent_game_id" , "super_game_id");
        //分页
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        //查询总记录数
        Sql countSql = Sqls.create(getRoleCombatRankServerDataSqlACount(cri));
        countSql.setCallback(Sqls.callback.integer());
        dao.execute(countSql);
        pager.setRecordCount(countSql.getInt());
        //排序条件
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isNotBlank(dto.getSortFiled())) {
            cri.getOrderBy().orderBy(dto.getSortFiled(), dto.getSortType());
        } else {
            //默认排序
            cri.getOrderBy().orderBy("dt", dto.getSortType());
            cri.getOrderBy().orderBy("server_combat_num", dto.getSortType());
        }
        //区服数据
        Sql sql = Sqls.create(getRoleCombatRankServerDataSqlA() + cri);
        sql.setCallback(Sqls.callback.maps());
        sql.setPager(pager);
        dao.execute(sql);

        //获取角色信息
        //查询条件
        Criteria criRole = Cnd.cri();
        if (dto.getParentGameId() != null) {
            //父游戏ID
            criRole.where().andEquals("parent_game_id", dto.getParentGameId());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //原始服开服时间
            criRole.where().andBetween("dt", dto.getBeginDate(), dto.getEndDate());
        }
        if (CollectionUtils.isNotEmpty(dto.getServerIds())) {
            //原始服区服列表
            criRole.where().andIn("server_id", dto.getServerIds().toArray(String[]::new));
        }
        if (StringUtils.isNotBlank(dto.getServerName())) {
            //区服名称
            cri.where().andLike("server_name", dto.getServerName());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            criRole.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (dto.getRankingNum() != null) {
            //排名的个数
            criRole.where().andLTE("rank_num", dto.getRankingNum());
        } else {
            //排名的个数
            criRole.where().andLTE("rank_num", 5);
        }
        Sql sqlRole = Sqls.create(getRoleCombatRankRoleDataSqlA() + criRole);
        sqlRole.setCallback(Sqls.callback.maps());
        dao.execute(sqlRole);
        //得到数据Map   （原始服ID-合服ID-父游戏ID）- （角色信息）
        Map<String, List<Map<String, Object>>> roleDataMap = new HashMap<>();
        //得到结果
        List<Map> listRole = sqlRole.getList(Map.class).stream().map(map -> {
            StringBuilder sb = new StringBuilder(StringUtils.EMPTY);
            sb.append(map.get("server_id"))
                    .append("-")
                    .append((String)map.get("boss_server_id"))
                    .append("-")
                    .append((String)map.get("parent_game_id"));
            if (roleDataMap.get(sb.toString()) != null) {
                //含有改主键 添加数据
                roleDataMap.get(sb.toString()).add(map);
            } else {
                //不含有该主键
                List<Map<String, Object>> tempList = new ArrayList<>();
                tempList.add(map);
                roleDataMap.put(String.valueOf(sb), tempList);
            }
            return map;
        }).collect(Collectors.toList());

        List<Map> list = sql.getList(Map.class).stream().map(map -> {
            StringBuilder sb = new StringBuilder(StringUtils.EMPTY);
            sb.append(map.get("server_id"))
                    .append("-")
                    .append((String)map.get("boss_server_id"))
                    .append("-")
                    .append((String)map.get("parent_game_id"));

            if (roleDataMap.get(sb.toString()) != null) {
                //将排名信息添加
                map.put("role_rank_date", roleDataMap.get(sb.toString()));
            }
            return map;

        }).collect(Collectors.toList());

        return new Page<>(list, pager);
    }

    /**
     * 合服-父游戏
     * @param dto RoleCombatRankingDTO
     * @return Page<Map>
     */
    private Page<Map> getMergeParentData(RoleCombatRankingDTO dto) {
        //查询条件
        Criteria cri = Cnd.cri();
        if (dto.getParentGameId() != null) {
            //父游戏ID
            cri.where().andEquals("parent_game_id", dto.getParentGameId());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //合服开服时间
            cri.where().andBetween("DATE(merge_time)", dto.getBeginDate(), dto.getEndDate());
        }
        if (CollectionUtils.isNotEmpty(dto.getServerIds())) {
            //合服区服列表
            cri.where().andIn("boss_server_id", dto.getServerIds().toArray(String[]::new));
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (StringUtils.isNotBlank(dto.getServerName())) {
            //区服名称
            cri.where().andLike("boss_server_name", dto.getServerName());
        }
        if (CollectionUtils.isEmpty(dto.getServerIds()) && StringUtils.isBlank(dto.getServerName())) {
            //默认不传查询的区服，只看最新的合服数据
            cri.where().andEquals("is_merge", 0);
        }
        //拼接分组条件
        cri.getGroupBy().groupBy("source_system" , "boss_server_id" , "parent_game_id" , "super_game_id");
        //分页
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        //查询总记录数
        Sql countSql = Sqls.create(getRoleCombatRankServerDataSqlBCount(cri));
        countSql.setCallback(Sqls.callback.integer());
        dao.execute(countSql);
        pager.setRecordCount(countSql.getInt());
        //排序条件
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isNotBlank(dto.getSortFiled())) {
            cri.getOrderBy().orderBy(dto.getSortFiled(), dto.getSortType());
        } else {
            //默认排序
            cri.getOrderBy().orderBy("merge_time", dto.getSortType());
            cri.getOrderBy().orderBy("server_combat_num", dto.getSortType());
        }
        //查询出区服相关数据
        Sql sql = Sqls.create(getRoleCombatRankServerDataSqlB() + cri);
        sql.setCallback(Sqls.callback.maps());
        sql.setPager(pager);
        dao.execute(sql);
        //获取角色信息
        //查询条件
        Criteria criRole = Cnd.cri();
        if (dto.getParentGameId() != null) {
            //父游戏ID
            criRole.where().andEquals("parent_game_id", dto.getParentGameId());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //开服时间
            criRole.where().andBetween("DATE(merge_time)", dto.getBeginDate(), dto.getEndDate());
        }
        if (CollectionUtils.isNotEmpty(dto.getServerIds())) {
            //合服区服列表
            criRole.where().andIn("boss_server_id", dto.getServerIds().toArray(String[]::new));
        }
        if (StringUtils.isNotBlank(dto.getServerName())) {
            //区服名称
            cri.where().andLike("boss_server_name", dto.getServerName());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            criRole.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (dto.getRankingNum() != null) {
            //排名的个数
            criRole.where().andLTE("rank_num", dto.getRankingNum());
        } else {
            //排名的个数
            criRole.where().andLTE("rank_num", 5);
        }
        Sql sqlRole = Sqls.create(getRoleCombatRankRoleDataSqlB() + criRole);
        sqlRole.setCallback(Sqls.callback.maps());
        dao.execute(sqlRole);
        //得到数据Map   （合服ID-父游戏ID）- （角色信息）
        Map<String, List<Map<String, Object>>> roleDataMap = new HashMap<>();
        //获取到相关角色排名信息
        List<Map> listRole = sqlRole.getList(Map.class).stream().map(dataMap -> {
            //拼接主键信息
            StringBuilder sb = new StringBuilder(StringUtils.EMPTY);
            sb.append((String)dataMap.get("boss_server_id"))
                    .append("-")
                    .append((String)dataMap.get("parent_game_id"));
            if (roleDataMap.get(sb.toString()) != null) {
                //含有改主键 添加数据
                roleDataMap.get(sb.toString()).add(dataMap);
            } else {
                //不含有该主键
                List<Map<String, Object>> tempList = new ArrayList<>();
                tempList.add(dataMap);
                roleDataMap.put(String.valueOf(sb), tempList);
            }
            return dataMap;
        }).collect(Collectors.toList());
        //将排名信息拼接到区服信息上
        List<Map> list = sql.getList(Map.class).stream().map(dataMap -> {
            //主键
            StringBuilder sb = new StringBuilder(StringUtils.EMPTY);
            sb.append((String)dataMap.get("boss_server_id"))
                    .append("-")
                    .append((String)dataMap.get("parent_game_id"));
            if (roleDataMap.get(sb.toString()) != null) {
                //将排名信息添加
                dataMap.put("role_rank_date", roleDataMap.get(sb.toString()));
            }
            //清除"null"字符串
            if ("null".equals(dataMap.get("son_server_ids"))) {
                dataMap.put("son_server_ids", StringUtils.EMPTY);
            }
            if ("null".equals(dataMap.get("source_server_ids"))) {
                dataMap.put("source_server_ids", StringUtils.EMPTY);
            }
            return dataMap;
        }).collect(Collectors.toList());

        return new Page<>(list, pager);
    }

    /**
     * 原始服-超父游戏
     * @param dto RoleCombatRankingDTO
     * @return Page<Map>
     */
    private Page<Map> getOriginSuperParentData(RoleCombatRankingDTO dto) {
        //查询条件
        Criteria cri = Cnd.cri();
        if (dto.getSuperParentGameId() != null) {
            //超父游戏ID
            cri.where().andEquals("super_game_id", dto.getSuperParentGameId());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //原始服开服时间
            cri.where().andBetween("dt", dto.getBeginDate(), dto.getEndDate());
        }
        if (CollectionUtils.isNotEmpty(dto.getServerIds())) {
            //原始区服列表
            cri.where().andIn("server_id", dto.getServerIds().toArray(String[]::new));
        }
        if (StringUtils.isNotBlank(dto.getServerName())) {
            //区服名称
            cri.where().andLike("server_name", dto.getServerName());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        //原始服可以参与多次合服，只看最新一次的合服的原始服信息
        cri.where().andEquals("is_merge", 0);
        /*//拼接分组条件
        cri.getGroupBy().groupBy("source_system" , "server_id" , "boss_server_id" , "super_game_id");*/
        //分页
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        //查询总记录数
        Sql countSql = Sqls.create(getRoleCombatRankServerDataSqlCCount(cri));
        countSql.setCallback(Sqls.callback.integer());
        dao.execute(countSql);
        pager.setRecordCount(countSql.getInt());
        //排序条件
        if ("min_server_start_days".equals(dto.getSortFiled())) {
            //原始区服没有 最小区服排序条件
            dto.setSortFiled(null);
        }
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isNotBlank(dto.getSortFiled())) {
            cri.getOrderBy().orderBy(dto.getSortFiled(), dto.getSortType());
        } else {
            //默认排序
            cri.getOrderBy().orderBy("dt", dto.getSortType());
            cri.getOrderBy().orderBy("server_combat_num", dto.getSortType());
        }
        //区服数据
        Sql sql = Sqls.create(getRoleCombatRankServerDataSqlC() + cri);
        sql.setCallback(Sqls.callback.maps());
        sql.setPager(pager);
        dao.execute(sql);

        //获取角色信息
        //查询条件
        Criteria criRole = Cnd.cri();
        if (dto.getSuperParentGameId() != null) {
            //超父游戏ID
            criRole.where().andEquals("super_game_id", dto.getSuperParentGameId());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //原始服开服时间
            criRole.where().andBetween("dt", dto.getBeginDate(), dto.getEndDate());
        }
        if (CollectionUtils.isNotEmpty(dto.getServerIds())) {
            //原始服区服列表
            criRole.where().andIn("server_id", dto.getServerIds().toArray(String[]::new));
        }
        if (StringUtils.isNotBlank(dto.getServerName())) {
            //区服名称
            cri.where().andLike("server_name", dto.getServerName());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            criRole.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (dto.getRankingNum() != null) {
            //排名的个数
            criRole.where().andLTE("rank_num", dto.getRankingNum());
        } else {
            //排名的个数
            criRole.where().andLTE("rank_num", 5);
        }
        Sql sqlRole = Sqls.create(getRoleCombatRankRoleDataSqlC() + criRole);
        sqlRole.setCallback(Sqls.callback.maps());
        dao.execute(sqlRole);
        //得到数据Map   （原始服ID-超父游戏ID）- （角色信息）
        Map<String, List<Map<String, Object>>> roleDataMap = new HashMap<>();
        //得到结果
        List<Map> listRole = sqlRole.getList(Map.class).stream().map(map -> {
            StringBuilder sb = new StringBuilder(StringUtils.EMPTY);
            sb.append(map.get("server_id"))
                    .append("-")
                    .append((String)map.get("super_game_id"));
            if (roleDataMap.get(sb.toString()) != null) {
                //含有改主键 添加数据
                roleDataMap.get(sb.toString()).add(map);
            } else {
                //不含有该主键
                List<Map<String, Object>> tempList = new ArrayList<>();
                tempList.add(map);
                roleDataMap.put(String.valueOf(sb), tempList);
            }
            return map;
        }).collect(Collectors.toList());

        List<Map> list = sql.getList(Map.class).stream().map(map -> {
            StringBuilder sb = new StringBuilder(StringUtils.EMPTY);
            sb.append(map.get("server_id"))
                    .append("-")
                    .append((String)map.get("super_game_id"));

            if (roleDataMap.get(sb.toString()) != null) {
                //将排名信息添加
                map.put("role_rank_date", roleDataMap.get(sb.toString()));
            }
            return map;

        }).collect(Collectors.toList());

        return new Page<>(list, pager);
    }

    /**
     * 合服-超父游戏
     * @param dto RoleCombatRankingDTO
     * @return Page<Map>
     */
    private Page<Map> getMergeSuperParentData(RoleCombatRankingDTO dto) {
        //查询条件
        Criteria cri = Cnd.cri();
        if (dto.getSuperParentGameId() != null) {
            //超父游戏ID
            cri.where().andEquals("super_game_id", dto.getSuperParentGameId());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //合服开服时间
            cri.where().andBetween("DATE(dt)", dto.getBeginDate(), dto.getEndDate());
        }
        if (CollectionUtils.isNotEmpty(dto.getServerIds())) {
            //合服区服列表
            cri.where().andIn("server_id", dto.getServerIds().toArray(String[]::new));
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            cri.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (StringUtils.isNotBlank(dto.getServerName())) {
            //区服名称
            cri.where().andLike("server_name", dto.getServerName());
        }
        if (CollectionUtils.isEmpty(dto.getServerIds()) && StringUtils.isBlank(dto.getServerName())) {
            //默认不传查询的区服，只看最新的合服数据
            cri.where().andEquals("is_merge", 0);
        }
        /*//拼接分组条件
        cri.getGroupBy().groupBy("source_system" , "boss_server_id" , "super_game_id");*/
        //分页
        Pager pager = dao.createPager(dto.getPageNum(), dto.getPageSize());
        //查询总记录数
        Sql countSql = Sqls.create(getRoleCombatRankServerDataSqlDCount(cri));
        countSql.setCallback(Sqls.callback.integer());
        dao.execute(countSql);
        pager.setRecordCount(countSql.getInt());
        //排序条件
        if (StringUtils.isBlank(dto.getSortType())) {
            dto.setSortType(OrderByEnum.DESC.getOrderType());
        }
        if (StringUtils.isNotBlank(dto.getSortFiled())) {
            cri.getOrderBy().orderBy(dto.getSortFiled(), dto.getSortType());
        } else {
            //默认排序
            cri.getOrderBy().orderBy("min_server_start_days", dto.getSortType());
            cri.getOrderBy().orderBy("server_combat_num", dto.getSortType());
        }
        //查询出区服相关数据
        Sql sql = Sqls.create(getRoleCombatRankServerDataSqlD() + cri);
        sql.setCallback(Sqls.callback.maps());
        sql.setPager(pager);
        dao.execute(sql);
        //获取角色信息
        //查询条件
        Criteria criRole = Cnd.cri();
        if (dto.getSuperParentGameId() != null) {
            //超父游戏ID
            criRole.where().andEquals("super_game_id", dto.getSuperParentGameId());
        }
        if (dto.getBeginDate() != null && dto.getEndDate() != null) {
            //开服时间
            criRole.where().andBetween("DATE(merge_time)", dto.getBeginDate(), dto.getEndDate());
        }
        if (CollectionUtils.isNotEmpty(dto.getServerIds())) {
            //合服区服列表
            criRole.where().andIn("boss_server_id", dto.getServerIds().toArray(String[]::new));
        }
        if (StringUtils.isNotBlank(dto.getServerName())) {
            //区服名称
            cri.where().andLike("boss_server_name", dto.getServerName());
        }
        if (StringUtils.isNotBlank(dto.getSourceSystem())) {
            criRole.where().andEquals("source_system", dto.getSourceSystem());
        }
        if (dto.getRankingNum() != null) {
            //排名的个数
            criRole.where().andLTE("rank_num", dto.getRankingNum());
        } else {
            //排名的个数
            criRole.where().andLTE("rank_num", 5);
        }
        Sql sqlRole = Sqls.create(getRoleCombatRankRoleDataSqlD() + criRole);
        sqlRole.setCallback(Sqls.callback.maps());
        dao.execute(sqlRole);
        //得到数据Map   （合服ID-超父游戏ID）- （角色信息）
        Map<String, List<Map<String, Object>>> roleDataMap = new HashMap<>();
        //获取到相关角色排名信息
        List<Map> listRole = sqlRole.getList(Map.class).stream().map(dataMap -> {
            //拼接主键信息
            StringBuilder sb = new StringBuilder(StringUtils.EMPTY);
            sb.append((String)dataMap.get("boss_server_id"))
                    .append("-")
                    .append((String)dataMap.get("super_game_id"));
            if (roleDataMap.get(sb.toString()) != null) {
                //含有改主键 添加数据
                roleDataMap.get(sb.toString()).add(dataMap);
            } else {
                //不含有该主键
                List<Map<String, Object>> tempList = new ArrayList<>();
                tempList.add(dataMap);
                roleDataMap.put(String.valueOf(sb), tempList);
            }
            return dataMap;
        }).collect(Collectors.toList());
        //将排名信息拼接到区服信息上
        List<Map> list = sql.getList(Map.class).stream().map(dataMap -> {
            //主键
            StringBuilder sb = new StringBuilder(StringUtils.EMPTY);
            sb.append((String)dataMap.get("server_id"))
                    .append("-")
                    .append((String)dataMap.get("super_game_id"));
            if (roleDataMap.get(sb.toString()) != null) {
                //将排名信息添加
                dataMap.put("role_rank_date", roleDataMap.get(sb.toString()));
            }
            //清除"null"字符串
            if ("null".equals(dataMap.get("son_server_ids"))) {
                dataMap.put("son_server_ids", StringUtils.EMPTY);
            }
            if ("null".equals(dataMap.get("source_server_ids"))) {
                dataMap.put("source_server_ids", StringUtils.EMPTY);
            }
            return dataMap;
        }).collect(Collectors.toList());

        return new Page<>(list, pager);
    }

    /**
     * 查询角色充值排行榜sql
     * @param criA 主表查询条件
     * @param criTodayAmount 当天充值金额相关的查询条件
     * @return String
     */
    private String getRoleRechargeRankingSql(Criteria criA, Criteria criTodayAmount){
        return """
                SELECT
                	*
                FROM (
                	SELECT
                		a.association_user_id as association_user_id, -- 来源的用户id
                		a.source_system as source_system, -- sdk来源
                		a.role_id as role_id, -- 角色id
                		u.role_name as role_name, -- 角色名
                		a.create_time as role_create_time, -- 角色创建时间
                		a.os as os, -- 角色操作系统
                		b.role_level as role_level, -- 角色等级
                		b.combat_num as combat_num, -- 角色攻击力
                		a.country as country, -- 国家属性
                		IFNULL(c.amount, 0) as amount, -- 角色累计充值金额
                		IFNULL(c.amount_count, 0) as amount_count, -- 角色累计充值次数
                		ROUND(IF(c.amount_count > 0, c.amount / c.amount_count, 0), 2) as avg_amount, -- 平均单价
                		(CASE
                			WHEN c.amount >= 0 and c.amount < 2000 THEN '1'
                			WHEN c.amount >= 2000 and c.amount < 5000 THEN '2'
                			WHEN c.amount >= 5000 and c.amount < 10000 THEN '3'
                			WHEN c.amount >= 10000 and c.amount < 20000 THEN '4'
                			WHEN c.amount >= 20000 and c.amount < 50000 THEN '5'
                			WHEN c.amount >= 50000 THEN '6'
                			ELSE '0'
                			END) as vip_level, -- 角色vip等级
                		a.game_id as role_reg_game_id, -- 角色注册游戏id
                		d.game_name as role_reg_game_name, -- 角色注册游戏名
                		d.classify as role_reg_game_classify, -- 角色注册游戏类型
                		IFNULL(d.parent_id, a.game_id) as role_reg_parent_game_id, -- 角色注册游戏的父游戏id
                		IFNULL(e.parent_game_name, d.game_name) as role_reg_parent_game_name, -- 角色注册游戏的父游戏名
                		IFNULL(d.super_game_id, a.game_id) as role_reg_super_game_id, -- 角色注册的超父游戏id
                  		IFNULL(s.name, d.game_name) as role_reg_super_game_name, -- 角色注册的超父游戏名
                		a.server_id as server_id, -- 角色所在区服id
                		q.server_name as server_name, -- 角色所在区服名
                		t.server_name as source_server_name, -- 角色所在区服原始名
                		r.server_id as merge_server_id, -- 角色所在合服id
                  		r.server_name as merge_server_name, -- 角色所在合服名
                		IFNULL(f.real_amount, 0) as role_last_amount, -- 角色最近充值金额
                		f.pay_time as role_last_pay_time, -- 角色最近充值时间
                		g.update_time as role_active_time, -- 角色最近活跃时间
                		TIMESTAMPDIFF(SECOND, g.update_time, NOW()) as role_active_until_now, -- 角色最近活跃距今
                		IFNULL(h.real_amount, 0) as role_first_amount, -- 角色首次充值金额
                		p.today_amount as today_amount, -- 角色当天充值金额
                		TIMESTAMPDIFF(SECOND, a.create_time, f.pay_time) as role_reg_pay_time, -- 角色创建充值时间差（秒）
                		TIMESTAMPDIFF(SECOND, f.pay_time, NOW()) as role_pay_until_now, -- 角色最近充值距今（秒）
                		a.user_id as user_id, -- 玩家id
                	    i.username as username, -- 玩家名称
                	    i.nickname as nickname, -- 玩家昵称
                	    i.reg_mobile as reg_mobile, -- 玩家注册手机
                	    i.reg_email as reg_email, -- 玩家注册邮箱
                	    i.user_create_time as user_create_time, -- 玩家注册时间
                	    i.user_reg_game_id as user_reg_game_id, -- 玩家注册游戏id
                	    i.user_reg_game_name as user_reg_game_name, -- 玩家注册游戏名
                	    i.agent_id as agent_id, -- 玩家注册渠道id
                	    IF(i.agent_id = 0 , '自然量', i.agent_name) as agent_name, -- 玩家注册渠道名
                	    i.put_user_id as put_user_id, -- 投手id
                	    i.user_active_time as user_active_time, -- 玩家最近活跃时间
                	    TIMESTAMPDIFF(SECOND, i.user_active_time, NOW()) as user_active_until_now, -- 玩家最近活跃距今（秒）
                	    i.user_last_recharge_game_id as user_last_recharge_game_id, -- 玩家最近充值游戏id
                	    i.user_last_recharge_game_name as user_last_recharge_game_name, -- 玩家最近充值游戏名
                		i.user_pay_time as user_pay_time, -- 玩家最近充值时间
                		TIMESTAMPDIFF(SECOND, i.user_create_time, i.user_pay_time) as user_reg_pay_time, -- 玩家注册充值时间差（秒）
                		TIMESTAMPDIFF(SECOND, i.user_pay_time, NOW()) as user_pay_until_now, -- 玩家最近充值距今（秒）
                		IFNULL(j.is_send_mail, 0) as is_send_mail, -- 是否发送邮件
                		j.send_gift_id as send_gift_id, -- 最新发送礼包id
                		k.gift_name as gift_name, -- 礼包名
                		j.is_change_game_type as is_change_game_type, -- 是否转端 1-是；0-否
                  		j.is_add_corp_wechat as is_add_corp_wechat, -- 是否添加企微 1-是； 0-否
                		j.add_corp_user_id as add_corp_user_id, -- 企微号
                		j.user_wechat as user_wechat, -- 客户微信号
                		j.user_phone as user_phone, -- 客户手机号
                		IF(TIMESTAMPDIFF(HOUR, g.update_time, NOW()) > 72,
                        				IF(TIMESTAMPDIFF(HOUR, f.pay_time, NOW()) < 72 , 0, 1) , 0) as is_remove_game_for_system, -- 是否退游 1-是;0-否(系统判定)
                        j.is_remove_game as is_remove_game, -- 是否退游 1-是;0-否;null-代表未操作数据
                		j.is_wake_up as is_wake_up, -- 是否唤醒 1-是；0-否
                		j.remark as remark, -- 备注
                		j.gs_id as gs_id, -- GS_ID
                		j.customer_service_id as customer_service_id, -- 客服ID
                		j.oper_user_id as oper_user_id, -- 运营ID
                		j.create_time as create_time, -- 创建时间
                		j.create_by as create_by, -- 创建者
                		j.update_time as update_time, -- 更新时间
                		j.update_by as update_by, -- 更新者
                		j.is_delete as is_delete -- 是否删除 1-删除；0-正常
                	FROM
                	(
                		SELECT
                			*
                		FROM
                		(
                			SELECT
                				-- 角色所在子游戏id、区服id、合服id、角色id、角色名、角色创建时间、角色操作系统os（取创建时间最早的信息）
                				b.association_user_id ,
                				a.source_system,
                				a.user_id,
                				a.game_id,
                				a.server_id,
                				a.role_id,
                				a.role_name,
                				a.create_time,
                				a.os,
                				a.country,
                				ROW_NUMBER()over(partition by a.role_id , a.source_system order by a.create_time asc, a.user_id asc) as num
                			FROM dm_game_order.t_game_user_role a
                			LEFT JOIN dm_game_order.t_game_user b on a.source_system = b.source_system AND a.user_id = b.id
                		) a
                		WHERE num = 1
                	) a
                	LEFT JOIN (
                		SELECT
                			-- 角色等级、角色攻击力（取最新的信息）
                			source_system,
                			role_id,
                			role_level,
                			combat_num,
                			ROW_NUMBER()over(partition by role_id , source_system order by update_time desc, user_id desc) as num
                		FROM dm_game_order.t_game_user_role
                	) b on a.source_system = b.source_system AND a.role_id = b.role_id AND b.num = 1
                	LEFT JOIN (
                		SELECT
                			-- 累计充值金额、累计充值次数
                	    	role_id,
                	    	source_system,
                	    	SUM(real_amount) as amount ,
                	    	COUNT(user_id) as amount_count
                		FROM dm_game_order.t_game_order
                		WHERE status =2
                		GROUP BY role_id , source_system
                	) c on a.source_system = c.source_system AND a.role_id = c.role_id
                	LEFT JOIN (
                		SELECT
                			-- 游戏名称、游戏类型、父游戏id、超父游戏id
                   			source_system,
                   			id,
                   			game_name,
                   			classify,
                   			parent_id,
                   			super_game_id
                		FROM dm_game_order.t_game
                	) d on a.source_system = d.source_system AND a.game_id = d.id
                	LEFT JOIN (
                		SELECT
                			-- 父游戏名称
                			source_system,
                			id,
                			game_name as parent_game_name
                		FROM dm_game_order.t_game
                	) e on d.source_system = e.source_system AND d.parent_id = e.id
                	LEFT JOIN (
                		SELECT
                			-- 角色最近充值时间、角色最近充值金额
                			source_system,
                			role_id,
                			real_amount,
                			pay_time,
                			ROW_NUMBER()over(partition by role_id , source_system order by pay_time desc) as num
                		FROM dm_game_order.t_game_order
                		WHERE status =2
                	) f on a.source_system = f.source_system AND a.role_id = f.role_id AND f.num = 1
                	LEFT JOIN (
                		-- 角色最近活跃时间
                  		SELECT
                  			source_system ,
                  			role_id ,
                  			active_time as update_time ,
                  			ROW_NUMBER()over(partition by role_id , source_system order by active_time desc) as num
                  		FROM game_dw.dw_active_log
                	) g on a.source_system = g.source_system AND a.role_id = g.role_id AND g.num = 1
                	LEFT JOIN (
                		SELECT
                			-- 角色首次充值金额
                			source_system,
                			role_id,
                			real_amount,
                			ROW_NUMBER()over(partition by role_id , source_system order by pay_time asc) as num
                		FROM dm_game_order.t_game_order  WHERE status =2
                	) h on a.source_system = h.source_system AND a.role_id = h.role_id AND h.num = 1
                	LEFT JOIN (
                		SELECT
                			-- 角色当天充值金额
                			source_system,
                			role_id,
                			SUM(real_amount) as today_amount
                		FROM dm_game_order.t_game_order
                """ + criTodayAmount +
                """
                        GROUP BY role_id ,source_system
                    ) p on a.source_system = p.source_system AND a.role_id = p.role_id
                    LEFT JOIN (
                        SELECT
                            -- 区服名
                            source_system ,
                            game_id,
                            server_id,
                            server_name
                        FROM dm_game_order.t_game_server
                    ) q on a.source_system = q.source_system AND a.game_id = q.game_id AND a.server_id = q.server_id
                    LEFT JOIN (
                        SELECT
                            -- 合服id、合服名称
                            source_system,
                            game_id,
                            source_server_ids,
                            server_id,
                            server_name
                        FROM dm_game_order.t_game_server_merge
                        WHERE is_merge = 0 AND is_delete = 0
                    ) r on a.source_system = r.source_system AND a.server_id = r.source_server_ids AND d.super_game_id = r.game_id
                    LEFT JOIN (
                        -- 玩家信息
                        -- 玩家ID、玩家账号、玩家昵称、注册电话、注册邮箱、注册时间、注册游戏、注册渠道
                        SELECT
                            i.source_system as source_system,
                            i.association_user_id as association_user_id, -- 来源用户id
                            i.id as id, -- 玩家id
                            i.username as username,
                            i.nickname as nickname,
                            i.reg_mobile as reg_mobile,
                            i.reg_email as reg_email,
                            i.user_create_time as user_create_time,
                            i.user_reg_game_id as user_reg_game_id,
                            k.game_name as user_reg_game_name,
                            i.agent_id as agent_id,
                            IF(i.agent_id = 0 , '自然量', j.agent_name) as agent_name,
                            j.pitcher_id as put_user_id,
                            l.update_time as user_active_time,
                            m.user_last_recharge_game_id as user_last_recharge_game_id,
                            n.game_name as user_last_recharge_game_name,
                            m.pay_time as user_pay_time -- 玩家最近充值时间
                        FROM
                        (
                            SELECT
                                *
                            FROM
                            (
                                SELECT
                                    source_system,
                                    association_user_id, -- 来源用户id
                                    id , -- 玩家id
                                    username ,
                                    nickname ,
                                    reg_mobile ,
                                    reg_email ,
                                    create_time as user_create_time,
                                    game_id as user_reg_game_id,
                                    agent_id ,
                                    ROW_NUMBER()over(partition by association_user_id, source_system order by create_time desc, id asc) as num
                                FROM dm_game_order.t_game_user
                            ) z WHERE z.num = 1
                        ) i
                        LEFT JOIN (
                            SELECT
                                -- 渠道名称、投手id
                                source_system,
                                id,
                                agent_name,
                                pitcher_id
                            FROM dm_game_order.t_pitcher_agent
                        ) j ON i.agent_id = j.id AND i.source_system  = j.source_system
                        LEFT JOIN (
                            SELECT
                                -- 玩家注册游戏名
                                source_system,
                                id,
                                game_name
                            FROM dm_game_order.t_game
                        ) k ON i.user_reg_game_id = k.id AND i.source_system = k.source_system
                        LEFT JOIN (
                            -- 玩家最近活跃时间
                			SELECT
                			    association_user_id,
                			    b.source_system,
                			    b.active_time as update_time,
                			    ROW_NUMBER()over(partition by association_user_id, b.source_system order by b.active_time desc) as num
                			FROM dm_game_order.t_game_user a
                			LEFT JOIN (
                			    SELECT
                			    	source_system ,
                			    	user_id,
                			    	active_time
                			    FROM game_dw.dw_active_log
                			) b ON a.source_system = b.source_system AND a.id = b.user_id
                        ) l ON i.association_user_id = l.association_user_id AND i.source_system = l.source_system AND l.num = 1
                        LEFT JOIN (
                            -- 玩家最近充值游戏、玩家最近充值时间
                            SELECT
                                association_user_id,
                                b.*,
                                ROW_NUMBER()over(partition by association_user_id, b.source_system order by pay_time desc) as num
                            FROM dm_game_order.t_game_user a
                            RIGHT JOIN (
                                -- 玩家最近充值游戏id、最近充值时间
                                SELECT
                                    source_system,
                                    user_id,
                                    game_id as user_last_recharge_game_id,
                                    pay_time
                                FROM dm_game_order.t_game_order
                                WHERE status = 2
                            ) b on a.id = b.user_id AND a.source_system = b.source_system
                        ) m on i.association_user_id = m.association_user_id AND i.source_system = m.source_system AND m.num = 1
                        LEFT JOIN (
                            SELECT
                                -- 玩家最近充值游戏名
                                source_system,
                                id,
                                game_name
                            FROM dm_game_order.t_game
                        ) n ON m.user_last_recharge_game_id = n.id AND m.source_system = n.source_system
                    ) i on a.source_system = i.source_system AND a.association_user_id = i.association_user_id
                    LEFT JOIN (
                        SELECT
                            -- 操作表相关数据
                            source_system,
                            user_id,
                            role_id,
                            server_id,
                            game_id,
                            is_send_mail, -- 是否发送邮件
                            send_gift_id, -- 最新发送礼包id
                            is_change_game_type, -- 是否转端 1-是；0-否
                            is_add_corp_wechat, -- 是否添加企微 1-是； 0-否
                            add_corp_user_id, -- 企微号
                            user_wechat, -- 客户微信号
                            user_phone, -- 客户手机号
                            is_remove_game, -- 是否退游 1-是；0-否
                            is_wake_up, -- 是否唤醒 1-是；0-否
                            remark, -- 备注
                            gs_id, -- GS_ID
                            customer_service_id, -- 客服ID
                            oper_user_id, -- 运营ID
                            create_time, -- 创建时间
                            create_by, -- 创建者
                            update_time, -- 更新时间
                            update_by, -- 更新者
                            is_delete -- 是否删除 1-删除；0-正常
                        FROM dm_game_order.t_role_operate
                    ) j on a.source_system = j.source_system AND a.association_user_id = j.user_id AND
                    a.role_id = j.role_id AND a.server_id = j.server_id AND a.game_id = j.game_id
                    LEFT JOIN (
                        SELECT
                            -- 礼包名
                            source_system,
                            id,
                            gift_name
                        FROM dm_game_order.t_game_gift
                    ) k on j.source_system = k.source_system AND j.send_gift_id = k.id
                    LEFT JOIN (
                        SELECT
                            source_system ,
                            id,
                            name
                        FROM dm_game_order.t_game_super
                    ) s on d.source_system = s.source_system AND d.super_game_id = s.id
                    LEFT JOIN (
                        SELECT
                            -- 原始服名
                            source_system,
                            game_id,
                            server_id,
                            server_name
                        FROM dm_game_order.t_game_server_merge
                        WHERE is_delete = 0
                    ) t on a.source_system = t.source_system AND a.server_id = t.server_id AND d.super_game_id = t.game_id
                    LEFT JOIN(
                		SELECT
                			a.source_system,
                			a.role_id,
                			a.role_name,
                			a.role_level,
                			a.combat_num ,
                			a.game_id ,
                			b.parent_game_id,
                			b.super_game_id ,
                			ROW_NUMBER()over(partition by a.source_system , a.role_id, b.parent_game_id order by a.role_level desc,a.combat_num desc) as num
                		FROM dm_game_order.t_game_user_role a
                		LEFT JOIN (
                			SELECT
                				source_system,
                				id,
                				IFNULL(parent_id, id) as parent_game_id,
                				IFNULL(super_game_id, id) as super_game_id
                			FROM dm_game_order.t_game
                		) b on a.source_system = b.source_system AND a.game_id = b.id
                	) u on a.source_system = u.source_system AND a.role_id = u.role_id AND d.super_game_id = u.super_game_id AND u.num =1
                ) a
                """ + criA;
    }

    /**
     * 查询记录数
     * @param criA 主表条件
     * @param criTodayAmount 今日充值条件
     * @return String
     */
    private String getCountNumSql(Criteria criA, Criteria criTodayAmount) {
        return """
                SELECT
                    COUNT(1)
                FROM (
                    SELECT
                        a.association_user_id as association_user_id, -- 来源的用户id
                        a.source_system as source_system, -- sdk来源
                        a.role_id as role_id, -- 角色id
                        u.role_name as role_name, -- 角色名
                        a.create_time as role_create_time, -- 角色创建时间
                        a.os as os, -- 角色操作系统
                        b.role_level as role_level, -- 角色等级
                        b.combat_num as combat_num, -- 角色攻击力
                        a.country as country, -- 国家属性
                        IFNULL(c.amount, 0) as amount, -- 角色累计充值金额
                        IFNULL(c.amount_count, 0) as amount_count, -- 角色累计充值次数
                        ROUND(IF(c.amount_count > 0, c.amount / c.amount_count, 0), 2) as avg_amount, -- 平均单价
                        (CASE
                            WHEN c.amount >= 0 and c.amount < 2000 THEN '1'
                            WHEN c.amount >= 2000 and c.amount < 5000 THEN '2'
                            WHEN c.amount >= 5000 and c.amount < 10000 THEN '3'
                            WHEN c.amount >= 10000 and c.amount < 20000 THEN '4'
                            WHEN c.amount >= 20000 and c.amount < 50000 THEN '5'
                            WHEN c.amount >= 50000 THEN '6'
                            ELSE '0'
                            END) as vip_level, -- 角色vip等级
                        a.game_id as role_reg_game_id, -- 角色注册游戏id
                        d.game_name as role_reg_game_name, -- 角色注册游戏名
                        d.classify as role_reg_game_classify, -- 角色注册游戏类型
                        IFNULL(d.parent_id, a.game_id) as role_reg_parent_game_id, -- 角色注册游戏的父游戏id
                        IFNULL(e.parent_game_name, d.game_name) as role_reg_parent_game_name, -- 角色注册游戏的父游戏名
                        IFNULL(d.super_game_id, a.game_id) as role_reg_super_game_id, -- 角色注册的超父游戏id
                        IFNULL(s.name, d.game_name) as role_reg_super_game_name, -- 角色注册的超父游戏名
                        a.server_id as server_id, -- 角色所在区服id
                        q.server_name as server_name, -- 角色所在区服名
                        t.server_name as source_server_name, -- 角色所在区服原始名
                        r.server_id as merge_server_id, -- 角色所在合服id
                        r.server_name as merge_server_name, -- 角色所在合服名
                        IFNULL(f.real_amount, 0) as role_last_amount, -- 角色最近充值金额
                        f.pay_time as role_last_pay_time, -- 角色最近充值时间
                        g.update_time as role_active_time, -- 角色最近活跃时间
                        TIMESTAMPDIFF(SECOND, g.update_time, NOW()) as role_active_until_now, -- 角色最近活跃距今
                        IFNULL(h.real_amount, 0) as role_first_amount, -- 角色首次充值金额
                        p.today_amount as today_amount, -- 角色当天充值金额
                        TIMESTAMPDIFF(SECOND, a.create_time, f.pay_time) as role_reg_pay_time, -- 角色创建充值时间差（秒）
                        TIMESTAMPDIFF(SECOND, f.pay_time, NOW()) as role_pay_until_now, -- 角色最近充值距今（秒）
                        a.user_id as user_id, -- 玩家id
                        i.username as username, -- 玩家名称
                        i.nickname as nickname, -- 玩家昵称
                        i.reg_mobile as reg_mobile, -- 玩家注册手机
                        i.reg_email as reg_email, -- 玩家注册邮箱
                        i.user_create_time as user_create_time, -- 玩家注册时间
                        i.user_reg_game_id as user_reg_game_id, -- 玩家注册游戏id
                        i.user_reg_game_name as user_reg_game_name, -- 玩家注册游戏名
                        i.agent_id as agent_id, -- 玩家注册渠道id
                        IF(i.agent_id = 0 , '自然量', i.agent_name) as agent_name, -- 玩家注册渠道名
                        i.put_user_id as put_user_id, -- 投手id
                        i.user_active_time as user_active_time, -- 玩家最近活跃时间
                        TIMESTAMPDIFF(SECOND, i.user_active_time, NOW()) as user_active_until_now, -- 玩家最近活跃距今（秒）
                        i.user_last_recharge_game_id as user_last_recharge_game_id, -- 玩家最近充值游戏id
                        i.user_last_recharge_game_name as user_last_recharge_game_name, -- 玩家最近充值游戏名
                        i.user_pay_time as user_pay_time, -- 玩家最近充值时间
                        TIMESTAMPDIFF(SECOND, i.user_create_time, i.user_pay_time) as user_reg_pay_time, -- 玩家注册充值时间差（秒）
                        TIMESTAMPDIFF(SECOND, i.user_pay_time, NOW()) as user_pay_until_now, -- 玩家最近充值距今（秒）
                        IFNULL(j.is_send_mail, 0) as is_send_mail, -- 是否发送邮件
                        j.send_gift_id as send_gift_id, -- 最新发送礼包id
                        k.gift_name as gift_name, -- 礼包名
                        j.is_change_game_type as is_change_game_type, -- 是否转端 1-是；0-否
                        j.is_add_corp_wechat as is_add_corp_wechat, -- 是否添加企微 1-是； 0-否
                        j.add_corp_user_id as add_corp_user_id, -- 企微号
                        j.user_wechat as user_wechat, -- 客户微信号
                        j.user_phone as user_phone, -- 客户手机号
                        IF(TIMESTAMPDIFF(HOUR, g.update_time, NOW()) > 72,
                                        IF(TIMESTAMPDIFF(HOUR, f.pay_time, NOW()) < 72 , 0, 1) , 0) as is_remove_game_for_system, -- 是否退游 1-是;0-否(系统判定)
                        j.is_remove_game as is_remove_game, -- 是否退游 1-是;0-否;null-代表未操作数据
                        j.is_wake_up as is_wake_up, -- 是否唤醒 1-是；0-否
                        j.remark as remark, -- 备注
                        j.gs_id as gs_id, -- GS_ID
                        j.customer_service_id as customer_service_id, -- 客服ID
                        j.oper_user_id as oper_user_id, -- 运营ID
                        j.create_time as create_time, -- 创建时间
                        j.create_by as create_by, -- 创建者
                        j.update_time as update_time, -- 更新时间
                        j.update_by as update_by, -- 更新者
                        j.is_delete as is_delete -- 是否删除 1-删除；0-正常
                    FROM
                    (
                        SELECT
                            *
                        FROM
                        (
                            SELECT
                                -- 角色所在子游戏id、区服id、合服id、角色id、角色名、角色创建时间、角色操作系统os（取创建时间最早的信息）
                                b.association_user_id ,
                                a.source_system,
                                a.user_id,
                                a.game_id,
                                a.server_id,
                                a.role_id,
                                a.role_name,
                                a.create_time,
                                a.os,
                                a.country,
                                ROW_NUMBER()over(partition by a.role_id , a.source_system order by a.create_time asc, a.user_id asc) as num
                            FROM dm_game_order.t_game_user_role a
                            LEFT JOIN dm_game_order.t_game_user b on a.source_system = b.source_system AND a.user_id = b.id
                        ) a
                        WHERE num = 1
                    ) a
                    LEFT JOIN (
                        SELECT
                            -- 角色等级、角色攻击力（取最新的信息）
                            source_system,
                            role_id,
                            role_level,
                            combat_num,
                            ROW_NUMBER()over(partition by role_id , source_system order by update_time desc, user_id desc) as num
                        FROM dm_game_order.t_game_user_role
                    ) b on a.source_system = b.source_system AND a.role_id = b.role_id AND b.num = 1
                    LEFT JOIN (
                        SELECT
                            -- 累计充值金额、累计充值次数
                            role_id,
                            source_system,
                            SUM(real_amount) as amount ,
                            COUNT(user_id) as amount_count
                        FROM dm_game_order.t_game_order
                        WHERE status =2
                        GROUP BY role_id , source_system
                    ) c on a.source_system = c.source_system AND a.role_id = c.role_id
                    LEFT JOIN (
                        SELECT
                            -- 游戏名称、游戏类型、父游戏id、超父游戏id
                            source_system,
                            id,
                            game_name,
                            classify,
                            parent_id,
                            super_game_id
                        FROM dm_game_order.t_game
                    ) d on a.source_system = d.source_system AND a.game_id = d.id
                    LEFT JOIN (
                        SELECT
                            -- 父游戏名称
                            source_system,
                            id,
                            game_name as parent_game_name
                        FROM dm_game_order.t_game
                    ) e on d.source_system = e.source_system AND d.parent_id = e.id
                    LEFT JOIN (
                        SELECT
                            -- 角色最近充值时间、角色最近充值金额
                            source_system,
                            role_id,
                            real_amount,
                            pay_time,
                            ROW_NUMBER()over(partition by role_id , source_system order by pay_time desc) as num
                        FROM dm_game_order.t_game_order
                        WHERE status =2
                    ) f on a.source_system = f.source_system AND a.role_id = f.role_id AND f.num = 1
                    LEFT JOIN (
                        -- 角色最近活跃时间
                        SELECT
                            source_system ,
                            role_id ,
                            active_time as update_time ,
                            ROW_NUMBER()over(partition by role_id , source_system order by active_time desc) as num
                        FROM game_dw.dw_active_log
                    ) g on a.source_system = g.source_system AND a.role_id = g.role_id AND g.num = 1
                    LEFT JOIN (
                        SELECT
                            -- 角色首次充值金额
                            source_system,
                            role_id,
                            real_amount,
                            ROW_NUMBER()over(partition by role_id , source_system order by pay_time asc) as num
                        FROM dm_game_order.t_game_order  WHERE status =2
                    ) h on a.source_system = h.source_system AND a.role_id = h.role_id AND h.num = 1
                    LEFT JOIN (
                        SELECT
                            -- 角色当天充值金额
                            source_system,
                            role_id,
                            SUM(real_amount) as today_amount
                        FROM dm_game_order.t_game_order
                """ + criTodayAmount +
                """
                        GROUP BY role_id ,source_system
                    ) p on a.source_system = p.source_system AND a.role_id = p.role_id
                    LEFT JOIN (
                        SELECT
                            -- 区服名
                            source_system ,
                            game_id,
                            server_id,
                            server_name
                        FROM dm_game_order.t_game_server
                    ) q on a.source_system = q.source_system AND a.game_id = q.game_id AND a.server_id = q.server_id
                    LEFT JOIN (
                        SELECT
                            -- 合服id、合服名称
                            source_system,
                            game_id,
                            source_server_ids,
                            server_id,
                            server_name
                        FROM dm_game_order.t_game_server_merge
                        WHERE is_merge = 0 AND is_delete = 0
                    ) r on a.source_system = r.source_system AND a.server_id = r.source_server_ids AND d.super_game_id = r.game_id
                    LEFT JOIN (
                        -- 玩家信息
                        -- 玩家ID、玩家账号、玩家昵称、注册电话、注册邮箱、注册时间、注册游戏、注册渠道
                        SELECT
                            i.source_system as source_system,
                            i.association_user_id as association_user_id, -- 来源用户id
                            i.id as id, -- 玩家id
                            i.username as username,
                            i.nickname as nickname,
                            i.reg_mobile as reg_mobile,
                            i.reg_email as reg_email,
                            i.user_create_time as user_create_time,
                            i.user_reg_game_id as user_reg_game_id,
                            k.game_name as user_reg_game_name,
                            i.agent_id as agent_id,
                            IF(i.agent_id = 0 , '自然量', j.agent_name) as agent_name,
                            j.pitcher_id as put_user_id,
                            l.update_time as user_active_time,
                            m.user_last_recharge_game_id as user_last_recharge_game_id,
                            n.game_name as user_last_recharge_game_name,
                            m.pay_time as user_pay_time -- 玩家最近充值时间
                        FROM
                        (
                            SELECT
                                *
                            FROM
                            (
                                SELECT
                                    source_system,
                                    association_user_id, -- 来源用户id
                                    id , -- 玩家id
                                    username ,
                                    nickname ,
                                    reg_mobile ,
                                    reg_email ,
                                    create_time as user_create_time,
                                    game_id as user_reg_game_id,
                                    agent_id ,
                                    ROW_NUMBER()over(partition by association_user_id, source_system order by create_time desc, id asc) as num
                                FROM dm_game_order.t_game_user
                            ) z WHERE z.num = 1
                        ) i
                        LEFT JOIN (
                            SELECT
                                -- 渠道名称、投手id
                                source_system,
                                id,
                                agent_name,
                                pitcher_id
                            FROM dm_game_order.t_pitcher_agent
                        ) j ON i.agent_id = j.id AND i.source_system  = j.source_system
                        LEFT JOIN (
                            SELECT
                                -- 玩家注册游戏名
                                source_system,
                                id,
                                game_name
                            FROM dm_game_order.t_game
                        ) k ON i.user_reg_game_id = k.id AND i.source_system = k.source_system
                        LEFT JOIN (
                            -- 玩家最近活跃时间
                            SELECT
                                association_user_id,
                                b.source_system,
                                b.active_time as update_time,
                                ROW_NUMBER()over(partition by association_user_id, b.source_system order by b.active_time desc) as num
                            FROM dm_game_order.t_game_user a
                            LEFT JOIN (
                                SELECT
                                    source_system ,
                                    user_id,
                                    active_time
                                FROM game_dw.dw_active_log
                            ) b ON a.source_system = b.source_system AND a.id = b.user_id
                        ) l ON i.association_user_id = l.association_user_id AND i.source_system = l.source_system AND l.num = 1
                        LEFT JOIN (
                            -- 玩家最近充值游戏、玩家最近充值时间
                            SELECT
                                association_user_id,
                                b.*,
                                ROW_NUMBER()over(partition by association_user_id, b.source_system order by pay_time desc) as num
                            FROM dm_game_order.t_game_user a
                            RIGHT JOIN (
                                -- 玩家最近充值游戏id、最近充值时间
                                SELECT
                                    source_system,
                                    user_id,
                                    game_id as user_last_recharge_game_id,
                                    pay_time
                                FROM dm_game_order.t_game_order
                                WHERE status = 2
                            ) b on a.id = b.user_id AND a.source_system = b.source_system
                        ) m on i.association_user_id = m.association_user_id AND i.source_system = m.source_system AND m.num = 1
                        LEFT JOIN (
                            SELECT
                                -- 玩家最近充值游戏名
                                source_system,
                                id,
                                game_name
                            FROM dm_game_order.t_game
                        ) n ON m.user_last_recharge_game_id = n.id AND m.source_system = n.source_system
                    ) i on a.source_system = i.source_system AND a.association_user_id = i.association_user_id
                    LEFT JOIN (
                        SELECT
                            -- 操作表相关数据
                            source_system,
                            user_id,
                            role_id,
                            server_id,
                            game_id,
                            is_send_mail, -- 是否发送邮件
                            send_gift_id, -- 最新发送礼包id
                            is_change_game_type, -- 是否转端 1-是；0-否
                            is_add_corp_wechat, -- 是否添加企微 1-是； 0-否
                            add_corp_user_id, -- 企微号
                            user_wechat, -- 客户微信号
                            user_phone, -- 客户手机号
                            is_remove_game, -- 是否退游 1-是；0-否
                            is_wake_up, -- 是否唤醒 1-是；0-否
                            remark, -- 备注
                            gs_id, -- GS_ID
                            customer_service_id, -- 客服ID
                            oper_user_id, -- 运营ID
                            create_time, -- 创建时间
                            create_by, -- 创建者
                            update_time, -- 更新时间
                            update_by, -- 更新者
                            is_delete -- 是否删除 1-删除；0-正常
                        FROM dm_game_order.t_role_operate
                    ) j on a.source_system = j.source_system AND a.association_user_id = j.user_id AND
                    a.role_id = j.role_id AND a.server_id = j.server_id AND a.game_id = j.game_id
                    LEFT JOIN (
                        SELECT
                            -- 礼包名
                            source_system,
                            id,
                            gift_name
                        FROM dm_game_order.t_game_gift
                    ) k on j.source_system = k.source_system AND j.send_gift_id = k.id
                    LEFT JOIN (
                        SELECT
                            source_system ,
                            id,
                            name
                        FROM dm_game_order.t_game_super
                    ) s on d.source_system = s.source_system AND d.super_game_id = s.id
                    LEFT JOIN (
                        SELECT
                            -- 原始服名
                            source_system,
                            game_id,
                            server_id,
                            server_name
                        FROM dm_game_order.t_game_server_merge
                        WHERE is_delete = 0
                    ) t on a.source_system = t.source_system AND a.server_id = t.server_id AND d.super_game_id = t.game_id
                    LEFT JOIN(
                        SELECT
                            a.source_system,
                            a.role_id,
                            a.role_name,
                            a.role_level,
                            a.combat_num ,
                            a.game_id ,
                            b.parent_game_id,
                            b.super_game_id ,
                            ROW_NUMBER()over(partition by a.source_system , a.role_id, b.parent_game_id order by a.role_level desc,a.combat_num desc) as num
                        FROM dm_game_order.t_game_user_role a
                        LEFT JOIN (
                            SELECT
                                source_system,
                                id,
                                IFNULL(parent_id, id) as parent_game_id,
                                IFNULL(super_game_id, id) as super_game_id
                            FROM dm_game_order.t_game
                        ) b on a.source_system = b.source_system AND a.game_id = b.id
                    ) u on a.source_system = u.source_system AND a.role_id = u.role_id AND d.super_game_id = u.super_game_id AND u.num =1
                ) a
                """ + criA;
    }

    /**
     * 原始服-父游戏维度（区服相关信息）
     * @return String
     */
    private String getRoleCombatRankServerDataSqlA() {
        return """
                SELECT
                	-- 查询每个父游戏-原始区服维度的信息
                	source_system,
                	parent_game_id, -- 父游戏ID
                	MAX(parent_game_name) as parent_game_name, -- 父游戏名
                	super_game_id , -- 超父游戏ID
                	MAX(super_game_name) as super_game_name , -- 超父游戏名
                	server_id, -- 区服ID
                	MAX(server_name) as server_name, -- 区服名
                	MAX(dt) as dt, -- 开服时间
                	boss_server_id, -- 合服ID
                	MAX(boss_server_name) as boss_server_name, -- 合服名
                	MAX(merge_time) as merge_time , -- 合服时间
                	MAX(sum_server_count) as sum_server_count, -- 合服次数
                	SUM(server_combat_num) as server_combat_num, -- 区服战力
                    SUM(new_user_count) as new_user_count, -- 新用户人数
                    SUM(create_role_count) as create_role_count, -- 创角人数
                    SUM(role_count) as role_count, -- 角色数量
                    SUM(total_num) as total_num, -- 总付费人数
                    SUM(total_amount) as total_amount, -- 总付费金额
                	ROUND(IF(SUM(total_num) > 0, SUM(total_amount) / SUM(total_num), 0), 2) as total_arppu, -- 总付费ARPPU
                    ROUND(IF(SUM(create_role_count) > 0, SUM(total_num) / SUM(create_role_count), 0), 4) as amount_rate, -- 付费率
                    SUM(ba1) as ba1, -- 今日付费金额
                    SUM(ba1_num) as ba1_num, -- 今日付费人数
                    SUM(ba1_count) as ba1_count, -- 今日付费次数
                    SUM(ba1_active_num) as ba1_active_num, -- 今日活跃人数
                    SUM(ba2) as ba2, -- 昨日付费金额
                    SUM(ba2_num) as ba2_num, -- 昨日付费人数
                    SUM(ba2_count) as ba2_count, -- 昨日付费次数
                    SUM(ba2_active_num) as ba2_active_num, -- 昨日活跃人数
                    SUM(ba3) as ba3, -- 前天付费金额
                    SUM(ba3_num) as ba3_num, -- 前天付费人数
                    SUM(ba3_count) as ba3_count, -- 前天付费次数
                    SUM(ba3_active_num) as ba3_active_num -- 前天活跃人数
                FROM (
                	-- 子查询，查询每个子游戏-原始区服维度的信息
                	SELECT
                		source_system,
                		game_id, -- 子游戏ID
                		MAX(game_name) as game_name, -- 子游戏名
                		parent_game_id, -- 父游戏ID
                		MAX(parent_game_name) as parent_game_name, -- 父游戏名
                		super_game_id , -- 超父游戏ID
                		MAX(super_game_name) as super_game_name , -- 超父游戏名
                		server_id, -- 区服ID
                		MAX(server_name) as server_name, -- 区服名
                		MAX(`date`) as dt, -- 开服日期
                		boss_server_id, -- 合服ID
                		MAX(boss_server_name) as boss_server_name, -- 合服名
                		MAX(merge_time) as merge_time , -- 合服时间
                		MAX(sum_server_count) as sum_server_count, -- 合服次数
                		MAX(server_combat_num) as server_combat_num, -- 区服战力
                		MAX(new_user_count) as new_user_count, -- 新用户人数
                		MAX(create_role_count) as create_role_count, -- 创角人数
                		MAX(role_count) as role_count, -- 角色数量
                		MAX(total_num) as total_num, -- 总付费人数
                		MAX(total_amount) as total_amount, -- 总付费金额
                		MAX(ba1) as ba1, -- 今日付费金额
                		MAX(ba1_num) as ba1_num, -- 今日付费人数
                		MAX(ba1_count) as ba1_count, -- 今日付费次数
                		MAX(ba1_active_num) as ba1_active_num, -- 今日活跃人数
                		MAX(ba2) as ba2, -- 昨日付费金额
                		MAX(ba2_num) as ba2_num, -- 昨日付费人数
                		MAX(ba2_count) as ba2_count, -- 昨日付费次数
                		MAX(ba2_active_num) as ba2_active_num, -- 昨日活跃人数
                		MAX(ba3) as ba3, -- 前天付费金额
                		MAX(ba3_num) as ba3_num, -- 前天付费人数
                		MAX(ba3_count) as ba3_count, -- 前天付费次数
                		MAX(ba3_active_num) as ba3_active_num -- 前天活跃人数
                	FROM game_ads.ads_role_combat_num_ranking
                	GROUP BY game_id ,parent_game_id ,super_game_id ,server_id ,boss_server_id ,source_system
                ) a
                """;
    }

    /**
     * 原始服-父游戏维度（区服相关信息）(总记录数)
     * @return String
     */
    private String getRoleCombatRankServerDataSqlACount(Criteria cri) {
        return """
                SELECT
                    COUNT(1)
                FROM(
                    SELECT
                    	-- 查询每个父游戏-原始区服维度的信息
                    	source_system,
                    	parent_game_id, -- 父游戏ID
                    	MAX(parent_game_name) as parent_game_name, -- 父游戏名
                    	super_game_id , -- 超父游戏ID
                    	MAX(super_game_name) as super_game_name , -- 超父游戏名
                    	server_id, -- 区服ID
                    	MAX(server_name) as server_name, -- 区服名
                    	MAX(dt) as dt, -- 开服时间
                    	TIMESTAMPDIFF(DAY, MAX(dt), NOW()) start_day, -- 开服天数
                    	boss_server_id, -- 合服ID
                    	MAX(boss_server_name) as boss_server_name, -- 合服名
                    	MAX(merge_time) as merge_time , -- 合服时间
                    	MAX(sum_server_count) as sum_server_count, -- 合服次数
                    	SUM(server_combat_num) as server_combat_num, -- 区服战力
                        SUM(new_user_count) as new_user_count, -- 新用户人数
                        SUM(create_role_count) as create_role_count, -- 创角人数
                        SUM(role_count) as role_count, -- 角色数量
                        SUM(total_num) as total_num, -- 总付费人数
                        SUM(total_amount) as total_amount, -- 总付费金额
                    	ROUND(IF(SUM(total_num) > 0, SUM(total_amount) / SUM(total_num), 0), 2) as total_arppu, -- 总付费ARPPU
                        ROUND(IF(SUM(create_role_count) > 0, SUM(total_num) / SUM(create_role_count), 0), 4) as amount_rate, -- 付费率
                        SUM(ba1) as ba1, -- 今日付费金额
                        SUM(ba1_num) as ba1_num, -- 今日付费人数
                        SUM(ba1_count) as ba1_count, -- 今日付费次数
                        SUM(ba1_active_num) as ba1_active_num, -- 今日活跃人数
                        SUM(ba2) as ba2, -- 昨日付费金额
                        SUM(ba2_num) as ba2_num, -- 昨日付费人数
                        SUM(ba2_count) as ba2_count, -- 昨日付费次数
                        SUM(ba2_active_num) as ba2_active_num, -- 昨日活跃人数
                        SUM(ba3) as ba3, -- 前天付费金额
                        SUM(ba3_num) as ba3_num, -- 前天付费人数
                        SUM(ba3_count) as ba3_count, -- 前天付费次数
                        SUM(ba3_active_num) as ba3_active_num -- 前天活跃人数
                    FROM (
                    	-- 子查询，查询每个子游戏-原始区服维度的信息
                    	SELECT
                    		source_system,
                    		game_id, -- 子游戏ID
                    		MAX(game_name) as game_name, -- 子游戏名
                    		parent_game_id, -- 父游戏ID
                    		MAX(parent_game_name) as parent_game_name, -- 父游戏名
                    		super_game_id , -- 超父游戏ID
                    		MAX(super_game_name) as super_game_name , -- 超父游戏名
                    		server_id, -- 区服ID
                    		MAX(server_name) as server_name, -- 区服名
                    		MAX(`date`) as dt, -- 开服日期
                    		boss_server_id, -- 合服ID
                    		MAX(boss_server_name) as boss_server_name, -- 合服名
                    		MAX(merge_time) as merge_time , -- 合服时间
                    		MAX(sum_server_count) as sum_server_count, -- 合服次数
                    		MAX(server_combat_num) as server_combat_num, -- 区服战力
                    		MAX(new_user_count) as new_user_count, -- 新用户人数
                    		MAX(create_role_count) as create_role_count, -- 创角人数
                    		MAX(role_count) as role_count, -- 角色数量
                    		MAX(total_num) as total_num, -- 总付费人数
                    		MAX(total_amount) as total_amount, -- 总付费金额
                    		MAX(ba1) as ba1, -- 今日付费金额
                    		MAX(ba1_num) as ba1_num, -- 今日付费人数
                    		MAX(ba1_count) as ba1_count, -- 今日付费次数
                    		MAX(ba1_active_num) as ba1_active_num, -- 今日活跃人数
                    		MAX(ba2) as ba2, -- 昨日付费金额
                    		MAX(ba2_num) as ba2_num, -- 昨日付费人数
                    		MAX(ba2_count) as ba2_count, -- 昨日付费次数
                    		MAX(ba2_active_num) as ba2_active_num, -- 昨日活跃人数
                    		MAX(ba3) as ba3, -- 前天付费金额
                    		MAX(ba3_num) as ba3_num, -- 前天付费人数
                    		MAX(ba3_count) as ba3_count, -- 前天付费次数
                    		MAX(ba3_active_num) as ba3_active_num -- 前天活跃人数
                    	FROM game_ads.ads_role_combat_num_ranking
                    	GROUP BY game_id ,parent_game_id ,super_game_id ,server_id ,boss_server_id ,source_system
                    ) a
                """ + cri+
                """
                ) a
                """;
    }

    /**
     * 合服-父游戏维度（区服相关信息）
     * @return String
     */
    private String getRoleCombatRankServerDataSqlB() {
        return """
                SELECT
                	-- 查询每个父游戏-合服服维度的信息
                	source_system,
                	parent_game_id, -- 父游戏ID
                	MAX(parent_game_name) as parent_game_name, -- 父游戏名
                	super_game_id , -- 超父游戏ID
                	MAX(super_game_name) as super_game_name , -- 超父游戏名
                	boss_server_id, -- 合服ID
                	MAX(boss_server_name) as boss_server_name, -- 合服名
                	MAX(merge_time) as merge_time , -- 合服时间
                	TIMESTAMPDIFF(DAY, MAX(merge_time), NOW()) start_day, -- 开服天数
                	MAX(sum_server_count) as sum_server_count, -- 合服次数
                	MAX(c.is_merge) as is_merge, -- 是否参与过合服
                	MAX(b.main_server_id) as main_server_id, -- 主服ID
                	MAX(b.main_server_name) as main_server_name, -- 主服名
                	MAX(son_server_ids) as son_server_ids, -- 合服的子服ID列表
                    MAX(source_server_ids) as source_server_ids, -- 子服ID列表
                    MAX(source_server_ids_name) as source_server_ids_name, -- 子服名列表
                	SUM(server_combat_num) as server_combat_num, -- 区服战力
                    SUM(new_user_count) as new_user_count, -- 新用户人数
                    SUM(create_role_count) as create_role_count, -- 创角人数
                    SUM(role_count) as role_count, -- 角色数量
                    SUM(total_num) as total_num, -- 总付费人数
                    SUM(total_amount) as total_amount, -- 总付费金额
                	ROUND(IF(SUM(total_num) > 0, SUM(total_amount) / SUM(total_num), 0), 2) as total_arppu, -- 总付费ARPPU
                    ROUND(IF(SUM(create_role_count) > 0, SUM(total_num) / SUM(create_role_count), 0), 4) as amount_rate, -- 付费率
                    SUM(ba1) as ba1, -- 今日付费金额
                    SUM(ba1_num) as ba1_num, -- 今日付费人数
                    SUM(ba1_count) as ba1_count, -- 今日付费次数
                    SUM(ba1_active_num) as ba1_active_num, -- 今日活跃人数
                    SUM(ba2) as ba2, -- 昨日付费金额
                    SUM(ba2_num) as ba2_num, -- 昨日付费人数
                    SUM(ba2_count) as ba2_count, -- 昨日付费次数
                    SUM(ba2_active_num) as ba2_active_num, -- 昨日活跃人数
                    SUM(ba3) as ba3, -- 前天付费金额
                    SUM(ba3_num) as ba3_num, -- 前天付费人数
                    SUM(ba3_count) as ba3_count, -- 前天付费次数
                    SUM(ba3_active_num) as ba3_active_num -- 前天活跃人数
                FROM (
                	-- 子查询，查询每个子游戏-原始区服维度的信息
                	SELECT
                		source_system,
                		game_id, -- 子游戏ID
                		MAX(game_name) as game_name, -- 子游戏名
                		parent_game_id, -- 父游戏ID
                		MAX(parent_game_name) as parent_game_name, -- 父游戏名
                		super_game_id , -- 超父游戏ID
                		MAX(super_game_name) as super_game_name , -- 超父游戏名
                		server_id, -- 区服ID
                		MAX(server_name) as server_name, -- 区服名
                		boss_server_id, -- 合服ID
                		MAX(boss_server_name) as boss_server_name, -- 合服名
                		MAX(merge_time) as merge_time , -- 合服时间
                		MAX(sum_server_count) as sum_server_count, -- 合服次数
                		MAX(server_combat_num) as server_combat_num, -- 区服战力
                		MAX(new_user_count) as new_user_count, -- 新用户人数
                		MAX(create_role_count) as create_role_count, -- 创角人数
                		MAX(role_count) as role_count, -- 角色数量
                		MAX(total_num) as total_num, -- 总付费人数
                		MAX(total_amount) as total_amount, -- 总付费金额
                		MAX(ba1) as ba1, -- 今日付费金额
                		MAX(ba1_num) as ba1_num, -- 今日付费人数
                		MAX(ba1_count) as ba1_count, -- 今日付费次数
                		MAX(ba1_active_num) as ba1_active_num, -- 今日活跃人数
                		MAX(ba2) as ba2, -- 昨日付费金额
                		MAX(ba2_num) as ba2_num, -- 昨日付费人数
                		MAX(ba2_count) as ba2_count, -- 昨日付费次数
                		MAX(ba2_active_num) as ba2_active_num, -- 昨日活跃人数
                		MAX(ba3) as ba3, -- 前天付费金额
                		MAX(ba3_num) as ba3_num, -- 前天付费人数
                		MAX(ba3_count) as ba3_count, -- 前天付费次数
                		MAX(ba3_active_num) as ba3_active_num -- 前天活跃人数
                	FROM game_ads.ads_role_combat_num_ranking
                	GROUP BY game_id ,parent_game_id ,super_game_id ,server_id ,boss_server_id ,source_system
                ) a
                LEFT JOIN (
                	SELECT
                		a.source_system as b_source_system,
                		a.server_id as b_server_id,
                		a.game_id as b_game_id,
                		MAX(a.server_name) as server_name  ,
                		MAX(a.main_server_id) as main_server_id,
                		MAX(c.server_name) as main_server_name,
                		MAX(a.son_server_ids) as son_server_ids ,
                		GROUP_CONCAT(a.source_server_ids, ',') as source_server_ids,
                		GROUP_CONCAT(b.server_name , ',') as source_server_ids_name
                	FROM dm_game_order.t_game_server_merge a
                	LEFT JOIN dm_game_order.t_game_server_merge b on a.source_system = b.source_system AND a.game_id = b.game_id AND a.source_server_ids = b.server_id
                	LEFT JOIN (
                		SELECT
                			source_system ,
                			game_id ,
                			server_id  ,
                			MAX(server_name) as server_name
                		FROM dm_game_order.t_game_server_merge
                		GROUP BY server_id ,source_system ,game_id
                	) c on a.source_system = c.source_system AND a.game_id = c.game_id AND a.main_server_id = c.server_id
                	group by a.server_id , a.source_system , a.game_id
                ) b ON a.source_system = b.b_source_system AND a.super_game_id = b.b_game_id AND a.boss_server_id = b.b_server_id
                LEFT JOIN (
                	SELECT
                		source_system as c_source_system,
                		game_id,
                		server_id,
                		is_merge
                	FROM dm_game_order.t_game_server_merge
                ) c on a.source_system = c.c_source_system AND a.boss_server_id = c.server_id AND a.super_game_id = c.game_id
                """;
    }

    /**
     * 合服-父游戏维度（区服相关信息）(总记录数)
     * @return String
     */
    private String getRoleCombatRankServerDataSqlBCount(Criteria cri) {
        return """
                SELECT
                    COUNT(1)
                FROM(
                    SELECT
                    	-- 查询每个父游戏-合服服维度的信息
                    	source_system,
                    	parent_game_id, -- 父游戏ID
                    	MAX(parent_game_name) as parent_game_name, -- 父游戏名
                    	super_game_id , -- 超父游戏ID
                    	MAX(super_game_name) as super_game_name , -- 超父游戏名
                    	boss_server_id, -- 合服ID
                    	MAX(boss_server_name) as boss_server_name, -- 合服名
                    	MAX(merge_time) as merge_time , -- 合服时间
                    	TIMESTAMPDIFF(DAY, MAX(merge_time), NOW()) start_day, -- 开服天数
                    	MAX(sum_server_count) as sum_server_count, -- 合服次数
                    	MAX(c.is_merge) as is_merge, -- 是否参与过合服
                    	MAX(b.main_server_id) as main_server_id, -- 主服ID
                    	MAX(b.main_server_name) as main_server_name, -- 主服名
                    	MAX(son_server_ids) as son_server_ids, -- 合服的子服ID列表
                        MAX(source_server_ids) as source_server_ids, -- 子服ID列表
                        MAX(source_server_ids_name) as source_server_ids_name, -- 子服名列表
                    	SUM(server_combat_num) as server_combat_num, -- 区服战力
                        SUM(new_user_count) as new_user_count, -- 新用户人数
                        SUM(create_role_count) as create_role_count, -- 创角人数
                        SUM(role_count) as role_count, -- 角色数量
                        SUM(total_num) as total_num, -- 总付费人数
                        SUM(total_amount) as total_amount, -- 总付费金额
                    	ROUND(IF(SUM(total_num) > 0, SUM(total_amount) / SUM(total_num), 0), 2) as total_arppu, -- 总付费ARPPU
                        ROUND(IF(SUM(create_role_count) > 0, SUM(total_num) / SUM(create_role_count), 0), 4) as amount_rate, -- 付费率
                        SUM(ba1) as ba1, -- 今日付费金额
                        SUM(ba1_num) as ba1_num, -- 今日付费人数
                        SUM(ba1_count) as ba1_count, -- 今日付费次数
                        SUM(ba1_active_num) as ba1_active_num, -- 今日活跃人数
                        SUM(ba2) as ba2, -- 昨日付费金额
                        SUM(ba2_num) as ba2_num, -- 昨日付费人数
                        SUM(ba2_count) as ba2_count, -- 昨日付费次数
                        SUM(ba2_active_num) as ba2_active_num, -- 昨日活跃人数
                        SUM(ba3) as ba3, -- 前天付费金额
                        SUM(ba3_num) as ba3_num, -- 前天付费人数
                        SUM(ba3_count) as ba3_count, -- 前天付费次数
                        SUM(ba3_active_num) as ba3_active_num -- 前天活跃人数
                    FROM (
                    	-- 子查询，查询每个子游戏-原始区服维度的信息
                    	SELECT
                    		source_system,
                    		game_id, -- 子游戏ID
                    		MAX(game_name) as game_name, -- 子游戏名
                    		parent_game_id, -- 父游戏ID
                    		MAX(parent_game_name) as parent_game_name, -- 父游戏名
                    		super_game_id , -- 超父游戏ID
                    		MAX(super_game_name) as super_game_name , -- 超父游戏名
                    		server_id, -- 区服ID
                    		MAX(server_name) as server_name, -- 区服名
                    		boss_server_id, -- 合服ID
                    		MAX(boss_server_name) as boss_server_name, -- 合服名
                    		MAX(merge_time) as merge_time , -- 合服时间
                    		MAX(sum_server_count) as sum_server_count, -- 合服次数
                    		MAX(server_combat_num) as server_combat_num, -- 区服战力
                    		MAX(new_user_count) as new_user_count, -- 新用户人数
                    		MAX(create_role_count) as create_role_count, -- 创角人数
                    		MAX(role_count) as role_count, -- 角色数量
                    		MAX(total_num) as total_num, -- 总付费人数
                    		MAX(total_amount) as total_amount, -- 总付费金额
                    		MAX(ba1) as ba1, -- 今日付费金额
                    		MAX(ba1_num) as ba1_num, -- 今日付费人数
                    		MAX(ba1_count) as ba1_count, -- 今日付费次数
                    		MAX(ba1_active_num) as ba1_active_num, -- 今日活跃人数
                    		MAX(ba2) as ba2, -- 昨日付费金额
                    		MAX(ba2_num) as ba2_num, -- 昨日付费人数
                    		MAX(ba2_count) as ba2_count, -- 昨日付费次数
                    		MAX(ba2_active_num) as ba2_active_num, -- 昨日活跃人数
                    		MAX(ba3) as ba3, -- 前天付费金额
                    		MAX(ba3_num) as ba3_num, -- 前天付费人数
                    		MAX(ba3_count) as ba3_count, -- 前天付费次数
                    		MAX(ba3_active_num) as ba3_active_num -- 前天活跃人数
                    	FROM game_ads.ads_role_combat_num_ranking
                    	GROUP BY game_id ,parent_game_id ,super_game_id ,server_id ,boss_server_id ,source_system
                    ) a
                    LEFT JOIN (
                    	SELECT
                    		a.source_system as b_source_system,
                    		a.server_id as b_server_id,
                    		a.game_id as b_game_id,
                    		MAX(a.server_name) as server_name  ,
                    		MAX(a.main_server_id) as main_server_id,
                    		MAX(c.server_name) as main_server_name,
                    		MAX(a.son_server_ids) as son_server_ids ,
                    		GROUP_CONCAT(a.source_server_ids, ',') as source_server_ids,
                    		GROUP_CONCAT(b.server_name , ',') as source_server_ids_name
                    	FROM dm_game_order.t_game_server_merge a
                    	LEFT JOIN dm_game_order.t_game_server_merge b on a.source_system = b.source_system AND a.game_id = b.game_id AND a.source_server_ids = b.server_id
                    	LEFT JOIN (
                    		SELECT
                    			source_system ,
                    			game_id ,
                    			server_id  ,
                    			MAX(server_name) as server_name
                    		FROM dm_game_order.t_game_server_merge
                    		GROUP BY server_id ,source_system ,game_id
                    	) c on a.source_system = c.source_system AND a.game_id = c.game_id AND a.main_server_id = c.server_id
                    	group by a.server_id , a.source_system , a.game_id
                    ) b ON a.source_system = b.b_source_system AND a.super_game_id = b.b_game_id AND a.boss_server_id = b.b_server_id
                    LEFT JOIN (
                    	SELECT
                    		source_system as c_source_system,
                    		game_id,
                    		server_id,
                    		is_merge
                    	FROM dm_game_order.t_game_server_merge
                    ) c on a.source_system = c.c_source_system AND a.boss_server_id = c.server_id AND a.super_game_id = c.game_id
                """ + cri +
                """
                ) a
                """;
    }

    /**
     * 原始服-超父游戏维度（区服相关信息）
     * @return String
     */
    private String getRoleCombatRankServerDataSqlC() {
        return """
                SELECT
                    *
                FROM
                (
                	SELECT
                		-- 查询每个超父游戏-原始区服维度的信息
                		source_system,
                		super_game_id , -- 超父游戏ID
                		MAX(super_game_name) as super_game_name , -- 超父游戏名
                		server_id, -- 区服ID
                		IFNULL(MAX(i.server_name), MAX(a.server_name)) as server_name, -- 区服名
                		MIN(dt) as dt, -- 开服日期
                		TIMESTAMPDIFF(DAY, DATE(MIN(dt)), DATE_ADD(Date(NOW()),1)) start_day, -- 开服天数
                		boss_server_id, -- 合服ID
                		MAX(boss_server_name) as boss_server_name, -- 合服名
                		MAX(merge_time) as merge_time , -- 合服时间
                		MAX(sum_server_count) as sum_server_count, -- 合服次数
                		IFNULL(MAX(h.is_merge), 0) as is_merge, -- 是否参与过合服
                		SUM(server_combat_num) as server_combat_num, -- 区服战力
                		MAX(c.new_user_count) as new_user_count, -- 新用户人数
                		MAX(d.create_role_count) as create_role_count , -- 创角人数
                	    MAX(b.role_num) as role_count , -- 角色数量
                	    MAX(e.total_num) as total_num, -- 总付费人数
                	    SUM(total_amount) as total_amount, -- 总付费金额
                	    ROUND(IF(MAX(e.total_num) > 0, SUM(total_amount) / MAX(e.total_num), 0), 2) as total_arppu, -- 总付费ARPPU
                	    ROUND(IF(MAX(d.create_role_count) > 0, MAX(e.total_num) / MAX(d.create_role_count), 0), 4) as amount_rate, -- 付费率
                	    SUM(ba1) as ba1, -- 今日付费金额
                	    MAX(e.ba1_num) as ba1_num, -- 今日付费人数
                	    SUM(ba1_count) as ba1_count, -- 今日付费次数
                	    MAX(f.ba1_active_num) as ba1_active_num, -- 今日活跃人数
                	    SUM(ba2) as ba2, -- 昨日付费金额
                	    MAX(e.ba2_num) as ba2_num, -- 昨日付费人数
                	    SUM(ba2_count) as ba2_count, -- 昨日付费次数
                	    MAX(f.ba2_active_num) as ba2_active_num, -- 昨日活跃人数
                	    SUM(ba3) as ba3, -- 前天付费金额
                	    MAX(e.ba3_num) as ba3_num, -- 前天付费人数
                	    SUM(ba3_count) as ba3_count, -- 前天付费次数
                	    MAX(f.ba3_active_num) as ba3_active_num -- 前天活跃人数
                	FROM (
                		SELECT
                			-- 子查询，查询每个父游戏-原始区服维度的信息
                			source_system,
                			parent_game_id, -- 父游戏ID
                			MAX(parent_game_name) as parent_game_name, -- 父游戏名
                			super_game_id , -- 超父游戏ID
                			MAX(super_game_name) as super_game_name , -- 超父游戏名
                			server_id, -- 区服ID
                			MAX(server_name) as server_name, -- 区服名
                			MIN(dt) as dt, -- 开服日期
                			boss_server_id, -- 合服ID
                			MAX(boss_server_name) as boss_server_name, -- 合服名
                			MAX(merge_time) as merge_time , -- 合服时间
                			MAX(sum_server_count) as sum_server_count, -- 合服次数
                			SUM(server_combat_num) as server_combat_num, -- 区服战力
                		    SUM(total_amount) as total_amount, -- 总付费金额
                		    SUM(ba1) as ba1, -- 今日付费金额
                		    SUM(ba1_count) as ba1_count, -- 今日付费次数
                		    SUM(ba2) as ba2, -- 昨日付费金额
                		    SUM(ba2_count) as ba2_count, -- 昨日付费次数
                		    SUM(ba3) as ba3, -- 前天付费金额
                		    SUM(ba3_count) as ba3_count -- 前天付费次数
                		FROM (
                			-- 子查询，查询每个子游戏-原始区服维度的信息
                			SELECT
                				source_system,
                				game_id, -- 子游戏ID
                				MAX(game_name) as game_name, -- 子游戏名
                				parent_game_id, -- 父游戏ID
                				MAX(parent_game_name) as parent_game_name, -- 父游戏名
                				super_game_id , -- 超父游戏ID
                				MAX(super_game_name) as super_game_name , -- 超父游戏名
                				server_id, -- 区服ID
                				MAX(server_name) as server_name, -- 区服名
                				MIN(`date`) as dt, -- 开服日期
                				boss_server_id, -- 合服ID
                				MAX(boss_server_name) as boss_server_name, -- 合服名
                				MAX(merge_time) as merge_time , -- 合服时间
                				MAX(sum_server_count) as sum_server_count, -- 合服次数
                				MAX(server_combat_num) as server_combat_num, -- 区服战力
                				MAX(total_amount) as total_amount, -- 总付费金额
                				MAX(ba1) as ba1, -- 今日付费金额
                				MAX(ba1_count) as ba1_count, -- 今日付费次数
                				MAX(ba2) as ba2, -- 昨日付费金额
                				MAX(ba2_count) as ba2_count, -- 昨日付费次数
                				MAX(ba3) as ba3, -- 前天付费金额
                				MAX(ba3_count) as ba3_count -- 前天付费次数
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY game_id ,parent_game_id ,super_game_id ,server_id ,boss_server_id ,source_system
                		) a GROUP BY parent_game_id ,super_game_id ,server_id ,boss_server_id ,source_system
                	) a
                	LEFT JOIN (
                		SELECT
                			-- 超父-原始区服维度
                			source_system as b_source_system,
                			super_game_id as b_super_game_id,
                			server_id as b_server_id,
                			boss_server_id as b_boss_server_id,
                			COUNT(DISTINCT role_id) as role_num -- 角色数量
                		FROM game_ads.ads_role_combat_num_ranking
                		GROUP BY super_game_id, source_system, server_id , boss_server_id
                	) b on a.source_system = b.b_source_system AND a.server_id = b.b_server_id AND a.super_game_id = b.b_super_game_id AND a.boss_server_id = b.b_boss_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-原始区服维度
                			a.source_system as c_source_system,
                			a.super_game_id as c_super_game_id,
                			a.server_id as c_server_id,
                			b.boss_server_id as c_boss_server_id,
                			COUNT(a.association_user_id) as new_user_count -- 新用户人数
                		FROM (
                			SELECT
                				a.source_system ,
                				b.id,
                				a.game_id ,
                				a.server_id,
                				a.role_id ,
                				a.role_name ,
                				b.association_user_id ,
                				a.create_time ,
                				IFNULL(c.super_game_id , a.game_id) as super_game_id,
                				ROW_NUMBER()over(partition by b.association_user_id , a.source_system order by a.create_time asc, a.user_id asc) as num
                			FROM dm_game_order.t_game_user_role a
                			LEFT JOIN dm_game_order.t_game_user b on a.source_system = b.source_system AND a.user_id = b.id
                			LEFT JOIN dm_game_order.t_game c on a.source_system = c.source_system AND a.game_id = c.id
                		) a
                		LEFT JOIN (
                			SELECT
                				source_system,
                				server_id,
                				boss_server_id,
                				super_game_id
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY source_system, super_game_id, server_id, boss_server_id
                		) b on a.source_system = b.source_system AND a.server_id = b.server_id AND a.super_game_id = b.super_game_id
                		WHERE num = 1
                		GROUP BY a.super_game_id, a.server_id, b.boss_server_id, a.source_system
                	) c on a.source_system = c.c_source_system AND a.super_game_id = c.c_super_game_id AND a.server_id = c.c_server_id AND a.boss_server_id = c.c_boss_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-原始区服维度
                			source_system as d_source_system,
                			super_game_id as d_super_game_id,
                			server_id as d_server_id,
                			boss_server_id as d_boss_server_id,
                			COUNT(DISTINCT user_id) as create_role_count -- 创角人数
                		FROM game_ads.ads_role_combat_num_ranking
                		GROUP BY super_game_id, source_system, server_id , boss_server_id
                	) d on a.source_system = d.d_source_system AND a.server_id = d.d_server_id AND a.super_game_id = d.d_super_game_id AND a.boss_server_id = d.d_boss_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-原始区服维度
                			a.source_system as e_source_system,
                			a.server_id as e_server_id,
                			c.super_game_id as e_super_game_id,
                			d.boss_server_id as e_boss_server_id,
                			count(DISTINCT IF(Date(NOW()) = day,user_id,NULL)) as ba1_num, -- 今日充值人数
                			count(DISTINCT IF(date_add(Date(NOW()),-1)=day,user_id,NULL)) as ba2_num, -- 昨日充值人数
                			count(DISTINCT IF(date_add(Date(NOW()),-2)=day,user_id,NULL)) as ba3_num, -- 前天充值人数
                			count(DISTINCT user_id) as total_num -- 总充值人数
                		FROM dm_game_order.t_game_server a
                		LEFT JOIN game_dw.dw_order_day_amount b
                		on a.source_system = b.source_system and a.server_id =b.server_id and a.game_id =b.game_id
                		LEFT JOIN (
                			SELECT
                				source_system ,
                				id,
                				IFNULL(parent_id, id) as parent_id,
                				IFNULL(super_game_id, id) as super_game_id
                			FROM dm_game_order.t_game
                		) c on a.source_system = c.source_system AND a.game_id = c.id
                		LEFT JOIN (
                			SELECT
                				source_system,
                				server_id,
                				boss_server_id,
                				super_game_id
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY source_system, super_game_id, server_id, boss_server_id
                		) d on a.source_system = d.source_system AND a.server_id = d.server_id AND c.super_game_id = d.super_game_id
                		group by a.source_system ,a.server_id ,d.boss_server_id, c.super_game_id
                	) e on a.source_system = e.e_source_system AND a.server_id = e.e_server_id AND a.super_game_id = e.e_super_game_id AND a.boss_server_id = e.e_boss_server_id
                	LEFT JOIN (
                		select
                			-- 超父-原始区服维度
                			a.source_system as f_source_system,
                			a.server_id as f_server_id,
                			d.boss_server_id as f_boss_server_id,
                			c.super_game_id as f_super_game_id,
                			count(distinct IF(Date(NOW()) = dt,user_id,NULL)) as ba1_active_num, -- 今日活跃人数
                			count(distinct IF(date_add(Date(NOW()),-1)=dt,user_id,NULL)) as ba2_active_num, -- 昨日活跃人数
                			count(distinct IF(date_add(Date(NOW()),-2)=dt,user_id,NULL)) as ba3_active_num -- 前日活跃人数
                		from dm_game_order.t_game_server a
                		left join game_dw.dw_active_log b on a.source_system = b.source_system and a.server_id = b.server_id and a.game_id = b.game_id
                		LEFT JOIN (
                			SELECT
                				source_system ,
                				id,
                				IFNULL(parent_id, id) as parent_id,
                				IFNULL(super_game_id, id) as super_game_id
                			FROM dm_game_order.t_game
                		) c on a.source_system = c.source_system AND a.game_id = c.id
                		LEFT JOIN (
                			SELECT
                				source_system,
                				server_id,
                				boss_server_id,
                				super_game_id
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY source_system, super_game_id, server_id, boss_server_id
                		) d on a.source_system = d.source_system AND a.server_id = d.server_id AND c.super_game_id = d.super_game_id
                		group by a.source_system ,a.server_id ,d.boss_server_id, c.super_game_id
                	) f on a.source_system = f.f_source_system AND a.server_id = f.f_server_id AND a.super_game_id = f.f_super_game_id AND a.boss_server_id = f.f_boss_server_id
                	LEFT JOIN (
                		SELECT
                			source_system as h_source_system,
                			game_id as h_game_id,
                			server_id as h_server_id,
                			server_name,
                			is_merge -- 是否参与过合服
                		FROM dm_game_order.t_game_server_merge
                		WHERE is_delete = 0
                	) h on a.source_system = h.h_source_system AND a.boss_server_id = h.h_server_id AND a.super_game_id = h.h_game_id
                	LEFT JOIN (
                		SELECT
                			source_system as i_source_system,
                			game_id as i_game_id,
                			server_id as i_server_id,
                			server_name
                		FROM dm_game_order.t_game_server_merge
                		WHERE is_delete = 0
                	) i on a.source_system = i.i_source_system AND a.server_id = i.i_server_id AND a.super_game_id = i.i_game_id
                	GROUP BY source_system,server_id,boss_server_id,super_game_id
                ) a
                """;
    }

    /**
     * 原始服-超父游戏维度（区服相关信息）(总记录数)
     * @return String
     */
    private String getRoleCombatRankServerDataSqlCCount(Criteria cri) {
        return """
                SELECT
                    COUNT(1)
                FROM
                (
                	SELECT
                		-- 查询每个超父游戏-原始区服维度的信息
                		source_system,
                		super_game_id , -- 超父游戏ID
                		MAX(super_game_name) as super_game_name , -- 超父游戏名
                		server_id, -- 区服ID
                		IFNULL(MAX(i.server_name), MAX(a.server_name)) as server_name, -- 区服名
                		MIN(dt) as dt, -- 开服日期
                		TIMESTAMPDIFF(DAY, DATE(MIN(dt)), DATE_ADD(Date(NOW()),1)) start_day, -- 开服天数
                		boss_server_id, -- 合服ID
                		MAX(boss_server_name) as boss_server_name, -- 合服名
                		MAX(merge_time) as merge_time , -- 合服时间
                		MAX(sum_server_count) as sum_server_count, -- 合服次数
                		IFNULL(MAX(h.is_merge), 0) as is_merge, -- 是否参与过合服
                		SUM(server_combat_num) as server_combat_num, -- 区服战力
                		MAX(c.new_user_count) as new_user_count, -- 新用户人数
                		MAX(d.create_role_count) as create_role_count , -- 创角人数
                	    MAX(b.role_num) as role_count , -- 角色数量
                	    MAX(e.total_num) as total_num, -- 总付费人数
                	    SUM(total_amount) as total_amount, -- 总付费金额
                	    ROUND(IF(MAX(e.total_num) > 0, SUM(total_amount) / MAX(e.total_num), 0), 2) as total_arppu, -- 总付费ARPPU
                	    ROUND(IF(MAX(d.create_role_count) > 0, MAX(e.total_num) / MAX(d.create_role_count), 0), 4) as amount_rate, -- 付费率
                	    SUM(ba1) as ba1, -- 今日付费金额
                	    MAX(e.ba1_num) as ba1_num, -- 今日付费人数
                	    SUM(ba1_count) as ba1_count, -- 今日付费次数
                	    MAX(f.ba1_active_num) as ba1_active_num, -- 今日活跃人数
                	    SUM(ba2) as ba2, -- 昨日付费金额
                	    MAX(e.ba2_num) as ba2_num, -- 昨日付费人数
                	    SUM(ba2_count) as ba2_count, -- 昨日付费次数
                	    MAX(f.ba2_active_num) as ba2_active_num, -- 昨日活跃人数
                	    SUM(ba3) as ba3, -- 前天付费金额
                	    MAX(e.ba3_num) as ba3_num, -- 前天付费人数
                	    SUM(ba3_count) as ba3_count, -- 前天付费次数
                	    MAX(f.ba3_active_num) as ba3_active_num -- 前天活跃人数
                	FROM (
                		SELECT
                			-- 子查询，查询每个父游戏-原始区服维度的信息
                			source_system,
                			parent_game_id, -- 父游戏ID
                			MAX(parent_game_name) as parent_game_name, -- 父游戏名
                			super_game_id , -- 超父游戏ID
                			MAX(super_game_name) as super_game_name , -- 超父游戏名
                			server_id, -- 区服ID
                			MAX(server_name) as server_name, -- 区服名
                			MIN(dt) as dt, -- 开服日期
                			boss_server_id, -- 合服ID
                			MAX(boss_server_name) as boss_server_name, -- 合服名
                			MAX(merge_time) as merge_time , -- 合服时间
                			MAX(sum_server_count) as sum_server_count, -- 合服次数
                			SUM(server_combat_num) as server_combat_num, -- 区服战力
                		    SUM(total_amount) as total_amount, -- 总付费金额
                		    SUM(ba1) as ba1, -- 今日付费金额
                		    SUM(ba1_count) as ba1_count, -- 今日付费次数
                		    SUM(ba2) as ba2, -- 昨日付费金额
                		    SUM(ba2_count) as ba2_count, -- 昨日付费次数
                		    SUM(ba3) as ba3, -- 前天付费金额
                		    SUM(ba3_count) as ba3_count -- 前天付费次数
                		FROM (
                			-- 子查询，查询每个子游戏-原始区服维度的信息
                			SELECT
                				source_system,
                				game_id, -- 子游戏ID
                				MAX(game_name) as game_name, -- 子游戏名
                				parent_game_id, -- 父游戏ID
                				MAX(parent_game_name) as parent_game_name, -- 父游戏名
                				super_game_id , -- 超父游戏ID
                				MAX(super_game_name) as super_game_name , -- 超父游戏名
                				server_id, -- 区服ID
                				MAX(server_name) as server_name, -- 区服名
                				MIN(`date`) as dt, -- 开服日期
                				boss_server_id, -- 合服ID
                				MAX(boss_server_name) as boss_server_name, -- 合服名
                				MAX(merge_time) as merge_time , -- 合服时间
                				MAX(sum_server_count) as sum_server_count, -- 合服次数
                				MAX(server_combat_num) as server_combat_num, -- 区服战力
                				MAX(total_amount) as total_amount, -- 总付费金额
                				MAX(ba1) as ba1, -- 今日付费金额
                				MAX(ba1_count) as ba1_count, -- 今日付费次数
                				MAX(ba2) as ba2, -- 昨日付费金额
                				MAX(ba2_count) as ba2_count, -- 昨日付费次数
                				MAX(ba3) as ba3, -- 前天付费金额
                				MAX(ba3_count) as ba3_count -- 前天付费次数
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY game_id ,parent_game_id ,super_game_id ,server_id ,boss_server_id ,source_system
                		) a GROUP BY parent_game_id ,super_game_id ,server_id ,boss_server_id ,source_system
                	) a
                	LEFT JOIN (
                		SELECT
                			-- 超父-原始区服维度
                			source_system as b_source_system,
                			super_game_id as b_super_game_id,
                			server_id as b_server_id,
                			boss_server_id as b_boss_server_id,
                			COUNT(DISTINCT role_id) as role_num -- 角色数量
                		FROM game_ads.ads_role_combat_num_ranking
                		GROUP BY super_game_id, source_system, server_id , boss_server_id
                	) b on a.source_system = b.b_source_system AND a.server_id = b.b_server_id AND a.super_game_id = b.b_super_game_id AND a.boss_server_id = b.b_boss_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-原始区服维度
                			a.source_system as c_source_system,
                			a.super_game_id as c_super_game_id,
                			a.server_id as c_server_id,
                			b.boss_server_id as c_boss_server_id,
                			COUNT(a.association_user_id) as new_user_count -- 新用户人数
                		FROM (
                			SELECT
                				a.source_system ,
                				b.id,
                				a.game_id ,
                				a.server_id,
                				a.role_id ,
                				a.role_name ,
                				b.association_user_id ,
                				a.create_time ,
                				IFNULL(c.super_game_id , a.game_id) as super_game_id,
                				ROW_NUMBER()over(partition by b.association_user_id , a.source_system order by a.create_time asc, a.user_id asc) as num
                			FROM dm_game_order.t_game_user_role a
                			LEFT JOIN dm_game_order.t_game_user b on a.source_system = b.source_system AND a.user_id = b.id
                			LEFT JOIN dm_game_order.t_game c on a.source_system = c.source_system AND a.game_id = c.id
                		) a
                		LEFT JOIN (
                			SELECT
                				source_system,
                				server_id,
                				boss_server_id,
                				super_game_id
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY source_system, super_game_id, server_id, boss_server_id
                		) b on a.source_system = b.source_system AND a.server_id = b.server_id AND a.super_game_id = b.super_game_id
                		WHERE num = 1
                		GROUP BY a.super_game_id, a.server_id, b.boss_server_id, a.source_system
                	) c on a.source_system = c.c_source_system AND a.super_game_id = c.c_super_game_id AND a.server_id = c.c_server_id AND a.boss_server_id = c.c_boss_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-原始区服维度
                			source_system as d_source_system,
                			super_game_id as d_super_game_id,
                			server_id as d_server_id,
                			boss_server_id as d_boss_server_id,
                			COUNT(DISTINCT user_id) as create_role_count -- 创角人数
                		FROM game_ads.ads_role_combat_num_ranking
                		GROUP BY super_game_id, source_system, server_id , boss_server_id
                	) d on a.source_system = d.d_source_system AND a.server_id = d.d_server_id AND a.super_game_id = d.d_super_game_id AND a.boss_server_id = d.d_boss_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-原始区服维度
                			a.source_system as e_source_system,
                			a.server_id as e_server_id,
                			c.super_game_id as e_super_game_id,
                			d.boss_server_id as e_boss_server_id,
                			count(DISTINCT IF(Date(NOW()) = day,user_id,NULL)) as ba1_num, -- 今日充值人数
                			count(DISTINCT IF(date_add(Date(NOW()),-1)=day,user_id,NULL)) as ba2_num, -- 昨日充值人数
                			count(DISTINCT IF(date_add(Date(NOW()),-2)=day,user_id,NULL)) as ba3_num, -- 前天充值人数
                			count(DISTINCT user_id) as total_num -- 总充值人数
                		FROM dm_game_order.t_game_server a
                		LEFT JOIN game_dw.dw_order_day_amount b
                		on a.source_system = b.source_system and a.server_id =b.server_id and a.game_id =b.game_id
                		LEFT JOIN (
                			SELECT
                				source_system ,
                				id,
                				IFNULL(parent_id, id) as parent_id,
                				IFNULL(super_game_id, id) as super_game_id
                			FROM dm_game_order.t_game
                		) c on a.source_system = c.source_system AND a.game_id = c.id
                		LEFT JOIN (
                			SELECT
                				source_system,
                				server_id,
                				boss_server_id,
                				super_game_id
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY source_system, super_game_id, server_id, boss_server_id
                		) d on a.source_system = d.source_system AND a.server_id = d.server_id AND c.super_game_id = d.super_game_id
                		group by a.source_system ,a.server_id ,d.boss_server_id, c.super_game_id
                	) e on a.source_system = e.e_source_system AND a.server_id = e.e_server_id AND a.super_game_id = e.e_super_game_id AND a.boss_server_id = e.e_boss_server_id
                	LEFT JOIN (
                		select
                			-- 超父-原始区服维度
                			a.source_system as f_source_system,
                			a.server_id as f_server_id,
                			d.boss_server_id as f_boss_server_id,
                			c.super_game_id as f_super_game_id,
                			count(distinct IF(Date(NOW()) = dt,user_id,NULL)) as ba1_active_num, -- 今日活跃人数
                			count(distinct IF(date_add(Date(NOW()),-1)=dt,user_id,NULL)) as ba2_active_num, -- 昨日活跃人数
                			count(distinct IF(date_add(Date(NOW()),-2)=dt,user_id,NULL)) as ba3_active_num -- 前日活跃人数
                		from dm_game_order.t_game_server a
                		left join game_dw.dw_active_log b on a.source_system = b.source_system and a.server_id = b.server_id and a.game_id = b.game_id
                		LEFT JOIN (
                			SELECT
                				source_system ,
                				id,
                				IFNULL(parent_id, id) as parent_id,
                				IFNULL(super_game_id, id) as super_game_id
                			FROM dm_game_order.t_game
                		) c on a.source_system = c.source_system AND a.game_id = c.id
                		LEFT JOIN (
                			SELECT
                				source_system,
                				server_id,
                				boss_server_id,
                				super_game_id
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY source_system, super_game_id, server_id, boss_server_id
                		) d on a.source_system = d.source_system AND a.server_id = d.server_id AND c.super_game_id = d.super_game_id
                		group by a.source_system ,a.server_id ,d.boss_server_id, c.super_game_id
                	) f on a.source_system = f.f_source_system AND a.server_id = f.f_server_id AND a.super_game_id = f.f_super_game_id AND a.boss_server_id = f.f_boss_server_id
                	LEFT JOIN (
                		SELECT
                			source_system as h_source_system,
                			game_id as h_game_id,
                			server_id as h_server_id,
                			server_name,
                			is_merge -- 是否参与过合服
                		FROM dm_game_order.t_game_server_merge
                		WHERE is_delete = 0
                	) h on a.source_system = h.h_source_system AND a.boss_server_id = h.h_server_id AND a.super_game_id = h.h_game_id
                	LEFT JOIN (
                		SELECT
                			source_system as i_source_system,
                			game_id as i_game_id,
                			server_id as i_server_id,
                			server_name
                		FROM dm_game_order.t_game_server_merge
                		WHERE is_delete = 0
                	) i on a.source_system = i.i_source_system AND a.server_id = i.i_server_id AND a.super_game_id = i.i_game_id
                	GROUP BY source_system,server_id,boss_server_id,super_game_id
                ) a
                """ + cri;
    }

    /**
     * 合服-超父游戏维度（区服相关信息）
     * @return String
     */
    private String getRoleCombatRankServerDataSqlD() {
        return """
                SELECT
                	*
                FROM
                (
                	SELECT
                		-- 查询每个超父游戏-合服维度的信息
                		source_system,
                		super_game_id , -- 超父游戏ID
                		MAX(super_game_name) as super_game_name , -- 超父游戏名
                		boss_server_id as server_id, -- 合服ID
                		IFNULL(MAX(h.server_name), MAX(a.boss_server_name)) as server_name, -- 合服名
                		MIN(merge_time) as dt, -- 合服时间
                		TIMESTAMPDIFF(DAY, DATE(MIN(merge_time)), DATE_ADD(Date(NOW()),1)) start_day, -- 开服天数
                		MAX(sum_server_count) as sum_server_count, -- 合服次数
                		IFNULL(MAX(h.is_merge), 0) as is_merge, -- 是否参与过合服
                		IFNULL(MIN(b.min_server_start_time), MIN(merge_time)) as min_server_start_time, -- 最小原始子服开服时间
                  	    IFNULL(TIMESTAMPDIFF(DAY, DATE(MIN(b.min_server_start_time)), DATE_ADD(Date(NOW()),1)),
                  	    	TIMESTAMPDIFF(DAY, DATE(MIN(merge_time)), DATE_ADD(Date(NOW()),1))) as min_server_start_days, -- 最小原始子服开服距今天数
                		MAX(b.main_server_id) as main_server_id, -- 主服ID
                		MAX(b.main_server_name) as main_server_name, -- 主服名
                		MAX(son_server_ids) as son_server_ids, -- 合服ID列表
                		MAX(son_server_ids_name) as son_server_ids_name, -- 合服ID列表
                	    MAX(source_server_ids) as source_server_ids, -- 原始子服ID列表
                	    MAX(source_server_ids_name) as source_server_ids_name, -- 原始子服名列表
                		MAX(server_combat_num) as server_combat_num, -- 区服战力
                	    MAX(d.new_user_count) as new_user_count, -- 新用户人数
                	    MAX(e.create_role_count) as create_role_count, -- 创角人数
                	    MAX(c.role_count) as role_count, -- 角色数量
                	    MAX(f.total_num) as total_num, -- 总付费人数
                	    MAX(total_amount) as total_amount, -- 总付费金额
                	    ROUND(IF(MAX(f.total_num) > 0, MAX(total_amount) / MAX(f.total_num), 0), 2) as total_arppu, -- 总付费ARPPU
                	    ROUND(IF(MAX(e.create_role_count) > 0, MAX(f.total_num) / MAX(e.create_role_count), 0), 4) as amount_rate, -- 付费率
                	    MAX(ba1) as ba1, -- 今日付费金额
                	    MAX(f.ba1_num) as ba1_num, -- 今日付费人数
                	    MAX(ba1_count) as ba1_count, -- 今日付费次数
                	    MAX(g.ba1_active_num) as ba1_active_num, -- 今日活跃人数
                	    MAX(ba2) as ba2, -- 昨日付费金额
                	    MAX(f.ba2_num) as ba2_num, -- 昨日付费人数
                	    MAX(ba2_count) as ba2_count, -- 昨日付费次数
                	    MAX(g.ba2_active_num) as ba2_active_num, -- 昨日活跃人数
                	    MAX(ba3) as ba3, -- 前天付费金额
                	    MAX(f.ba3_num) as ba3_num, -- 前天付费人数
                	    MAX(ba3_count) as ba3_count, -- 前天付费次数
                	    MAX(g.ba3_active_num) as ba3_active_num -- 前天活跃人数
                	FROM (
                		SELECT
                			-- 子查询，查询每个超父游戏-原始区服维度的信息
                			source_system,
                			super_game_id , -- 超父游戏ID
                			MAX(super_game_name) as super_game_name , -- 超父游戏名
                			-- server_id, -- 区服ID
                			MAX(server_name) as server_name, -- 区服名
                			boss_server_id, -- 合服ID
                			MAX(boss_server_name) as boss_server_name, -- 合服名
                			MIN(merge_time) as merge_time , -- 合服时间
                			MAX(sum_server_count) as sum_server_count, -- 合服次数
                			SUM(server_combat_num) as server_combat_num, -- 区服战力
                		    SUM(total_amount) as total_amount, -- 总付费金额
                		    SUM(ba1) as ba1, -- 今日付费金额
                		    SUM(ba1_count) as ba1_count, -- 今日付费次数
                		    SUM(ba2) as ba2, -- 昨日付费金额
                		    SUM(ba2_count) as ba2_count, -- 昨日付费次数
                		    SUM(ba3) as ba3, -- 前天付费金额
                		    SUM(ba3_count) as ba3_count -- 前天付费次数
                		FROM (
                			SELECT
                				-- 子查询，查询每个父游戏-区服维度的信息
                				source_system,
                				parent_game_id, -- 父游戏ID
                				MAX(parent_game_name) as parent_game_name, -- 父游戏名
                				super_game_id , -- 超父游戏ID
                				MAX(super_game_name) as super_game_name , -- 超父游戏名
                				server_id, -- 区服ID
                				MAX(server_name) as server_name, -- 区服名
                				boss_server_id, -- 合服ID
                				MAX(boss_server_name) as boss_server_name, -- 合服名
                				MIN(merge_time) as merge_time , -- 合服时间
                				MAX(sum_server_count) as sum_server_count, -- 合服次数
                				SUM(server_combat_num) as server_combat_num, -- 区服战力
                			    SUM(total_amount) as total_amount, -- 总付费金额
                			    SUM(ba1) as ba1, -- 今日付费金额
                			    SUM(ba1_count) as ba1_count, -- 今日付费次数
                			    SUM(ba2) as ba2, -- 昨日付费金额
                			    SUM(ba2_count) as ba2_count, -- 昨日付费次数
                			    SUM(ba3) as ba3, -- 前天付费金额
                			    SUM(ba3_count) as ba3_count -- 前天付费次数
                			FROM (
                				-- 子查询，查询每个子游戏-原始区服维度的信息
                				SELECT
                					source_system,
                					game_id, -- 子游戏ID
                					MAX(game_name) as game_name, -- 子游戏名
                					parent_game_id, -- 父游戏ID
                					MAX(parent_game_name) as parent_game_name, -- 父游戏名
                					super_game_id , -- 超父游戏ID
                					MAX(super_game_name) as super_game_name , -- 超父游戏名
                					server_id, -- 区服ID
                					MAX(server_name) as server_name, -- 区服名
                					boss_server_id, -- 合服ID
                					MAX(boss_server_name) as boss_server_name, -- 合服名
                					MIN(merge_time) as merge_time , -- 合服时间
                					MAX(sum_server_count) as sum_server_count, -- 合服次数
                					MAX(server_combat_num) as server_combat_num, -- 区服战力
                					MAX(total_amount) as total_amount, -- 总付费金额
                					MAX(ba1) as ba1, -- 今日付费金额
                					MAX(ba1_count) as ba1_count, -- 今日付费次数
                					MAX(ba2) as ba2, -- 昨日付费金额
                					MAX(ba2_count) as ba2_count, -- 昨日付费次数
                					MAX(ba3) as ba3, -- 前天付费金额
                					MAX(ba3_count) as ba3_count -- 前天付费次数
                				FROM game_ads.ads_role_combat_num_ranking
                				GROUP BY game_id ,parent_game_id ,super_game_id ,server_id ,boss_server_id ,source_system
                			) a GROUP BY parent_game_id ,super_game_id ,server_id ,boss_server_id ,source_system
                		) a GROUP BY super_game_id ,boss_server_id ,source_system
                	) a
                	LEFT JOIN (
                		SELECT
                			a.source_system as b_source_system,
                			a.server_id as b_server_id,
                			a.game_id as b_game_id,
                			MAX(a.server_name) as server_name,
                			MAX(a.main_server_id) as main_server_id, -- 主服ID
                			MAX(c.server_name) as main_server_name, -- 主服名
                			MAX(a.son_server_ids) as son_server_ids , -- 合服子服ID列表
                			MAX(d.son_server_ids_name) as son_server_ids_name , -- 合服子服ID列表
                			GROUP_CONCAT(a.source_server_ids, ',') as source_server_ids, -- 原始子服ID列表
                			GROUP_CONCAT(b.server_name , ',') as source_server_ids_name, -- 原始子服名
                			MIN(e.start_time) as min_server_start_time -- 最小原始子服开服时间
                		FROM (
                			SELECT
                				*
                			FROM dm_game_order.t_game_server_merge
                			WHERE is_delete = 0
                		) a
                		LEFT JOIN (
                			SELECT
                				*
                			FROM dm_game_order.t_game_server_merge
                			WHERE is_delete = 0
                		) b on a.source_system = b.source_system AND a.game_id = b.game_id AND a.source_server_ids = b.server_id
                		LEFT JOIN (
                			SELECT
                				source_system ,
                				game_id ,
                				server_id  ,
                				MAX(server_name) as server_name
                			FROM dm_game_order.t_game_server_merge
                			WHERE is_delete = 0
                			GROUP BY server_id ,source_system ,game_id
                		) c on a.source_system = c.source_system AND a.game_id = c.game_id AND a.main_server_id = c.server_id
                		LEFT JOIN (
                	 		SELECT
                	 			a.source_system ,
                	 			a.game_id,
                	 			a.server_id,
                	 			GROUP_CONCAT(b.server_name , ',') as son_server_ids_name -- 合服列表名
                	 		FROM (
                	 			SELECT
                	 				*
                	 			FROM dm_game_order.t_game_server_son_merge
                	 			WHERE is_delete = 0
                	 		) a
                	 		LEFT JOIN (
                	 			SELECT
                	 				source_system ,
                	 				game_id ,
                	 				server_id  ,
                	 				MAX(server_name) as server_name
                	 			FROM dm_game_order.t_game_server_merge
                	 			WHERE is_delete = 0
                	 			GROUP BY server_id ,source_system ,game_id
                	 		) b on a.source_system = b.source_system AND a.game_id = b.game_id AND a.son_server_ids = b.server_id
                	 		GROUP BY a.source_system , a.game_id, a.server_id
                	 	) d on a.source_system = d.source_system AND a.game_id = d.game_id AND a.server_id = d.server_id
                		LEFT JOIN (
                			SELECT
                				*
                			FROM dm_game_order.t_game_server_merge
                			WHERE is_delete = 0
                		) e on a.source_system = e.source_system AND a.source_server_ids = e.server_id AND a.game_id = e.game_id
                		group by a.server_id , a.source_system , a.game_id
                	) b ON a.source_system = b.b_source_system AND a.super_game_id = b.b_game_id AND a.boss_server_id = b.b_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-合服维度
                			source_system as c_source_system,
                			super_game_id as c_super_game_id,
                			boss_server_id as c_boss_server_id,
                			COUNT(DISTINCT role_id) as role_count -- 角色数量
                		FROM game_ads.ads_role_combat_num_ranking
                		GROUP BY super_game_id, source_system, boss_server_id
                	) c on a.source_system = c.c_source_system AND a.super_game_id = c.c_super_game_id AND a.boss_server_id = c.c_boss_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-合服维度
                			a.source_system as d_source_system,
                			a.super_game_id as d_super_game_id,
                			b.boss_server_id as d_boss_server_id,
                			COUNT(a.association_user_id) as new_user_count -- 新用户人数
                		FROM (
                			SELECT
                				a.source_system ,
                				b.id,
                				a.game_id ,
                				a.server_id,
                				a.role_id ,
                				a.role_name ,
                				b.association_user_id ,
                				a.create_time ,
                				IFNULL(c.super_game_id , a.game_id) as super_game_id,
                				ROW_NUMBER()over(partition by b.association_user_id , a.source_system order by a.create_time asc, a.user_id asc) as num
                			FROM dm_game_order.t_game_user_role a
                			LEFT JOIN dm_game_order.t_game_user b on a.source_system = b.source_system AND a.user_id = b.id
                			LEFT JOIN dm_game_order.t_game c on a.source_system = c.source_system AND a.game_id = c.id
                		) a
                		LEFT JOIN (
                			SELECT
                				source_system,
                				server_id,
                				boss_server_id,
                				super_game_id
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY source_system, super_game_id, server_id, boss_server_id
                		) b on a.source_system = b.source_system AND a.server_id = b.server_id AND a.super_game_id = b.super_game_id
                		WHERE num = 1
                		GROUP BY a.super_game_id, b.boss_server_id, a.source_system
                	) d on a.source_system = d.d_source_system AND a.super_game_id = d.d_super_game_id AND a.boss_server_id = d.d_boss_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-合服维度
                			source_system as e_source_system,
                			super_game_id as e_super_game_id,
                			boss_server_id as e_boss_server_id,
                			COUNT(DISTINCT user_id) as create_role_count -- 创角人数
                		FROM game_ads.ads_role_combat_num_ranking
                		GROUP BY super_game_id, source_system, boss_server_id
                	) e on a.source_system = e.e_source_system AND a.super_game_id = e.e_super_game_id AND a.boss_server_id = e.e_boss_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-合服维度
                			a.source_system as f_source_system,
                			c.super_game_id as f_super_game_id,
                			d.boss_server_id as f_boss_server_id,
                			count(DISTINCT IF(Date(NOW()) = day,user_id,NULL)) as ba1_num, -- 今日充值人数
                			count(DISTINCT IF(date_add(Date(NOW()),-1)=day,user_id,NULL)) as ba2_num, -- 昨日充值人数
                			count(DISTINCT IF(date_add(Date(NOW()),-2)=day,user_id,NULL)) as ba3_num, -- 前天充值人数
                			count(DISTINCT user_id) as total_num -- 总充值人数
                		FROM dm_game_order.t_game_server a
                		LEFT JOIN game_dw.dw_order_day_amount b
                		on a.source_system = b.source_system and a.server_id =b.server_id and a.game_id =b.game_id
                		LEFT JOIN (
                			SELECT
                				source_system ,
                				id,
                				IFNULL(parent_id, id) as parent_id,
                				IFNULL(super_game_id, id) as super_game_id
                			FROM dm_game_order.t_game
                		) c on a.source_system = c.source_system AND a.game_id = c.id
                		LEFT JOIN (
                			SELECT
                				source_system,
                				server_id,
                				boss_server_id,
                				super_game_id
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY source_system, super_game_id, server_id, boss_server_id
                		) d on a.source_system = d.source_system AND a.server_id = d.server_id AND c.super_game_id = d.super_game_id
                		group by a.source_system ,d.boss_server_id, c.super_game_id
                	) f on a.source_system = f.f_source_system AND a.super_game_id = f.f_super_game_id AND a.boss_server_id = f.f_boss_server_id
                	LEFT JOIN (
                		select
                			-- 超父-合服维度
                			a.source_system as g_source_system,
                			d.boss_server_id as g_boss_server_id,
                			c.super_game_id as g_super_game_id,
                			count(distinct IF(Date(NOW()) = dt,user_id,NULL)) as ba1_active_num, -- 今日活跃人数
                			count(distinct IF(date_add(Date(NOW()),-1)=dt,user_id,NULL)) as ba2_active_num, -- 昨日活跃人数
                			count(distinct IF(date_add(Date(NOW()),-2)=dt,user_id,NULL)) as ba3_active_num -- 前日活跃人数
                		from dm_game_order.t_game_server a
                		left join game_dw.dw_active_log b on a.source_system = b.source_system and a.server_id = b.server_id and a.game_id = b.game_id
                		LEFT JOIN (
                			SELECT
                				source_system ,
                				id,
                				IFNULL(parent_id, id) as parent_id,
                				IFNULL(super_game_id, id) as super_game_id
                			FROM dm_game_order.t_game
                		) c on a.source_system = c.source_system AND a.game_id = c.id
                		LEFT JOIN (
                			SELECT
                				source_system,
                				server_id,
                				boss_server_id,
                				super_game_id
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY source_system, super_game_id, server_id, boss_server_id
                		) d on a.source_system = d.source_system AND a.server_id = d.server_id AND c.super_game_id = d.super_game_id
                		group by a.source_system ,d.boss_server_id, c.super_game_id
                	) g on a.source_system = g.g_source_system AND a.super_game_id = g.g_super_game_id AND a.boss_server_id = g.g_boss_server_id
                	LEFT JOIN (
                		SELECT
                			source_system as h_source_system,
                			game_id,
                			server_id,
                			server_name,
                			is_merge -- 是否参与过合服
                		FROM dm_game_order.t_game_server_merge
                		WHERE is_delete = 0
                	) h on a.source_system = h.h_source_system AND a.boss_server_id = h.server_id AND a.super_game_id = h.game_id
                	GROUP BY source_system,boss_server_id,super_game_id
                ) a
                """;
    }

    /**
     * 合服-超父游戏维度（区服相关信息）(总记录数)
     * @return String
     */
    private String getRoleCombatRankServerDataSqlDCount(Criteria cri) {
        return """
                SELECT
                	COUNT(1)
                FROM
                (
                	SELECT
                		-- 查询每个超父游戏-合服维度的信息
                		source_system,
                		super_game_id , -- 超父游戏ID
                		MAX(super_game_name) as super_game_name , -- 超父游戏名
                		boss_server_id as server_id, -- 合服ID
                		IFNULL(MAX(h.server_name), MAX(a.boss_server_name)) as server_name, -- 合服名
                		MIN(merge_time) as dt, -- 合服时间
                		TIMESTAMPDIFF(DAY, DATE(MIN(merge_time)), DATE_ADD(Date(NOW()),1)) start_day, -- 开服天数
                		MAX(sum_server_count) as sum_server_count, -- 合服次数
                		IFNULL(MAX(h.is_merge), 0) as is_merge, -- 是否参与过合服
                		IFNULL(MIN(b.min_server_start_time), MIN(merge_time)) as min_server_start_time, -- 最小原始子服开服时间
                  	    IFNULL(TIMESTAMPDIFF(DAY, DATE(MIN(b.min_server_start_time)), DATE_ADD(Date(NOW()),1)),
                  	    	TIMESTAMPDIFF(DAY, DATE(MIN(merge_time)), DATE_ADD(Date(NOW()),1))) as min_server_start_days, -- 最小原始子服开服距今天数
                		MAX(b.main_server_id) as main_server_id, -- 主服ID
                		MAX(b.main_server_name) as main_server_name, -- 主服名
                		MAX(son_server_ids) as son_server_ids, -- 合服ID列表
                		MAX(son_server_ids_name) as son_server_ids_name, -- 合服ID列表
                	    MAX(source_server_ids) as source_server_ids, -- 原始子服ID列表
                	    MAX(source_server_ids_name) as source_server_ids_name, -- 原始子服名列表
                		MAX(server_combat_num) as server_combat_num, -- 区服战力
                	    MAX(d.new_user_count) as new_user_count, -- 新用户人数
                	    MAX(e.create_role_count) as create_role_count, -- 创角人数
                	    MAX(c.role_count) as role_count, -- 角色数量
                	    MAX(f.total_num) as total_num, -- 总付费人数
                	    MAX(total_amount) as total_amount, -- 总付费金额
                	    ROUND(IF(MAX(f.total_num) > 0, MAX(total_amount) / MAX(f.total_num), 0), 2) as total_arppu, -- 总付费ARPPU
                	    ROUND(IF(MAX(e.create_role_count) > 0, MAX(f.total_num) / MAX(e.create_role_count), 0), 4) as amount_rate, -- 付费率
                	    MAX(ba1) as ba1, -- 今日付费金额
                	    MAX(f.ba1_num) as ba1_num, -- 今日付费人数
                	    MAX(ba1_count) as ba1_count, -- 今日付费次数
                	    MAX(g.ba1_active_num) as ba1_active_num, -- 今日活跃人数
                	    MAX(ba2) as ba2, -- 昨日付费金额
                	    MAX(f.ba2_num) as ba2_num, -- 昨日付费人数
                	    MAX(ba2_count) as ba2_count, -- 昨日付费次数
                	    MAX(g.ba2_active_num) as ba2_active_num, -- 昨日活跃人数
                	    MAX(ba3) as ba3, -- 前天付费金额
                	    MAX(f.ba3_num) as ba3_num, -- 前天付费人数
                	    MAX(ba3_count) as ba3_count, -- 前天付费次数
                	    MAX(g.ba3_active_num) as ba3_active_num -- 前天活跃人数
                	FROM (
                		SELECT
                			-- 子查询，查询每个超父游戏-原始区服维度的信息
                			source_system,
                			super_game_id , -- 超父游戏ID
                			MAX(super_game_name) as super_game_name , -- 超父游戏名
                			-- server_id, -- 区服ID
                			MAX(server_name) as server_name, -- 区服名
                			boss_server_id, -- 合服ID
                			MAX(boss_server_name) as boss_server_name, -- 合服名
                			MIN(merge_time) as merge_time , -- 合服时间
                			MAX(sum_server_count) as sum_server_count, -- 合服次数
                			SUM(server_combat_num) as server_combat_num, -- 区服战力
                		    SUM(total_amount) as total_amount, -- 总付费金额
                		    SUM(ba1) as ba1, -- 今日付费金额
                		    SUM(ba1_count) as ba1_count, -- 今日付费次数
                		    SUM(ba2) as ba2, -- 昨日付费金额
                		    SUM(ba2_count) as ba2_count, -- 昨日付费次数
                		    SUM(ba3) as ba3, -- 前天付费金额
                		    SUM(ba3_count) as ba3_count -- 前天付费次数
                		FROM (
                			SELECT
                				-- 子查询，查询每个父游戏-区服维度的信息
                				source_system,
                				parent_game_id, -- 父游戏ID
                				MAX(parent_game_name) as parent_game_name, -- 父游戏名
                				super_game_id , -- 超父游戏ID
                				MAX(super_game_name) as super_game_name , -- 超父游戏名
                				server_id, -- 区服ID
                				MAX(server_name) as server_name, -- 区服名
                				boss_server_id, -- 合服ID
                				MAX(boss_server_name) as boss_server_name, -- 合服名
                				MIN(merge_time) as merge_time , -- 合服时间
                				MAX(sum_server_count) as sum_server_count, -- 合服次数
                				SUM(server_combat_num) as server_combat_num, -- 区服战力
                			    SUM(total_amount) as total_amount, -- 总付费金额
                			    SUM(ba1) as ba1, -- 今日付费金额
                			    SUM(ba1_count) as ba1_count, -- 今日付费次数
                			    SUM(ba2) as ba2, -- 昨日付费金额
                			    SUM(ba2_count) as ba2_count, -- 昨日付费次数
                			    SUM(ba3) as ba3, -- 前天付费金额
                			    SUM(ba3_count) as ba3_count -- 前天付费次数
                			FROM (
                				-- 子查询，查询每个子游戏-原始区服维度的信息
                				SELECT
                					source_system,
                					game_id, -- 子游戏ID
                					MAX(game_name) as game_name, -- 子游戏名
                					parent_game_id, -- 父游戏ID
                					MAX(parent_game_name) as parent_game_name, -- 父游戏名
                					super_game_id , -- 超父游戏ID
                					MAX(super_game_name) as super_game_name , -- 超父游戏名
                					server_id, -- 区服ID
                					MAX(server_name) as server_name, -- 区服名
                					boss_server_id, -- 合服ID
                					MAX(boss_server_name) as boss_server_name, -- 合服名
                					MIN(merge_time) as merge_time , -- 合服时间
                					MAX(sum_server_count) as sum_server_count, -- 合服次数
                					MAX(server_combat_num) as server_combat_num, -- 区服战力
                					MAX(total_amount) as total_amount, -- 总付费金额
                					MAX(ba1) as ba1, -- 今日付费金额
                					MAX(ba1_count) as ba1_count, -- 今日付费次数
                					MAX(ba2) as ba2, -- 昨日付费金额
                					MAX(ba2_count) as ba2_count, -- 昨日付费次数
                					MAX(ba3) as ba3, -- 前天付费金额
                					MAX(ba3_count) as ba3_count -- 前天付费次数
                				FROM game_ads.ads_role_combat_num_ranking
                				GROUP BY game_id ,parent_game_id ,super_game_id ,server_id ,boss_server_id ,source_system
                			) a GROUP BY parent_game_id ,super_game_id ,server_id ,boss_server_id ,source_system
                		) a GROUP BY super_game_id ,boss_server_id ,source_system
                	) a
                	LEFT JOIN (
                		SELECT
                			a.source_system as b_source_system,
                			a.server_id as b_server_id,
                			a.game_id as b_game_id,
                			MAX(a.server_name) as server_name,
                			MAX(a.main_server_id) as main_server_id, -- 主服ID
                			MAX(c.server_name) as main_server_name, -- 主服名
                			MAX(a.son_server_ids) as son_server_ids , -- 合服子服ID列表
                			MAX(d.son_server_ids_name) as son_server_ids_name , -- 合服子服ID列表
                			GROUP_CONCAT(a.source_server_ids, ',') as source_server_ids, -- 原始子服ID列表
                			GROUP_CONCAT(b.server_name , ',') as source_server_ids_name, -- 原始子服名
                			MIN(e.start_time) as min_server_start_time -- 最小原始子服开服时间
                		FROM (
                			SELECT
                				*
                			FROM dm_game_order.t_game_server_merge
                			WHERE is_delete = 0
                		) a
                		LEFT JOIN (
                			SELECT
                				*
                			FROM dm_game_order.t_game_server_merge
                			WHERE is_delete = 0
                		) b on a.source_system = b.source_system AND a.game_id = b.game_id AND a.source_server_ids = b.server_id
                		LEFT JOIN (
                			SELECT
                				source_system ,
                				game_id ,
                				server_id  ,
                				MAX(server_name) as server_name
                			FROM dm_game_order.t_game_server_merge
                			WHERE is_delete = 0
                			GROUP BY server_id ,source_system ,game_id
                		) c on a.source_system = c.source_system AND a.game_id = c.game_id AND a.main_server_id = c.server_id
                		LEFT JOIN (
                	 		SELECT
                	 			a.source_system ,
                	 			a.game_id,
                	 			a.server_id,
                	 			GROUP_CONCAT(b.server_name , ',') as son_server_ids_name -- 合服列表名
                	 		FROM (
                	 			SELECT
                	 				*
                	 			FROM dm_game_order.t_game_server_son_merge
                	 			WHERE is_delete = 0
                	 		) a
                	 		LEFT JOIN (
                	 			SELECT
                	 				source_system ,
                	 				game_id ,
                	 				server_id  ,
                	 				MAX(server_name) as server_name
                	 			FROM dm_game_order.t_game_server_merge
                	 			WHERE is_delete = 0
                	 			GROUP BY server_id ,source_system ,game_id
                	 		) b on a.source_system = b.source_system AND a.game_id = b.game_id AND a.son_server_ids = b.server_id
                	 		GROUP BY a.source_system , a.game_id, a.server_id
                	 	) d on a.source_system = d.source_system AND a.game_id = d.game_id AND a.server_id = d.server_id
                		LEFT JOIN (
                			SELECT
                				*
                			FROM dm_game_order.t_game_server_merge
                			WHERE is_delete = 0
                		) e on a.source_system = e.source_system AND a.source_server_ids = e.server_id AND a.game_id = e.game_id
                		group by a.server_id , a.source_system , a.game_id
                	) b ON a.source_system = b.b_source_system AND a.super_game_id = b.b_game_id AND a.boss_server_id = b.b_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-合服维度
                			source_system as c_source_system,
                			super_game_id as c_super_game_id,
                			boss_server_id as c_boss_server_id,
                			COUNT(DISTINCT role_id) as role_count -- 角色数量
                		FROM game_ads.ads_role_combat_num_ranking
                		GROUP BY super_game_id, source_system, boss_server_id
                	) c on a.source_system = c.c_source_system AND a.super_game_id = c.c_super_game_id AND a.boss_server_id = c.c_boss_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-合服维度
                			a.source_system as d_source_system,
                			a.super_game_id as d_super_game_id,
                			b.boss_server_id as d_boss_server_id,
                			COUNT(a.association_user_id) as new_user_count -- 新用户人数
                		FROM (
                			SELECT
                				a.source_system ,
                				b.id,
                				a.game_id ,
                				a.server_id,
                				a.role_id ,
                				a.role_name ,
                				b.association_user_id ,
                				a.create_time ,
                				IFNULL(c.super_game_id , a.game_id) as super_game_id,
                				ROW_NUMBER()over(partition by b.association_user_id , a.source_system order by a.create_time asc, a.user_id asc) as num
                			FROM dm_game_order.t_game_user_role a
                			LEFT JOIN dm_game_order.t_game_user b on a.source_system = b.source_system AND a.user_id = b.id
                			LEFT JOIN dm_game_order.t_game c on a.source_system = c.source_system AND a.game_id = c.id
                		) a
                		LEFT JOIN (
                			SELECT
                				source_system,
                				server_id,
                				boss_server_id,
                				super_game_id
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY source_system, super_game_id, server_id, boss_server_id
                		) b on a.source_system = b.source_system AND a.server_id = b.server_id AND a.super_game_id = b.super_game_id
                		WHERE num = 1
                		GROUP BY a.super_game_id, b.boss_server_id, a.source_system
                	) d on a.source_system = d.d_source_system AND a.super_game_id = d.d_super_game_id AND a.boss_server_id = d.d_boss_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-合服维度
                			source_system as e_source_system,
                			super_game_id as e_super_game_id,
                			boss_server_id as e_boss_server_id,
                			COUNT(DISTINCT user_id) as create_role_count -- 创角人数
                		FROM game_ads.ads_role_combat_num_ranking
                		GROUP BY super_game_id, source_system, boss_server_id
                	) e on a.source_system = e.e_source_system AND a.super_game_id = e.e_super_game_id AND a.boss_server_id = e.e_boss_server_id
                	LEFT JOIN (
                		SELECT
                			-- 超父-合服维度
                			a.source_system as f_source_system,
                			c.super_game_id as f_super_game_id,
                			d.boss_server_id as f_boss_server_id,
                			count(DISTINCT IF(Date(NOW()) = day,user_id,NULL)) as ba1_num, -- 今日充值人数
                			count(DISTINCT IF(date_add(Date(NOW()),-1)=day,user_id,NULL)) as ba2_num, -- 昨日充值人数
                			count(DISTINCT IF(date_add(Date(NOW()),-2)=day,user_id,NULL)) as ba3_num, -- 前天充值人数
                			count(DISTINCT user_id) as total_num -- 总充值人数
                		FROM dm_game_order.t_game_server a
                		LEFT JOIN game_dw.dw_order_day_amount b
                		on a.source_system = b.source_system and a.server_id =b.server_id and a.game_id =b.game_id
                		LEFT JOIN (
                			SELECT
                				source_system ,
                				id,
                				IFNULL(parent_id, id) as parent_id,
                				IFNULL(super_game_id, id) as super_game_id
                			FROM dm_game_order.t_game
                		) c on a.source_system = c.source_system AND a.game_id = c.id
                		LEFT JOIN (
                			SELECT
                				source_system,
                				server_id,
                				boss_server_id,
                				super_game_id
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY source_system, super_game_id, server_id, boss_server_id
                		) d on a.source_system = d.source_system AND a.server_id = d.server_id AND c.super_game_id = d.super_game_id
                		group by a.source_system ,d.boss_server_id, c.super_game_id
                	) f on a.source_system = f.f_source_system AND a.super_game_id = f.f_super_game_id AND a.boss_server_id = f.f_boss_server_id
                	LEFT JOIN (
                		select
                			-- 超父-合服维度
                			a.source_system as g_source_system,
                			d.boss_server_id as g_boss_server_id,
                			c.super_game_id as g_super_game_id,
                			count(distinct IF(Date(NOW()) = dt,user_id,NULL)) as ba1_active_num, -- 今日活跃人数
                			count(distinct IF(date_add(Date(NOW()),-1)=dt,user_id,NULL)) as ba2_active_num, -- 昨日活跃人数
                			count(distinct IF(date_add(Date(NOW()),-2)=dt,user_id,NULL)) as ba3_active_num -- 前日活跃人数
                		from dm_game_order.t_game_server a
                		left join game_dw.dw_active_log b on a.source_system = b.source_system and a.server_id = b.server_id and a.game_id = b.game_id
                		LEFT JOIN (
                			SELECT
                				source_system ,
                				id,
                				IFNULL(parent_id, id) as parent_id,
                				IFNULL(super_game_id, id) as super_game_id
                			FROM dm_game_order.t_game
                		) c on a.source_system = c.source_system AND a.game_id = c.id
                		LEFT JOIN (
                			SELECT
                				source_system,
                				server_id,
                				boss_server_id,
                				super_game_id
                			FROM game_ads.ads_role_combat_num_ranking
                			GROUP BY source_system, super_game_id, server_id, boss_server_id
                		) d on a.source_system = d.source_system AND a.server_id = d.server_id AND c.super_game_id = d.super_game_id
                		group by a.source_system ,d.boss_server_id, c.super_game_id
                	) g on a.source_system = g.g_source_system AND a.super_game_id = g.g_super_game_id AND a.boss_server_id = g.g_boss_server_id
                	LEFT JOIN (
                		SELECT
                			source_system as h_source_system,
                			game_id,
                			server_id,
                			server_name,
                			is_merge -- 是否参与过合服
                		FROM dm_game_order.t_game_server_merge
                		WHERE is_delete = 0
                	) h on a.source_system = h.h_source_system AND a.boss_server_id = h.server_id AND a.super_game_id = h.game_id
                	GROUP BY source_system,boss_server_id,super_game_id
                ) a
                """ + cri;
    }

    /**
     * 原始区服-父游戏维度（角色排行相关信息）
     * @return String
     */
    private String getRoleCombatRankRoleDataSqlA() {
        return """
                SELECT
                	*
                FROM (
                	SELECT
                		source_system ,
                		server_id ,
                		boss_server_id,
                		parent_game_id ,
                		super_game_id ,
                		dt,
                		merge_time,
                		role_id ,
                		role_name,
                		role_level ,
                		role_vip ,
                		combat_num ,
                		ROW_NUMBER()over(partition by source_system ,server_id ,boss_server_id, parent_game_id, super_game_id order by combat_num desc) as rank_num
                	FROM (
                		SELECT
                			source_system ,
                			server_id ,
                			boss_server_id,
                			parent_game_id ,
                			super_game_id ,
                			MAX(`date`) as dt,
                			MAX(merge_time) as merge_time ,
                			role_id ,
                			MAX(role_name) as role_name,
                			MAX(role_level) as role_level ,
                			MAX(role_vip) as role_vip ,
                			MAX(combat_num) as combat_num
                		FROM game_ads.ads_role_combat_num_ranking
                		GROUP BY role_id , source_system ,parent_game_id ,super_game_id, server_id, boss_server_id
                	) a
                ) a
                """;
    }

    /**
     * 合服-父游戏维度（角色排行相关信息）
     * @return String
     */
    private String getRoleCombatRankRoleDataSqlB() {
        return """
                SELECT
                	*
                FROM (
                	SELECT
                		source_system ,
                		boss_server_id ,
                		parent_game_id ,
                		super_game_id ,
                		merge_time,
                		role_id ,
                		role_name,
                		role_level ,
                		role_vip ,
                		combat_num ,
                		ROW_NUMBER()over(partition by source_system ,boss_server_id ,parent_game_id, super_game_id order by combat_num desc) as rank_num
                	FROM (
                		SELECT
                			source_system ,
                			boss_server_id ,
                			parent_game_id ,
                			super_game_id ,
                			MAX(merge_time) as merge_time ,
                			role_id ,
                			MAX(role_name) as role_name,
                			MAX(role_level) as role_level ,
                			MAX(role_vip) as role_vip ,
                			MAX(combat_num) as combat_num
                		FROM game_ads.ads_role_combat_num_ranking
                		GROUP BY role_id , source_system ,parent_game_id ,super_game_id, boss_server_id
                	) a
                ) a
                """;
    }

    /**
     * 原始区服-超父游戏维度（角色排行相关信息）
     * @return String
     */
    private String getRoleCombatRankRoleDataSqlC() {
        return """
                SELECT
                	*
                FROM (
                	SELECT
                		a.source_system ,
                		a.server_id ,
                		a.super_game_id ,
                		a.dt,
                		a.merge_time,
                		a.role_id ,
                		b.role_name,
                		a.role_level ,
                		IF(b.country = 'null',NULL,b.country) as country,
                		a.role_vip ,
                		a.total_amount ,
                		a.combat_num ,
                		ROW_NUMBER()over(partition by a.source_system ,a.server_id ,a.super_game_id order by a.combat_num desc) as rank_num
                	FROM (
                		SELECT
                			source_system ,
                			server_id ,
                			super_game_id ,
                			MIN(`date`) as dt,
                			MAX(merge_time) as merge_time ,
                			role_id ,
                			SUM(role_total_amount) as total_amount ,
                			MAX(role_name) as role_name,
                			MAX(role_level) as role_level ,
                			MAX(role_vip) as role_vip ,
                			MAX(combat_num) as combat_num
                		FROM game_ads.ads_role_combat_num_ranking a
                		LEFT JOIN (
                			SELECT
                				source_system as b_source_system,
                				server_id as b_server_id,
                				game_id as b_game_id,
                				MAX(is_merge) as b_is_merge
                			FROM dm_game_order.t_game_server_merge
                			GROUP BY source_system , server_id , game_id
                		) b on a.source_system = b.b_source_system AND a.boss_server_id = b.b_server_id AND a.super_game_id = b.b_game_id
                		WHERE b_is_merge = 0
                		GROUP BY role_id , source_system ,super_game_id, server_id , boss_server_id
                	) a LEFT JOIN(
                		SELECT
                			a.source_system,
                			a.role_id,
                			a.role_name,
                			a.role_level,
                			a.country,
                			a.combat_num ,
                			a.game_id ,
                			b.parent_game_id,
                			b.super_game_id ,
                			ROW_NUMBER()over(partition by a.source_system , a.role_id, b.parent_game_id order by a.role_level desc,a.combat_num desc) as num
                		FROM dm_game_order.t_game_user_role a
                		LEFT JOIN (
                			SELECT
                				source_system,
                				id,
                				IFNULL(parent_id, id) as parent_game_id,
                				IFNULL(super_game_id, id) as super_game_id
                			FROM dm_game_order.t_game
                		) b on a.source_system = b.source_system AND a.game_id = b.id
                	) b on a.source_system = b.source_system AND a.role_id = b.role_id AND a.super_game_id = b.super_game_id AND b.num =1
                ) a
                """;
    }

    /**
     * 合服-超父游戏维度（角色排行相关信息）
     * @return String
     */
    private String getRoleCombatRankRoleDataSqlD() {
        return """
                SELECT
                	*
                FROM (
                	SELECT
                		a.source_system ,
                		a.boss_server_id ,
                		a.super_game_id ,
                		a.merge_time,
                		a.role_id ,
                		b.role_name,
                		a.role_level ,
                		a.role_vip ,
                		a.total_amount ,
                		a.combat_num ,
                		IF(b.country = 'null',NULL,b.country) as country,
                		ROW_NUMBER()over(partition by a.source_system ,a.boss_server_id ,a.super_game_id order by a.combat_num desc) as rank_num
                	FROM (
                		SELECT
                			source_system ,
                			boss_server_id ,
                			super_game_id ,
                			MIN(merge_time) as merge_time ,
                			role_id ,
                			SUM(role_total_amount) as total_amount ,
                			MAX(role_name) as role_name,
                			MAX(role_level) as role_level ,
                			MAX(role_vip) as role_vip ,
                			MAX(combat_num) as combat_num
                		FROM game_ads.ads_role_combat_num_ranking
                		GROUP BY role_id , source_system ,super_game_id, boss_server_id
                	) a LEFT JOIN(
                		SELECT
                			a.source_system,
                			a.role_id,
                			a.role_name,
                			a.role_level,
                			a.combat_num ,
                			a.country,
                			a.game_id ,
                			b.parent_game_id,
                			b.super_game_id ,
                			ROW_NUMBER()over(partition by a.source_system , a.role_id, b.parent_game_id order by a.role_level desc,a.combat_num desc) as num
                		FROM dm_game_order.t_game_user_role a
                		LEFT JOIN (
                			SELECT
                				source_system,
                				id,
                				IFNULL(parent_id, id) as parent_game_id,
                				IFNULL(super_game_id, id) as super_game_id
                			FROM dm_game_order.t_game
                		) b on a.source_system = b.source_system AND a.game_id = b.id
                	) b on a.source_system = b.source_system AND a.role_id = b.role_id AND a.super_game_id = b.super_game_id AND b.num =1
                ) a
                """;
    }

}
