find the difference between a column ion two rows grouped by one and sort by another column-CodePudding

Need to find the sequential difference and average between within a columns of two rows group by brand column and order by bill_id column and find the difference of worth column between rows in a single query.

I have a data

brand bill_id worth
Moto    1    2550
Samsung 1    3430
Samsung 2    3450
Moto    2    2500
Moto    3    2530

Expected Output

brand bill_id worth net_diff avg_diff
Moto    1     2550   0        00
Moto    2     2560   10       5
Moto    3     2540   -20     -5
Samsung 1     3430   0        0
Samsung 2     3450   20       10

CodePudding user response：

With the following data :

CREATE TABLE T (brand VARCHAR(16),  bill_id INT, worth DECIMAL(16,2))
INSERT INTO T VALUES 
('Moto',    1,    2550),
('Samsung', 1,    3430),
('Samsung', 2,    3450),
('Moto',    2,    2500),
('Moto',    3,    2530);

One possible solution could be :

WITH 
T0 AS
(
SELECT *, worth - COALESCE(LAG(worth) OVER(PARTITION BY brand ORDER BY bill_id), worth) AS net_diff
FROM T
)
SELECT *, AVG(net_diff) OVER(PARTITION BY brand ORDER BY bill_id) 
FROM   T0;

But I do not understand the computation formulae of your example for AVG...

CodePudding user response：

It appears that by average you are looking for 1/2 the difference between 2 consecutive bill_id for a brand. You can get this by applying the lag() function twice (with answer from @SQLpro as a base) arriving at: (see demo)

with  bill_net(brand, bill_id, worth,net_diff)  as
      ( select  billing.*, worth - coalesce(lag(worth) over(partition by brand order by bill_id), worth) 
          from billing
      )
select brand, bill_id, worth, net_diff, coalesce(round(((net_diff - lag(net_diff) over(partition by brand order by bill_id))/2.0),2),0.00)  
from   bill_net;

NOTE: Due to inconsistency between Input and Results it does not exactly produce you expected results.