Home » SQL & PL/SQL » SQL & PL/SQL » Weighted sum of intervals
Weighted sum of intervals [message #245117] Fri, 15 June 2007 03:54 Go to next message
Buchas
Messages: 83
Registered: March 2006
Member
Hello,

I have the following test script to illustrate my problem:

CREATE TABLE TEST.PAYMENT_T AS SELECT 1 ID, 250 MONEY FROM DUAL;

CREATE TABLE TEST.INTERVAL_PERCENT_T
(
INTERVAL_FROM NUMBER NOT NULL,
INTERVAL_TO NUMBER,
INTERVAL_PERCENT NUMBER NOT NULL,
PAYMENT_ID NUMBER NOT NULL
);

INSERT INTO TEST.INTERVAL_PERCENT_T (INTERVAL_FROM, INTERVAL_TO, INTERVAL_PERCENT, PAYMENT_ID) VALUES (0, 110, 0.1,1);
INSERT INTO TEST.INTERVAL_PERCENT_T (INTERVAL_FROM, INTERVAL_TO, INTERVAL_PERCENT, PAYMENT_ID) VALUES (110, 230, 0.2,1);
INSERT INTO TEST.INTERVAL_PERCENT_T (INTERVAL_FROM, INTERVAL_TO, INTERVAL_PERCENT, PAYMENT_ID) VALUES (230, 300, 0.3,1);
INSERT INTO TEST.INTERVAL_PERCENT_T (INTERVAL_FROM, INTERVAL_TO, INTERVAL_PERCENT, PAYMENT_ID) VALUES (300, NULL, 0.5,1);

COMMIT;

Now if I do:

SELECT *
FROM TEST.PAYMENT_T P, TEST.INTERVAL_PERCENT_T I
WHERE I.PAYMENT_ID = P.ID
ORDER BY I.INTERVAL_FROM;

I see:

ID MONEY INTERVAL_FROM INTERVAL_TO INTERVAL_PERCENT PAYMENT_ID
1 1 250 0 110 0,1 1
2 1 250 110 230 0,2 1
3 1 250 230 300 0,3 1
4 1 250 300 0,5 1

What I want to do is this. I would like to evaluate the weighted sum of money (250) falling into defined intervals e.g.: (110-0)*0.1+(230-110)*0.2+(250-230)*0.3 in a single SELECT query.

There can be any number of intervals. First interval always start with 0 and the last interval ends with infinity (NULL). Intervals are always connected (no gaps between them). INTERVAL_FROM is inclusive, INTERVAL_TO is non inclusive: [0, 110). MONEY might be any number. INTERVAL_PERCENT is any number between [0,1].

Help please.
Re: Weighted sum of intervals [message #245120 is a reply to message #245117] Fri, 15 June 2007 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please always post your Oracle version (4 decimals).
Please read and follow How to format your posts.
Please break your lines to 80/100 characters max.

Regards
Michel
Re: Weighted sum of intervals [message #245122 is a reply to message #245117] Fri, 15 June 2007 04:12 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I don't understand what you're trying to do. Why does your formula suddenly switch from (interval_to - interval_from) to (money - interval_from) ?

(110-0)*0.1+(230-110)*0.2+(250-230)*0.3
Re: Weighted sum of intervals [message #245135 is a reply to message #245122] Fri, 15 June 2007 04:48 Go to previous messageGo to next message
Buchas
Messages: 83
Registered: March 2006
Member
because only 20 of 250 fall into interval [230,300)
Re: Weighted sum of intervals [message #245718 is a reply to message #245117] Mon, 18 June 2007 11:50 Go to previous message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

Buchas said
" because only 20 of 250 fall into interval [230,300) "


I fail to gather how this 20 came about. Where is that info ?

How does this value causes your formula to change ? I mean explain the logical criteria .

Srini
Previous Topic: Advantage and disadvantage of Procedure
Next Topic: I want to send mail with attached document
Goto Forum:
  


Current Time: Sun Dec 04 06:51:23 CST 2016

Total time taken to generate the page: 0.10888 seconds