Question 113930 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – find the difference between a column ion two rows grouped by one and sort by another column

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
Tags:

2

Answers


  1. 0

    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…

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search