【psql】array元素聚合运算解决办法
psql提供了数组类型array,但是没有直接 对array中元素求和/求均值/中位数等统计运算的方法。对于用习惯了python中简单的np.sum()的我来讲,简直是非常不方便了。尽管有将array中的元素挨个提取出来相加的笨办法,但是如果 不同行的array中元素个数不一样,这一方式就不是很友好了。
这几天刚好工作中碰到了这个问题,百度大法完全不管用,可能不同行array元素长度不一样本来就是不太规范的做法,但是毕竟表结构是不受自己控制的,因此在大牛同事的帮助下想出了这样一个办法:
简单来讲就是两个步骤:
比如现在salary表里有一行数据是这样的: 某个销售员工叫张三基本工资10000,每完成一单,提成2%,这个月他一共做了四单,提成分别是2000,300,20,780;但是因为公司内控需要或者数据库格式设计的缺陷,他的每笔工资数据都被合并记在一行,最后是这样一条:
name | salarys |
---|---|
张三 | 10000+2000+300+20+780 |
这里的salarys还是string格式
现在问题来了,这样的员工有几千个,财务月底要结算每个人这个月的总工资,该怎么求和?
- 将string转成array:string_to_array(string,sep)
其中,string是要转换的字符串,sep是分隔符
比如有一个string是“a,b,c”,指定分隔符为逗号,就可以把它转成{a, b, c}的array
显然,在我们的问题里,分隔符是加号‘+’,sql如下:
select name, string_to_array(salarys, ‘+’) salarys from salary
得到的结果就会是:
name | salarys |
---|---|
张三 | {10000, 2000, 300, 20, 780} |
- 将array列拆成多行:unnest(array)
unnest这个函数可以把一个array里的每一个元素单独放到一行,其他列直接复制,此时sql变为:
select name, unnest(string_to_array(salarys, ‘+’)) salarys from salary
得到的结果就会是:
name | salarys |
---|---|
张三 | 10000 |
张三 | 2000 |
张三 | 300 |
张三 | 20 |
张三 | 780 |
- 用group by汇总聚合:
看到上一步的结果以后,我想只要有sql基础的人都知道该怎么处理了,用name作为key进行groupby即可,这里的例子里我们要做的是求和所以用sum,如果需求不是求和而是其他类型的聚合,只要在这一步里把sum函数换成其他聚合函数即可。
所以最后的sql是:
**
select name, sum(salarys) from
(select name, unnest(string_to_array(salarys, ‘+’)) salarys from salary) a
group by name**
得到的结果:
name | salarys |
---|---|
张三 | 13100 |