我可以在这里使用甲骨文分析功能吗?

Question

我有这个查询：

SELECT year,
       month,
       week,
       C.cpg_pk                                                        CPG,
       C.dep_pk                                                        DEPT,
       T.cust_id                                                       CUST_ID,
       D1.r_id                                                         R_ID,
       Decode(d2.at_code, '3', func1.Get_att(d2.at_code, D2.val_code)) AS P1,
       Decode(d2.at_code, '2', func1.Get_att(d2.at_code, D2.val_code)) AS IC,
       Decode(d2.at_code, '1', func1.Get_att(d2.at_code, D2.val_code)) AS B1,
       Decode(func1.Get_att(d2.at_code, D2.val_code), 2, d2.at_code)   AS P2,
       Decode(func1.Get_att(d2.at_code, D2.val_code), 5, d2.at_code)   AS B2,
       Count(DISTINCT A.cust_id)                                       TOTAL_ACC
       ,
       Count(DISTINCT T.txn_pk)
       TOTAL_TXN,
       SUM(am_amount)                                                  TOTAL_AMT
FROM   t_header T,
       cust_master A,
       tx_details1 D1,
       tx_details2 D2,
       cpg_master C
WHERE  A.TYPE = 0
       AND T.cust_id = A.cust_id
       AND T.txn_pk = 5001
       AND T.txn_pk = D1.txn_pk
       AND T.txn_pk = D2.txn_pk
       AND D1.cpg_pk = C.cpg_pk
       AND D1.op = 1
GROUP  BY year,
          month,
          week,
          C.cpg_pk,
          C.dep_pk,
          t.cust_id,
          D1.r_id,
          Decode(d2.at_code, '3', func1.Get_att(d2.at_code, D2.val_code)),
          Decode(d2.at_code, '2', func1.Get_att(d2.at_code, D2.val_code)),
          Decode(d2.at_code, '1', func1.Get_att(d2.at_code, D2.val_code)),
          Decode(func1.Get_att(d2.at_code, D2.val_code), 2, d2.at_code),
          Decode(func1.Get_att(d2.at_code, D2.val_code), 5, d2.at_code) 

其生成的输出如下：

YEAR    MONTH   WEEK    CPG DEPT    CUST_ID R_ID    P1  IC  B1  P2  B2   TOTAL
2012    08      32     127 -1      10019   3665                     134   23100.09   
2012    08      32     127 -1      10019   3665                     135   23100.09  
2012    08      32     127 -1      10019   3665             723           23100.09  
2012    08      32     127 -1      10019   3665        714                23100.09  
2012    08      32     127 -1      10019   3665     21                    23100.09
2012    08      32     128 -1      10019   3665                      134  23100.09 
2012    08      32     128 -1      10019   3665                      135  23100.09 
2012    08      32     128 -1      10019   3665             723           23100.09 
2012    08      32     128 -1      10019   3665        714                23100.09 
2012    08      32     128 -1      10019   3665     21                    23100.09

这里的值是重复的。我试图用group by消除重复，但没有成功。你能帮我吗？

所需的输出如下：

YEAR    MONTH   WEEK    CPG DEPT    CUST_ID R_ID    P1  IC  B1  P2  B2  TOTAL_AMT
---------------------------------------------------------------------------------
2012    08      32      127 -1      10019   3665    21  714 723     134 23100.09
2012    08      32      127 -1      10019   3665    21  714 723     135 23100.09
2012    08      32      128 -1      10019   3665    21  714 723     134 23100.09
2012    08      32      128 -1      10019   3665    21  714 723     135 23100.09

主要是year、month、week、cpg、dept、cust_id、r_id、p1、ic、b1、p2，b2它应该是唯一的行。使用分析函数可以实现还是需要编写PL/SQL？

Answer 1

当然，您可以使用分析，如下所示：

SELECT DISTINCT YEAR, MONTH, WEEK, CPG, DEPT, CUST_ID, R_ID,
       SUM(P1) OVER (PARTITION BY YEAR,MONTH,WEEK, CPG, DEPT, CUST_ID, R_ID) P1,
       SUM(IC) OVER (PARTITION BY YEAR,MONTH,WEEK, CPG, DEPT, CUST_ID, R_ID) IC,
       SUM(B1) OVER (PARTITION BY YEAR,MONTH,WEEK, CPG, DEPT, CUST_ID, R_ID) B1, 
       P2, B2, TOT, TOTAL_TXN, TOTAL_AMT 
  FROM (your_query)