PostgreSQL array 应用 计算多栏位平均

前言

ptt database版有一篇 计算多栏位平均 传送门

测试资料

我将table 简化,重点在于平均数计算,就使用一个table.

create table t231121a (  sid int not null primary key, chi int, eng int, math int);insert into t231121aselect n     , 60 + floor(40*random())     , 60 + floor(40*random())     , 60 + floor(40*random())  from generate_series(1, 5) as n;  select *  from t231121a; sid | chi | eng | math -----+-----+-----+------   1 |  65 |  98 |   66   2 |  95 |  85 |   84   3 |  85 |  71 |   81   4 |  63 |  75 |   85   5 |  87 |  84 |   70(5 rows)

传统方式

select sid, avg(score)  from (select sid             , chi as score          from t231121a         union all        select sid             , eng as score          from t231121a         union all        select sid             , math as score          from t231121a        ) a group by sid order by sid; sid |         avg         -----+---------------------   1 | 76.3333333333333333   2 | 88.0000000000000000   3 | 79.0000000000000000   4 | 74.3333333333333333   5 | 80.3333333333333333(5 rows)

传统方式的缺点在于需要做多次的存取扫描.

改变table 设计的方式

create table t231121b (  sid int not null primary key, sname text);create table t231121c (  sjid int not null primary key, sjname text);create table t231121d (  sid int not null, sjid int not null, score int, primary key (sid, sjid));insert into t231121b values(1, '小帅'),(2, '小美'),(3, '大壮'),(4, '铁柱'),(5, '老皮');insert into t231121c values(1, '国文'), (2, '英文'), (3, '数学');insert into t231121d values(1, 1, 65), (1, 2, 98), (1, 3, 66),(2, 1, 95), (2, 2, 85), (2, 3, 84),(3, 1, 85), (3, 2, 71), (3, 3, 81),(4, 1, 63), (4, 2, 75), (4, 3, 85),(5, 1, 87), (5, 2, 84), (5, 3, 70);select avg(score)  from t231121d group by sid order by sid;         avg         --------------------- 76.3333333333333333 88.0000000000000000 79.0000000000000000 74.3333333333333333 80.3333333333333333(5 rows)

哪种方式较好?

第二种方式就很容易计算出平均.但是在实际运用中,第一种方式开一条龙的也不在少数.一来科目数量较为固定,变动的不频繁.二来在平时的显示或是报表,时常需要将各科目列出,若用第二种方式,又需要做直转横.转成类似第一种方式的显示.
所以在项目固定的情况下,採用第一种方式,例如每月的分数,放12个栏位,也不见得不好.
但是这种一条龙的,要做多栏位的计算,例如平均,总和,因为系统内建的函数是针对单一栏位的,这时候要做横转直.

Array 又再次登场

之前已经有几篇关于Array的应用.这次我们再使用Array.

-- 使用array 结合的情况select sid     , array[chi, eng, math]  from t231121a; sid |   array    -----+------------   1 | {65,98,66}   2 | {95,85,84}   3 | {85,71,81}   4 | {63,75,85}   5 | {87,84,70}(5 rows)-- 将array 再次展开select sid     , (select avg(score) from unnest(array[chi, eng, math]) as score)  from t231121a; sid |         avg         -----+---------------------   1 | 76.3333333333333333   2 | 88.0000000000000000   3 | 79.0000000000000000   4 | 74.3333333333333333   5 | 80.3333333333333333(5 rows)

可以看到这样做方便计算多栏位,执行效率也较高,只需做一次扫描.

结语

在资料项目有限的情况下,可以善用array来减少table,提高效能.