Home » SQL & PL/SQL » SQL & PL/SQL » Weighted sum of intervals
Weighted sum of intervals Fri, 15 June 2007 03:54
 Buchas Messages: 85Registered: 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
 Michel Cadot Messages: 65148Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 Cthulhu Messages: 381Registered: 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
 Buchas Messages: 85Registered: 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
 srinivnp Messages: 136Registered: 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: which table are updated Next Topic: Table Design....
Goto Forum:

Current Time: Mon Aug 21 13:01:00 CDT 2017

Total time taken to generate the page: 0.01431 seconds