This forum is currently locked. You can't register or post questions at this time. (read more)

Oracle 10g, Ranking the records based on Amount

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Naveed Ul islam
Member
Posts: 10
Joined: Wed Jul 24, 2013 9:58 am

Oracle 10g, Ranking the records based on Amount

Postby Naveed Ul islam » Wed Jul 24, 2013 10:32 am

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
PL SQL Developer version 8.0.4.1514

I've a list of 2 million subscribers with column billed_amount and i need to update a column Ranking_flag based on top 10%, next 20% and next 70%.
At 1st i calculated the total subscriber count and updated it in base table of 2 M subscribers.

But rownum is not operational for update statement. Pl suggest.

Thanks & Regards

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Oracle 10g, Ranking the records based on Amount

Postby Tim... » Wed Jul 24, 2013 1:36 pm

Hi.

You don't really give enough information to give a definite answer, but I think you may mean something like this.

Code: Select all

MERGE INTO mytable a
  USING (SELECT col1,
                col2,
                ROW_NUMBER() OVER (ORDER BY billed_amount DESC) AS rowno
         FROM   my_table) b
    ON (a.col1 = b.col1)
  WHEN MATCHED THEN
    UPDATE SET a.ranking_flag = CASE
                                  WHEN b.rowno <= ?? THEN 'top 10%'
                                  WHEN b.rowno <= ?? THEN 'next 20%'
                                  ELSE 'next 70%'
                                END;


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Naveed Ul islam
Member
Posts: 10
Joined: Wed Jul 24, 2013 9:58 am

Re: Oracle 10g, Ranking the records based on Amount

Postby Naveed Ul islam » Thu Jul 25, 2013 4:35 am

We need to calculate the rowno dynamically based on count of records in table. Let me explain:
Two tables: Cust_Segment and Cust_segment_count
In Cust Segment table we have 2 M records with column amount. We need to sort it based on amount (desc) and then we've to mark ranking_flag dynamically (based on total records in table) to 5,4,3,2,1 based on top 10%, next 10%, next 40%, next 20%, next 20%. Then we will calculate the average revenue per user by dividing total amount with record count of that particular ranking_flag.

At 1st we will update the ranking flag and then based on these segments we will calculate the ARPU. Pl guide.

Regards

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Oracle 10g, Ranking the records based on Amount

Postby Tim... » Thu Jul 25, 2013 6:45 am

Hi.

From what you've said I believe the query I posted is the solution. So either you've misunderstood my answer, or you've not clearly explained the issue.

If you need further assistance you should:

- Send the CREATE TABLE statements to build a simplified version of the relevant tables that allow me to see what you are working with.
- The INSERT statements to populate those tables with enough data to allow me to get a feel for the issue.
- An example of the output you except from your query, or how the data should look after the DML is complete.

I know you think you've explained the issue, but you really haven't. Think, if you asked a developer to do this, would you expect what you've said to be enough for them to understand? Now add in the fact that I don;t work with your system and you can see you are not making my life easy. You *must* build a test case if you want me to help.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Naveed Ul islam
Member
Posts: 10
Joined: Wed Jul 24, 2013 9:58 am

Re: Oracle 10g, Ranking the records based on Amount

Postby Naveed Ul islam » Thu Jul 25, 2013 9:00 am

Thanks Tim for support and help. I will try my best to explain.
With what you have shared; i've created the script as:

MERGE INTO RPT_CUST_SEGMENT A
USING (SELECT ACCT_ID,
BILLING_MONTH,
ROW_NUMBER() OVER(ORDER BY CUR_TOTAL_DUES DESC) AS ROWNO
FROM RPT_CUST_SEGMENT) B
ON (A.ACCT_ID = B.ACCT_ID AND A.BILLING_MONTH = B.BILLING_MONTH)
WHEN MATCHED THEN
UPDATE
SET A.RANKING_FLAG = CASE
WHEN B.ROWNO <= ROUND(A.TOTAL_SUBSC * .1, 0) THEN
5
WHEN B.ROWNO <= ROUND(A.TOTAL_SUBSC * .1, 0) THEN
4
WHEN B.ROWNO <= ROUND(A.TOTAL_SUBSC * .4, 0) THEN
3
WHEN B.ROWNO <= ROUND(A.TOTAL_SUBSC * .2, 0) THEN
2
WHEN B.ROWNO <= ROUND(A.TOTAL_SUBSC * .2, 0) THEN
1
ELSE
0
END
WHERE A.BILLING_MONTH = '201306';

