Java服务_ck服务弹性接口service层和dao层设计实战2

Java服务_ck服务弹性接口service层和dao层设计实战2

1.背景

在ck等olap数据服务层，为了抽象化我们创建的接口，扩展接口的可服用性，我们一般会把接口设计成弹性接口。弹性接口的意思就是，传入的维度参数列表是可选的、可变的，传出的指标数据列表也是可选的、可变的。

如下表所描述的一个接口为例来对弹性接口进行理解：

serviceName	getBrandIndBrandRank
demensions	STime_ETime_SecondIndId_[ThirdIndId]_[ShopType]_[TerminalId]_[BrandId]_[PriceRangeId]
indicators	BrandId_BrandName_RankRound_DealAmt_DealProNum_PV_UV_DealCustPriceAvg_DealRate_CartUser
options	PageNum_RowNum_OrderBy
attributes

首先，维度组合是该接口的入参列表，按照属性我们一般将维度划分为demensions、options。对于弹性接口，就设置了一些可传可不穿的参数，上表中中括号中的demensions维度即为弹性维度，全部options都是弹性维度。通过控制传入的维度参数，就可以通过一个接口实现多维度数据的查询。

指标组合就是该接口返回的出参列表，按照属性我们一般将指标划分为indicators、attributes。弹性接口的指标就全部都是可选的，只需要在入参中执行本次请求需要哪些指标即可。

排序字段和分页字段就是比较典型的options维度。

2.接口文档设计

3.创建dao层param类

dao层的param类实际上就是承接接口入参列表的bean类，一般直接借助fastjson组件将jsonObject对象转化为param类对象。

一个需求的所有弹性接口共用一个param类，那么需要将这些接口所涉及到的维度组合全部放进来即可。

创建param类如下：

public class IndustryRankParam {

    private static final long serialVersionUID = 1L;

    // 请求唯一标识
    private String uuid;
    // 开始时间
    @JSONField(name = "STime")
    private String sTime;
    // 结束时间
    @JSONField(name = "ETime")
    private String eTime;
    // 二级类目
    @JSONField(name = "SecondIndId")
    private Set<String> secondIndId;
    // 三级类目
    @JSONField(name = "ThirdIndId")
    private Set<String> thirdIndId;
    // 店铺类型
    @JSONField(name = "ShopType")
    private Set<String> shopType;
    // 终端
    @JSONField(name = "TerminalId")
    private Set<String> terminalId;
    // 主品牌
    @JSONField(name = "BrandId")
    private Set<String> brandId;
    // 主品牌名称
    @JSONField(name = "BrandName")
    private String brandName;
    // 子品牌
    @JSONField(name = "SonBrandId")
    private String sonBrandId;
    // 子品牌名称
    @JSONField(name = "SonBrandName")
    private String sonBrandName;
    // 店铺
    @JSONField(name = "ShopId")
    private Set<String> shopId;
    // 店铺名称
    @JSONField(name = "ShopName")
    private String shopName;
    // 环比开始时间
    @JSONField(name = "CRSTime")
    private String cRSTime;
    // 环比结束时间
    @JSONField(name = "CRETime")
    private String cRETime;
    // sku
    @JSONField(name = "SkuId")
    private Set<String> skuId;
    // 排序
    @JSONField(name = "OrderBy")
    private String orderBy;
    // 页数
    @JSONField(name = "PageNum")
    private int pageNum = 1;
    // 显示条数
    @JSONField(name = "RowNum")
    private int rowNum = 0;
    // 价格段ID
    @JSONField(name = "PriceRangeId")
    private String priceRangeId;
    // 二级类目价格段id  (暂未启用，预留)
    @JSONField(name="SecondPriceRangeId")
    private String secondPriceRangeId;

    private Set<String> detQueryCols;
    private Set<String> cRQueryCols;
    private Set<String> sonDetQueryCols;
    private Set<String> sonCRDetQueryCols;
    private Set<String> shopQueryCols;
    private Set<String> priceMoleculeCols;
    private Set<String> priceDenominatorCols;
    private String queryAllCols;
//    已经进行排序之后，拼接一个查询所有列的参数
    private String queryAllColsAndRanked;
    private String queryAllColsWithoutDt;

    private Set<String> aggQueryCols;
    private Set<String> catgSearchQueryCols;
    private Set<String> sumQueryCols;
    private Set<String> dealDetailCols;

    public String getAggQueryCols() {
        return getStrBySet(aggQueryCols);
    }

    public void setAggQueryCols(Set<String> aggQueryCols) {
        this.aggQueryCols = aggQueryCols;
    }

    public String getCatgSearchQueryCols() {
        return getStrBySet(catgSearchQueryCols);
    }

    public void setCatgSearchQueryCols(Set<String> catgSearchQueryCols) {
        this.catgSearchQueryCols = catgSearchQueryCols;
    }

    public String getSumQueryCols() {
        return getStrBySet(sumQueryCols);
    }

    public void setSumQueryCols(Set<String> sumQueryCols) {
        this.sumQueryCols = sumQueryCols;
    }

    public String getDealDetailCols() {
        return getStrBySet(dealDetailCols);
    }

    public void setDealDetailCols(Set<String> dealDetailCols) {
        this.dealDetailCols = dealDetailCols;
    }

    public Set<String> getShopTypeSet() {
        return shopType;
    }

    public String getUuid() {
        return uuid;
    }

    public void setUuid(String uuid) {
        this.uuid = uuid;
    }

    public String getsTime() {
        return sTime;
    }

    public void setsTime(String sTime) {
        this.sTime = sTime;
    }

    public String geteTime() {
        return eTime;
    }

    public void seteTime(String eTime) {
        this.eTime = eTime;
    }

    public String getThirdIndId() {
        return getQuotationStrBySet(thirdIndId);
    }

    public void setThirdIndId(Set<String> thirdIndId) {
        this.thirdIndId = thirdIndId;
    }

    public String getSecondIndId() {
        return getQuotationStrBySet(secondIndId);
    }

    public void setSecondIndId(Set<String> secondIndId) {
        this.secondIndId = secondIndId;
    }

    public String getShopType() {
        return getQuotationStrBySet(shopType);
    }

    public void setShopType(Set<String> shopType) {
        this.shopType = shopType;
    }

    public String getTerminalId() {
        //6代表无线整体，初始化所以无线渠道
        if (terminalId != null && terminalId.size() > 0 && terminalId.contains("6")){
            terminalId.remove("6");
            terminalId.add("1"); //App
            terminalId.add("3"); //WX
            terminalId.add("4"); //QQ
            terminalId.add("5"); //M
        }
        return getQuotationStrBySet(terminalId);
    }

