Subtract values from two rows

[Rem-8]

Hello. I'm in the middle of a problem.
I have many many rows in a table. I need to add another column to it
which has to be subtract of value A from row N and value B from row
N-1. For example

. NEW COLUMN
2008-10-10 12:05 223
2008-10-10 12:10 226 3
2008-10-10 12:15 238 12

Etc... How this can be acomplished? Thanks in advance.

[Rem-8]

[MW Mann]

Is this a once off thing:

The first thing that comes into mind is to create a new table (same table structure) with an additional sequence column.

In an anonymous block you can loop through all the records in a cursor and insert into the new table ordered correctly (populating the new seq column with a counter variable or a database sequence).

With that you will be able to then write another anon block to calculate the new value for your new field and update the record accordingly.

If that is not possible for your situation, I would look into the analytic sql functions, there may be something you could use similar to RANK, to 'number' your rows.

Hope that helps/or givers you some ideas.

--
Michael Mann

[ddf]

Use the LAG function:

SQL> create table data(
2 cr_dt date,
3 cum_sales number
4 );

Table created.

SQL>
SQL> insert all
2 into data
3 values(to_date('2008-10-10 12:05', 'RRRR-MM-DD HH24:MI'),223)
4 into data
5 values(to_date('2008-10-10 12:10', 'RRRR-MM-DD HH24:MI'),226)
6 into data
7 values(to_date('2008-10-10 12:15', 'RRRR-MM-DD HH24:MI'),238)
8 into data
9 values(to_date('2008-10-10 12:25', 'RRRR-MM-DD HH24:MI'),258)
10 into data
11 values(to_date('2008-10-10 12:45', 'RRRR-MM-DD HH24:MI'),287)
12 into data
13 values(to_date('2008-10-10 12:55', 'RRRR-MM-DD HH24:MI'),341)
14 select * From dual;

6 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select cr_dt, cum_sales, cum_sales-lag(cum_sales) over (order by
cr_dt) incr_sales
2 from data;

CR_DT CUM_SALES INCR_SALES
--------- ---------- ----------
10-OCT-08 223
10-OCT-08 226 3
10-OCT-08 238 12
10-OCT-08 258 20
10-OCT-08 287 29
10-OCT-08 341 54

6 rows selected.

SQL>


David Fitzjarrell

[Rem-8]

Hello ddf.

Lag function was perfect. I was reading all day about it and it's a
perfect solution for me. When I have one row of data I can sort it by
CR_DT, as you stated, but let's assume I have few columns like:

CR_DT COUNTRY STATE CITY CUM_SALES INCR_SALES

Each sales person is in one table with CUM_SALES values in it. Each
person is from countr, state and city. When records will be ordered by
CR_DT, only the newest entry, regardless of the rest, will be caught
into lag function. When I order lag by CR_DT, COUNTRY, STATE and CITY
(in that order) it would also give me wrong last row as it will take
the lowest/highest (depending on sort order) value from last column
ordered (here is CITY). Can this lag function work for columns which
are only the same? So INCR_SALES would count subtract from two New
Yorks ordered by date :) This would accomplish my whole task and I
will buy a beer for proper solution :D

[ddf]

You'll need to provide some sample data before anyone can positively
answer that question.


David Fitzjarrell

[ddf]

> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

I was looking through my stash of examples and remember one I recently
posted here; possibly it is what you're wanting:

SQL> select usr, value,
2 case when usr = prev_usr then value - lag(value) over
(order by usr, value) end diff
3 from
4 (select usr, value, lag(usr) over (order by usr, value) prev_usr
5 from data);

USR VALUE DIFF
---------- ---------- ----------
1 7
1 9 2
1 11 2
1 15 4
2 5
2 8 3
2 15 7
3 5
3 7 2
3 8 1
3 9 1

USR VALUE DIFF
---------- ---------- ----------
3 11 2
3 15 4
4 5
4 7 2
4 8 1
4 9 1
4 11 2
4 14 3
4 15 1
4 19 4
4 22 3

USR VALUE DIFF
---------- ---------- ----------
4 25 3
4 27 2
4 28 1
4 35 7