But the issue is that the results are not correct; as i need to flag top 10% of total subscriber count and then next 10% then next 40% then next 20% then next 20%.
Acct ID and billing month are unique in table.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Oracle 10g, Ranking the records based on Amount

Postby Tim... » Thu Jul 25, 2013 9:31 am

Hi.

Your maths is flawed. The numbers are cumulative. For the second 10% slot, you were testing for the first 10% again, which those rows will never match.

Code: Select all

MERGE INTO RPT_CUST_SEGMENT A
USING (SELECT ACCT_ID,
BILLING_MONTH,
ROW_NUMBER() OVER(ORDER BY CUR_TOTAL_DUES DESC) AS ROWNO
FROM RPT_CUST_SEGMENT) B
ON (A.ACCT_ID = B.ACCT_ID AND A.BILLING_MONTH = B.BILLING_MONTH)
WHEN MATCHED THEN
UPDATE
SET A.RANKING_FLAG = CASE
WHEN B.ROWNO <= ROUND(A.TOTAL_SUBSC * .1, 0) THEN
5
WHEN B.ROWNO <= ROUND(A.TOTAL_SUBSC * .2, 0) THEN
4
WHEN B.ROWNO <= ROUND(A.TOTAL_SUBSC * .6, 0) THEN
3
WHEN B.ROWNO <= ROUND(A.TOTAL_SUBSC * .8, 0) THEN
2
WHEN B.ROWNO <= ROUND(A.TOTAL_SUBSC * 1, 0) THEN
1
ELSE
0
END
WHERE A.BILLING_MONTH = '201306';


Remember, a case is matched from top to bottom, so once a match is made, the execution of the case stops. So what you are now saying is,

If they are in the first 10%, they are 5. (0-10%) = 10%
If they are in the first 20%, they are 4. (11-20%) = 10%
If they are in the first 60%, they are 3. (21-60%) = 40%
If they are in the first 80%, they are 2. (61-80%) = 20%
If they are in the first 100%, they are 1. (81-100%) =20%

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Naveed Ul islam
Member
Posts: 10
Joined: Wed Jul 24, 2013 9:58 am

Re: Oracle 10g, Ranking the records based on Amount

Postby Naveed Ul islam » Thu Jul 25, 2013 9:47 am

Thank you TIme, Based on your guidance i ve modified the query as:

MERGE INTO RPT_CUST_SEGMENT A
USING (SELECT ACCT_ID,
BILLING_MONTH,
ROW_NUMBER() OVER(ORDER BY CUR_TOTAL_DUES DESC) AS ROWNO
FROM RPT_CUST_SEGMENT) B
ON (A.ACCT_ID = B.ACCT_ID AND A.BILLING_MONTH = B.BILLING_MONTH)
WHEN MATCHED THEN
UPDATE
SET A.RANKING_FLAG = CASE
WHEN B.ROWNO <= ROUND(A.TOTAL_SUBSC * .1, 0) THEN
5
WHEN B.ROWNO <= ROUND(A.TOTAL_SUBSC * .2, 0) THEN
4
WHEN B.ROWNO <= ROUND(A.TOTAL_SUBSC * .6, 0) THEN
3
WHEN B.ROWNO <= ROUND(A.TOTAL_SUBSC * .8, 0) THEN
2
WHEN B.ROWNO <= ROUND(A.TOTAL_SUBSC * 1, 0) THEN
1
ELSE
0
END
WHERE A.BILLING_MONTH = '201306';

Pl check if its correct.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Oracle 10g, Ranking the records based on Amount

Postby Tim... » Thu Jul 25, 2013 10:14 am

Hi.

That's the same query I posted to you. Why would I need to check if it is correct? I don't understand the question...

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Naveed Ul islam
Member
Posts: 10
Joined: Wed Jul 24, 2013 9:58 am

Re: Oracle 10g, Ranking the records based on Amount

Postby Naveed Ul islam » Thu Jul 25, 2013 10:17 am

