我可以在这里使用Oracle分析功能吗？ [英] Can I use Oracle analytical function here?

问题描述

我有以下查询：

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

此处的值在重复。我试图通过组来消除重复，，但没有成功。您能帮我吗？

Here the values are repeating. I tried to eliminate the repeating with a group by, but didn't succeed. Could you help me?

所需的输出是这样的：

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

主要是年，月，周， cpg ，部门， cust_id ， r_id ， p1 ， ic ， b1 ， p2 ， b2 ，它应该是唯一的行。

The main thing is year, month, week, cpg, dept, cust_id, r_id, p1, ic, b1, p2, b2 it should be unique row. Is it achievable using analytical functions or do I need to write PL/SQL?

推荐答案

当然，您可以使用分析功能，例如

Sure, you can use analytics, something like this:

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)

这篇关于我可以在这里使用Oracle分析功能吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！