    public void setTerminalId(Set<String> terminalId) {
        this.terminalId = terminalId;
    }

    public String getBrandId() {
        return getQuotationStrBySet(brandId);
    }

    public void setBrandId(Set<String> brandId) {
        this.brandId = brandId;
    }

    public String getBrandName() {
        return brandName;
    }

    public void setBrandName(String brandName) {
        this.brandName = brandName;
    }

    public String getSonBrandId() {
        return sonBrandId;
    }

    public void setSonBrandId(String sonBrandId) {
        this.sonBrandId = sonBrandId;
    }

    public String getSonBrandName() {
        return sonBrandName;
    }

    public void setSonBrandName(String sonBrandName) {
        this.sonBrandName = sonBrandName;
    }

    public String getShopId() {
        return getQuotationStrBySet(shopId);
    }

    public void setShopId(Set<String> shopId) {
        this.shopId = shopId;
    }

    public String getShopName() {
        return shopName;
    }

    public void setShopName(String shopName) {
        this.shopName = shopName;
    }

    public String getcRSTime() {
        return cRSTime;
    }

    public void setcRSTime(String cRSTime) {
        this.cRSTime = cRSTime;
    }

    public String getcRETime() {
        return cRETime;
    }

    public void setcRETime(String cRETime) {
        this.cRETime = cRETime;
    }

    public String getDetQueryCols() {
        return getStrBySet(detQueryCols);
    }

    public void setDetQueryCols(Set<String> detQueryCols) {
        this.detQueryCols = detQueryCols;
    }

    public String getQueryAllCols() {
        return queryAllCols;
    }

    public void setQueryAllCols(String queryAllCols) {
        this.queryAllCols = queryAllCols;
    }

    public String getcRQueryCols() {
        return getStrBySet(cRQueryCols);
    }

    public void setcRQueryCols(Set<String> cRQueryCols) {
        this.cRQueryCols = cRQueryCols;
    }

    public String getSonDetQueryCols() {
        return getStrBySet(sonDetQueryCols);
    }

    public void setSonDetQueryCols(Set<String> sonDetQueryCols) {
        this.sonDetQueryCols = sonDetQueryCols;
    }

    public String getSonCRDetQueryCols() {
        return getStrBySet(sonCRDetQueryCols);
    }

    public void setSonCRDetQueryCols(Set<String> sonCRDetQueryCols) {
        this.sonCRDetQueryCols = sonCRDetQueryCols;
    }

    public String getShopQueryCols() {
        return getStrBySet(shopQueryCols);
    }

    public void setShopQueryCols(Set<String> shopQueryCols) {
        this.shopQueryCols = shopQueryCols;
    }

    public String getPriceMoleculeCols() {
        return getStrBySet(priceMoleculeCols);
    }

    public void setPriceMoleculeCols(Set<String> priceMoleculeCols) {
        this.priceMoleculeCols = priceMoleculeCols;
    }

    public String getPriceDenominatorCols() {
        return getStrBySet(priceDenominatorCols);
    }

    public void setPriceDenominatorCols(Set<String> priceDenominatorCols) {
        this.priceDenominatorCols = priceDenominatorCols;
    }

    public String getSkuId() {
        return getQuotationStrBySet(skuId);
    }

    public void setSkuId(Set<String> skuId) {
        this.skuId = skuId;
    }

    public String getOrderBy() {
        return orderBy;
    }

    public void setOrderBy(String orderBy) {
        this.orderBy = orderBy;
    }

    public int getPageNum() {
        return pageNum;
    }

    public void setPageNum(int pageNum) {
        this.pageNum = pageNum;
    }

    public int getRowNum() {
        return rowNum;
    }

    public void setRowNum(int rowNum) {
        this.rowNum = rowNum;
    }

    public String getPriceRangeId() {
        return priceRangeId;
    }

    public void setPriceRangeId(String priceRangeId) {
        this.priceRangeId = priceRangeId;
    }

    public String getSecondPriceRangeId() { return secondPriceRangeId; }

    public void setSecondPriceRangeId(String secondPriceRangeId) { this.secondPriceRangeId = secondPriceRangeId ; }

//    已经进行排序之后，拼接一个查询所有列的参数
    public String getQueryAllColsAndRanked() {
        return queryAllColsAndRanked;
    }

    public void setQueryAllColsAndRanked(String queryAllColsAndRanked) {
        this.queryAllColsAndRanked = queryAllColsAndRanked;
    }

    public String getQueryAllColsWithoutDt() {
        return queryAllColsWithoutDt;
    }

    public void setQueryAllColsWithoutDt(String queryAllColsWithoutDt) {
        this.queryAllColsWithoutDt = queryAllColsWithoutDt;
    }
}

注意添加@JSONField注解设置json字段与param属性的对应关系。

4.创建dao层的mapper接口类

dao层的mapper接口类是mybatis基于spring框架生成dao方法实例的基础，供service层依赖引用。

package com.jd.ad.dao.mapper.sztrade;

import com.jd.ad.dao.bean.sztrade.SZTradeFeatureParam;
import org.springframework.stereotype.Repository;

import java.util.LinkedHashMap;
import java.util.List;

@Repository
public interface SZTradeFeatureMapper {
    //交易-交易特征-渠道特征
    List<LinkedHashMap<String, Object>> getTradeChannelFeature(SZTradeFeatureParam param);

    //交易-交易特征-类目特征
    List<LinkedHashMap<String, Object>> getTradeCategoryFeature(SZTradeFeatureParam param);

    //交易-交易特征-品牌特征
    List<LinkedHashMap<String, Object>> getTradeBrandFeature(SZTradeFeatureParam param);

    //交易-交易特征-商品价格带特征
    List<LinkedHashMap<String, Object>> getTradePriceDistrictFeature(SZTradeFeatureParam param);

    //交易-交易特征-新老客户特征
    List<LinkedHashMap<String, Object>> getTradeOldNewCustomerFeature(SZTradeFeatureParam param);

    //交易-交易特征-支付方式特征
    List<LinkedHashMap<String, Object>> getTradePayTypeFeature(SZTradeFeatureParam param);

    //交易-交易特征-客单件数特征
    List<LinkedHashMap<String, Object>> getTradeSaleQuantityFeature(SZTradeFeatureParam param);
}

5.创建继承整个微服务抽象类的service方法

我们在service类中，为了优雅和美观，将所有接口抽象到同一个query方法中：

@Service
public class IndustryRankService extends BrandService {

    @Resource
    IndustryRankMapper industryRankMapper;

