Home » SQL & PL/SQL » SQL & PL/SQL » row-to-row navigation query
row-to-row navigation query [message #665970] |
Thu, 05 October 2017 02:34 |
martjosh
Messages: 13 Registered: December 2009 Location: Riyadh
|
Junior Member |
|
|
Hi Experts,
Need your help.
create table test_case (id varchar2(10),num number,c1 number,c2 number);
Insert into TEST_CASE (ID,NUM,C1,C2) values ('12345',1,7140,21685);
Insert into TEST_CASE (ID,NUM,C1,C2) values ('12345',2,-14545,21685);
Insert into TEST_CASE (ID,NUM,C1,C2) values ('12345',3,-36230,21685);
Insert into TEST_CASE (ID,NUM,C1,C2) values ('12345',4,-57915,21685);
Insert into TEST_CASE (ID,NUM,C1,C2) values ('12345',5,-79600,21685);
Insert into TEST_CASE (ID,NUM,C1,C2) values ('12345',6,-101285,21685);
Insert into TEST_CASE (ID,NUM,C1,C2) values ('12345',7,-122970,21685);
Insert into TEST_CASE (ID,NUM,C1,C2) values ('12345',8,-144655,21685);
Insert into TEST_CASE (ID,NUM,C1,C2) values ('12345',9,-166340,21685);
Insert into TEST_CASE (ID,NUM,C1,C2) values ('12345',10,-188025,21685);
Insert into TEST_CASE (ID,NUM,C1,C2) values ('12345',11,-209710,21685);
I need the out put like this --
ID NUM C1 C2
------------------------------
12345 1 7140 21685
12345 2 -14545 21685
12345 3 -36230 21685
12345 4 -57915 21685
12345 5 -79600 21685
12345 6 -101285 21685
12345 7 -122970 21685
12345 8 -144655 21685
12345 9 -166340 21685
12345 10 -188025 21685
12345 11 -209710 21685
logic
------
row 2(C1)= row1(C1)- row1(C2) --> 7140 - 21685
row 3(C1)= row2(C1)- row2(C2) --> -14545 - 21685
row 4(C1)= row3(C1)- row3(C2) --> -36230 - 21685
..
..
and so on
--moderator update: I've added [code] tags, please do so yourself in future.
[Updated on: Thu, 05 October 2017 02:56] by Moderator Report message to a moderator
|
|
|
|
Re: row-to-row navigation query [message #665972 is a reply to message #665970] |
Thu, 05 October 2017 02:59 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I've split your post off from the topic you hijacked and given it what I hope is a meaningful title.
with regard to your query, I don't understand what you want at all. Get the output you give, all that is needed is this:orclx> select * from test_case order by 2;
ID NUM C1 C2
---------- ---------- ---------- ----------
12345 1 7140 21685
12345 2 -14545 21685
12345 3 -36230 21685
12345 4 -57915 21685
12345 5 -79600 21685
12345 6 -101285 21685
12345 7 -122970 21685
12345 8 -144655 21685
12345 9 -166340 21685
12345 10 -188025 21685
12345 11 -209710 21685
11 rows selected.
orclx>
|
|
|
Re: row-to-row navigation query [message #665974 is a reply to message #665972] |
Thu, 05 October 2017 04:27 |
martjosh
Messages: 13 Registered: December 2009 Location: Riyadh
|
Junior Member |
|
|
Sorry my mistakes. Initial values should be.
ID NUM C1 C2
---------- ---------- ---------- ----------
12345 1 7140 21685
12345 2 7140 21685
12345 3 7140 21685
12345 4 7140 21685
12345 5 7140 21685
12345 6 7140 21685
12345 7 7140 21685
12345 8 7140 21685
12345 9 7140 21685
12345 10 7140 21685
12345 11 7140 21685
|
|
|
Re: row-to-row navigation query [message #665975 is a reply to message #665974] |
Thu, 05 October 2017 04:42 |
|
quirks
Messages: 82 Registered: October 2014
|
Member |
|
|
Probably by using the running total?WITH
TEST_CASE(ID ,NUM ,C1 ,C2)
AS
(SELECT '12345', 1, 7140, 21685 FROM DUAL
UNION ALL
SELECT '12345' ,2 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,3 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,4 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,5 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,6 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,7 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,8 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,9 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,10 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,11 ,7140 ,21685 FROM DUAL),
PREPARE4CALC
AS
(SELECT ID
,NUM
,C1
,C2
,SUM(C2) OVER(PARTITION BY ID ORDER BY NUM) RUNNING_TOTAL
FROM TEST_CASE)
SELECT ID
,NUM
,C1
,C2
,C1 - COALESCE(LAG(RUNNING_TOTAL) OVER(PARTITION BY ID ORDER BY NUM), 0) CALC_VALUE
FROM PREPARE4CALC
Unfortunately you can't encapsulate windows functions, so you have to split it like that.
If you want to go fancy, try a recursive statement WITH
TEST_CASE(ID
,NUM
,C1
,C2)
AS
(SELECT '12345', 1, 7140, 21685 FROM DUAL
UNION ALL
SELECT '12345' ,2 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,3 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,4 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,5 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,6 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,7 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,8 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,9 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,10 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,11 ,7140 ,21685 FROM DUAL),
PREPARE4CALC
AS
(SELECT ID
,NUM
,C1
,C2
,COALESCE(LAG(NUM) OVER(PARTITION BY ID ORDER BY NUM), 0) PARENT_NUM
FROM TEST_CASE),
CALC(ID
,NUM
,C1
,C2
,FULL_PATH
,PARENT_NUM
,CALC_VALUE)
AS
(SELECT ID
,NUM
,C1
,C2
,TO_CHAR(NUM) AS FULL_PATH
,PARENT_NUM
,C1 AS CALC_VALUE
FROM PREPARE4CALC
WHERE NUM = 1
UNION ALL
SELECT PREPARE4CALC.ID
,PREPARE4CALC.NUM
,PREPARE4CALC.C1
,PREPARE4CALC.C2
,CALC.FULL_PATH || ' --> ' || PREPARE4CALC.NUM AS FULL_PATH
,PREPARE4CALC.PARENT_NUM
,CALC.CALC_VALUE - PREPARE4CALC.C2
FROM CALC, PREPARE4CALC
WHERE CALC.NUM = PREPARE4CALC.PARENT_NUM)
SELECT *
FROM CALC
[Updated on: Thu, 05 October 2017 05:25] Report message to a moderator
|
|
|
|
Re: row-to-row navigation query [message #665998 is a reply to message #665975] |
Fri, 06 October 2017 07:30 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
1. RTFM LAG. Your
COALESCE(LAG(RUNNING_TOTAL) OVER(PARTITION BY ID ORDER BY NUM), 0)
is a complicated way of:
LAG(RUNNING_TOTAL,1,0) OVER(PARTITION BY ID ORDER BY NUM)
2. No need to PREPARE4CALC:
WITH TEST_CASE(ID,NUM,C1,C2)
AS (
SELECT '12345', 1, 7140, 21685 FROM DUAL UNION ALL
SELECT '12345' ,2 ,7140 ,21685 FROM DUAL UNION ALL
SELECT '12345' ,3 ,7140 ,21685 FROM DUAL UNION ALL
SELECT '12345' ,4 ,7140 ,21685 FROM DUAL UNION ALL
SELECT '12345' ,5 ,7140 ,21685 FROM DUAL UNION ALL
SELECT '12345' ,6 ,7140 ,21685 FROM DUAL UNION ALL
SELECT '12345' ,7 ,7140 ,21685 FROM DUAL UNION ALL
SELECT '12345' ,8 ,7140 ,21685 FROM DUAL UNION ALL
SELECT '12345' ,9 ,7140 ,21685 FROM DUAL UNION ALL
SELECT '12345' ,10 ,7140 ,21685 FROM DUAL UNION ALL
SELECT '12345' ,11 ,7140 ,21685 FROM DUAL
)
SELECT ID,
NUM,
C1,
C2,
C1 + C2 - SUM(C2) OVER(PARTITION BY ID ORDER BY NUM) CALC_VALUE
FROM TEST_CASE
/
ID NUM C1 C2 CALC_VALUE
----- ----------- ---------- ---------- ----------
12345 1 7140 21685 7140
12345 2 7140 21685 -14545
12345 3 7140 21685 -36230
12345 4 7140 21685 -57915
12345 5 7140 21685 -79600
12345 6 7140 21685 -101285
12345 7 7140 21685 -122970
12345 8 7140 21685 -144655
12345 9 7140 21685 -166340
12345 10 7140 21685 -188025
12345 11 7140 21685 -209710
11 rows selected.
SQL>
SY.
|
|
|
Re: row-to-row navigation query [message #666021 is a reply to message #665970] |
Mon, 09 October 2017 02:19 |
|
quirks
Messages: 82 Registered: October 2014
|
Member |
|
|
Solomon Yakobson wrote on Fri, 06 October 2017 07:301. RTFM LAG. Your
... snip ...
is a complicated way of:
... snip ...
Thanks for the correction. I'm always keen to learn.
Solomon Yakobson wrote on Fri, 06 October 2017 07:30
2. No need to PREPARE4CALC:
... snip ...
SY.
His requested logic was:martjosh wrote on Thu, 05 October 2017 02:34logic
------
row 2(C1)= row1(C1)- row1(C2) --> 7140 - 21685
row 3(C1)= row2(C1)- row2(C2) --> -14545 - 21685
row 4(C1)= row3(C1)- row3(C2) --> -36230 - 21685
..
..
and so on
So in reality I even "forgot" the LAG() for the first C1.
But if I'd done that the result would have been:
WITH
TEST_CASE(ID ,NUM ,C1 ,C2)
AS
(SELECT '12345', 1, 7140, 21685 FROM DUAL
UNION ALL
SELECT '12345' ,2 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,3 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,4 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,5 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,6 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,7 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,8 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,9 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,10 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,11 ,7140 ,21685 FROM DUAL),
PREPARE4CALC
AS
(SELECT ID
,NUM
,C1
,C2
,SUM(C2) OVER(PARTITION BY ID ORDER BY NUM) RUNNING_TOTAL
FROM TEST_CASE)
SELECT ID
,NUM
,C1
,C2
,LAG(C1,1,0) OVER(PARTITION BY ID ORDER BY NUM) - LAG(RUNNING_TOTAL,1,0) OVER(PARTITION BY ID ORDER BY NUM) CALC_VALUE
FROM PREPARE4CALC;
ID NUM C1 C2 CALC_VALUE
----- ---------- ---------- ---------- ----------
12345 1 7140 21685 0 <-- this must be 7140
12345 2 7140 21685 -14545
12345 3 7140 21685 -36230
12345 4 7140 21685 -57915
12345 5 7140 21685 -79600
12345 6 7140 21685 -101285
12345 7 7140 21685 -122970
12345 8 7140 21685 -144655
12345 9 7140 21685 -166340
12345 10 7140 21685 -188025
12345 11 7140 21685 -209710
11 rows selected.
which did not match to his desired result. So I skipped the first LAG (on C1) in anticipatory obedience.
But while playing around with his example I figured that it felt strange to have in C1 always the same redundant data. That's why I suspected, that he just wants to subtract the previous C2 from a given / inital value (which he repeats in C1 over and over again). That's why I came up with the second solution which might a little bit over shot .
In the end he seems to be happy with what he's got.
Anyway. Thanks again for your correction. I'll incorporate that knowledge in future solutions
|
|
|
Re: row-to-row navigation query [message #666038 is a reply to message #666021] |
Tue, 10 October 2017 00:09 |
martjosh
Messages: 13 Registered: December 2009 Location: Riyadh
|
Junior Member |
|
|
This is great. how about if I want the below output for calc_value.
ID NUM C1 C2 CALC_VALUE
----- ----- ---- ----- ---------
12345 1 7140 21685 7140
12345 2 7140 21685 28825
12345 3 7140 21685 50510
12345 4 7140 21685 72195
12345 5 7140 21685 93880
12345 6 7140 21685 115565
12345 7 7140 21685 137250
12345 8 7140 21685 158935
12345 9 7140 21685 180620
12345 10 7140 21685 202305
12345 11 7140 21685 223990
Logic for CALC_VALUE
row 1(CALC_VALUE)= C1
row 2(CALC_VALUE)= row1(CALC_VALUE)+ row1(C2) --> 7140 + 21685
row 3(CALC_VALUE)= row2(CALC_VALUE)+ row2(C2) --> 28825 + 21685
row 4(CALC_VALUE)= row3(CALC_VALUE)+ row3(C2) --> 50510 + 21685
and so on .....
|
|
|
|
Re: row-to-row navigation query [message #666044 is a reply to message #666021] |
Tue, 10 October 2017 06:46 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
quirks wrote on Mon, 09 October 2017 03:19
So in reality I even "forgot" the LAG() for the first C1.
But if I'd done that the result would have been:
Why are you calculating LAG of C1??? It wasn't in your solution and my comment was on LAG for RUNNING_TOTAL. If you want to use that unnecessary PREPARE4CALC, then:
WITH
TEST_CASE(ID ,NUM ,C1 ,C2)
AS
(SELECT '12345', 1, 7140, 21685 FROM DUAL
UNION ALL
SELECT '12345' ,2 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,3 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,4 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,5 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,6 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,7 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,8 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,9 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,10 ,7140 ,21685 FROM DUAL
UNION ALL
SELECT '12345' ,11 ,7140 ,21685 FROM DUAL),
PREPARE4CALC
AS
(SELECT ID
,NUM
,C1
,C2
,SUM(C2) OVER(PARTITION BY ID ORDER BY NUM) RUNNING_TOTAL
FROM TEST_CASE)
SELECT ID
,NUM
,C1
,C2
,C1 - LAG(RUNNING_TOTAL,1,0) OVER(PARTITION BY ID ORDER BY NUM) CALC_VALUE
FROM PREPARE4CALC
/
ID NUM C1 C2 CALC_VALUE
----- ---------- ---------- ---------- ----------
12345 1 7140 21685 7140
12345 2 7140 21685 -14545
12345 3 7140 21685 -36230
12345 4 7140 21685 -57915
12345 5 7140 21685 -79600
12345 6 7140 21685 -101285
12345 7 7140 21685 -122970
12345 8 7140 21685 -144655
12345 9 7140 21685 -166340
12345 10 7140 21685 -188025
12345 11 7140 21685 -209710
11 rows selected.
SQL>
SY.
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 06:35:35 CDT 2024
|