ClickHouse_行业模块ClickHouse服务优化实战

1.行业底表基本数据统计

1.1 典型二级类目数据量统计与超时请求统计

2022-10-12至2022-11-15的总数据行数与对应的时间较长自定义请求，使用到的索引只有：secondIndId+terminal+shopType

二级类目	底表粒度 (sku+browerAcct)	聚合表粒度 (sku)	数据量缩减倍数	行业趋势 groupby datetime	品牌榜单top10 groupby brand	店铺榜单top10 groupby shop	商品榜单top200 groupby sku
女装1343	542134328	123311605	4.39645829	5.97s	6.33s	4.36s	10.55s
男装1342	606898996	153551059	3.952424685	5.21s	7.144s	5.1s	13.12s
饮料冲调1585	396352751	14614808	27.11994239	4.53s	3.24s	3s	2.31s
奶粉1523	145654211	3533643	41.21927739	2.56s	1.47s	4.23s	4.97s
灯饰照明9856	143318024	24340347	5.888084669	3.99s	2.16s	2.11s	2.91s
减肥塑身9193	3648896	384148	9.498672387	0.68s	0.8s	1.51s	0.83s
特医配方食品27992	3659201	154811	23.63656975	0.66s	0.82s	1.48s	0.75s

1.2 top20数据量的二级类目

secondIndId	count
1342	153551059
1343	123311605
12099	57374279
9858	55893288
1345	54457959
11842	49468349
11730	49372352
830	41721031
6745	40055769
12102	38226249
11731	37844022
11158	36543128
729	32773074
9857	32323407
15249	30765820
1583	29960139
1584	28067354
12147	26508877
9856	24340347
1381	23281586

2.行业底表优化方案

2.1 物化视图与projection方案

针对不同请求接口具体分析请求超时原因，采用不同的优化方案：

商品榜单：更改请求sql，改用二次聚合；
店铺榜单：新建聚合到店铺粒度的物化视图，日店铺数量27w；
品牌榜单：新建聚合到品牌粒度的物化视图，日品牌数量22w；
行业趋势：新建聚合到三级类目粒度的物化视图。

创建测试聚合表

CREATE TABLE bc_online.ck_zh_industry_product_union_all_agg_jiazhengyang3 on cluster LFRH_CK_Pub_173
(
    `dateTime` String,
    `skuId` String,
    `skuName` String,
    `shopId` String,
    `shopName` String,
    `sonBrandId` String,
    `sonBrandName` String,
    `terminalId` String,
    `shopType` String,
    `secondIndId` String,
    `thirdIndId` String,
    `priceRangeId` String,
    `secondPriceRangeId` String,
    `priceRangeName` String,
    `secondPriceRangeName` String,
    `pv` AggregateFunction(sum, Int64),
    `browseAcct` AggregateFunction(uniq, Nullable(String)),
    `dealAmt` AggregateFunction(sum, Float64),
    `dealProNum` AggregateFunction(sum, Int64),
    `dealAcct` AggregateFunction(uniq, Nullable(String)),
    `cartAcct` AggregateFunction(uniq, Nullable(String)),
    `followAcct` AggregateFunction(uniq, Nullable(String)),
    `searchClickNum` AggregateFunction(sum, Int64),
    `browseSku` AggregateFunction(uniq, Nullable(String)),
    `dealSku` AggregateFunction(uniq, Nullable(String))
)
ENGINE = ReplicatedMergeTree('/ckpub173.olap.jd.com/tables/bc_online/ck_zh_industry_product_union_all_agg_jiazhengyang3/{shard}', '{replica}')
PARTITION BY dateTime
ORDER BY (dateTime, secondIndId, thirdIndId, shopType, terminalId, priceRangeId, secondPriceRangeId)
SETTINGS index_granularity = 8192

CREATE TABLE bc_online.ck_zh_industry_product_union_all_agg_jiazhengyang3_d ON CLUSTER LFRH_CK_Pub_173 as bc_online.ck_zh_industry_product_union_all_agg_jiazhengyang3 ENGINE = Distributed('LFRH_CK_Pub_173', 'bc_online', 'ck_zh_industry_product_union_all_agg_jiazhengyang3', rand());

创建三级类目粒度projection

因为要基于该projection继续聚合，所以对去重指标只能使用uniqMerge()中间状态。