26 rows selected.

SQL>


David Fitzjarrell

[Rem-8]

TS TARGET SUB_TARGET LOAD
2008-01-01 12:14 192.168.1.1 1a 144
2008-01-02 12:14 192.168.1.1 1b 145
2008-01-03 12:14 192.168.1.1 2a 142
2008-01-04 12:14 192.168.1.1 2b 145
2008-01-05 12:14 192.168.1.2 6s 53
2008-01-06 12:14 192.168.1.2 6t 56
2008-01-07 12:14 192.168.1.2 6u 53
2008-01-08 12:14 192.168.1.2 6v 58
2008-01-09 12:14 192.168.1.3 7a 22
2008-01-10 12:14 192.168.1.4 8a 34
2008-01-11 12:14 192.168.1.5 9a 11
2008-01-12 12:14 192.168.1.6 9g 8
2008-01-13 12:14 192.168.1.7 9h 4
2008-01-14 12:14 192.168.1.8 9i 2
2008-01-01 12:24 192.168.1.1 1a 147
2008-01-02 12:24 192.168.1.1 1b 150
2008-01-03 12:24 192.168.1.1 2a 142
2008-01-04 12:24 192.168.1.1 2b 147
2008-01-05 12:24 192.168.1.2 6s 66
2008-01-06 12:24 192.168.1.2 6t 62
2008-01-07 12:24 192.168.1.2 6u 63
2008-01-08 12:24 192.168.1.2 6v 71
2008-01-09 12:24 192.168.1.3 7a 29
2008-01-10 12:24 192.168.1.4 8a 35
2008-01-11 12:24 192.168.1.5 9a 15
2008-01-12 12:24 192.168.1.6 9g 9
2008-01-13 12:24 192.168.1.7 9h 5
2008-01-14 12:24 192.168.1.8 9i 7



This is the example. I need to sort values and get subtract from
sorting first Target, then Subtarget and then Timestamp and return a
new column names INC_LOAD

[Rem-8]

TS TARGET SUB_TARGET LOAD
2008-01-01 12:14 192.168.1.1 1a 144

2008-01-01 12:14 192.168.1.1 1b 145
2008-01-01 12:14 192.168.1.1 2a 142
2008-01-01 12:14 192.168.1.1 2b 145
2008-01-01 12:14 192.168.1.2 6s 53
2008-01-01 12:14 192.168.1.2 6t 56
2008-01-01 12:14 192.168.1.2 6u 53
2008-01-01 12:14 192.168.1.2 6v 58
2008-01-01 12:14 192.168.1.3 7a 22
2008-01-01 12:14 192.168.1.4 8a 34
2008-01-01 12:14 192.168.1.5 9a 11
2008-01-01 12:14 192.168.1.6 9g 8
2008-01-01 12:14 192.168.1.7 9h 4
2008-01-01 12:14 192.168.1.8 9i 2

2008-01-01 12:24 192.168.1.1 1a 147

2008-01-01 12:24 192.168.1.1 1b 150
2008-01-01 12:24 192.168.1.1 2a 142
2008-01-01 12:24 192.168.1.1 2b 147
2008-01-01 12:24 192.168.1.2 6s 66
2008-01-01 12:24 192.168.1.2 6t 62
2008-01-01 12:24 192.168.1.2 6u 63
2008-01-01 12:24 192.168.1.2 6v 71
2008-01-01 12:24 192.168.1.3 7a 29
2008-01-01 12:24 192.168.1.4 8a 35
2008-01-01 12:24 192.168.1.5 9a 15
2008-01-01 12:24 192.168.1.6 9g 9
2008-01-01 12:24 192.168.1.7 9h 5
2008-01-01 12:24 192.168.1.8 9i 7

This is correct one. Previous has wrong days column. Generated values
in Excel...

[ddf]

Is this what you want:

SQL> select target, sub_target,load,
2 case when target = prev_target then load - lag(load) over
(order by target, sub_target, ts) end incr_load
3 from
4 (select target, sub_target, ts, load, lag(target) over (order by
target, sub_target, ts) prev_target
5 from sales_data);

