Re: Multiple use of RANK analytic in a single query

From: Geoff Muldoon <geoff.muldoon_at_trap.gmail.com>
Date: Wed, 16 Nov 2011 09:49:54 +1100
Message-ID: <MPG.292d97a09f005c2a81_at_news.x-privat.org>

hooperc2000_at_yahoo.com says...

> > I'm a novice in using the RANK analytical function, struggling with
the
> > basic syntax of it let alone trying to figure out if what I'm trying to
> > achieve is possible, maybe even using subqueries ...

> I think that what needs to be done is to build the solution in
> stages. First, let's calculate the SUM values for the rows - I took a
> best guess at the partition clause using the raw data that you
> provided and the expected output:

> SUM(LOAD) OVER (PARTITION BY ID,DEPT) SUM_LOAD_ID,
> SUM(LOAD) OVER (PARTITION BY ID,LOCATION) SUM_LOAD_LOCATION,
Eureka.

Thank you Charles, that partitioned summing in a subquery was just the thing!

IOU big time.

> Are you able to provide a sample table with data for your real-ish
> code? It is difficult to determine the original data values from the
> two output examples that you provided.

Working example:

CREATE TABLE test_table (load_year NUMBER, person_id VARCHAR2(5), org_code VARCHAR2(5), class_code VARCHAR2(5), load_value NUMBER);

INSERT INTO test_table (load_year, person_id, org_code, class_code, load_value)
VALUES (2010, '001', 'A64', 'HEW3', 55); INSERT INTO test_table (load_year, person_id, org_code, class_code, load_value)
VALUES (2010, '001', 'A62', 'HEW3', 15); INSERT INTO test_table (load_year, person_id, org_code, class_code, load_value)
VALUES (2010, '001', 'A62', 'HEW4', 15); INSERT INTO test_table (load_year, person_id, org_code, class_code, load_value)
VALUES (2010, '001', 'A64', 'HEW4', 10); INSERT INTO test_table (load_year, person_id, org_code, class_code, load_value)
VALUES (2010, '002', 'A52', 'HEW3', 20); INSERT INTO test_table (load_year, person_id, org_code, class_code, load_value)
VALUES (2010, '002', 'A53', 'HEW4', 15); INSERT INTO test_table (load_year, person_id, org_code, class_code, load_value)
VALUES (2010, '002', 'A54', 'HEW4', 10); SELECT
  load_year,
  person_id,
  org_code,
  DENSE_RANK() OVER (PARTITION BY load_year, person_id

                     ORDER BY org_load DESC) 

    AS org_rank,
  class_code,
  DENSE_RANK() OVER (PARTITION BY load_year, person_id

                     ORDER BY class_load DESC) 
    AS class_rank,
  load_value                                     

FROM
 (SELECT
   load_year,
   person_id,
   org_code,
   SUM(load_value) OVER (PARTITION BY load_year, person_id, org_code)      AS org_load,
   class_code,
   SUM(load_value) OVER (PARTITION BY load_year, person_id, class_code)      AS class_load,
   load_value
  FROM test_table);

LOAD_YEAR PERSON_ID ORG_CODE ORG_RANK CLASS_CODE CLASS_RANK LOAD_VALUE --------- --------- -------- ---------- ---------- ---------- ----------

     2010 001       A64               1 HEW3                1         55
     2010 001       A64               1 HEW4                2         10
     2010 001       A62               2 HEW4                2         15
     2010 001       A62               2 HEW3                1         15
     2010 002       A52               1 HEW3                2         20
     2010 002       A53               2 HEW4                1         15
     2010 002       A54               3 HEW4                1         10

Perfecto!

Geoff Received on Tue Nov 15 2011 - 16:49:54 CST