Home » SQL & PL/SQL » SQL & PL/SQL » Query help with CUBE function (Oracle 11.2.0.4, Linux)
| Query help with CUBE function [message #615829] |
Mon, 09 June 2014 14:00  |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
Hi,
I've the following data in a table.
Create table tst_cust_dtl
(cust_id number(10),
yr number(4),
wk number(2),
seg_cd varchar2(20),
channel varchar2(20),
enroll_src varchar2(20),
cust_status varchar2(30),
total_amt number(10,2),
total_txn number(10),
redeem_cnt number(10)
);
insert into tst_cust_dtl (cust_id, yr, wk, seg_cd, channel, enroll_src, cust_status, total_amt, total_txn, redeem_cnt)
values (10, 2014, 13, 'EMAIL', 'Store', 'POS', 'New', 123.25, 3, 0);
insert into tst_cust_dtl (cust_id, yr, wk, seg_cd, channel, enroll_src, cust_status, total_amt, total_txn, redeem_cnt)
values (20, 2014, 13, 'PLAT', 'Store', 'WEB', 'Existing', 22.48, 1, 1);
insert into tst_cust_dtl (cust_id, yr, wk, seg_cd, channel, enroll_src, cust_status, total_amt, total_txn, redeem_cnt)
values (30, 2014, 13, 'GOLD', 'Online', 'WEB', 'Existing', 57.15, 1, 0);
insert into tst_cust_dtl (cust_id, yr, wk, seg_cd, channel, enroll_src, cust_status, total_amt, total_txn, redeem_cnt)
values (30, 2014, 13, 'REG', 'Multi', 'OTHER', 'New', 114.83, 2, 1);
commit;
select * from tst_cust_dtl;
I need to create a cube for the above data. Using below query to get the results but this is running very very slow on my production data (i've more aggregate fields in my acutal table). Is there anyway i can improve the performance of this query?
select * from (
select yr, wk, CASE WHEN lvl = 1 AND 1 = 1 THEN 'All'
WHEN lvl = 2 AND seg_cd IN ('PLAT', 'GOLD', 'REG') THEN 'ALL CCARD'
WHEN lvl = 3 AND seg_cd = 'PLAT' THEN 'ALL PLAT'
WHEN lvl = 4 AND seg_cd = 'GOLD' THEN 'ALL GOLD'
WHEN lvl = 5 AND seg_cd = 'REG' THEN 'ALL REG'
WHEN lvl = 6 AND seg_cd = 'EMAIL' THEN 'ALL Non-CCARD'
END as seg_cd,
coalesce(channel,'Total') as channel,
coalesce(enroll_src,'Total') as enroll_Src,
coalesce(cust_status,'Total') as cust_status,
sum(total_amt) as total_amt,
sum(total_txn) as total_txns,
sum(redeem_cnt) as redeem_cnt
from tst_cust_dtl a,
( SELECT LEVEL lvl
FROM DUAL
CONNECT BY LEVEL <= 6)
group by yr, wk, CASE WHEN lvl = 1 AND 1 = 1 THEN 'All'
WHEN lvl = 2 AND seg_cd IN ('PLAT', 'GOLD', 'REG') THEN 'ALL CCARD'
WHEN lvl = 3 AND seg_cd = 'PLAT' THEN 'ALL PLAT'
WHEN lvl = 4 AND seg_cd = 'GOLD' THEN 'ALL GOLD'
WHEN lvl = 5 AND seg_cd = 'REG' THEN 'ALL REG'
WHEN lvl = 6 AND seg_cd = 'EMAIL' THEN 'ALL Non-CCARD'
END,
cube(channel,
enroll_src,
cust_status)
) where seg_cd is not null order by 2,3,4;
Appreciate your help!!!
Thanks
SS
|
|
|
|
|
|
| Re: Query help with CUBE function [message #615887 is a reply to message #615853] |
Tue, 10 June 2014 10:12  |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
Hi Lalit,
This is for reporting where user can select any combination and based on the selection corresponding data will show up in the report. So im using cube in my query to get the data for all possible combinations.
In my query i'm using CONNECT BY LEVEL to get the aggregation for different values in the seg_cd. I don't know if this is the best approach to use LEVEL to do the loop in the SQL. Is there a better way to do this without using LEVEL?
Thank you for your help!
Thanks
SS
|
|
|
|
| Re: Query help with CUBE function [message #615888 is a reply to message #615887] |
Tue, 10 June 2014 10:47 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
|
You are yet to tell the rules to get the required output. The rule is to construct the query and the conditions. Simply saying that you need all possible combinations leads to cartesian product. You could use cartesian then, but its mostly unwanted. So please explain the rules.
|
|
|
|
| Re: Query help with CUBE function [message #615891 is a reply to message #615888] |
Tue, 10 June 2014 11:09 |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
In the table i've 4 seg_cd's EMAIL, PLAT, GOLD and REG. FOllowing are the rules.
1) For seg_cd 'EMAIL' need all posssible combinations for channel, enroll_src and cust_status fields.
2) For Seg_cd 'PLAT' need all posssible combinations for channel, enroll_src and cust_status fields.
3) For Seg_cd 'GOLD' need all posssible combinations for channel, enroll_src and cust_status fields.
4) For Seg_cd 'REG' need all posssible combinations for channel, enroll_src and cust_status fields.
5) Combine the seg_cd's PLAT, GOLD and REG and need all possible combinations for channel, enroll_src and cust_status fields.
6) Combine the seg_cd's EMAIL, PLAT, GOLD and REG and need all possible combinations for channel, enroll_src and cust_status fields.
Hope i might have answered your question.
Thanks
SS
|
|
|
|
| Re: Query help with CUBE function [message #615942 is a reply to message #615891] |
Wed, 11 June 2014 06:02 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
I think there should be a total of 44 rows for all the possible correct combinations then :
SQL> SELECT *
2 FROM (WITH data
3 AS (SELECT *
4 FROM tst_cust_dtl)
5 -- 1) For seg_cd 'EMAIL' need all posssible combinations for channel, enroll_src and cust_status fields.
6 -- 2) For Seg_cd 'PLAT' need all posssible combinations for channel, enroll_src and cust_status fields.
7 -- 3) For Seg_cd 'GOLD' need all posssible combinations for channel, enroll_src and cust_status fields.
8 -- 4) For Seg_cd 'REG' need all posssible combinations for channel, enroll_src and cust_status fields.
9 SELECT A.yr,
10 A.wk,
11 CASE
12 WHEN A.seg_cd = 'PLAT' THEN 'ALL PLAT'
13 WHEN A.seg_cd = 'GOLD' THEN 'ALL GOLD'
14 WHEN A.seg_cd = 'REG' THEN 'ALL REG'
15 WHEN A.seg_cd = 'EMAIL' THEN 'ALL Non-CCARD'
16 END SEG_CD,
17 A.channel,
18 A.enroll_src,
19 A.cust_status,
20 A.total_amt,
21 A.total_txn,
22 A.redeem_cnt
23 FROM data A,
24 tst_cust_dtl B
25 UNION ALL
26 -- 5) Combine the seg_cd's PLAT, GOLD and REG and need all possible combinations for channel, enroll_src and cust_status fields.
27 SELECT A.yr,
28 A.wk,
29 CASE
30 WHEN A.seg_cd IN ( 'PLAT', 'GOLD', 'REG' ) THEN 'ALL CCARD'
31 END SEG_CD,
32 A.channel,
33 A.enroll_src,
34 A.cust_status,
35 A.total_amt,
36 A.total_txn,
37 A.redeem_cnt
38 FROM data A,
39 tst_cust_dtl B
40 UNION ALL
41 -- 6) Combine the seg_cd's EMAIL, PLAT, GOLD and REG and need all possible combinations for channel, enroll_src and cust_status fields.
42 SELECT A.yr,
43 A.wk,
44 CASE
45 WHEN A.seg_cd IN ( 'EMAIL', 'PLAT', 'GOLD', 'REG' ) THEN
46 'ALL SEG_CD'
47 END SEG_CD,
48 A.channel,
49 A.enroll_src,
50 A.cust_status,
51 A.total_amt,
52 A.total_txn,
53 A.redeem_cnt
54 FROM data A,
55 tst_cust_dtl B)
56 WHERE seg_cd IS NOT NULL
57 ORDER BY 2,
58 3,
59 4;
YR WK SEG_CD CHANNEL ENROLL_SRC CUST_STATUS TOTAL_AMT TOTAL_TXN REDEEM_CNT
----- --- ------------- -------------------- -------------------- ------------------------------ ------------ ----------- -----------
2014 13 ALL CCARD Multi OTHER New 114.83 2 1
2014 13 ALL CCARD Multi OTHER New 114.83 2 1
2014 13 ALL CCARD Multi OTHER New 114.83 2 1
2014 13 ALL CCARD Multi OTHER New 114.83 2 1
2014 13 ALL CCARD Online WEB Existing 57.15 1 0
2014 13 ALL CCARD Online WEB Existing 57.15 1 0
2014 13 ALL CCARD Online WEB Existing 57.15 1 0
2014 13 ALL CCARD Online WEB Existing 57.15 1 0
2014 13 ALL CCARD Store WEB Existing 22.48 1 1
2014 13 ALL CCARD Store WEB Existing 22.48 1 1
2014 13 ALL CCARD Store WEB Existing 22.48 1 1
2014 13 ALL CCARD Store WEB Existing 22.48 1 1
2014 13 ALL GOLD Online WEB Existing 57.15 1 0
2014 13 ALL GOLD Online WEB Existing 57.15 1 0
2014 13 ALL GOLD Online WEB Existing 57.15 1 0
2014 13 ALL GOLD Online WEB Existing 57.15 1 0
2014 13 ALL Non-CCARD Store POS New 123.25 3 0
2014 13 ALL Non-CCARD Store POS New 123.25 3 0
2014 13 ALL Non-CCARD Store POS New 123.25 3 0
2014 13 ALL Non-CCARD Store POS New 123.25 3 0
YR WK SEG_CD CHANNEL ENROLL_SRC CUST_STATUS TOTAL_AMT TOTAL_TXN REDEEM_CNT
----- --- ------------- -------------------- -------------------- ------------------------------ ------------ ----------- -----------
2014 13 ALL PLAT Store WEB Existing 22.48 1 1
2014 13 ALL PLAT Store WEB Existing 22.48 1 1
2014 13 ALL PLAT Store WEB Existing 22.48 1 1
2014 13 ALL PLAT Store WEB Existing 22.48 1 1
2014 13 ALL REG Multi OTHER New 114.83 2 1
2014 13 ALL REG Multi OTHER New 114.83 2 1
2014 13 ALL REG Multi OTHER New 114.83 2 1
2014 13 ALL REG Multi OTHER New 114.83 2 1
2014 13 ALL SEG_CD Multi OTHER New 114.83 2 1
2014 13 ALL SEG_CD Multi OTHER New 114.83 2 1
2014 13 ALL SEG_CD Multi OTHER New 114.83 2 1
2014 13 ALL SEG_CD Multi OTHER New 114.83 2 1
2014 13 ALL SEG_CD Online WEB Existing 57.15 1 0
2014 13 ALL SEG_CD Online WEB Existing 57.15 1 0
2014 13 ALL SEG_CD Online WEB Existing 57.15 1 0
2014 13 ALL SEG_CD Online WEB Existing 57.15 1 0
2014 13 ALL SEG_CD Store POS New 123.25 3 0
2014 13 ALL SEG_CD Store POS New 123.25 3 0
2014 13 ALL SEG_CD Store POS New 123.25 3 0
2014 13 ALL SEG_CD Store POS New 123.25 3 0
2014 13 ALL SEG_CD Store WEB Existing 22.48 1 1
YR WK SEG_CD CHANNEL ENROLL_SRC CUST_STATUS TOTAL_AMT TOTAL_TXN REDEEM_CNT
----- --- ------------- -------------------- -------------------- ------------------------------ ------------ ----------- -----------
2014 13 ALL SEG_CD Store WEB Existing 22.48 1 1
2014 13 ALL SEG_CD Store WEB Existing 22.48 1 1
2014 13 ALL SEG_CD Store WEB Existing 22.48 1 1
44 rows selected
The reason for 44 rows is, for the 11 SEG_CDs combinations and 4 rows in the base table, you will have 11x4 = 44 rows. Why do you want to use cube?
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Apr 16 05:30:21 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