alter table bc_online.ck_zh_industry_product_union_all_agg_jiazhengyang3 on cluster LFRH_CK_Pub_173 add projection ck_zh_industry_product_union_all_agg_jiazhengyang3_shop_agg
(
  select
  dateTime,
  terminalId,
  shopType,
  secondIndId,
  thirdIndId,
  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
  group by
  dateTime,
  terminalId,
  shopType,
  secondIndId,
  thirdIndId
)

ALTER TABLE bc_online.ck_zh_industry_product_union_all_agg_jiazhengyang3 on
cluster LFRH_CK_Pub_173 MATERIALIZE PROJECTION ck_zh_industry_product_union_all_agg_jiazhengyang3_shop_agg;

ALTER TABLE bc_online.ck_zh_industry_product_union_all_agg_jiazhengyang3 on
cluster LFRH_CK_Pub_173 DROP PROJECTION ck_zh_industry_product_union_all_agg_jiazhengyang3_shop_agg;

查询测试sql

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_jiazhengyang3_d
	where
		dateTime >= '2023-04=3-23'
		and dateTime <= '2023-04-22'
		and secondIndId in ('1342')
		and shopType in ('POP', 'B2C')
	group by
		dateTime;

没创建projection的执行计划

Expression ((Projection + Before ORDER BY))
  MergingAggregated
    SettingQuotaAndLimits (Set limits and quota after reading from storage)
      Union
        Aggregating
          Expression (Before GROUP BY)
            Filter (WHERE)
              SettingQuotaAndLimits (Set limits and quota after reading from storage)
                ReadFromMergeTree
        ReadFromRemote (Read from remote replica)

创建了projection的执行计划

Expression ((Projection + Before ORDER BY))
  MergingAggregated
    SettingQuotaAndLimits (Set limits and quota after reading from storage)
      Union
        SettingQuotaAndLimits (Set limits and quota after reading from storage)
          ReadFromStorage (MergeTree(with Aggregate projection ck_zh_industry_product_union_all_agg_jiazhengyang3_shop_agg))
        ReadFromRemote (Read from remote replica)

查询ck query日志查看sql执行情况，读取数据量、是否使用projection等

SELECT
	*
from
	`system`.query_log_all
where
	event_date = '2023-04-24'
--	and query_kind = 'Insert'
--	and `exception`<>''
	and query like '%select
dateTime as DateTime,
		sumMerge(pv) as PV,%'
order by
	event_time desc;

查看ck projection系统表

参考文献：https://blog.csdn.net/qq_21383435/article/details/122357463

SELECT
    name,
    partition,
    formatReadableSize(bytes_on_disk) AS bytes,
    formatReadableSize(parent_bytes_on_disk) AS parent_bytes,
    parent_rows,
    rows / parent_rows AS ratio
FROM system.projection_parts

查看ck进程

1
2
3

show processlist

SELECT * from system.processes

结论

查询query日志查看sql执行情况

type	query_duration_ms	read_rows	read_bytes	projections
QueryFinish	2609	20818506	11747434199	[‘bc_online.ck_zh_industry_product_union_all_agg_jiazhengyang3.ck_zh_industry_product_union_all_agg_jiazhengyang3_shop_agg’]
QueryFinish	1094	116128739	105200969395	[]

从日志可以看出使用projection扫描数据量少了，但是查询时间反而变长了，然后ck引擎根据扫描数据量来判断用不用projection，这样就用了projection导致最终呈现出来的查询时间变长了。所以不用projection这种方案。

2.2 个性化sql优化

相比于18集群，173集群已经重建了分片字段，之前使用dt，现在用skuid，查询并发读和效率已经提高了很多。

针对不同请求接口具体分析请求超时原因，采用不同的优化方案：

商品榜单：更改请求sql，改用本地聚合；
品牌榜单：使用字典关联维表；
店铺榜单：关联维表使用字典dictget函数；
行业趋势：

1）行业概况商品榜单

重建分区键之后，使用settings distributed_group_by_no_merge = 1直接进行本地聚合，不再在分布式表中聚合所有数据。

优化前7.3s

优化后1.2s

select 
	UV,
	PV,
	if(UV = 0,
	0,
	DealUser / UV) as DealRate,
	SearchClickNum,
	SonBrandName,
	CartUser,
	DealAmt,
	DealProNum,
	SkuName,
	SonBrandId,
	rowNumberInAllBlocks()+ 1 AS RankRound,
	SkuId,
	ProFollowUser,
	if(DealUser = 0,
	0,
	DealAmt / DealUser) as DealCustPriceAvg
