CLICKHOUSE函数使用经验（arrayJoin与arrayMap函数应用场景）

clickhouse array Join与arrayMap的使用及一些场景

万山数据@风中追风

5027人浏览 · 2023-04-10 14:56:59

万山数据@风中追风 · 2023-04-10 14:56:59 发布

1.函数简介

（1）arrayJoin函数

先把官方的文档贴上来

Array JOIN 子句允许在数据表的内部，与数组或者嵌套的字段进行JOIN操作，从而将一行数据变多行。适用于行转列操作

示例：

CREATE TABLE arrays_test
(
    s String,
    arr Array(UInt8)
) ENGINE = Memory;

INSERT INTO arrays_test
VALUES ('Hello', [1,2]), ('World', [3,4,5]), ('Goodbye', []);

┌─s───────────┬─arr─────┐
│ Hello       │ [1,2]   │
│ World       │ [3,4,5] │
│ Goodbye     │ []      │
└─────────────┴─────────┘

array join

SELECT s, arr
FROM arrays_test
ARRAY JOIN arr;

┌─s─────┬─arr─┐
│ Hello │   1 │
│ Hello │   2 │
│ World │   3 │
│ World │   4 │
│ World │   5 │
└───────┴─────┘

left array join

SELECT s, arr
FROM arrays_test
LEFT ARRAY JOIN arr;

┌─s───────────┬─arr─┐
│ Hello       │   1 │
│ Hello       │   2 │
│ World       │   3 │
│ World       │   4 │
│ World       │   5 │
│ Goodbye     │   0 │
└─────────────┴─────┘

（2）arrayMap函数

arrayMap(fun1,arr1)

参数1：这是一个高阶函数，您必须将 lambda 函数作为第一个参数传递给它，并且不能省略它，它规定了对于数组中元素的操作

参数2：要执行的数组

示例：对数组中每个元素+2

SELECT arrayMap(x -> (x + 2), [1, 2, 3]) as arr;

┌─res─────┐
│ [3,4,5] │
└─────────┘

从不同的数组创建元素元组：

SELECT arrayMap((x, y) -> (x, y), [1, 2, 3], [4, 5, 6]) AS res

┌─res─────────────────┐
│ [(1,4),(2,5),(3,6)] │
└─────────────────────┘

2.使用场景

先看数据：

┌─dept──┬─budget_name─┬─type──┬────money─┬─posting_date─┐
│ 部门1 │ 主营收入    │ type1 │  3654.32 │   2023-04-10 │
│ 部门1 │ 所得税      │ type1 │      300 │   2023-04-10 │
│ 部门1 │ 成本        │ type1 │  2036.32 │   2023-04-10 │
│ 部门1 │ 折旧和摊销  │ type1 │   462.98 │   2023-04-10 │
│ 部门1 │ 发货套数    │ type1 │      100 │   2023-04-10 │
│ 部门1 │ 主营收入    │ type2 │  45632.5 │   2023-04-10 │
│ 部门1 │ 所得税      │ type2 │ 12036.32 │   2023-04-10 │
│ 部门1 │ 成本        │ type2 │ 10630.34 │   2023-04-10 │
│ 部门1 │ 折旧和摊销  │ type2 │   4123.2 │   2023-04-10 │
│ 部门1 │ 发货套数    │ type2 │     1000 │   2023-04-10 │
│ 部门1 │ 主营收入    │ 公共  │ 10000000 │   2023-04-10 │
│ 部门2 │ 主营收入    │ type1 │ 13654.32 │   2023-04-10 │
│ 部门2 │ 所得税      │ type1 │  3000.37 │   2023-04-10 │
│ 部门2 │ 成本        │ type1 │      300 │   2023-04-10 │
│ 部门2 │ 折旧和摊销  │ type1 │      300 │   2023-04-10 │
│ 部门2 │ 发货套数    │ type1 │      300 │   2023-04-10 │
│ 部门2 │ 主营收入    │ type2 │  3654.32 │   2023-04-10 │
│ 部门2 │ 所得税      │ type2 │    300.3 │   2023-04-10 │
│ 部门2 │ 成本        │ type2 │   2203.9 │   2023-04-10 │
│ 部门2 │ 折旧和摊销  │ type2 │      300 │   2023-04-10 │
│ 部门2 │ 发货套数    │ type2 │      300 │   2023-04-10 │
│ 部门2 │ 主营收入    │ 公共  │ 30000000 │   2023-04-10 │
└───────┴─────────────┴───────┴──────────┴──────────────┘

