clickhouse--求累计数值

---

参考：
clickhouse–行列转换
clickhouse–开窗函数

数据准备

create table test.test_accum(
	pkg String,
	day String,
	region_0 UInt32,
	region_1 UInt32,
	region_2 UInt32
) ENGINE = MergeTree()
ORDER BY pkg

向数据表中插入数据：

INSERT INTO test.test_accum VALUES
('pkg1', '20220510', 1000, 2000, 3000),
('pkg1', '20220511', 1000, 2000, 3000),
('pkg1', '20220512', 1000, 2000, 3000),
('pkg1', '20220513', 1000, 2000, 3000),
('pkg1', '20220411', 1000, 2000, 3000),
('pkg1', '20220412', 1000, 2000, 3000),
('pkg2', '20220510', 1000, 2000, 3000),
('pkg2', '20220511', 2000, 3000, 3000),
('pkg2', '20220512', 1000, 3000, 3000),
('pkg2', '20220513', 3000, 2000, 3000)

查询数据，得到结果：

┌─pkg──┬─day──────┬─region_0─┬─region_1─┬─region_2─┐
│ pkg1 │ 20220510 │     1000 │     2000 │     3000 │
│ pkg1 │ 20220511 │     1000 │     2000 │     3000 │
│ pkg1 │ 20220512 │     1000 │     2000 │     3000 │
│ pkg1 │ 20220513 │     1000 │     2000 │     3000 │
│ pkg1 │ 20220411 │     1000 │     2000 │     3000 │
│ pkg1 │ 20220412 │     1000 │     2000 │     3000 │
│ pkg2 │ 20220510 │     1000 │     2000 │     3000 │
│ pkg2 │ 20220511 │     2000 │     3000 │     3000 │
│ pkg2 │ 20220512 │     1000 │     3000 │     3000 │
│ pkg2 │ 20220513 │     3000 │     2000 │     3000 │
└──────┴──────────┴──────────┴──────────┴──────────┘

数据需求

希望按照APP来统计不同区间内数值的百分比，同时也需要计算按照区间递增的累计百分比。预期结果如下：

┌─pkg──┬─region───┬─value─┬───────value_percent─┬───────accum_percent─┐
│ pkg1 │ region_0 │  6000 │ 0.16666666666666666 │ 0.16666666666666666 │
│ pkg1 │ region_1 │ 12000 │  0.3333333333333333 │                 0.5 │
│ pkg1 │ region_2 │ 18000 │                 0.5 │                   1 │
│ pkg2 │ region_0 │  7000 │  0.2413793103448276 │  0.2413793103448276 │
│ pkg2 │ region_1 │ 10000 │  0.3448275862068966 │  0.5862068965517241 │
│ pkg2 │ region_2 │ 12000 │ 0.41379310344827586 │                   1 │
└──────┴──────────┴───────┴─────────────────────┴─────────────────────┘

实现方法

arraySum

我们的思路是先将数据初步统计，然后进行数组压缩，得到数组以后使用array join进行展开。而在展开的过程中，通过arraySlice不断获取数组的一部分子集，然后用arraySum对子集进行求和，从而得到最终的累计值。

select
	pkg,
	region,
	value,
	value/sum_value value_percent,
	arraySum(arraySlice(value_arr, 1, num))/sum_value accum_percent
from
	(select
		pkg,
		region_0 + region_1 + region_2 as sum_value,
		array(region_0, region_1, region_2) as value_arr
	from
		(select 
			pkg,
			sum(region_0) as region_0,
			sum(region_1) as region_1,
			sum(region_2) as region_2
		from test.test_accum
		group by pkg
		) aa
	) bb
array join
	['region_0', 'region_1', 'region_2'] as region,
	value_arr as value,
	arrayEnumerate(value_arr) AS num

结果如下：

