报表函数

报表函数：

总计：

select 1
,t.month
,t.prd_type_id
,t.emp_id
,t.amount
/*,sum(t.amount) */
,sum(t.amount) over (partition by t.month) as  prdValue
,sum(t.amount) over (partition by t.prd_type_id) as  prdValue
from all_sales t
where t.year=2003
and month =1
and t.emp_id=21
========================================
1	1	1	1	21	10034.84	15139.36	10034.84
2	1	1	2	21	1034.84	15139.36	1034.84
3	1	1	5	21		15139.36	
4	1	1	4	21	3034.84	15139.36	3034.84
5	1	1	3	21	1034.84	15139.36	1034.84

 

百分比：

select 
t.year
,t.month
,t.prd_type_id
,t.emp_id
,t.amount
,ratio_to_report(t.amount) over(partition by t.month) as prd_type_ratio
from all_sales t
where t.year=2003
and t.month<=2
and t.prd_type_id in (1,2,3)
and t.emp_id=21
order by t.month,t.prd_type_id
======================================
1	2003	1	1	21	10034.84	0.82901593784801
2	2003	1	2	21	1034.84	0.0854920310759948
3	2003	1	3	21	1034.84	0.0854920310759948
4	2003	2	1	21	15144.65	0.812745016488721
5	2003	2	2	21	1544.65	0.0828943943715637
6	2003	2	3	21	1944.65	0.104360589139715