    private JSONObject query(JSONObject jsonObject, String methodName) {
        List<LinkedHashMap<String, Object>> result = new ArrayList<>();
        JSONObject obj;
        try {
            // 校验时间参数，已修改到serviceList.ini中校验
            // CheckUtils.checkDimensionsTime(jsonObject);
            IndustryRankParam param = (IndustryRankParam) ReqUtils.generateParam(jsonObject, IndustryRankParam.class);

            Set<String> cols = ReqUtils.generateCols(jsonObject);
            if (cols == null || cols.size() == 0) {
                BrandLog.logError("no query any message:" + jsonObject.toJSONString());
                throw new ValidateException(ErrorEnum.DATA_QUERY_ERROR.code, "no query any message");
            }
            // 设置查询列信息
            IndustryRankUtil.setQueryCols(cols, param);
            Method m = industryRankMapper.getClass().getMethod(methodName, IndustryRankParam.class);
            List<LinkedHashMap<String, Object>> ckData = (List<LinkedHashMap<String, Object>>) m.invoke(industryRankMapper, param);

            if (ckData != null && ckData.size() > 0) {
               result.addAll(ckData);
            }
            
            obj = new TransformForClickHouse().transform(result);

        } catch (Exception e) {
            BrandLog.logError(e.getMessage(), e);
            throw new ValidateException(ErrorEnum.DATA_QUERY_ERROR.code, e.getMessage());
        }
        return obj;
    }

    //行业-品牌榜单
    public JSONObject getBrandIndBrandRank(JSONObject jsonObject) {
        return query(jsonObject, "getBrandIndBrandRank");
    }

    //行业-品牌榜单-汇总
    public JSONObject getBrandIndBrandSum(JSONObject jsonObject) {
        return query(jsonObject, "getBrandIndBrandSum");
    }

    //行业-品牌榜单-下载
    public JSONObject getBrandIndBrandRankDown(JSONObject jsonObject) {
        return query(jsonObject, "getBrandIndBrandRankDown");
    }

    //行业-品牌榜单-汇总下载
    public JSONObject getBrandIndBrandSumDown(JSONObject jsonObject) {
        return query(jsonObject, "getBrandIndBrandSumDown");
    }

    //行业-品牌榜单-价格区间
    public JSONObject getBrandIndBrandPrice(JSONObject jsonObject) {
        return query(jsonObject, "getBrandIndBrandPrice");
    }

    //行业-品牌榜单-价格区间（全部品牌）
    public JSONObject getBrandIndBrandPriceSum(JSONObject jsonObject) {
        return query(jsonObject, "getBrandIndBrandPriceSum");
    }

    //行业-店铺榜单
    public JSONObject getBrandIndShopRank(JSONObject jsonObject) {
        return query(jsonObject, "getBrandIndShopRank");
    }

//    行业-店铺榜单-详情
    public JSONObject getBrandIndShopRankDet(JSONObject jsonObject) {
        return query(jsonObject, "getBrandIndShopRankDet");
    }

    //行业-店铺榜单-下载
    public JSONObject getBrandIndShopRankDown(JSONObject jsonObject) {
        return query(jsonObject, "getBrandIndShopRankDown");
    }

    //行业-商品榜单
    public JSONObject getBrandIndProRank(JSONObject jsonObject) {
        return query(jsonObject, "getBrandIndProRank");
    }

    //行业-商品榜单-下载
    public JSONObject getBrandIndProRankDown(JSONObject jsonObject) {
        return query(jsonObject, "getBrandIndProRankDown");
    }

    //行业-店铺榜单-二级类目
    public JSONObject getBrandScndIndShopRank(JSONObject jsonObject) {
        return query(jsonObject, "getBrandScndIndShopRank");
    }

//    行业-店铺榜单-详情-二级类目
    public JSONObject getBrandScndIndShopRankDet(JSONObject jsonObject) {
        return query(jsonObject, "getBrandScndIndShopRankDet");
    }

    //行业-店铺榜单-二级类目-下载
    public JSONObject getBrandScndIndShopRankDown(JSONObject jsonObject) {
        return query(jsonObject, "getBrandScndIndShopRankDown");
    }

    //行业-商品榜单-二级类目
    public JSONObject getBrandScndIndProRank(JSONObject jsonObject) {
        return query(jsonObject, "getBrandScndIndProRank");
    }

    //行业-商品榜单-二级类目-下载
    public JSONObject getBrandScndIndProRankDown(JSONObject jsonObject) {
        return query(jsonObject, "getBrandScndIndProRankDown");
    }

    //行业-商品榜单-二级类目-下载
    public JSONObject getBrandIndShopRankTrend(JSONObject jsonObject) {
        return query(jsonObject, "getBrandIndShopRankTrend");
    }

    //行业-商品榜单-二级类目-下载
    public JSONObject getBrandScndIndShopRankTrend(JSONObject jsonObject) {
        return query(jsonObject, "getBrandScndIndShopRankTrend");
    }

    //行业-商品榜单-二级类目-下载
    public JSONObject getBrandIndProRankTrend(JSONObject jsonObject) {
        return query(jsonObject, "getBrandIndProRankTrend");
    }

    //行业-品牌榜单-品牌详情-趋势图
    public JSONObject getBrandIndBrandRankTrend(JSONObject jsonObject) {
        return query(jsonObject, "getBrandIndBrandRankTrend");
    }

    //行业-品牌榜单-品牌详情-商品榜单
    public JSONObject getBrandIndBrandRankProRank(JSONObject jsonObject) {
        return query(jsonObject, "getBrandIndBrandRankProRank");
    }

    //行业-品牌榜单-品牌详情-趋势图-汇总
    public JSONObject getBrandIndBrandSumTrend(JSONObject jsonObject) {
        return query(jsonObject, "getBrandIndBrandSumTrend");
    }

    //行业分析-行业概况-大盘概况
    public JSONObject getBrandIndCateSummary(JSONObject jsonObject) {
        return query(jsonObject, "getBrandIndCateSummary");
    }

    //行业分析-行业概况-大盘走势-趋势图
    public JSONObject getBrandIndCateTrend(JSONObject jsonObject) {
        return query(jsonObject, "getBrandIndCateTrend");
    }
}

6.创建ReqUtils类与工具方法

1.generateParam()方法将jsonObject转化为parambean类对象：

public static Object generateParam(JSONObject jsonObject, Class<?> clazz) {

    JSONObject bodyMsg = jsonObject.getJSONObject("body");
    JSONObject dimsMsg = bodyMsg.getJSONObject("dimensions");
    JSONObject optionsMsg = bodyMsg.getJSONObject("options");
    String uuid = jsonObject.getJSONObject("header").getString("uuid");
    String pin = jsonObject.getJSONObject("header").getString("BRAND_PIN");
    JSONObject uuidMsg = new JSONObject();
    uuidMsg.put("uuid", uuid);

    JSONObject conditionMsg = new JSONObject();
    conditionMsg.putAll(dimsMsg);
    if (optionsMsg != null) {
        conditionMsg.putAll(optionsMsg);
    }
    conditionMsg.putAll(uuidMsg);
    conditionMsg.put("BRAND_PIN", pin);

    return JSONObject.toJavaObject(conditionMsg, clazz);
}

