Home » SQL & PL/SQL » SQL & PL/SQL » Weighted sum in group by
Weighted sum in group by [message #288801] Tue, 18 December 2007 14:49 Go to next message
emilSverige
Messages: 21
Registered: October 2006
Junior Member
I have a poker database, with a table like this:
CREATE TABLE DECISIONHISTORIES
(
   ID NUMBER(19) PRIMARY KEY using index ...,
   POSITION NUMBER(10) not null,
   NUMOFOPPONENTS NUMBER(10) not null,
   PLAYERTYPE_ID NUMBER(19) not null,
   HAND_ID NUMBER(19) not null
   NUMOFCALLS NUMBER(10),
   NUMOFFOLDS NUMBER(10),
   NUMOFRAISES NUMBER(10)
)


I want to summarize how a player type plays different hands in a specific situation.

If I query like this:
SELECT  HAND_ID,
        SUM(NUMOFCALLS) AS NUMOFCALLS, 
        SUM(NUMOFFOLDS) AS NUMOFFOLDS, 
        SUM(NUMOFRAISES) AS NUMOFRAISES
FROM  DECISIONHISTORIES WHERE 
POSITION = ? AND 
NUMOFOPPONENTS = ? AND
PLAYERTYPE_ID = ? AND
GROUP BY HAND_ID;


Then I get what I want.

The problem arises when there aren't any rows returned. Then I would like to "loosen the criteria". For instance, if I ignore POSITION, do I get any hits? If not, try ignoring NUMOFOPPONENTS, and so on until I get a satisfying result.

That means a lot of (unnecessary?) queries.

I would like to do all of the above in the same query, with weighted results depending on how close it is. If both POSITION, NUMOFOPPONENTS and PLAYERTYPE_ID are correct, multiply the row with 3, if two are correct, multiply with 2.

I bet it's possible and not that difficult to do, I just don't have a clue how to do it.

[Updated on: Tue, 18 December 2007 15:14] by Moderator

Report message to a moderator

Re: Weighted sum in group by [message #288816 is a reply to message #288801] Tue, 18 December 2007 15:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Could you post a test case (insert statements) with different cases and the result you want to get for each one.

Regards
Michel
Re: Weighted sum in group by [message #288822 is a reply to message #288816] Tue, 18 December 2007 16:40 Go to previous messageGo to next message
emilSverige
Messages: 21
Registered: October 2006
Junior Member
Of course, here are an example:

PLEASE NOTE: THE ACTUAL VALUES ARE NOT IMPORTANT, ONLY THE RATIO BETWEEN CHECK, RAISE AND FOLD.


------------- CREATE TABLE ----------------

CREATE TABLE DECISIONHISTORIES
(
ID NUMBER(19) PRIMARY KEY not null,
POSITION NUMBER(10) not null,
NUMOFOPPONENTS NUMBER(10) not null,
PLAYERTYPE_ID NUMBER(19) not null,
HAND_ID NUMBER(19) not null,
NUMOFCALLS NUMBER(10),
NUMOFFOLDS NUMBER(10),
NUMOFRAISES NUMBER(10)
);

------------- INSERT DATA ----------------

insert into DECISIONHISTORIES values (1, 1, 1, 1, 1, 500, 0, 150);
insert into DECISIONHISTORIES values (2, 1, 3, 1, 1, 500, 0, 150);
insert into DECISIONHISTORIES values (3, 1, 4, 1, 1, 500, 0, 150);
insert into DECISIONHISTORIES values (4, 1, 1, 2, 1, 500, 0, 150);
insert into DECISIONHISTORIES values (5, 1, 1, 3, 1, 500, 0, 150);
insert into DECISIONHISTORIES values (6, 1, 1, 4, 1, 500, 0, 150);
insert into DECISIONHISTORIES values (7, 1, 1, 4, 2, 500, 0, 150);
insert into DECISIONHISTORIES values (8, 1, 2, 4, 3, 500, 0, 150);
commit;

------------- SELECT ----------------

Now I want to summarize the situation where position = 1, NUMOFOPPONENTS = 1 and PLAYERTYPE_ID = 1.
Instead of using a query like this:
SELECT HAND_ID,
SUM(NUMOFCALLS) AS NUMOFCALLS,
SUM(NUMOFFOLDS) AS NUMOFFOLDS,
SUM(NUMOFRAISES) AS NUMOFRAISES
FROM DECISIONHISTORIES WHERE
POSITION = 1 AND
NUMOFOPPONENTS = 1 AND
PLAYERTYPE_ID = 1
GROUP BY HAND_ID;

With this result set:
HAND_ID NUMOFCALLS NUMOFFOLDS NUMOFRAISES
1 500 0 150

I would want to use this query:

SELECT HAND_ID, POSITION, NUMOFOPPONENTS, PLAYERTYPE_ID,
SUM(NUMOFCALLS) AS NUMOFCALLS,
SUM(NUMOFFOLDS) AS NUMOFFOLDS,
SUM(NUMOFRAISES) AS NUMOFRAISES
FROM DECISIONHISTORIES
GROUP BY HAND_ID, POSITION, NUMOFOPPONENTS, PLAYERTYPE_ID;

HAND_ID POSITION NUMOFOPPONENTS PLAYERTYPE_ID NUMOFCALLS NUMOFFOLDS NUMOFRAISES
1 1 1 1 500 0 150
1 1 1 2 500 0 150
1 1 1 3 500 0 150
1 1 1 4 500 0 150
1 1 3 1 500 0 150
1 1 4 1 500 0 150
2 1 1 4 500 0 150
3 1 1 4 500 0 150


Use that result set to sum with different weights per row depending on how close the row is to the mentioned criteria.
Group by hand_id:

HAND_ID POSITION NUMOFOPPONENTS PLAYERTYPE_ID NUMOFCALLS NUMOFFOLDS NUMOFRAISES
1 1 1 1 500 0 150 x 3
1 1 1 2 500 0 150 x 2
1 1 1 3 500 0 150 x 2
1 1 1 4 500 0 150 x 2
1 1 3 1 500 0 150 x 2
1 1 4 1 500 0 150 x 2
2 1 1 4 500 0 150 x 2
3 1 2 4 500 0 150 x 1

This is the result I am hoping for:

HAND_ID NUMOFCALLS NUMOFFOLDS NUMOFRAISES
1 6500 0 1950
2 1000 0 300
3 500 0 150
Re: Weighted sum in group by [message #288827 is a reply to message #288822] Tue, 18 December 2007 20:58 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
For performance, I would place the logic in a PL/SQL function. Pass your values into the function, it will call all of the variations in order until it gets a hit. Then you won't need to run unnecessary queries.

A less efficient way without PL/SQL is to use a ROLLUP or CUBE on your GROUP BY. In this case, you would have to effectively perform a FULL SCAN of the table for each query to return every row plus every sub-total. You would then embed that as a sub-query.
Try running this:
SELECT *
FROM   decisionhistories
GROUP BY ROLLUP(POSITION, NUMOFOPPONENTS, PLAYERTYPE_ID)


Now can you filter it down to get the results you need?

Ross Leishman

[Updated on: Tue, 18 December 2007 20:59]

Report message to a moderator

Re: Weighted sum in group by [message #288953 is a reply to message #288822] Wed, 19 December 2007 02:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from DECISIONHISTORIES order by 1;
        ID   POSITION NUMOFOPPONENTS PLAYERTYPE_ID    HAND_ID NUMOFCALLS NUMOFFOLDS NUMOFRAISES
---------- ---------- -------------- ------------- ---------- ---------- ---------- -----------
         1          1              1             1          1        500          0         150
         2          1              3             1          1        500          0         150
         3          1              4             1          1        500          0         150
         4          1              1             2          1        500          0         150
         5          1              1             3          1        500          0         150
         6          1              1             4          1        500          0         150
         7          1              1             4          2        500          0         150
         8          1              2             4          3        500          0         150

8 rows selected.

SQL> SELECT HAND_ID,
  2         SUM(NUMOFCALLS * 
  3             (decode(POSITION,1,1,0)+decode(NUMOFOPPONENTS,1,1,0)+decode(PLAYERTYPE_ID,1,1,0))
  4            ) AS NUMOFCALLS, 
  5         SUM(NUMOFFOLDS * 
  6             (decode(POSITION,1,1,0)+decode(NUMOFOPPONENTS,1,1,0)+decode(PLAYERTYPE_ID,1,1,0))
  7            ) AS NUMOFFOLDS, 
  8         SUM(NUMOFRAISES * 
  9             (decode(POSITION,1,1,0)+decode(NUMOFOPPONENTS,1,1,0)+decode(PLAYERTYPE_ID,1,1,0))
 10            ) AS NUMOFRAISES
 11  FROM DECISIONHISTORIES
 12  GROUP BY HAND_ID
 13  order by HAND_ID;
   HAND_ID NUMOFCALLS NUMOFFOLDS NUMOFRAISES
---------- ---------- ---------- -----------
         1       6500          0        1950
         2       1000          0         300
         3        500          0         150

3 rows selected.

Regards
Michel
Re: Weighted sum in group by [message #289126 is a reply to message #288953] Wed, 19 December 2007 17:49 Go to previous message
emilSverige
Messages: 21
Registered: October 2006
Junior Member
Thank you both, it seems like what I am looking for. I will try it out in the morning.
Previous Topic: Where is a problem? :)
Next Topic: Unable to access Tkprof output...
Goto Forum:
  


Current Time: Tue Dec 06 12:01:37 CST 2016

Total time taken to generate the page: 0.11979 seconds