┌─pkg──┬─region───┬─value─┬───────value_percent─┬───────accum_percent─┐
│ pkg1 │ region_0 │  6000 │ 0.16666666666666666 │ 0.16666666666666666 │
│ pkg1 │ region_1 │ 12000 │  0.3333333333333333 │                 0.5 │
│ pkg1 │ region_2 │ 18000 │                 0.5 │                   1 │
│ pkg2 │ region_0 │  7000 │  0.2413793103448276 │  0.2413793103448276 │
│ pkg2 │ region_1 │ 10000 │  0.3448275862068966 │  0.5862068965517241 │
│ pkg2 │ region_2 │ 12000 │ 0.41379310344827586 │                   1 │
└──────┴──────────┴───────┴─────────────────────┴─────────────────────┘

arrayCumSum

前一种方法要不断对数组进行切割，我们其实也可以直接使用arrayCumSum获取数组的每个元素到当前遍历元素的所有元素和，从而得到目标累计值。

select
	pkg,
	region,
	value,
	value/sum_value value_percent,
	value_accum/sum_value accum_percent
from
	(select
		pkg,
		region_0 + region_1 + region_2 as sum_value,
		array(region_0, region_1, region_2) as value_arr,
		arrayCumSum(array(region_0, region_1, region_2)) as value_accum
	from
		(select 
			pkg,
			sum(region_0) as region_0,
			sum(region_1) as region_1,
			sum(region_2) as region_2
		from test.test_accum
		group by pkg
		) aa
	) bb
array join
	['region_0', 'region_1', 'region_2'] as region,
	value_arr as value,
	value_accum

结果如下：

┌─pkg──┬─region───┬─value─┬───────value_percent─┬───────accum_percent─┐
│ pkg1 │ region_0 │  6000 │ 0.16666666666666666 │ 0.16666666666666666 │
│ pkg1 │ region_1 │ 12000 │  0.3333333333333333 │                 0.5 │
│ pkg1 │ region_2 │ 18000 │                 0.5 │                   1 │
│ pkg2 │ region_0 │  7000 │  0.2413793103448276 │  0.2413793103448276 │
│ pkg2 │ region_1 │ 10000 │  0.3448275862068966 │  0.5862068965517241 │
│ pkg2 │ region_2 │ 12000 │ 0.41379310344827586 │                   1 │
└──────┴──────────┴───────┴─────────────────────┴─────────────────────┘

开窗

除了上述两种方法，也可以使用开窗函数进行累计值的获取，如下所示；

select
	pkg,
	region,
	value,
	value/sum_value value_percent,
	sum(value/sum_value) over win as accum_percent
from
	(select
		pkg,
		region_0 + region_1 + region_2 as sum_value,
		array(region_0, region_1, region_2) as value_arr
	from
		(select 
			pkg,
			sum(region_0) as region_0,
			sum(region_1) as region_1,
			sum(region_2) as region_2
		from test.test_accum
		group by pkg
		) aa
	) bb
array join
	['region_0', 'region_1', 'region_2'] as region,
	value_arr as value
WINDOW win as (partition by pkg ORDER BY region rows between unbounded preceding and current row)
SETTINGS allow_experimental_window_functions = 1

结果如下：

┌─pkg──┬─region───┬─value─┬───────value_percent─┬───────accum_percent─┐
│ pkg1 │ region_0 │  6000 │ 0.16666666666666666 │ 0.16666666666666666 │
│ pkg1 │ region_1 │ 12000 │  0.3333333333333333 │                 0.5 │
│ pkg1 │ region_2 │ 18000 │                 0.5 │                   1 │
│ pkg2 │ region_0 │  7000 │  0.2413793103448276 │  0.2413793103448276 │
│ pkg2 │ region_1 │ 10000 │  0.3448275862068966 │  0.5862068965517242 │
│ pkg2 │ region_2 │ 12000 │ 0.41379310344827586 │                   1 │
└──────┴──────────┴───────┴─────────────────────┴─────────────────────┘