Home » SQL & PL/SQL » SQL & PL/SQL » Query help (Oracle 11.2.0.3, Linux)
Query help [message #608316] Tue, 18 February 2014 10:23 Go to next message
sspn2010
Messages: 137
Registered: October 2008
Senior Member
Hi,

I've below table with cust_id and coupon_id association. Customer can have upto 10 coupons. If the customer has <= 3 coupons then the query has to return 1st, 2nd and 3rd coupon rows. If the customer has more than 3 coupons then query has to return 1st, 2nd coupon records and 3rd row with '3+' without any coupon.


create table TST_COUPON_TBL
(
  cust_id   NUMBER(38) not null,
  coupon_id NUMBER(8),
  bucket_no NUMBER
);

insert into tst_coupon_tbl (CUST_ID, COUPON_ID, BUCKET_NO)
values ('104988', '78130483', '1');

insert into tst_coupon_tbl (CUST_ID, COUPON_ID, BUCKET_NO)
values ('104988', '52084310', '10');

insert into tst_coupon_tbl (CUST_ID, COUPON_ID, BUCKET_NO)
values ('104988', '42043812', '9');

insert into tst_coupon_tbl (CUST_ID, COUPON_ID, BUCKET_NO)
values ('104988', '32012358', '8');

insert into tst_coupon_tbl (CUST_ID, COUPON_ID, BUCKET_NO)
values ('104988', '22043821', '7');

insert into tst_coupon_tbl (CUST_ID, COUPON_ID, BUCKET_NO)
values ('104988', '32138454', '6');

insert into tst_coupon_tbl (CUST_ID, COUPON_ID, BUCKET_NO)
values ('104988', '71043837', '2');

insert into tst_coupon_tbl (CUST_ID, COUPON_ID, BUCKET_NO)
values ('104988', '12043849', '5');

insert into tst_coupon_tbl (CUST_ID, COUPON_ID, BUCKET_NO)
values ('104988', '91403823', '4');

insert into tst_coupon_tbl (CUST_ID, COUPON_ID, BUCKET_NO)
values ('104988', '88314097', '3');

insert into tst_coupon_tbl (CUST_ID, COUPON_ID, BUCKET_NO)
values ('106606', '78130483', '1');

insert into tst_coupon_tbl (CUST_ID, COUPON_ID, BUCKET_NO)
values ('106606', '71043837', '2');

insert into tst_coupon_tbl (CUST_ID, COUPON_ID, BUCKET_NO)
values ('106790', '78130483', '1');

insert into tst_coupon_tbl (CUST_ID, COUPON_ID, BUCKET_NO)
values ('117177', '78130483', '1');

insert into tst_coupon_tbl (CUST_ID, COUPON_ID, BUCKET_NO)
values ('117177', '88314097', '3');

insert into tst_coupon_tbl (CUST_ID, COUPON_ID, BUCKET_NO)
values ('117177', '71043837', '2');

insert into tst_coupon_tbl (CUST_ID, COUPON_ID, BUCKET_NO)
values ('117274', '78130483', '1');

insert into tst_coupon_tbl (CUST_ID, COUPON_ID, BUCKET_NO)
values ('117274', '71043837', '2');

insert into tst_coupon_tbl (CUST_ID, COUPON_ID, BUCKET_NO)
values ('117274', '88314097', '3');

insert into tst_coupon_tbl (CUST_ID, COUPON_ID, BUCKET_NO)
values ('117274', '91403823', '4');

insert into tst_coupon_tbl (CUST_ID, COUPON_ID, BUCKET_NO)
values ('117274', '12043849', '5');

commit;

select * from tst_coupon_tbl;

SQL> select * from tst_coupon_tbl;
 
                                CUST_ID COUPON_ID                 BUCKET_NO
--------------------------------------- ------------------------ ----------
                                 104988 78130483                          1
                                 104988 52084310                         10
                                 104988 42043812                          9
                                 104988 32012358                          8
                                 104988 22043821                          7
                                 104988 32138454                          6
                                 104988 71043837                          2
                                 104988 12043849                          5
                                 104988 91403823                          4
                                 104988 88314097                          3
                                 106606 78130483                          1
                                 106606 71043837                          2
                                 106790 78130483                          1
                                 117177 78130483                          1
                                 117177 88314097                          3
                                 117177 71043837                          2
                                 117274 78130483                          1
                                 117274 71043837                          2
                                 117274 88314097                          3
                                 117274 91403823                          4
                                 117274 12043849                          5
 
21 rows selected

I'm looking for below output

                                CUST_ID COUPON_ID                 BUCKET_NO
--------------------------------------- ------------------------ ----------
                                 117274 78130483                          1
                                 117274 71043837                          2
                                 117274                                   3+
                                 106606 78130483                          1
                                 106606 71043837                          2
                                 106790 78130483                          1
                                 117177 78130483                          1
                                 117177 71043837                          2
                                 117177 88314097                          3
                                 104988 78130483                          1
                                 104988 71043837                          2
                                 104988                                   3+

I've below query to get the above results. However i need to run ths against a big table which has millions of rows. Is there any efficient way of doing this without UNION?

with tmp_tbl as
(
select cust_id, coupon_id, bucket_no, row_number() over(partition by cust_id order by bucket_no) coupon_number,
       count(*) over(partition by cust_id) as total_Coupons
 from tst_coupon_tbl
 order by 1,3
)
select cust_id, coupon_id, cast(bucket_no as varchar2(5)) as bucket_no, coupon_number, total_coupons
  from tmp_tbl where total_coupons < 4
union
select cust_id, case when coupon_number = 3 then null else coupon_id end as coupon_id, 
       case when coupon_number = 3 then '3+' else to_char(bucket_no) end as bucket_no,
       coupon_number, total_coupons
  from tmp_tbl where total_coupons > 3 and coupon_number < 4
order by 1,3;


Re: Query help [message #608321 is a reply to message #608316] Tue, 18 February 2014 11:37 Go to previous messageGo to next message
Michel Cadot
Messages: 57607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> with 
  2    data as (
  3      select cust_id,
  4             max(decode(bucket_no, 1,coupon_id, 2,coupon_id, 3,coupon_id, null)) coupon_id,
  5             decode(bucket_no, 1,'1', 2,'2', 3,'3', '3+') bucket
  6      from tst_coupon_tbl
  7      group by cust_id, decode(bucket_no, 1,'1', 2,'2', 3,'3', '3+') 
  8    )
  9  select cust_id, 
 10         decode(count(*), 1, max(coupon_id), null) coupon_id,
 11         max(bucket) bucket_no
 12  from data 
 13  group by cust_id, substr(bucket,1,1)
 14  order by cust_id, bucket_no
 15  /
   CUST_ID  COUPON_ID BU
---------- ---------- --
    104988   78130483 1
    104988   71043837 2
    104988            3+
    106606   78130483 1
    106606   71043837 2
    106790   78130483 1
    117177   78130483 1
    117177   71043837 2
    117177   88314097 3
    117274   78130483 1
    117274   71043837 2
    117274            3+

12 rows selected.

Re: Query help [message #608322 is a reply to message #608321] Tue, 18 February 2014 11:38 Go to previous messageGo to next message
Michel Cadot
Messages: 57607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or simpler:
SQL> with 
  2    data as (
  3      select cust_id, max(coupon_id) coupon_id,
  4             decode(bucket_no, 1,'1', 2,'2', 3,'3', '3+') bucket
  5      from tst_coupon_tbl
  6      group by cust_id, decode(bucket_no, 1,'1', 2,'2', 3,'3', '3+') 
  7    )
  8  select cust_id, 
  9         decode(count(*), 1, max(coupon_id), null) coupon_id,
 10         max(bucket) bucket_no
 11  from data 
 12  group by cust_id, substr(bucket,1,1)
 13  order by cust_id, bucket_no
 14  /
   CUST_ID  COUPON_ID BU
---------- ---------- --
    104988   78130483 1
    104988   71043837 2
    104988            3+
    106606   78130483 1
    106606   71043837 2
    106790   78130483 1
    117177   78130483 1
    117177   71043837 2
    117177   88314097 3
    117274   78130483 1
    117274   71043837 2
    117274            3+

Re: Query help [message #608323 is a reply to message #608322] Tue, 18 February 2014 11:55 Go to previous messageGo to next message
sspn2010
Messages: 137
Registered: October 2008
Senior Member
Thank you Micahel.

I've given only 3 fields in my test data. But the tst_coupon_tbl has another 6 fiedls in my actual table, so i need to get MAX() for all those fields in the subquery cluase right?

Thanks
SS
Re: Query help [message #608325 is a reply to message #608323] Tue, 18 February 2014 12:10 Go to previous messageGo to next message
Michel Cadot
Messages: 57607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If the result for these columns should follow the same rule than for coupon_id, yes.

Re: Query help [message #608327 is a reply to message #608325] Tue, 18 February 2014 13:21 Go to previous message
sspn2010
Messages: 137
Registered: October 2008
Senior Member
Ok. Thank you!
Previous Topic: Alter Table Command in relation to another table primary key
Next Topic: Oracle Pl/SQL Case Statement
Goto Forum:
  


Current Time: Fri Apr 18 00:02:47 CDT 2014

Total time taken to generate the page: 0.09490 seconds