需求1：求出各个dept各个type（不包括公共）的收入，所得税，成本，折旧和摊销，发货套数，产值（收入*1.13），毛利（收入-成本），单套收入（收入/发货套数），利润总额（收入-成本-折旧和摊销-所得税），净利润（利润总额-所得税），要求输出的数据集中带有指标名称。

select dept,type,arr.1 as budget,arr.2 as money from
(select dept,type,
       [('主营收入',sumIf(ifNull(money,0),budget_name='主营收入') as `营业收入`),
        ('所得税',sumIf(ifNull(money,0),budget_name='所得税') as `所得税`),
        ('成本',sumIf(ifNull(money,0),budget_name='成本') as `成本`),
        ('折旧和摊销',sumIf(ifNull(money,0),budget_name='折旧和摊销') as `折旧和摊销`),
        ('发货套数',sumIf(ifNull(money,0),budget_name='发货套数') as `发货套数`),
        ('产值', toDecimal64((`营业收入` * 1.13), 2) as `产值`),
        ('毛利', toDecimal64((`营业收入` - `成本`), 2) as  `毛利`),
        ('单套收入', toDecimal64(if(`发货套数` != 0, (`营业收入` / `发货套数`), 0), 2) as `单套收入`),
        ('利润总额',toDecimal64((`营业收入` - `成本` - `折旧和摊销`), 2) as  `利润总额`),
        ('净利润',toDecimal64((`利润总额` - `所得税` ), 2) as  `净利润`)] as arr
from factory.factory
where toYear(posting_date)=2023 and type!='公共'
group by dept,type) array join arr;

┌─dept──┬─type──┬─budget─────┬────money─┐
│ 部门1 │ type1 │ 主营收入   │  3654.32 │
│ 部门1 │ type1 │ 产值       │  4129.38 │
│ 部门1 │ type1 │ 净利润     │   855.02 │
│ 部门1 │ type1 │ 利润总额   │  1155.02 │
│ 部门1 │ type1 │ 单套收入   │    36.54 │
│ 部门1 │ type1 │ 发货套数   │      100 │
│ 部门1 │ type1 │ 成本       │  2036.32 │
│ 部门1 │ type1 │ 所得税     │      300 │
│ 部门1 │ type1 │ 折旧和摊销 │   462.98 │
│ 部门1 │ type1 │ 毛利       │     1618 │
│ 部门1 │ type2 │ 主营收入   │  45632.5 │
│ 部门1 │ type2 │ 产值       │ 51564.72 │
│ 部门1 │ type2 │ 净利润     │ 18842.64 │
│ 部门1 │ type2 │ 利润总额   │ 30878.96 │
│ 部门1 │ type2 │ 单套收入   │    45.63 │
│ 部门1 │ type2 │ 发货套数   │     1000 │
│ 部门1 │ type2 │ 成本       │ 10630.34 │
│ 部门1 │ type2 │ 所得税     │ 12036.32 │
│ 部门1 │ type2 │ 折旧和摊销 │   4123.2 │
│ 部门1 │ type2 │ 毛利       │ 35002.16 │
│ 部门2 │ type1 │ 主营收入   │ 13654.32 │
│ 部门2 │ type1 │ 产值       │ 15429.38 │
│ 部门2 │ type1 │ 净利润     │ 10053.95 │
│ 部门2 │ type1 │ 利润总额   │ 13054.32 │
│ 部门2 │ type1 │ 单套收入   │    45.51 │
│ 部门2 │ type1 │ 发货套数   │      300 │
│ 部门2 │ type1 │ 成本       │      300 │
│ 部门2 │ type1 │ 所得税     │  3000.37 │
│ 部门2 │ type1 │ 折旧和摊销 │      300 │
│ 部门2 │ type1 │ 毛利       │ 13354.32 │
│ 部门2 │ type2 │ 主营收入   │  3654.32 │
│ 部门2 │ type2 │ 产值       │  4129.38 │
│ 部门2 │ type2 │ 净利润     │   850.12 │
│ 部门2 │ type2 │ 利润总额   │  1150.42 │
│ 部门2 │ type2 │ 单套收入   │    12.18 │
│ 部门2 │ type2 │ 发货套数   │      300 │
│ 部门2 │ type2 │ 成本       │   2203.9 │
│ 部门2 │ type2 │ 所得税     │    300.3 │
│ 部门2 │ type2 │ 折旧和摊销 │      300 │
│ 部门2 │ type2 │ 毛利       │  1450.42 │
└───────┴───────┴────────────┴──────────┘

