ClickHouse学习笔记 - 数组拆分再分组

2022-01-14

全文 ≈ 1.7k字 | 预计阅读时长 7分钟

ClickHouse是个由老毛子国内市场使用率第一的搜索引擎Yandex开源的OLAP型列式数据库。

背景

最近，数据分析项目要做一个广告素材推广成效分析功能。需求评审下来大概是通过给广告素材打标签进行分类，然后按标签纬度进行分析数据。

数据库选型

该项目之前的数据是存放在Elasticsearch，但总是感觉ES的DSL语句过于繁琐不利于阅读且学习成本较高，内部针对该问题讨论之后选用了在大数据处理上同样出色的ClickHouse作为新功能的数据仓库（试点功能233）。

数据结构

因为是列式数据库，所以不需要遵守数据库三大范式。

伪表结构：

CREATE TABLE creative_daily_summary
(
    `date` Date,
    `creative_id` String,
    `tags` Array(String),
    `revenue` Float64,
    `cost` Float64,
    `impressions` Int64,
    `clicks` Int64,
    `installs` Int64
)
ENGINE = MergeTree
ORDER BY date
SETTINGS index_granularity = 8192

这里因为考虑数据冗余的问题，标签存储为数组类型（所以也就出现了这篇笔记）。

存储一些数据：

INSERT INTO creative_daily_summary (`date`,creative_id,tags,revenue,cost,impressions,clicks,installs) VALUES ('2022-01-07','1721',['代入/代入角色/屏幕外角色','玩法/装修/修理','玩法/模拟经营/建筑升级','玩法/小游戏/情景选择(废除)','元素/其他/人手','元素/其他/表情包','元素/场景/农场'],0.0,2.41,2204,0,0);
INSERT INTO creative_daily_summary (`date`,creative_id,tags,revenue,cost,impressions,clicks,installs) VALUES ('2022-01-07','1721',['代入/代入角色/屏幕外角色','玩法/装修/修理','玩法/模拟经营/建筑升级','玩法/小游戏/情景选择(废除)','元素/其他/人手','元素/其他/表情包','元素/场景/农场'],0.0,0.05,9,1,0);
INSERT INTO creative_daily_summary (`date`,creative_id,tags,revenue,cost,impressions,clicks,installs) VALUES ('2022-01-08','1788',['代入/视角镜头/第一人称','剧情/情感共鸣/育儿','剧情/援助/解救','剧情/困境/自然灾害','玩法/模拟经营/摘果','玩法/模拟经营/引水','玩法/模拟经营/建筑升级','玩法/改变地形/挖河道'],0.0,130.64,31868,63,7);
INSERT INTO creative_daily_summary (`date`,creative_id,tags,revenue,cost,impressions,clicks,installs) VALUES ('2022-01-09','1788',['代入/视角镜头/第一人称','剧情/情感共鸣/育儿','剧情/援助/解救','剧情/困境/自然灾害','玩法/模拟经营/摘果','玩法/模拟经营/引水','玩法/模拟经营/建筑升级','玩法/改变地形/挖河道'],0.0,0.04,76,0,0);
INSERT INTO creative_daily_summary (`date`,creative_id,tags,revenue,cost,impressions,clicks,installs) VALUES ('2022-01-10','2124',['代入/代入角色/固定角色-女性','剧情/情感共鸣/购物焦虑','目的/目的/拉新','形式/技术形式/3D','元素/场景/城市街道','元素/场景/室内公共场所'],0.0,0.02,5,0,0);

date	creative_id	tags	cost	impressions	clicks	installs
2022-01-07	1721	[‘代入/代入角色/屏幕外角色’,’玩法/装修/修理’,’玩法/模拟经营/建筑升级’,’玩法/小游戏/情景选择(废除)’,’元素/其他/人手’,’元素/其他/表情包’,’元素/场景/农场’]	2.41	2204	0	0
2022-01-07	1721	[‘代入/代入角色/屏幕外角色’,’玩法/装修/修理’,’玩法/模拟经营/建筑升级’,’玩法/小游戏/情景选择(废除)’,’元素/其他/人手’,’元素/其他/表情包’,’元素/场景/农场’]	0.05	9	1	0
2022-01-08	1788	[‘代入/视角镜头/第一人称’,’剧情/情感共鸣/育儿’,’剧情/援助/解救’,’剧情/困境/自然灾害’,’玩法/模拟经营/摘果’,’玩法/模拟经营/引水’,’玩法/模拟经营/建筑升级’,’玩法/改变地形/挖河道’]	130.64	31868	63	7
2022-01-09	1788	[‘代入/视角镜头/第一人称’,’剧情/情感共鸣/育儿’,’剧情/援助/解救’,’剧情/困境/自然灾害’,’玩法/模拟经营/摘果’,’玩法/模拟经营/引水’,’玩法/模拟经营/建筑升级’,’玩法/改变地形/挖河道’]	0.04	76	0	0
2022-01-10	2124	[‘代入/代入角色/固定角色-女性’,’剧情/情感共鸣/购物焦虑’,’目的/目的/拉新’,’形式/技术形式/3D’,’元素/场景/城市街道’,’元素/场景/室内公共场所’]	0.02	5	0	0

检索效果

产品期望实现的需求是统计“标签”的数据，但这个“标签”并不是完整的标签（代入/代入角色/屏幕外角色），而是标签的一部分（比如代入、代入角色、屏幕外角色等，拆分开来的），同时一个广告素材如果满足多个标签即拆分成多条数据。

思路1

利用子查询再分组，这样会有数据重复的问题，所以需要再套一层查询去重。

select
	date,
	tag,
	sum(cost) as cost
from
	(
	select
		date,
		tag,
		creative_id,
		sumDistinct(cost) as cost
	from
		(
		select
			date,
			arrayJoin(tags) as t,
			concat(arrayElement(splitByChar('/',t),1),'/',arrayElement(splitByChar('/',t),2)) as tag,
			creative_id,
			sum(cost) as cost
		from
			creative_daily_summary
		where
			tag in ('代入/代入角色','玩法/模拟经营')
		group by
			t,
			creative_id,
			date)
	group by
		tag,
		creative_id,
		date)
group by
	tag,
	date

date	tag	cost
2022-01-08	玩法/模拟经营	130.64
2022-01-07	代入/代入角色	2.46
2022-01-10	代入/代入角色	0.02
2022-01-09	玩法/模拟经营	0.04
2022-01-07	玩法/模拟经营	2.46

思路2

因为深感第一种方式太挫，效率不高且毫无美感（最重要的是总感觉解决的方向不对）。

select
	date,
	arrayJoin(arrayDistinct(arrayMap(tag -> concat(arrayElement(splitByChar('/',tag),1),'/',arrayElement(splitByChar('/',tag),2)),tags))) as tag,
	sum(cost) as cost
from
	creative_daily_summary
where
	 tag in('代入/代入角色','玩法/模拟经营')
group by
	date,
	tag

date	tag	cost
2022-01-10	代入/代入角色	0.02
2022-01-07	代入/代入角色	2.46
2022-01-08	玩法/模拟经营	130.64
2022-01-07	玩法/模拟经营	2.46
2022-01-09	玩法/模拟经营	0.04

总结

接触新的事物还是要抱着谦逊的态度先系统的了解一下优势和缺点，不然解决问题时总会陷入定式思维的怪圈（在错误的方向浪费时间和精力）。