本文介绍了我可以在这里使用Oracle分析功能吗？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有以下查询：
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分析功能吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！