TARGET SUB_ LOAD INCR_LOAD
---------------- ---- ---------- ----------
192.168.1.1 1a 144
192.168.1.1 1a 147 3
192.168.1.1 1b 145 -2
192.168.1.1 1b 150 5
192.168.1.1 2a 142 -8
192.168.1.1 2a 142 0
192.168.1.1 2b 145 3
192.168.1.1 2b 147 2
192.168.1.2 6s 53
192.168.1.2 6s 66 13
192.168.1.2 6t 56 -10

TARGET SUB_ LOAD INCR_LOAD
---------------- ---- ---------- ----------
192.168.1.2 6t 62 6
192.168.1.2 6u 53 -9
192.168.1.2 6u 63 10
192.168.1.2 6v 58 -5
192.168.1.2 6v 71 13
192.168.1.3 7a 22
192.168.1.3 7a 29 7
192.168.1.4 8a 34
192.168.1.4 8a 35 1
192.168.1.5 9a 11
192.168.1.5 9a 15 4

TARGET SUB_ LOAD INCR_LOAD
---------------- ---- ---------- ----------
192.168.1.6 9g 8
192.168.1.6 9g 9 1
192.168.1.7 9h 4
192.168.1.7 9h 5 1
192.168.1.8 9i 2
192.168.1.8 9i 7 5

28 rows selected.

SQL>


David Fitzjarrell

[Rem-8]

Well not yet. INCR_LOAD has to be subtracted by the TARGET and
SUB_TARGET, so if TARGET A is 192.168.1.1 and SUB_ is 1a, INCR_LOAD
has to be a result only for that pair of that targets.
So in case of such situation

2008-01-01 12:14 192.168.1.1 1a 100 0
2008-01-01 12:14 192.168.1.1 1b 140 0
2008-01-01 12:24 192.168.1.1 1a 104 4
2008-01-01 12:24 192.168.1.1 1b 143 3
2008-01-01 12:34 192.168.1.1 1a 108 4
2008-01-01 12:34 192.168.1.1 1b 145 2
2008-01-01 12:44 192.168.1.1 1a 117 9
2008-01-01 12:44 192.168.1.1 1b 149 4
2008-01-01 12:54 192.168.1.1 1a 122 5
2008-01-01 12:54 192.168.1.1 1b 157 6

Result has to be in the same row, but Subtract has to be a result of
two values from the same target and sub_. There can't be minus values.

[Rem-8]

Oh, I got this one on my own :)


SQL> select target, sub_target,load,
2 case when target = prev_target and sub = prev_sub then

load - lag(load) over
(order by target, sub_target, ts) end incr_load
3 from
4 (select target, sub_target, ts, load, lag(target) over (order by

target, sub_target, ts) prev_target, lag(sub) over (order by
target, sub_target, ts) prev_sub
5 from sales_data);

Thanks David for all. It was a great pleasure to work with you :)

[ddf]

You're welcome. But, I see some typos in your code; correcting those
produces a working query and the correct output:

SQL> select target, sub_target,load,
2 case when target = prev_target and sub_target = prev_sub

then load - lag(load) over (order by target, sub_target, ts) end
incr_load
3 from
4 (select target, sub_target, ts, load, lag(target) over (order by

target, sub_target, ts) prev_target, lag(sub_target) over (order by

target, sub_target, ts) prev_sub
5 from sales_data);

TARGET SUB_ LOAD INCR_LOAD
---------------- ---- ---------- ----------
192.168.1.1 1a 144
192.168.1.1 1a 147 3
192.168.1.1 1b 145

192.168.1.1 1b 150 5
192.168.1.1 2a 142

192.168.1.1 2a 142 0
192.168.1.1 2b 145

192.168.1.1 2b 147 2
192.168.1.2 6s 53
192.168.1.2 6s 66 13
192.168.1.2 6t 56

TARGET SUB_ LOAD INCR_LOAD
---------------- ---- ---------- ----------
192.168.1.2 6t 62 6
192.168.1.2 6u 53

192.168.1.2 6u 63 10
192.168.1.2 6v 58