CODEWITHSUNDEEP
sqloracle11g
Crazymango
Crazymango

Reputation: 111

How to compare multiple rows in same table?

I have one table likes:

ID    Name     Amount     Date         
1      A        50      03/04/2018 
2      A        40      03/04/2018 
3      A        30      03/04/2018 
4      A        20      03/04/2018
5      A        10      08/04/2018
6      B        20      08/04/2018
7      B        15      08/04/2018
8      B        10      08/04/2018
9      A        100     08/07/2018
10     A        90      08/07/2018
11     A        80      08/07/2018
12     A        70      08/07/2018

I want to find the changes of the Amount at A in the table, For example, A's amount just goes down at 08/04/2018, but get new amount at 08/07/2018. I want to find these two rows for analysis( min value at old amount and max value at new amount):

5      A        10      08/04/2018
9      A        100     08/07/2018

I want both rows before and after an exclusively positive change.

Is that possible?

My code is:

SELECT cur.name, cur.amount
FROM tableA pre INNER JOIN tableA cur ON cur.id = pre.id + 1
WHERE cur.name = pre.name
ORDER BY cur.ID, cur.amount.

Upvotes: 2

Views: 1636

Answers (2)

wolφi
wolφi

Reputation: 8361

That is very difficult to do in a relational language, but very frequently needed. Therefore welcome to the wonderful world of Oracle's analytic functions.

CREATE TABLE t (id NUMBER, name VARCHAR2(30), amount NUMBER, d DATE);
INSERT INTO t VALUES ( 1,'A', 50, DATE '2018-04-03');
INSERT INTO t VALUES ( 2,'A', 40, DATE '2018-04-03');
INSERT INTO t VALUES ( 3,'A', 30, DATE '2018-04-03');
INSERT INTO t VALUES ( 4,'A', 20, DATE '2018-04-03');
INSERT INTO t VALUES ( 5,'A', 10, DATE '2018-04-08');
INSERT INTO t VALUES ( 6,'B', 20, DATE '2018-04-08');
INSERT INTO t VALUES ( 7,'B', 15, DATE '2018-04-08');
INSERT INTO t VALUES ( 8,'B', 10, DATE '2018-04-08');
INSERT INTO t VALUES ( 9,'A',100, DATE '2018-07-08');
INSERT INTO t VALUES (10,'A', 90, DATE '2018-07-08');
INSERT INTO t VALUES (11,'A', 80, DATE '2018-07-08');
INSERT INTO t VALUES (12,'A', 70, DATE '2018-07-08');

The function LEAD () OVER () refers to the next row in the query. In your case, you are only interested in changes for the same value of column name, so you specify PARTITION BY NAME. For "next" to be meaningful, you'll need to define an order, so you say ORDER BY id:

SELECT id, name, amount, d,
       LEAD(amount) OVER (PARTITION BY NAME ORDER BY id) AS next_amount,
       LEAD(D)      OVER (PARTITION BY NAME ORDER BY id) AS next_date
  FROM T;

This would return your normal values, plus the next amount of the same name:

id  name amount d           next_amount  next_date
1   A    50     2018-04-03  40           2018-04-03
2   A    40     2018-04-03  30           2018-04-03
3   A    30     2018-04-03  20           2018-04-03
....

But you want only the positive changes, so you'll add a WHERE clause. Unfortunately, this works only in a subquery, so it will look like:

SELECT id, name, amount, d, next_amount, next_date 
  FROM (
        SELECT id, name, amount, D,
               LEAD(amount) OVER (PARTITION BY name ORDER BY id) AS next_amount,
               LEAD(D)      OVER (PARTITION BY name ORDER BY id) AS next_date
          FROM t
       ) 
 WHERE amount < next_amount;

The result is 

id  name amount  next_amount  d          next_date
5   A    10      100          2018-04-08 2018-07-08

Upvotes: 0

Socrates
Socrates

Reputation: 9604

You need to work with two views on the same table, which is what you did with pre and cur. What you didn't do though is limit your output to A fields, hence put that in the WHERE clause. You then have to calculate the amount difference between the pre view and the cur view by creating a new field.

Check out this query showing you every field you use here. You'll have to adapt it to your needs.

SELECT pre.ID AS pre_id, pre.name AS pre_name, pre.amount AS pre_amount, cur.ID AS cur_id, cur.name AS cur_name, cur.amount AS cur_amount, cur.amount - pre.amount AS pre_cur_amount_diff 
FROM tableA pre INNER JOIN tableA cur ON pre.ID + 1 = cur.ID 
WHERE pre.name = cur.name;

EDIT 1:

You may be looking at this:

SELECT cur.ID, cur.name, cur.amount - pre.amount as Amount 
FROM tableA pre INNER JOIN tableA cur ON pre.ID + 1 = cur.ID 
WHERE pre.name = cur.name
ORDER BY cur.ID, Amount;

Upvotes: 2

Related Questions