from
	(
	select
		SkuId,
		SkuName,
		SonBrandId,
		SonBrandName,
		PV,
		ProFollowUser,
		DealUser,
		DealProNum,
		SearchClickNum,
		UV,
		DealAmt,
		CartUser
	from
		(
		select
			skuId AS SkuId,
			any(skuName) AS SkuName,
			any(sonBrandId) AS SonBrandId,
			any(sonBrandName) AS SonBrandName ,
			sumMerge(pv) as PV,
			uniqMerge(followAcct) as ProFollowUser,
			uniqMerge(dealAcct) as DealUser,
			sumMerge(dealProNum) as DealProNum,
			sumMerge(searchClickNum) as SearchClickNum,
			uniqMerge(browseAcct) as UV,
			sumMerge(dealAmt) as DealAmt,
			uniqMerge(cartAcct) as CartUser
		from
			bc_online. ck_zh_industry_product_union_all_agg_d
		where
			dateTime >= '2022-10-12'
			and dateTime <= '2022-11-15'
			and secondIndId in ('1343')
			and shopType in ('POP', 'B2C')
			and shopId not in ('10278978', '11810231', '11624131', '11574099', '11444736')
		group by
			skuId
		order by
			DealAmt desc
		limit 0,
		200 settings distributed_group_by_no_merge = 1
	)
	order by
		DealAmt desc
	limit 0,
	200);

2）行业概况品牌榜单

发现uniqMergeState好像不仅没有提升效率，反而还降低了效率，主要还是使用字典对效率提升很高。

优化前4.5s

select
	BrandName,
	UV,
	PV,
	DealAmt,
	DealProNum,
	rowNumberInAllBlocks()+ 1 AS RankRound,
	BrandId
from
	(
	select
		BrandId,
		any(BrandName) as BrandName ,
		sumMerge(pv) as PV,
		sumMerge(dealProNum) as DealProNum,
		uniqMerge(browseAcct) as UV,
		sumMerge(dealAmt) as DealAmt
	from
		(
		select
			sonBrandId AS SonBrandId,
			sonBrandName AS SonBrandName ,
			dealProNum,
			pv,
			browseAcct,
			dealAmt
		from
			bc_online. ck_zh_industry_product_union_all_agg_d
		where
			dateTime >= '2022-10-12'
			and dateTime <= '2022-11-15'
			and secondIndId in ('1343')
			and shopType in ('POP', 'B2C') ) any
	inner join (
		select
			brandId AS SonBrandId,
			mainBrandId AS BrandId,
			mainGroupName AS BrandName
		from
			bc_online. mainbrand_dic )
			using(SonBrandId)
	group by
		BrandId
	order by
		DealAmt desc
	limit 0,
	10 );

优化后1.6s

select
	BrandName,
	UV,
	PV,
	DealAmt,
	DealProNum,
	rowNumberInAllBlocks()+ 1 AS RankRound,
	BrandId
	from
	(
SELECT
	dictGet('bc_online.mainbrand_dic',
	'mainGroupName',
	sonBrandId) AS BrandName,
	uniqMerge(browseAcct) AS UV,
	sumMerge(pv) AS PV,
	sumMerge(dealAmt) AS DealAmt,
	sumMerge(dealProNum) AS DealProNum,
	dictGet('bc_online.mainbrand_dic',
	'mainBrandId',
	sonBrandId) AS BrandId
FROM
	bc_online.ck_zh_industry_product_union_all_agg_d
WHERE
	dateTime >= '2022-10-12'
	AND dateTime <= '2022-11-15'
	AND secondIndId IN ('1343')
	AND shopType IN ('POP', 'B2C')
GROUP BY
	BrandId,
	BrandName
ORDER BY
	DealAmt DESC
LIMIT 10
    );

3）行业概况店铺榜单

这是一个按成交金额排序的榜单，优化点主要是先只算成交金额一个指标的榜单，把前十店铺给先扒出来了，然后再算这是个店铺的所有指标，这样对效率的提升很大。

优化前3.4s

