CODEWITHSUNDEEP
sqloracle-database
rayne
rayne

Reputation: 49

Combine rank and sum in sql

DB - Oracle. All constraints omitted.

create table customer (cid number(10), cname varchar(50));

create table exercise (eid number(10), ecode varchar(2), score number(2));

-- mapping table
create table customer_exercise (cid number(10), eid number(10), cnt number(10))  

Customer table

cid    cname
100    e1
200    e2
300    e3
400    e4

Exercise table

eid    ecode   score
1      c1       5
2      c2       10
3      c3       6
4      c4       3

Customer_Exercise

cid  eid count
100   1    2
200   2    5
100   2    3
300   4   10

SQL to retrieve total count -

 SELECT   c.cid
    ,e.eid
    ,COALESCE(SUM(ce.cnt), 0) AS total_cnt
FROM customer c
     CROSS JOIN exercise e
     LEFT JOIN customer_exercise ce
        ON     ce.cid = c.cid
           AND ce.eid = e.eid
WHERE c.cid IN (100, 200, 300)
      AND e.eid IN (1, 2)
GROUP BY c.cid, e.eid
ORDER by c.cid

Result -

c.cid e.eid total_cnt
100    1         2
100    2         3
200    1         0
200    2         5
300    1         0
300    2         0

SQL to calculate rank for each customer-

select cid , RANK() OVER (ORDER BY sum(total_score) desc) as rank from 
(
SELECT   c.cid as cid
    ,e.eid
    ,COALESCE(SUM(ce.cnt), 0) AS total_cnt
    , COALESCE(SUM(ce.cnt), 0) * e.score as total_score
FROM customer c
     CROSS JOIN exercise e
     LEFT JOIN customer_exercise ce
        ON     ce.cid = c.cid
           AND ce.eid = e.eid
WHERE     c.cid IN (100, 200, 300)
     AND e.eid IN (1, 2)
GROUP BY c.cid, e.eid, e.score 
)
GROUP BY cid
ORDER BY rank

Result -

 c.cid  rank
  200    1
  100    2
  300    3

Is it possible to get the resultset with one query instead of the two above? I am looking to merge the result of above two queries into one. Expected result is posted below.

Expected result -

c.cid   e.eid    total_cnt  rank
200       1      0           1  
200       2      5           1
100       1      2           2
100       2      3           2
300       1      0           3
300       2      0           3

Upvotes: 3

Views: 6644

Answers (4)

Trung Duong
Trung Duong

Reputation: 3475

You could use SUM(...) OVER(...) to calculate total score for each customer then rank by this value.

SELECT  cid, eid, SUM(cnt) AS total_cnt, DENSE_RANK() OVER (ORDER BY cid, total_score DESC) AS rank
FROM
    (
       select c.cid, e.eid, e.score, ce.cnt, SUM(ce.cnt * ce.score) OVER (PARTITION BY c.cid) AS total_score
       from   customer c cross join exercise e
              left outer join customer_exercise ce   on c.cid = ce.cid
                                                    and e.eid = ce.eid
       where  c.cid in (100, 200, 300)
         and  e.eid in (1, 2) 
    ) data   
GROUP BY cid, eid, total_score
ORDER BY rank, eid

I've created a demo here

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269923

I would do this with two levels of analytic functions:

select cid, eid, score, cnt,
       dense_rank() over (order by coalece(total_score, 0) desc) as rnk
from (select c.cid, e.eid, e.score, ce.cnt,
             sum(e.score) over (partition by c.cid) as total_score
      from customer c cross join
           exercise e left outer join
           customer_exercise ce
           on c.cid = ce.cid and e.eid = ce.eid
      where  c.cid in (100, 200, 300) and e.eid in (1, 2)
     ) ce;

Upvotes: 0

Aman Prajapati
Aman Prajapati

Reputation: 157

You can also use multiple CTE + SUM + DENSE_RANK() specially for MS SQL database query

;WITH CTE_CUST AS
(
SELECT   cust.cid
        ,exe.eid
        ,exe.score
        ,SUM(ISNULL(cust_exe.cnt,0)) AS total_cnt
FROM    customer cust
        CROSS JOIN
        exercise exe
        LEFT JOIN 
        customer_exercise cust_exe
            ON cust_exe.cid = cust.cid 
                AND cust_exe.eid = exe.eid 
GROUP BY cust.cid, exe.eid,exe.score
)
, CTE_RANK AS
(
SELECT  cid, DENSE_RANK() OVER (order by (SUM(ISNULL((total_cnt * cte.score),0))) DESC) rank_score
FROM    CTE_CUST cte
GROUP BY cid
)
SELECT  cust.cid, cust.eid, cust.total_cnt as 'count', rnk.rank_score 
FROM    CTE_RANK AS rnk
        JOIN
        CTE_CUST AS cust
            ON rnk.cid = cust.cid
WHERE   cust.cid IN (100, 200, 300)
            AND cust.eid IN (1, 2)  
ORDER BY rank_score

Upvotes: 1

user5683823
user5683823

Reputation:

The join of the three tables is used twice - once to show the counts and once more to compute the ranks. The first version is not aggregated, while the second one is (and with analytic rank calculated after aggregation). This is a perfect application of the WITH clause: do the join of three tables in a factored subquery (CTE, WITH clause) and use it twice.

with
     j ( cid, eid, score, cnt ) as (
       select c.cid, e.eid, e.score, ce.cnt
       from   customer c cross join exercise e
              left outer join customer_exercise ce   on c.cid = ce.cid
                                                    and e.eid = ce.eid
       where  c.cid in (100, 200, 300)
         and  e.eid in (1, 2)
     )
select j.cid, j.eid, nvl(j.cnt, 0) as total_count, r.rnk
from   j left join ( select   cid, 
                              rank() over (order by sum(cnt*score) desc nulls last) as rnk
                     from     j
                     group by cid
                   ) r
                     on j.cid = r.cid
order by rnk, eid
;

Upvotes: 4

Related Questions