很明显，该方法中最主要的步骤，就是把传入的维度组合dimensions、options放入param类对象中。

2.generateCols()生成查询信息：

public static Set generateCols(JSONObject jsonObject) {

    JSONObject bodyMsg = jsonObject.getJSONObject("body");
    JSONArray indicatorsMsg = bodyMsg.getJSONArray("indicators");
    JSONArray attributesMsg = bodyMsg.getJSONArray("attributes");

    JSONArray conditionMsg = new JSONArray();
    conditionMsg.addAll(indicatorsMsg);
    if (null != attributesMsg)
        conditionMsg.addAll(attributesMsg);

    Object[] colsArray = conditionMsg.toArray();
    Set<String> cols = new HashSet<>();
    for (Object col : colsArray) {
        cols.add(String.valueOf(col));
    }
    return cols;
}

很明显，该方法最主要的步骤，就是从传入的参数中获取本次请求所需要的指标组合indicators、attributes。

7.写出一个接口查询全量指标的sql

做这一步的目的是为了观察sql，sql中抽象出共性较大的弹性查询字段：

getBrandIndCateSummary

select
	UV,
	VisitedShopNum,
	CateClickNum,
	DealSkuNum,
	PV,
	DealUser / UV as DealRate,
	BrandNum,
	CateSearchNum,
	VisitedSkuNum,
	CartUser,
	DealAmt,
	DealProNum,
	DealShopNum,
	DealBrandNum,
	VisitedBrandNum,
	CateClickNum / CateSearchNum as SearchClickRate,
	ShopNum,
	ProFollowUser,
	DealAmt / DealUser as DealCustPriceAvg
from
	(
	select
		1 as joinKey ,
		sumMerge(pv) as PV,
		uniqMerge(dealSku) as DealSkuNum,
		uniqMerge(followAcct) as ProFollowUser,
		uniqMerge(dealAcct) as DealUser,
		sumMerge(dealProNum) as DealProNum,
		sumMerge(searchClickNum) as CateClickNum,
		uniqMerge(browseAcct) as UV,
		sumMerge(dealAmt) as DealAmt,
		uniqMerge(browseSku) as VisitedSkuNum,
		uniqMerge(cartAcct) as CartUser
	from
		bc_online. ck_zh_industry_product_union_all_agg_d
	where
		dateTime >= '2022-11-15'
		and dateTime <= '2022-11-15'
		and thirdIndId in ('1602')
		and secondIndId in ('1585')
		and shopType in ('POP', 'B2C') ) a
left join (
	select
		1 as joinKey,
		sum(catgSearchNum) as CateSearchNum
	from
		bc_online. ck_industry_catg_search_click_num_d
	where
		dateTime >= '2022-11-15'
		and dateTime <= '2022-11-15'
		and thirdIndId in ('1602')
			and secondIndId in ('1585') ) d on
	a.joinKey = d.joinKey
left join (
	select
    	1 as joinKey,
    	uniq(sonBrandId) as BrandNum,
    	uniq(shopId) as ShopNum
    from
    	bc_online.ck_industry_catg_brand_shop_d
    where
    	dateTime >= '2022-11-15'
			and dateTime <= '2022-11-15'
			and thirdIndId in ('1602')
			and secondIndId in ('1585')
			and shopType in ('POP', 'B2C')) e on
	a.joinKey = e.joinKey
	left join (
	select
    	1 as joinKey,
    	uniq(isDealShop) as DealShopNum
    from
    	bc_online.ck_industry_catg_brand_shop_d
    where
    	dateTime >= '2022-11-15'
			and dateTime <= '2022-11-15'
			and thirdIndId in ('1602')
			and secondIndId in ('1585')
			and shopType in ('POP', 'B2C')
  		--and dealShopTerminal in ('1')
  ) f on
	a.joinKey = f.joinKey
	left join (
	select
    	1 as joinKey,
    	uniq(isDealBrand) as DealBrandNum
    from
    	bc_online.ck_industry_catg_brand_shop_d
    where
    	dateTime >= '2022-11-15'
			and dateTime <= '2022-11-15'
			and thirdIndId in ('1602')
			and secondIndId in ('1585')
			and shopType in ('POP', 'B2C')
    	--and dealBrandTerminal in ('1')
  ) g on
	a.joinKey = g.joinKey
	left join (
	select
    	1 as joinKey,
    	uniq(isVisitedShop) as VisitedShopNum
    from
    	bc_online.ck_industry_catg_brand_shop_d
    where
    	dateTime >= '2022-11-15'
			and dateTime <= '2022-11-15'
			and thirdIndId in ('1602')
			and secondIndId in ('1585')
			and shopType in ('POP', 'B2C')
    	--and visitedShopTerminal in ('1')
  ) h on
	a.joinKey = h.joinKey
	left join (
	select
    	1 as joinKey,
    	uniq(isVisitedBrand) as VisitedBrandNum
    from
    	bc_online.ck_industry_catg_brand_shop_d
    where
    	dateTime >= '2022-11-15'
			and dateTime <= '2022-11-15'
			and thirdIndId in ('1602')
			and secondIndId in ('1585')
			and shopType in ('POP', 'B2C')
  		--and visitedBrandTerminal in ('1')
  ) i on
	a.joinKey = i.joinKey;

getBrandIndCateTrend

select
	UV,
	VisitedShopNum,
	CateClickNum,
	DealSkuNum,
	PV,
	DealUser / UV as DealRate,
	BrandNum,
	CateSearchNum,
	DealNum,
	VisitedSkuNum,
	DealUser,
	CartUser,
	DealAmt,
	DealProNum,
	DealShopNum,
	DealBrandNum,
	VisitedBrandNum,
	CateClickNum / CateSearchNum as SearchClickRate,
	ShopNum,
	ProFollowUser,
	DealAmt / DealUser as DealCustPriceAvg,
	a.DateTime as DateTime
