今天有个同事问我一个问题,想通过一条sql语句完成一个稍显复杂的查询。 结构如下面所示。需要算出tax apply 的值,但是需要汇总charge_amount列和tax_amount列的值
CDL_CRD | Charge_Amount | Tax_Amount | Tax apply | |
---|---|---|---|---|
50 | 20 | 5 | 50*5/(100+18) | |
50 | 30 | 6 | 50*6/(100+18) | |
50 | 50 | 7 | 50*7/(100+18) | |
Total | 50 | 100 | 18 |
比如第一条数据。
50*5/(100+18) 其中100=20+30+50 18=5+6+7
因为数据量很小,就几千条,所以我决定使用分析函数来,要不按照以前的方法,建临时表之类的还是有些麻烦了。
按照要求建了一个简单的表来测试一下。
SQL> create table test(cdl_crd number,charge_amount number,tax_amount number);
Table created.
SQL> insert into test values(50,20,5);
insert into test values(50,30,6);
1 row created.
SQL>
1 row created.
SQL> insert into test values(50,50,7);
1 row created.
SQL> commit;
Commit complete.
查看数据分布情况,和示例的一样。
SQL> select *from test;
CDL_CRD CHARGE_AMOUNT TAX_AMOUNT
---------- ------------- ----------
50 20 5
50 30 6
50 50 7
开始计算。
SQL> select cdl_crd*tax_amount/sum(tax_amount+charge_amount) over(partition by cdl_crd) apply_amount from test;
APPLY_AMOUNT
------------
2.11864407
2.96610169
2.54237288
关于分析函数确实能省去不少表的自连接带来的困扰,而且性能也不赖,在充分的测试之后使用其实还是很不错的。