select
	UV,
	ShopName,
	DealSkuNum,
	PV,
	if(UV = 0,
	0,
	DealUser / UV) as DealRate,
	ShopFollowUser,
	SearchClickNum,
	VisitedSkuNum,
	ShopType,
	CartUser,
	DealAmt,
	DealProNum,
	rowNumberInAllBlocks()+ 1 AS RankRound,
	ShopId,
	ProFollowUser,
	if(DealUser = 0,
	0,
	DealAmt / DealUser) as DealCustPriceAvg
from
	(
	select
		shopId as ShopId,
		any(shopName) AS ShopName,
		any(shopType) AS ShopType ,
		sumMerge(pv) as PV,
		uniqMerge(dealSku) as DealSkuNum,
		uniqMerge(followAcct) as ProFollowUser,
		uniqMerge(dealAcct) as DealUser,
		sumMerge(dealProNum) as DealProNum,
		sumMerge(searchClickNum) as SearchClickNum,
		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-10-12'
		and dateTime <= '2022-11-15'
		and secondIndId in ('1343')
		and shopType in ('POP', 'B2C')
		and shopId not in ('10278978', '11810231', '11624131', '11574099', '11444736')
		and toInt32(shopId) > 0
	group by
		ShopId
	order by
		DealAmt desc
	limit 0,
	10 ) any
left join (
	select
		shopId as ShopId,
		uniqMerge(shopFollowAcct) as ShopFollowUser
	from
		bc_online. ck_zh_industry_shop_follow_agg_d
	where
		dateTime >= '2022-10-12'
		and dateTime <= '2022-11-15'
		and shopId GLOBAL IN (
		select
			shopId
		from
			(
			select
				shopId ,
				sumMerge(dealAmt) as DealAmt
			from
				bc_online. ck_zh_industry_product_union_all_agg_d
			where
				dateTime >= '2022-10-12'
				and dateTime <= '2022-11-15'
				and secondIndId in ('1343')
					and shopType in ('POP', 'B2C')
						and shopId not in ('10278978', '11810231', '11624131', '11574099', '11444736')
							and toInt32(shopId) > 0
						group by
							shopId
						order by
							DealAmt desc
						limit 0,
						10 ))
	group by
		shopId )
		using (ShopId)

优化后1.4s

select
	UV,
	ShopName,
	DealSkuNum,
	PV,
	if(UV = 0,
	0,
	DealUser / UV) as DealRate,
	ShopFollowUser,
	SearchClickNum,
	VisitedSkuNum,
	ShopType,
	CartUser,
	DealAmt,
	DealProNum,
	rowNumberInAllBlocks()+ 1 AS RankRound,
	ShopId,
	ProFollowUser,
	if(DealUser = 0,
	0,
	DealAmt / DealUser) as DealCustPriceAvg
from
	(
	select
		shopId as ShopId,
		any(shopName) AS ShopName,
		any(shopType) AS ShopType ,
		sumMerge(pv) as PV,
		uniqMerge(dealSku) as DealSkuNum,
		uniqMerge(followAcct) as ProFollowUser,
		uniqMerge(dealAcct) as DealUser,
		sumMerge(dealProNum) as DealProNum,
		sumMerge(searchClickNum) as SearchClickNum,
		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-10-12'
		and dateTime <= '2022-11-15'
		and secondIndId in ('1343')
		and shopType in ('POP', 'B2C')
		and shopId not in ('10278978', '11810231', '11624131', '11574099', '11444736')
		and toInt32(shopId) > 0
		and shopId GLOBAL IN (
		select
			shopId
		from
			(
			select
				shopId ,
				sumMerge(dealAmt) as DealAmt
			from
				bc_online. ck_zh_industry_product_union_all_agg_d
			where
				dateTime >= '2022-10-12'
				and dateTime <= '2022-11-15'
				and secondIndId in ('1343')
					and shopType in ('POP', 'B2C')
						and shopId not in ('10278978', '11810231', '11624131', '11574099', '11444736')
							and toInt32(shopId) > 0
						group by
							shopId
						order by
							DealAmt desc
						limit 0,
						10 ))
	group by ShopId order by DealAmt desc limit 0,10 
	) any