from
	(
	select
		sumMerge(pv) as PV,
		uniqMerge(dealSku) as DealSkuNum,
		uniqMerge(followAcct) as ProFollowUser,
		uniqMerge(dealAcct) as DealUser,
		sumMerge(dealProNum) as DealProNum,
		sumMerge(searchClickNum) as CateClickNum,
		uniqMerge(browseAcct) as UV,
		dateTime as DateTime,
		sumMerge(dealAmt) as DealAmt,
		uniqMerge(browseSku) as VisitedSkuNum,
		uniqMerge(cartAcct) as CartUser
	from
		bc_online. ck_zh_industry_product_union_all_agg_d
	where
		dateTime >= '2022-11-09'
		and dateTime <= '2022-11-15'
		and thirdIndId in ('1602')
		and secondIndId in ('1585')
		and shopType in ('POP', 'B2C')
	group by
		dateTime ) a
left join (
	select
		sum(catgSearchNum) as CateSearchNum,
		dateTime as DateTime1
	from
		bc_online. ck_industry_catg_search_click_num_d
	where
		dateTime >= '2022-11-09'
		and dateTime <= '2022-11-15'
		and thirdIndId in ('1602')
			and secondIndId in ('1585')
		group by
			dateTime ) d on
	a.DateTime = d.DateTime1
left join (
	select
		uniq(shopId) as ShopNum,
		dateTime as DateTime1,
		uniq(sonBrandId) as BrandNum
	from
		bc_online. ck_industry_catg_brand_shop_d
	where
		dateTime >= '2022-11-09'
		and dateTime <= '2022-11-15'
		and thirdIndId in ('1602')
			and secondIndId in ('1585')
				and shopType in ('POP', 'B2C')
			group by
				dateTime ) e on
	a.DateTime = e.DateTime1
left join (
	select
		uniq(saleOrdId) as DealNum,
		dateTime as DateTime1
	from
		bc_online. ck_product_deal_detail_d
	where
		dateTime >= '2022-11-09'
		and dateTime <= '2022-11-15'
		and thirdIndId in ('1602')
			and secondIndId in ('1585')
				and shopType in ('POP', 'B2C')
			group by
				dateTime ) f on
	a.DateTime = f.DateTime1
	left join (
	select
    	dateTime as DateTime1,
    	uniq(isDealShop) as DealShopNum
    from
    	bc_online.ck_industry_catg_brand_shop_d
    where
    	dateTime >= '2022-11-09'
			and dateTime <= '2022-11-15'
			and thirdIndId in ('1602')
			and secondIndId in ('1585')
			and shopType in ('POP', 'B2C')
  		--and dealShopTerminal in ('1')
    group by dateTime
  ) g on
	a.DateTime = g.DateTime1
	left join (
	select
    	dateTime as DateTime1,
    	uniq(isDealBrand) as DealBrandNum
    from
    	bc_online.ck_industry_catg_brand_shop_d
    where
    	dateTime >= '2022-11-09'
			and dateTime <= '2022-11-15'
			and thirdIndId in ('1602')
			and secondIndId in ('1585')
			and shopType in ('POP', 'B2C')
    	--and dealBrandTerminal in ('1')
    group by dateTime
  ) h on
	a.DateTime = h.DateTime1
	left join (
	select
    	dateTime as DateTime1,
    	uniq(isVisitedShop) as VisitedShopNum
    from
    	bc_online.ck_industry_catg_brand_shop_d
    where
    	dateTime >= '2022-11-09'
			and dateTime <= '2022-11-15'
			and thirdIndId in ('1602')
			and secondIndId in ('1585')
			and shopType in ('POP', 'B2C')
    	--and visitedShopTerminal in ('1')
    group by dateTime
  ) i on
	a.DateTime = i.DateTime1
	left join (
	select
    	dateTime as DateTime1,
    	uniq(isVisitedBrand) as VisitedBrandNum
    from
    	bc_online.ck_industry_catg_brand_shop_d
    where
    	dateTime >= '2022-11-09'
			and dateTime <= '2022-11-15'
			and thirdIndId in ('1602')
			and secondIndId in ('1585')
			and shopType in ('POP', 'B2C')
  		--and visitedBrandTerminal in ('1')
    group by dateTime
  ) j on
	a.DateTime = j.DateTime1;

从所有sql中可以抽象出比较常用的指标字段组合，比如商品宽表聚合表指标、类目搜索数据表指标、品牌店铺数表指标、商品成交明细表指标、所有指标等。

8.创建IndustryRankUtil类与工具方法

在上述总结的可以弹性变化的指标字段组合的基础之上，创建对应的查询指标组合Set<String>字段或String字段，根据入参中传入的indicators和attributes指标组合来构建这四个字段，并放入到param类对象中，方便后续以占位符的形式放入sql中：

public class IndustryRankUtil {