Results are incorrect:
Total Subscriber's Count: 2,802,935; 1st 10% should be 280,293.5
Ranking_flag Count
0 1866961
1 200495
2 193100
3 361342
4 90707
5 90330

Naveed Ul islam
Member
Posts: 10
Joined: Wed Jul 24, 2013 9:58 am

Re: Oracle 10g, Ranking the records based on Amount

Postby Naveed Ul islam » Thu Jul 25, 2013 10:23 am

Top (based on CUR_TOTAL_DUES) 10% of subscribers are required to be marked as ranking_flag 5; next 10% as 4 and so on but the results are incorrect. Pl check and share the query.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Oracle 10g, Ranking the records based on Amount

Postby Tim... » Thu Jul 25, 2013 10:38 am

Hi.

Your results are all calculated based on the A.TOTAL_SUBSC value. If the A.TOTAL_SUBSC is not the same in every row your code will not work.

I've told you before, I am not able to do much to help because I don;t have a test case to work with. Everything I post is guesswork. You need to take this as a point and work with it, rather than expecting me to solve the problem to completion.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Naveed Ul islam
Member
Posts: 10
Joined: Wed Jul 24, 2013 9:58 am

Re: Oracle 10g, Ranking the records based on Amount

Postby Naveed Ul islam » Thu Jul 25, 2013 10:53 am

TOTAL_SUBSC is same in all rows of each billing_month. Do i need to add partition on billing_month in order by clause?

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Oracle 10g, Ranking the records based on Amount

Postby Tim... » Thu Jul 25, 2013 10:54 am

Hi.

Then your rank would be based on the month, not on the overall total. I don't know if this is what you need.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Oracle 10g, Ranking the records based on Amount

Postby Tim... » Thu Jul 25, 2013 11:08 am

Hi.

Here is the sort of test case I would expect to be sent for this type of question.

Here are the schema objects needed for the test case.

Code: Select all

DROP TABLE t1 PURGE;
CREATE TABLE t1 (
  id NUMBER,
  money NUMBER(10,2),
  status NUMBER
);

INSERT /*+ APPEND */ INTO t1 (id, money)
SELECT level, DBMS_RANDOM.value(1,9999999)
FROM   dual
CONNECT BY level <= 100000;
COMMIT;



The DML:

Code: Select all

VARIABLE total_rows NUMBER;
EXEC :total_rows := 100000;

MERGE INTO t1 A
USING (SELECT id,
              money,
              ROW_NUMBER() OVER(ORDER BY money DESC) AS ROWNO
       FROM   t1) B
ON (A.ID = B.id)
WHEN MATCHED THEN
UPDATE
SET A.status = CASE
                 WHEN B.ROWNO <= ROUND(:total_rows * .1, 0) THEN 5
                 WHEN B.ROWNO <= ROUND(:total_rows * .2, 0) THEN 4
                 WHEN B.ROWNO <= ROUND(:total_rows * .6, 0) THEN 3
                 WHEN B.ROWNO <= ROUND(:total_rows * .8, 0) THEN 2
                 WHEN B.ROWNO <= ROUND(:total_rows * 1, 0) THEN 1
                 ELSE 0
               END;


Here are the results after the DML is run.

Code: Select all

SELECT status, COUNT(*)
FROM   t1
GROUP BY status
ORDER BY status DESC;

    STATUS   COUNT(*)
---------- ----------
         5      10000
         4      10000
         3      40000
         2      20000
         1      20000

SQL>


As you can see, the approach is sound.

If you are not getting the result you expect it is because you have either not provided the correct information, or your data is not what you think it is.

If you expect people to help you on forums, you must provide simplified test cases to give them a chance at being able to help you. If not, it is all guesswork and *very* frustrating for the person trying to help. You are asking people for help, so you should make it as easy as possible for those people to help you. Asking lazy questions is a sure fire way to annoy people and make them avoid answering your questions.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Naveed Ul islam
Member
Posts: 10
Joined: Wed Jul 24, 2013 9:58 am

Re: Oracle 10g, Ranking the records based on Amount

Postby Naveed Ul islam » Fri Jul 26, 2013 5:10 am

Thank you so much Tim; for guidance in detail. I got it now and have applied on my scenario. Its working 100%.
I also understand the concern of test case and i will post the test case as well along with the problem; for any assistance in future.

Thanks & Regards.


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 3 guests

cron