因为需求中要输出指标名称，但是有的指标名称是通过现有指标延申出来的，这种指标名称需要造出来，所以如果对于clickhouse的array join不熟悉的话，很多人可能第一个想法是通过union all 和as的方式

select dept,type,'主营收入' as name,sum(money) from factory.factory
where toYear(posting_date)=2023 and type!='公共'
group by dept,type
union all
select dept,type,'产值' as name,toDecimal64(sumIf(money,budget_name='主营收入')*1.13,2) as money from factory.factory
where toYear(posting_date)=2023 and type!='公共'
group by dept,type
union all
.....

这种方式也能实现需求，这是因为我们举例的dept，type以及指标都很少，但是如果部门和指标很多呢，union all连接起来的sql将会特别多，而且每个sql都会全量数据查询，这将会执行很长时间，且各个指标的计算逻辑非常不清晰，如果后期有一个指标逻辑调整，需要改动的不止一段sql，特别容易出错，相反array join的方式，只会全量查询一次且指标逻辑清晰，没有重复sql，效果翻倍

再对于金额类数据时需要注意精度问题，比如下面这个需求

需求2：计算每个dept的各type的分摊收入

计算逻辑：每个dept的各个非公共type占所有非公共type的收入占比*公共的收入

select A.dept,A.type,A.rate*B.public as money,B.public from
(select dept,type,if(bb!=0,aa / bb,0) as rate from(
select distinct
       dept,
       type,
       sum(toDecimal64(money,9)) over(partition by dept,type) as aa,
       sum(toDecimal64(money,9)) over(partition by dept)  as bb
from factory.factory
where toYear(posting_date)=2023 and type!='公共' and budget_name='主营收入') ) A left join
(select dept,ifNull(sum(money),0) as public from factory.factory
where budget_name='主营收入' and toYear(posting_date)=2023 and type='公共' group by dept) B
on A.dept=B.dept

┌─dept──┬─type──┬───────money─┬───public─┐
│ 部门1 │ type1 │   741439.59 │ 10000000 │
│ 部门1 │ type2 │   9258560.4 │ 10000000 │
│ 部门2 │ type1 │ 23666192.13 │ 30000000 │
│ 部门2 │ type2 │  6333807.84 │ 30000000 │
└───────┴───────┴─────────────┴──────────┘

我们可以看到每个部门的公共金额特别大，如果我们不做decimal9处理，除出来的小数位只有2位，而且decimal他是截取小数位，2位后面的小数会直接截取掉，即使他截取0.00001个精度，0.00001*10000000 也将丢失100，而decimal64，9处理后他的误差只有0.01，在可以接受的范围内。

我们可以把decimal64，9改成2

┌─dept──┬─type──┬────money─┬───public─┐
│ 部门1 │ type1 │   700000 │ 10000000 │
│ 部门1 │ type2 │  9200000 │ 10000000 │
│ 部门2 │ type1 │ 23400000 │ 30000000 │
│ 部门2 │ type2 │  6300000 │ 30000000 │
└───────┴───────┴──────────┴──────────┘

可以看到部门1的误差达到了100000，部门2的误差达到了300000