    public static void setQueryCols(Set<String> cols, IndustryRankParam param) {

        //商品宽表指标
        Set<String> detQueryCols = new HashSet<String>();
        //环比指标
        Set<String> cRQueryCols = new HashSet<String>();
        //商品宽表子查询
        Set<String> sonDetQueryCols = new HashSet<String>();
        //商品宽表对比子查询
        Set<String> sonCRDetQueryCols = new HashSet<String>();
        //店铺关注
        Set<String> shopQueryCols = new HashSet<String>();
        //价格段占比分子
        Set<String> priceMoleculeCols = new HashSet<String>();
        //价格段占比分母
        Set<String> priceDenominatorCols = new HashSet<String>();
        String queryAllCols = null;
        String queryAllColsAndRanked = null;
        String queryAllColsWithoutDt = null;

        //商品宽表聚合表指标
        Set<String> aggQueryCols = new HashSet<String>();
        //类目搜索数据表指标
        Set<String> catgSearchQueryCols = new HashSet<String>();
        //品牌店铺数表指标
        Set<String> sumQueryCols = new HashSet<String>();
        //商品成交明细表指标
        Set<String> dealDetailCols = new HashSet<String>();

        //成交金额
        if (cols.contains("DealAmt")) {
            detQueryCols.add("sumMerge(dealAmt) as DealAmt");
            sonDetQueryCols.add("dealAmt");

            aggQueryCols.add("sumMerge(dealAmt) as DealAmt");
        }
        //成交件数
        if (cols.contains("DealProNum")){
            detQueryCols.add("sumMerge(dealProNum) as DealProNum");
            sonDetQueryCols.add("dealProNum");

            aggQueryCols.add("sumMerge(dealProNum) as DealProNum");
        }
        //浏览量
        if (cols.contains("PV")){
            detQueryCols.add("sumMerge(pv) as PV");
            sonDetQueryCols.add("pv");

            aggQueryCols.add("sumMerge(pv) as PV");
        }
        //访客数
        if (cols.contains("UV")){
            detQueryCols.add("uniqMerge(browseAcct) as UV");
            sonDetQueryCols.add("browseAcct");

            aggQueryCols.add("uniqMerge(browseAcct) as UV");
        }
        //加购人数
        if (cols.contains("CartUser")){
            detQueryCols.add("uniqMerge(cartAcct) as CartUser");
            sonDetQueryCols.add("cartAcct");

            aggQueryCols.add("uniqMerge(cartAcct) as CartUser");
        }
        //商品关注人数
        if (cols.contains("ProFollowUser")){
            detQueryCols.add("uniqMerge(followAcct) as ProFollowUser");
            sonDetQueryCols.add("followAcct");

            aggQueryCols.add("uniqMerge(followAcct) as ProFollowUser");
        }
        //搜索点击次数
        if (cols.contains("SearchClickNum")){
            detQueryCols.add("sumMerge(searchClickNum) as SearchClickNum");
            sonDetQueryCols.add("searchClickNum");
        }
        //被访问商品数
        if (cols.contains("VisitedSkuNum")){
            detQueryCols.add("uniqMerge(browseSku) as VisitedSkuNum");
            sonDetQueryCols.add("browseSku");

            aggQueryCols.add("uniqMerge(browseSku) as VisitedSkuNum");
        }
        //动销商品数
        if (cols.contains("DealSkuNum")){
            detQueryCols.add("uniqMerge(dealSku) as DealSkuNum");
            sonDetQueryCols.add("dealSku");

            aggQueryCols.add("uniqMerge(dealSku) as DealSkuNum");
        }
        //成交客单价
        if (cols.contains("DealCustPriceAvg")){
            detQueryCols.add("uniqMerge(dealAcct) as DealUser");
            detQueryCols.add("sumMerge(dealAmt) as DealAmt");
            sonDetQueryCols.add("dealAcct");
            sonDetQueryCols.add("dealAmt");

            aggQueryCols.add("sumMerge(dealAmt) as DealAmt");
            aggQueryCols.add("uniqMerge(dealAcct) as DealUser");
        }
        //成交转化率
        if (cols.contains("DealRate")){
            detQueryCols.add("uniqMerge(dealAcct) as DealUser");
            detQueryCols.add("uniqMerge(browseAcct) as UV");
            sonDetQueryCols.add("dealAcct");
            sonDetQueryCols.add("browseAcct");

            aggQueryCols.add("uniqMerge(dealAcct) as DealUser");
            aggQueryCols.add("uniqMerge(browseAcct) as UV");
        }
        //成交人数
        if (cols.contains("DealUser")){

            aggQueryCols.add("uniqMerge(dealAcct) as DealUser");
        }
        //成交单量
        if (cols.contains("DealNum")){

            dealDetailCols.add("uniq(saleOrdId) as DealNum");
        }
        //成交金额环比
        if (cols.contains("CRDealAmt")){
            detQueryCols.add("sumMerge(dealAmt) as DealAmt");
            cRQueryCols.add("sumMerge(dealAmt) as CDealAmt");
            sonDetQueryCols.add("dealAmt");
            sonCRDetQueryCols.add("dealAmt");
        }
        //成交件数环比
        if (cols.contains("CRDealProNum")){
            detQueryCols.add("sumMerge(dealProNum) as DealProNum");
            cRQueryCols.add("sumMerge(dealProNum) as CDealProNum");
            sonDetQueryCols.add("dealProNum");
            sonCRDetQueryCols.add("dealProNum");
        }
        //成交人数对比
        if (cols.contains("CRDealUser")){
            detQueryCols.add("uniqMerge(dealAcct) as DealUser");
            cRQueryCols.add("uniqMerge(dealAcct) as CDealUser");
            sonDetQueryCols.add("dealAcct");
            sonCRDetQueryCols.add("dealAcct");
        }
        //店铺关注
        if (cols.contains("ShopFollowUser")){
            shopQueryCols.add("uniqMerge(shopFollowAcct) as ShopFollowUser");
        }
        //趋势图dt
        if (cols.contains("DateTime")){
            detQueryCols.add("dateTime as DateTime");
            shopQueryCols.add("dateTime as DateTime");

            aggQueryCols.add("dateTime as DateTime");
            catgSearchQueryCols.add("dateTime as DateTime1");
            sumQueryCols.add("dateTime as DateTime1");
            dealDetailCols.add("dateTime as DateTime1");
        }
        //价格段占比
        if (cols.contains("PriceRangePer")){
            priceMoleculeCols.add("sum(DealAmt) as DealAmt");
            priceDenominatorCols.add("sum(DealAmt) as SumDealAmt");
        }
        //类目搜索次数
        if (cols.contains("CateSearchNum")){
            catgSearchQueryCols.add("sum(catgSearchNum) as CateSearchNum");
        }
        //类目点击次数
        if (cols.contains("CateClickNum")){
            aggQueryCols.add("sumMerge(searchClickNum) as CateClickNum");
        }
        //搜索点击率
        if (cols.contains("SearchClickRate")){
            aggQueryCols.add("sumMerge(searchClickNum) as CateClickNum");
            catgSearchQueryCols.add("sum(catgSearchNum) as CateSearchNum");
        }
        //总品牌数
        if (cols.contains("BrandNum")){
            sumQueryCols.add("uniq(sonBrandId) as BrandNum");
        }
        //总店铺数
        if (cols.contains("ShopNum")){
            sumQueryCols.add("uniq(shopId) as ShopNum");
        }

        StringBuffer queryAllColsSb = new StringBuffer();
        StringBuffer queryAllColsWithoutDtSb = new StringBuffer();
        for (String col : cols) {
            String column = col;
            if ("DealCustPriceAvg".equals(column)) {
                column = "DealAmt/DealUser as DealCustPriceAvg";
            }
            if ("DealRate".equals(column)) {
                column = "DealUser/UV as DealRate";
            }
            if ("CRDealAmt".equals(column)){
                column = "(DealAmt-CDealAmt)/abs(CDealAmt) as CRDealAmt";
            }
            if ("CRDealProNum".equals(column)){
                column = "(DealProNum-CDealProNum)/abs(CDealProNum) as CRDealProNum";
            }
            if ("CRDealUser".equals(column)){
                column = "(DealUser-CDealUser)/abs(CDealUser) as CRDealUser";
            }
            if ("RankRound".equals(column)){
                column = "rowNumberInAllBlocks()+1 AS RankRound";
            }
            if ("PriceRangePer".equals(column)){
                column = "if(DealAmt/SumDealAmt < 0,0.0,DealAmt/SumDealAmt) AS PriceRangePer";
            }
            if ("SearchClickRate".equals(column)){
                column = "CateClickNum/CateSearchNum as SearchClickRate";
            }
            queryAllColsSb.append(column).append(",");
            if ("DateTime".equals(column)) {
                continue;
            }else {
                queryAllColsWithoutDtSb.append(column).append(",");
            }
        }
        queryAllCols = queryAllColsSb.toString();
        if (queryAllCols.length() > 1) {
            queryAllCols = queryAllCols.substring(0, queryAllCols.length() - 1);
        }

        //对于行业趋势接口，DateTime字段直接在xml中添加。
        queryAllColsWithoutDt = queryAllColsWithoutDtSb.toString();
        if (queryAllColsWithoutDt.length() > 1) {
            queryAllColsWithoutDt = queryAllColsWithoutDt.substring(0, queryAllColsWithoutDt.length() - 1);
        }

//        已经进行排序之后，拼接一个查询所有列的参数
        StringBuffer queryAllColsAndRankedSb = new StringBuffer();
        for (String col : cols) {
            String column = col;
            queryAllColsAndRankedSb.append(column).append(",");
        }
        queryAllColsAndRanked = queryAllColsAndRankedSb.toString();
        if(queryAllColsAndRanked.length() > 1){
            queryAllColsAndRanked = queryAllColsAndRanked.substring(0, queryAllColsAndRanked.length() - 1);
        }

        param.setDetQueryCols(detQueryCols);
        param.setcRQueryCols(cRQueryCols);
        param.setSonDetQueryCols(sonDetQueryCols);
        param.setSonCRDetQueryCols(sonCRDetQueryCols);
        param.setShopQueryCols(shopQueryCols);
        param.setPriceMoleculeCols(priceMoleculeCols);
        param.setPriceDenominatorCols(priceDenominatorCols);
        param.setQueryAllCols(queryAllCols);
        param.setQueryAllColsAndRanked(queryAllColsAndRanked);
        param.setQueryAllColsWithoutDt(queryAllColsWithoutDt);

        param.setAggQueryCols(aggQueryCols);
        param.setCatgSearchQueryCols(catgSearchQueryCols);
        param.setSumQueryCols(sumQueryCols);
        param.setDealDetailCols(dealDetailCols);
    }
}

