在组内执行组操作

Question

在一个名为“business_line”的表中，我有两个列business_line(值为X，Y)和business_segment(值X，Y，Z与business_line的X和Y相同)。我还有一个名为type_of_payment的列(值为A、B、C、D、E)和最后一个名为transaction_value的列。这就是我想要做的：

对按business_line和business_segment分组的交易求和，并找出这些付款中来自A、C、E的比例。因此，我的输出表将如下所示

                                             (last three columns can be named anything
                                              but they specify proportions of A,C,E)  
Business_line       SUM(transaction_value)   A        C        E
and business seg.
X                   100                      20%      30%      50%
Y                   200                      11%      12%      77%     
X                   300                        and so on
Y                   170
Z                   230

我该怎么做？？

PS :A、C、E的和不必是100%，因为B和D也存在

Answer 1

这是标准的SQL，应该可以在Oracle上运行(但未经测试)：

SELECT
   business_line,
   business_segment,
   grand_total,
   A_total * 100.0 / grand_total as A,
   C_total * 100.0 / grand_total as C,
   E_total * 100.0 / grand_total as E
FROM
   (
       SELECT
          business_line,
          business_segment,
          SUM(transaction_value) as grand_total,
          SUM(CASE WHEN payment_type = 'A' THEN transaction_value END) as A_total,
          SUM(CASE WHEN payment_type = 'C' THEN transaction_value END) as C_total,
          SUM(CASE WHEN payment_type = 'E' THEN transaction_value END) as E_total
       FROM
          SomeTable
       GROUP BY
          business_line,
          business_segment
    ) as t

Answer 2

对于Oracle11g和更高版本，您可以使用PIVOT

select *
from
(
select sometable.line, paymenttype,total, 100.0*transaction_value/total as percentage
from sometable
    inner join
    (select line, sum(transaction_value) as total
        from sometable
        group by line) total
    on sometable.line = total.line          
) 
pivot 
(
    sum(percentage) for paymenttype in (a,c,e)
)