上次的问题(postgresql):http://topic.csdn.net/u/20081015/15/a66f114a-1863-4a8c-9203-bf9d3790abdf.html表A1:id brand value createDate
1 BM1 1.2 2007-06-03
2 BM2 2.6 2007-06-06
3 BM3 1.8 2008-09-03
4 DZ1 2.1 2008-10-02
5 DZ2 3.4 2008-10-03
6 DZ2 3.2 2008-10-13
7 BM2 2.8 2008-10-14
表B1: id brands
1 BM1,BM2
2 BM2
3 BM1,BM3,DZ2
4 DZ2
5 DZ2 现在需要对B1表中brands进行value值统计
若:id=1 BM1=1.2 value = 1.2+2.6+2.8 = 6.6
BM2=2.6
BM2=2.8
id=2 BM2=2.6 value = 2.6+2.8 = 5.4
BM2=2.8 id=3 BM1=1.2 value = 1.2+1.8+3.4+3.2 = 9.6
BM3=1.8
DZ2=3.4
DZ2=3.2
......现在新增需求:表B1可以根据当前日期按照“年,季,月,周”来统计
如:今天是2008-10-16
按照“年”(2008)查询:
若: id=1 BM2=2.8 value=2.8
id=3 BM3=1.8 value=1.8+3.4+3.2=8.4
DZ2=3.4
DZ2=3.2 ......依此类推
按照“周”(10月12-18)查询:
若: id=1 value=0
id=3 DZ2=3.4 value=3.4+3.2=6.6
DZ2=3.2
1 BM1 1.2 2007-06-03
2 BM2 2.6 2007-06-06
3 BM3 1.8 2008-09-03
4 DZ1 2.1 2008-10-02
5 DZ2 3.4 2008-10-03
6 DZ2 3.2 2008-10-13
7 BM2 2.8 2008-10-14
表B1: id brands
1 BM1,BM2
2 BM2
3 BM1,BM3,DZ2
4 DZ2
5 DZ2 现在需要对B1表中brands进行value值统计
若:id=1 BM1=1.2 value = 1.2+2.6+2.8 = 6.6
BM2=2.6
BM2=2.8
id=2 BM2=2.6 value = 2.6+2.8 = 5.4
BM2=2.8 id=3 BM1=1.2 value = 1.2+1.8+3.4+3.2 = 9.6
BM3=1.8
DZ2=3.4
DZ2=3.2
......现在新增需求:表B1可以根据当前日期按照“年,季,月,周”来统计
如:今天是2008-10-16
按照“年”(2008)查询:
若: id=1 BM2=2.8 value=2.8
id=3 BM3=1.8 value=1.8+3.4+3.2=8.4
DZ2=3.4
DZ2=3.2 ......依此类推
按照“周”(10月12-18)查询:
若: id=1 value=0
id=3 DZ2=3.4 value=3.4+3.2=6.6
DZ2=3.2
create table A1(id int,brand varchar(100),value numeric(18,2),createdate date);
insert into A1 values(1,'BM1',1.2,'20081012');
insert into A1 values(1,'BM2',2.6,'20071012');
insert into A1 values(1,'BM2',2.8,'20081013');create table B1(id int,brands varchar(100));
insert into B1 values(1,'BM1,BM2'); --使用join的方法,年统计select b.id,sum(value)
from B1 b join A1 a
on a.brand = any (string_to_array(brands,','))
where date_trunc('year',a.createDate) = date_trunc('year',current_date)
group by b.id;--result--------------------------- id | brands | sum_value
----+---------+-----------
1 | BM1,BM2 | 4.00--使用子查询的方法,年统计select *,
(select sum(value) from A1
where brand = any (string_to_array(B1.brands,','))
and date_trunc('year',createDate) = date_trunc('year',current_date)
) as sum_value
from B1;--result---------------------------- id | brands | sum_value
----+---------+-----------
1 | BM1,BM2 | 4.00我们可以修改date_trunc函数把'year'修改成'week','month','decade'来达到我们周,月,季的统计.
position(b.brand in a.brands)>0 where year(createDate )=2008 and month(createDate )=10
group by a.id
其它的参考你的函数
id | brands | year_value | quarter_value | month_value | week_value大家帮忙看看。现在效率太低。
有什么好的建议或者(程序)实现方式(数据表结构不变)
用子查询来写成,代码如上述
(select sum(value) from A1
where brand = any (string_to_array(B1.brands,','))
and date_trunc('year',createDate) = date_trunc('year',current_date)
) as year_value,
(select sum(value) from A1
where brand = any (string_to_array(B1.brands,','))
and date_trunc('week',createDate) = date_trunc('week',current_date)
) as week_value,
(select sum(value) from A1
where brand = any (string_to_array(B1.brands,','))
and date_trunc('month',createDate) = date_trunc('month',current_date)
) as month_value
from B1;
再根据ID、brands连接