11.创建dao层的mapper.xml配置文件

创建这个文件最重要的就是优雅，实现优雅的根本秘诀就是抽象与复用，大体顺序就是resultMap-select-dbsql-tablesql-wheresql：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.jd.ad.dao.mapper.industry.IndustryRankMapper">

    <resultMap type="java.util.LinkedHashMap" id="returnHashMap">
    </resultMap>

    <!--省略了很多接口select，此处只以大盘概况和大盘走势两个接口为范例--> 
  
    <!--行业分析-行业概况-大盘概况-->
    <select id="getBrandIndCateSummary" resultMap="returnHashMap"
            parameterType="com.jd.ad.dao.bean.industry.IndustryRankParam">
        <if test="queryAllCols!=null and queryAllCols.length()>0">
            select
            ${queryAllCols}
            from
            (
            select
            1 as joinKey<if test="aggQueryCols !=null and aggQueryCols.length()>0">,${aggQueryCols}
            from <include refid="database"/><include refid="detTable"/><include refid="where"/>
            </if>
            ) a
            <if test="catgSearchQueryCols!=null and catgSearchQueryCols.length()>0">
                left join
                (
                select
                1 as joinKey,${catgSearchQueryCols}
                from <include refid="database"/><include refid="searchTable"/><include refid="searchWhere"/>
                ) d on a.joinKey = d.joinKey
            </if>
            <if test="sumQueryCols!=null and sumQueryCols.length()>0">
                left join
                (
                select
                1 as joinKey,${sumQueryCols}
                from <include refid="database"/><include refid="brandShopTable"/><include refid="brandShopWhere"/>
                ) e on a.joinKey = e.joinKey
            </if>
            left join (
            select
            1 as joinKey, uniq(isDealShop) as DealShopNum
            from <include refid="database"/><include refid="brandShopTable"/><include refid="dealVisitedWhere"/>
            <if test="terminalId != null and terminalId.length()>0">
                and dealShopTerminal in (${terminalId})
            </if>
            ) f on a.joinKey = f.joinKey
            left join (
            select
            1 as joinKey, uniq(isDealBrand) as DealBrandNum
            from <include refid="database"/><include refid="brandShopTable"/><include refid="dealVisitedWhere"/>
            <if test="terminalId != null and terminalId.length()>0">
                and dealBrandTerminal in (${terminalId})
            </if>
            ) g on a.joinKey = g.joinKey
            left join (
            select
            1 as joinKey, uniq(isVisitedShop) as VisitedShopNum
            from <include refid="database"/><include refid="brandShopTable"/><include refid="dealVisitedWhere"/>
            <if test="terminalId != null and terminalId.length()>0">
                and visitedShopTerminal in (${terminalId})
            </if>
            ) h on a.joinKey = h.joinKey
            left join (
            select
            1 as joinKey, uniq(isVisitedBrand) as VisitedBrandNum
            from <include refid="database"/><include refid="brandShopTable"/><include refid="dealVisitedWhere"/>
            <if test="terminalId != null and terminalId.length()>0">
                and visitedBrandTerminal in (${terminalId})
            </if>
            ) i on
            a.joinKey = i.joinKey
        </if>
    </select>

    <!--行业分析-行业概况-大盘走势-趋势图-->
    <select id="getBrandIndCateTrend" resultMap="returnHashMap"
            parameterType="com.jd.ad.dao.bean.industry.IndustryRankParam">
        <if test="queryAllColsWithoutDt!=null and queryAllColsWithoutDt.length()>0">
            select
            ${queryAllColsWithoutDt},a.DateTime as DateTime
            from
            (
            select
            <if test="aggQueryCols !=null and aggQueryCols.length()>0">${aggQueryCols}</if>
            from <include refid="database"/><include refid="detTable"/><include refid="where"/>
            group by dateTime
            ) a
            <if test="catgSearchQueryCols!=null and catgSearchQueryCols.length()>0">
                left join
                (
                select
                ${catgSearchQueryCols}
                from <include refid="database"/><include refid="searchTable"/><include refid="searchWhere"/>
                group by dateTime
                ) d
                on a.DateTime = d.DateTime1
            </if>
            <if test="sumQueryCols!=null and sumQueryCols.length()>0">
                left join
                (
                select
                ${sumQueryCols}
                from <include refid="database"/><include refid="brandShopTable"/><include refid="brandShopWhere"/>
                group by dateTime
                ) e
                on a.DateTime = e.DateTime1
            </if>
            <if test="dealDetailCols!=null and dealDetailCols.length()>0">
                left join
                (
                select
                ${dealDetailCols}
                from <include refid="database"/><include refid="dealTable"/><include refid="dealWhere"/>
                group by dateTime
                ) f
                on a.DateTime = f.DateTime1
            </if>
            left join (
            select
            dateTime as DateTime1, uniq(isDealShop) as DealShopNum
            from <include refid="database"/><include refid="brandShopTable"/><include refid="dealVisitedWhere"/>
            <if test="terminalId != null and terminalId.length()>0">
                and dealShopTerminal in (${terminalId})
            </if>
            group by dateTime
            ) g on a.DateTime = g.DateTime1
            left join (
            select
            dateTime as DateTime1, uniq(isDealBrand) as DealBrandNum
            from <include refid="database"/><include refid="brandShopTable"/><include refid="dealVisitedWhere"/>
            <if test="terminalId != null and terminalId.length()>0">
                and dealBrandTerminal in (${terminalId})
            </if>
            group by dateTime
            ) h on a.DateTime = h.DateTime1
            left join (
            select
            dateTime as DateTime1, uniq(isVisitedShop) as VisitedShopNum
            from <include refid="database"/><include refid="brandShopTable"/><include refid="dealVisitedWhere"/>
            <if test="terminalId != null and terminalId.length()>0">
                and visitedShopTerminal in (${terminalId})
            </if>
            group by dateTime
            ) i on a.DateTime = i.DateTime1
            left join (
            select
            dateTime as DateTime1, uniq(isVisitedBrand) as VisitedBrandNum
            from <include refid="database"/><include refid="brandShopTable"/><include refid="dealVisitedWhere"/>
            <if test="terminalId != null and terminalId.length()>0">
                and visitedBrandTerminal in (${terminalId})
            </if>
            group by dateTime
            ) j on a.DateTime = j.DateTime1
        </if>
    </select>

    <!--行业商品宽表条件-->
    <sql id="where">
        where dateTime &gt;= #{sTime} and dateTime &lt;= #{eTime}

        <if test="thirdIndId != null and thirdIndId.length()>0">
            and thirdIndId in (${thirdIndId})
        </if>

        <if test="secondIndId != null and secondIndId.length()>0">
            and secondIndId in (${secondIndId})
        </if>

        <if test="shopId != null and shopId.length()>0">
            and shopId in (${shopId})
        </if>

        <if test="skuId != null and skuId.length()>0">
            and skuId in (${skuId})
        </if>

        <if test="priceRangeId != null and priceRangeId.length()>0">
            and priceRangeId = #{priceRangeId}
        </if>

        <if test="secondPriceRangeId != null and secondPriceRangeId.length()>0">
            and secondPriceRangeId = #{secondPriceRangeId}
        </if>

        <if test="terminalId != null and terminalId.length()>0">
            and terminalId in (${terminalId})
        </if>

        <if test="shopType != null and shopType.length()>0">
            and shopType in (${shopType})
        </if>

    </sql>

    <!--类目搜索次数条件，没有ShopType的限制-->
    <sql id="searchWhere">
        where dateTime &gt;= #{sTime} and dateTime &lt;= #{eTime}

        <if test="thirdIndId != null and thirdIndId.length()>0">
            and thirdIndId in (${thirdIndId})
        </if>

        <if test="secondIndId != null and secondIndId.length()>0">
            and secondIndId in (${secondIndId})
        </if>

        <if test="terminalId != null and terminalId.length()>0">
            and terminalId in (${terminalId})
        </if>
    </sql>

    <!--品牌店铺数条件，没有TerminalId限制时返回数据，有TerminalId限制时返回0-->
    <sql id="brandShopWhere">
        where dateTime &gt;= #{sTime} and dateTime &lt;= #{eTime}

        <if test="thirdIndId != null and thirdIndId.length()>0">
            and thirdIndId in (${thirdIndId})
        </if>

        <if test="secondIndId != null and secondIndId.length()>0">
            and secondIndId in (${secondIndId})
        </if>

        <if test="terminalId != null and terminalId.length()>0">
            and dateTime in ('0000-00-00')
        </if>

        <if test="shopType != null and shopType.length()>0">
            and shopType in (${shopType})
        </if>
    </sql>

    <!--成交单量查询条件，shopType限制要转化为isLogic限制-->
    <sql id="dealWhere">
        where dateTime &gt;= #{sTime} and dateTime &lt;= #{eTime}

        <if test="thirdIndId != null and thirdIndId.length()>0">
            and thirdIndId in (${thirdIndId})
        </if>

        <if test="secondIndId != null and secondIndId.length()>0">
            and secondIndId in (${secondIndId})
        </if>

        <if test="terminalId != null and terminalId.length()>0">
            and terminalId in (${terminalId})
        </if>

        <if test="shopType != null and shopType.length()>0">
            and shopType in (${shopType})
        </if>
    </sql>

    <!--成交访问品牌店铺数查询条件-->
    <sql id="dealVisitedWhere">
        where dateTime &gt;= #{sTime} and dateTime &lt;= #{eTime}

        <if test="thirdIndId != null and thirdIndId.length()>0">
            and thirdIndId in (${thirdIndId})
        </if>

        <if test="secondIndId != null and secondIndId.length()>0">
            and secondIndId in (${secondIndId})
        </if>

        <if test="shopType != null and shopType.length()>0">
            and shopType in (${shopType})
        </if>
    </sql>

    <!--剔除指定店铺-->
    <sql id="dropShop">
        and shopId not in ('10278978','11810231','11624131','11574099','11444736')
    </sql>

    <!--指定品牌-->
    <sql id="spBrand">
        <if test="brandId != null and brandId.length()>0">
            where BrandId in (${brandId})
        </if>
    </sql>

    <!--过滤店铺ID小于等于0的店铺-->
    <sql id="shopFilter">
        and toInt32(shopId) > 0
    </sql>

    <!--排序-->
    <sql id="orderBy">
        <if test="orderBy != null and orderBy.length()>0">
            order by ${orderBy}
        </if>
    </sql>

    <!--翻页-->
    <sql id="limit">
        <if test="rowNum >0 ">
            limit ${(pageNum-1)*rowNum},${rowNum}
        </if>
    </sql>

    <sql id="database">bc_online.</sql>
    <sql id="detTable">ck_zh_industry_product_union_all_agg_d</sql>
    <sql id="brandDict">mainbrand_dic</sql>
    <sql id="shopFollowTable">ck_zh_industry_shop_follow_agg_d</sql>
    <sql id="bottomTable">ck_zh_industry_product_union_all_d</sql>
    <sql id="searchTable">ck_industry_catg_search_click_num_d</sql>
    <sql id="brandShopTable">ck_industry_catg_brand_shop_d</sql>
    <sql id="dealTable">ck_product_deal_detail_d</sql>
</mapper>