-测试代码
create table t_dist
(
TYPE_CD NUMBER,
BUYER_ID VARCHAR2(50),
ORDER_DT DATE,
SO_ID VARCHAR2(50) not null,
STOCK_ID VARCHAR2(50) not null,
UNIT_PRICE NUMBER,
DISCOUNT NUMBER,
QTY NUMBER
);
truncate table t_dist;
insert into t_dist values(1,'CN1001',to_date('2008-04-01','yyyy-mm-dd'),'S9001','29110311',50,10,8);
insert into t_dist values(1,'CN1001',to_date('2008-04-02','yyyy-mm-dd'),'S9002','29110312',60,20,2);
insert into t_dist values(1,'CN1001',to_date('2008-04-03','yyyy-mm-dd'),'S9003','29110313',70,15,3);
insert into t_dist values(2,'CN1001',to_date('2008-04-04','yyyy-mm-dd'),'S9004','29110312',60,15,5);
insert into t_dist values(2,'CN1001',to_date('2008-04-05','yyyy-mm-dd'),'S9005','29110311',70,10,6);
insert into t_dist values(3,'CN1001',to_date('2008-04-06','yyyy-mm-dd'),'S9006','29110313',55,20,4);
insert into t_dist values(3,'CN1001',to_date('2008-04-06','yyyy-mm-dd'),'S9007','29110311',40,10,3);
insert into t_dist values(3,'CN1001',to_date('2008-04-07','yyyy-mm-dd'),'S9008','29110312',50,50,5);
insert into t_dist values(3,'CN1001',to_date('2008-04-07','yyyy-mm-dd'),'S9009','29110313',80,10,2);
insert into t_dist values(1,'CN1001',to_date('2008-04-08','yyyy-mm-dd'),'S9010','29110311',65,10,1);
commit;
请问:如何实现如下结果,谢谢!
即计算按stock_id,type_cd,distount分组,计算每个产品的销售额(qty*unit_price)及销售百分比,并有小计
STOCK_ID TYPE_CD DISCOUNT AVG_PRICE SUM_TOT PCT
-------------------------------------------------------------------------------------------------
29110311 1 10 57.50 465 46.27%
29110311 2 10 70.00 420 41.79%
29110311 3 10 40.00 120 11.94%
小计 55.83 1005 100.00%
29110312 1 20 60.00 120 17.91%
29110312 2 15 60.00 300 44.78%
29110312 3 50 50.00 250 37.31%
小计 56.67 670 100.00%
29110313 1 15 70.00 210 35.59%
29110313 3 10 80.00 160 27.12%
29110313 3 20 55.00 220 37.29%
小计 68.33 590 100.00%
具体如下：
SQL> select * from t_dist;
TYPE_CD BUYER_ID ORDER_DT SO_ID STOCK_ID UNIT_PRICE DISCOUNT QTY
---------- ------------ ----------- ------------ ------------ ---------- ---------- ----------
1 CN1001 2008-4-1 S9001 29110311 50 10 8
1 CN1001 2008-4-2 S9002 29110312 60 20 2
1 CN1001 2008-4-3 S9003 29110313 70 15 3
2 CN1001 2008-4-4 S9004 29110312 60 15 5
2 CN1001 2008-4-5 S9005 29110311 70 10 6
3 CN1001 2008-4-6 S9006 29110313 55 20 4
3 CN1001 2008-4-6 S9007 29110311 40 10 3
3 CN1001 2008-4-7 S9008 29110312 50 50 5
3 CN1001 2008-4-7 S9009 29110313 80 10 2
1 CN1001 2008-4-8 S9010 29110311 65 10 1
10 rows selected
SQL>
SQL> select decode(rn, 1, '小计', STOCK_ID) STOCK_ID,
TYPE_CD,
DISCOUNT,
AVG_PRICE,
SUM_TOT,
round(nvl(RATIO_TO_REPORT(decode(rn, 0, SUM_TOT, null)) over(partition by STOCK_ID), 1) * 100, 2) || '%' PCT
from
(select STOCK_ID,
TYPE_CD,
DISCOUNT,
round(avg(UNIT_PRICE), 2) AVG_PRICE,
sum(QTY * UNIT_PRICE) SUM_TOT,
grouping(TYPE_CD) rn
from t_dist
group by grouping sets((STOCK_ID), (STOCK_ID, TYPE_CD, DISCOUNT)));
STOCK_ID TYPE_CD DISCOUNT AVG_PRICE SUM_TOT PCT
------------ ---------- ---------- ---------- ---------- -----------------------------------------
29110311 1 10 57.5 465 46.27%
29110311 2 10 70 420 41.79%
29110311 3 10 40 120 11.94%
小计 56.25 1005 100%
29110312 1 20 60 120 17.91%
29110312 2 15 60 300 44.78%
29110312 3 50 50 250 37.31%
小计 56.67 670 100%
29110313 1 15 70 210 35.59%
29110313 3 10 80 160 27.12%
29110313 3 20 55 220 37.29%
小计 68.33 590 100%
12 rows selected
SQL>