レポート関数
1377 ワード
:
:
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