通常对于数字型数据处理时还要注意ifNull判断，因为null和任何数字做算法都为null

┌─plus(NULL, 1)─┐
│ ᴺᵁᴸᴸ          │
└───────────────┘

如果我们想要对null值进行转换操作，还需要注意是否存在空字符串，空字符串并不会被当作null值处理，可以结合empty

array join还可以临时创建配置表使用：比如求3种产品的当月销量，某个产品当月没有数据但是又需要展示出来时可以造一个配置表去left join 比如select arrayJoin( array('室内门','入户','窗')) as product

3. array join与arrayMap的结合

先看数据

┌─dept─┬─money─┬─posting_date─┐
│ dept │   100 │   2021-01-01 │
│ dept │   100 │   2021-02-01 │
│ dept │   100 │   2021-03-01 │
│ dept │   100 │   2021-04-01 │
│ dept │   100 │   2021-05-01 │
│ dept │   100 │   2021-06-01 │
│ dept │   100 │   2021-07-01 │
│ dept │   100 │   2021-08-01 │
│ dept │   100 │   2021-09-01 │
│ dept │   100 │   2021-10-01 │
│ dept │   100 │   2021-11-01 │
│ dept │   100 │   2021-12-01 │
│ dept │   100 │   2022-01-01 │
│ dept │   100 │   2022-02-01 │
│ dept │   100 │   2022-03-01 │
│ dept │   100 │   2022-04-01 │
│ dept │   100 │   2022-05-01 │
│ dept │   100 │   2022-06-01 │
│ dept │   100 │   2022-07-01 │
│ dept │   100 │   2022-08-01 │
│ dept │   100 │   2022-09-01 │
│ dept │   100 │   2022-10-01 │
│ dept │   100 │   2022-11-01 │
│ dept │   100 │   2022-12-01 │
└──────┴───────┴──────────────┘

需求：求dept近12个月的金额，每个月的计算范围往是截止到当前月的近12个月

select dept,sum(money) as money,toYear(m) as year,toMonth(m) as month
from factory.factory_1 array join  arrayMap(x->addMonths(posting_date, x), range(0, 12)) as m
where posting_date between toStartOfMonth(subtractMonths(toDate(now()), 23)) and now()
and m between toStartOfMonth(subtractMonths(toDate(now()), 11)) and now()
group by dept ,year,month
order by year asc,month asc

┌─dept─┬─money─┬─year─┬─month─┐
│ dept │  1200 │ 2022 │     5 │
│ dept │  1200 │ 2022 │     6 │
│ dept │  1200 │ 2022 │     7 │
│ dept │  1200 │ 2022 │     8 │
│ dept │  1200 │ 2022 │     9 │
│ dept │  1200 │ 2022 │    10 │
│ dept │  1200 │ 2022 │    11 │
│ dept │  1200 │ 2022 │    12 │
│ dept │  1100 │ 2023 │     1 │
│ dept │  1000 │ 2023 │     2 │
│ dept │   900 │ 2023 │     3 │
│ dept │   800 │ 2023 │     4 │
└──────┴───────┴──────┴───────┘

range（0，12）形成一个0-11的数组，x代表数组的每个元素

arrayMap(x->addMonths(posting_date, x), range(0, 12))

arrayMap让posting_date依次去addMonths，每个时间都被addMonths执行了12次

可以看到数据是截止到22年12月，所以23年开始每个月都少了100，截止到23年4月的近12个月只有800

开放原子开发者工作坊

开放原子开发者工作坊旨在鼓励更多人参与开源活动，与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动，如meetup、训练营等，主打技术交流，干货满满，真诚地邀请各位开发者共同参与！

更多推荐

第二届开放原子大赛open-eBackup/Cantian两大赛项火热开启

开放原子开发者工作坊

人民邮电报：“开源”到底是什么？为啥热度越来越高？

开放原子开发者工作坊

第二届开放原子大赛——vivo蓝河操作系统创新赛火热进行中

开放原子开发者工作坊

所有评论(0)

查看更多评论

万山数据@风中追风

@m0_62401275

已为社区贡献1条内容