left join (
	select
		shopId as ShopId,
		uniqMerge(shopFollowAcct) as ShopFollowUser
	from
		bc_online. ck_zh_industry_shop_follow_agg_d
	where
		dateTime >= '2022-10-12'
		and dateTime <= '2022-11-15'
		and shopId GLOBAL IN (
		select
			shopId
		from
			(
			select
				shopId ,
				sumMerge(dealAmt) as DealAmt
			from
				bc_online. ck_zh_industry_product_union_all_agg_d
			where
				dateTime >= '2022-10-12'
				and dateTime <= '2022-11-15'
				and secondIndId in ('1343')
					and shopType in ('POP', 'B2C')
						and shopId not in ('10278978', '11810231', '11624131', '11574099', '11444736')
							and toInt32(shopId) > 0
						group by
							shopId
						order by
							DealAmt desc
						limit 0,
						10 ))
	group by
		shopId )
		using (ShopId);

4）行业趋势

把从同一张表里出的指标放到同一张表中去，就是纯sql结构优化。

优化前2.5s

select
	UV,
	VisitedShopNum,
	CateClickNum,
	DealSkuNum,
	PV,
	if(UV = 0,
	0,
	DealUser / UV) as DealRate,
	BrandNum,
	CateSearchNum,
	DealNum,
	VisitedSkuNum,
	DealUser,
	CartUser,
	DealAmt,
	DealProNum,
	DealShopNum,
	DealBrandNum,
	VisitedBrandNum,
	if(CateSearchNum = 0,
	0,
	CateClickNum / CateSearchNum) as SearchClickRate,
	ShopNum,
	ProFollowUser,
	if(DealUser = 0,
	0,
	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-10-12'
		and dateTime <= '2022-11-15'
		and secondIndId in ('1343')
		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-10-12'
		and dateTime <= '2022-11-15'
		and secondIndId in ('1343')
			and shopType in ('POP', 'B2C' , 'ALL' )
		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-10-12'
		and dateTime <= '2022-11-15'
		and secondIndId in ('1343')
			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-10-12'
		and dateTime <= '2022-11-15'
		and secondIndId in ('1343')
			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-10-12'
		and dateTime <= '2022-11-15'
		and secondIndId in ('1343')
			and shopType in ('POP', 'B2C')
		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-10-12'
		and dateTime <= '2022-11-15'
		and secondIndId in ('1343')
			and shopType in ('POP', 'B2C')
		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-10-12'
		and dateTime <= '2022-11-15'
		and secondIndId in ('1343')
			and shopType in ('POP', 'B2C')
		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-10-12'
		and dateTime <= '2022-11-15'
		and secondIndId in ('1343')
			and shopType in ('POP', 'B2C')
		group by
			dateTime ) j on
	a.DateTime = j.DateTime1;

优化后1.9s

select
	UV,
	VisitedShopNum,
	CateClickNum,
	DealSkuNum,
	PV,
	if(UV = 0,
	0,
	DealUser / UV) as DealRate,
	BrandNum,
	CateSearchNum,
	DealNum,
	VisitedSkuNum,
	DealUser,
	CartUser,
	DealAmt,
	DealProNum,
	DealShopNum,
	DealBrandNum,
	VisitedBrandNum,
	if(CateSearchNum = 0,
	0,
	CateClickNum / CateSearchNum) as SearchClickRate,
	ShopNum,
	ProFollowUser,
	if(DealUser = 0,
	0,
	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-10-12'
		and dateTime <= '2022-11-15'
		and secondIndId in ('1343')
		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-10-12'
		and dateTime <= '2022-11-15'
		and secondIndId in ('1343')
			and shopType in ('POP', 'B2C' , 'ALL' )
		group by
			dateTime ) d on
	a.DateTime = d.DateTime1
left join (
	select
		uniq(shopId) as ShopNum,
		dateTime as DateTime1,
		uniq(sonBrandId) as BrandNum,
		uniq(isDealShop) as DealShopNum,
		uniq(isDealBrand) as DealBrandNum,
		uniq(isVisitedShop) as VisitedShopNum,
		uniq(isVisitedBrand) as VisitedBrandNum
	from
		bc_online. ck_industry_catg_brand_shop_d
	where
		dateTime >= '2022-10-12'
		and dateTime <= '2022-11-15'
		and secondIndId in ('1343')
			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-10-12'
		and dateTime <= '2022-11-15'
		and secondIndId in ('1343')
			and shopType in ('POP', 'B2C')
		group by
			dateTime ) f on
	a.DateTime = f.DateTime1;