How to calculate percentage difference from first row to other rows-CodePudding

Say I have the data like the below:

create table Test(ID int,
A int)

insert into Test values
(1,   100),
(2,   200),
(3,   300),
(4,   400)

I want to calculate the percentage difference of the first row with other subsequent rows. For instance here % difference would be 0,100,200,300. If the percentage difference is > 20% I could pick those rows. I could do it in Python but I was wondering whether there's some SQL version only?

I am using SQLite DB.

CodePudding user response：

The lag window function will allow you to access a previous row's value. From there on, dividing a row and the previous row will give the ratio between them, and subtracting 1 (i.e., 100%) will give you the percentage change:

SELECT id, a
FROM   (SELECT id, a, LAG(a) OVER (ORDER BY id ASC) AS lg
        FROM   mytable)
WHERE  lg IS NULL OR (a * 100.0 / lg) >= 120

CodePudding user response：

Use FIRST_VALUE() window function:

SELECT ID, A
FROM (
  SELECT *, 1.0 * A / FIRST_VALUE(A) OVER (ORDER BY ID) - 1 diff
  FROM Test
)
WHERE diff > 0.2;

See the demo.