Calculating Percent Change Between Two Rows

Calculating Percent Change between two rows

Something like this would do it I think

SELECT x.Date, x.Close_Price, (((x.Close_Price / y.Close_Price) - 1) * 100) AS '% Change'
FROM 
(
    SELECT a.Date AS aDate, MAX(b.Date) AS aPrevDate
    FROM SomeTable a
    INNER JOIN SomeTable b
    WHERE a.Date > b.Date
    GROUP BY a.Date
) Sub1
INNER JOIN SomeTable x ON Sub1.aDate = x.Date
INNER JOIN SomeTable y ON Sub1.aPrevDate = y.Date
ORDER BY x.Close_Price DESC

This will cope when the days are not one after the other (eg, missing records for non processing days).

The sub query gets each date, and the max date that is less than it. This is then joined against the table to get the full records for that date and the previous one, and then the calculation can be done.

EDIT - and update based on this SELECT:-

UPDATE treasury x
INNER JOIN 
(
    SELECT a.Date AS aDate, MAX(b.Date) AS aPrevDate
    FROM treasury a
    INNER JOIN treasury b
    WHERE a.Date > b.Date
    GROUP BY a.Date
) Sub1 ON Sub1.aDate = x.Date
INNER JOIN treasury y ON Sub1.aPrevDate = y.Date
SET x.PercentChange = (((x.Close_Price / y.Close_Price) - 1) * 100)

R how to calculate 'Percentage of Change' between two rows?

We can use shift from data.table. Convert the 'data.frame' to 'data.table' (setDt(df)), grouped by 'item', we get the "Row" from .I and create the "Percentage_Change" by dividing the "cumVol" by the lag of "cumVol" (got by shift) and multiplying by 100. If needed the grouping column can be removed by assigning (:=) it to NULL.

library(data.table)
setDT(df)[, list(Row = .I, Percentage_Change=round(cumVol*
   (100/shift(cumVol)),2)), .(item)][, item := list(NULL)][]
#   Row Percentage_Change
#1:   1               NA
#2:   2           500.00
#3:   3           300.00
#4:   4               NA
#5:   5            32.26
#6:   6           120.00
#7:   7               NA
#8:   8           150.00

NOTE: This gives NA for elements where there are no comparison, instead of the Skip.

Calculate percentage difference between two rows

Use a Common Table Expression ("CTE") for the grouped result and join it to itself to calculate the difference to the previous season:

with summary as (
  select
    season,
    guildname,
    count(*) as mp_count, -- simplified equivalent expression
    count(*)/6 as grank -- simplified equivalent expression
  from mp_rankings
  group by season, guildname
)
select
  a.season,
  a.guildname,
  a.mp_count,
  a.grank,
  a.mp_count - b.mp_count as prev_season_percent_diff
from summary a
left join summary b on b.guildname = a.guildname
  and b.season = a.season - 1
where a.season = (select max(season) from summary)
order by a.grank desc

If you actually want a % in the result, concatenate a % to the difference calculation.

how to calculate the percentage between two rows based on a group in R

Here is one solution if you need percentages for all the parameters

library(dplyr)
library(tidyr)

df_new<-df %>%
select(-(total))%>%
  replace(is.na(.), 0)%>%
  pivot_longer(cols = c(3:8),
               names_to = 'classe',
               values_to = 'area') %>%
  pivot_wider(names_from=year, values_from=area) %>%
  mutate(percent=(`2018`-`2005`)/`2005`) %>%
  select(-`2018`,-`2005`) %>%
  pivot_wider( names_from="classe", values_from="percent")
  

df_new
#> # A tibble: 6 × 7
#>   place      Veg  Agri   Past     Urb SoloExp   Hidro
#>   <chr>    <dbl> <dbl>  <dbl>   <dbl>   <dbl>   <dbl>
#> 1 F01   0.000867  11.2 -0.145   0.376  0.112   -0.135
#> 2 F02   0.0322    11.3 -0.249   0.376  0.0218  -0.135
#> 3 F03   0.0896   Inf   -0.185   0.381  0.0929 NaN    
#> 4 F04   0.0630   Inf   -0.194   0.381  0.0711 NaN    
#> 5 F05   0.0199    10.8 -0.275   0.237 -0.227   -0.146
#> 6 F06   0.0494   161.  -0.321 NaN     -0.816   -0.206

^{Created on 2022-01-09 by the reprex package (v2.0.1)}

Calculating Percent Change Between Two Rows