How to Calculate Difference of Values in a Successive Rows

How to calculate difference of values in a successive rows?

If you aren't on a version which supports windowing functions you could use subqueries to get yesterdays price

select `date`,speed,
          speed - ifnull((select speed from t t1 where t1.date < t.date order by t1.date desc limit 1),speed) yesterdayspeed,
          petrol,
          petrol - ifnull((select petrol from t t1 where t1.date < t.date order by t1.date desc limit 1),petrol) yesterdayspetrol,
          diesel,
          diesel - ifnull((select diesel from t t1 where t1.date < t.date order by t1.date desc limit 1),diesel) yesterdaysdiesel
from t;

+------------+-------+----------------+--------+------------------+--------+------------------+
| date       | speed | yesterdayspeed | petrol | yesterdayspetrol | diesel | yesterdaysdiesel |
+------------+-------+----------------+--------+------------------+--------+------------------+
| 2018-04-01 | 79.22 |           0.00 |  76.41 |             0.00 |  68.89 |             0.00 |
| 2018-04-02 | 79.32 |           0.10 |  76.52 |             0.11 |  69.01 |             0.12 |
| 2018-04-04 | 79.45 |           0.13 |  76.64 |             0.12 |  69.15 |             0.14 |
| 2018-04-05 | 79.48 |           0.03 |  76.67 |             0.03 |  69.18 |             0.03 |
| 2018-04-06 | 79.48 |           0.00 |  76.67 |             0.00 |  69.21 |             0.03 |
| 2018-04-07 | 79.50 |           0.02 |  76.69 |             0.02 |  69.25 |             0.04 |
| 2018-04-08 | 79.53 |           0.03 |  76.72 |             0.03 |  69.30 |             0.05 |
| 2018-04-09 | 79.49 |          -0.04 |  76.69 |            -0.03 |  69.27 |            -0.03 |
| 2018-04-10 | 79.48 |          -0.01 |  76.67 |            -0.02 |  69.30 |             0.03 |
| 2018-04-11 | 79.48 |           0.00 |  76.67 |             0.00 |  69.30 |             0.00 |
| 2018-04-12 | 79.44 |          -0.04 |  76.63 |            -0.04 |  69.27 |            -0.03 |
| 2018-04-13 | 79.40 |          -0.04 |  76.60 |            -0.03 |  69.30 |             0.03 |
+------------+-------+----------------+--------+------------------+--------+------------------+
12 rows in set (0.00 sec)

Calculate difference between values in consecutive rows by group

The package data.table can do this fairly quickly, using the shift function.

require(data.table)
df <- data.table(group = rep(c(1, 2), each = 3), value = c(10,20,25,5,10,15))
#setDT(df) #if df is already a data frame

df[ , diff := value - shift(value), by = group]    
#   group value diff
#1:     1    10   NA
#2:     1    20   10
#3:     1    25    5
#4:     2     5   NA
#5:     2    10    5
#6:     2    15    5
setDF(df) #if you want to convert back to old data.frame syntax

Or using the lag function in dplyr

df %>%
    group_by(group) %>%
    mutate(Diff = value - lag(value))
#   group value  Diff
#   <int> <int> <int>
# 1     1    10    NA
# 2     1    20    10
# 3     1    25     5
# 4     2     5    NA
# 5     2    10     5
# 6     2    15     5

For alternatives pre-data.table::shift and pre-dplyr::lag, see edits.

calculate difference between consecutive numbers in different rows of a field in the same model

I'm Using the form_valid **RefuelCreatView** when validation the new record to get the oldest data and perform my calculation and store the RefuelConsumption Model.

class RefuelCreationView(LoginRequiredMixin, CreateView):
    model = Refuel
    form_class = RefuelCreationForm
    template_name = "refuel/refuel_form.html"
    success_url = reverse_lazy("refuel:controlor-refuel-list")

    def form_valid(self, form):
        form.instance.user_id = self.request.user.id
        form.instance.gaz_station = GazStation.objects.get(Controlor_id=self.request.user.id)
        old_refuel_data = Refuel.objects.order_by().distinct("vehicle")
        for refuel in old_refuel_data:
            if refuel.vehicle == form.instance.vehicle and form.instance.odometer_reading:
                consumption = (refuel.fuel_quantity / (form.instance.odometer_reading - refuel.odometer_reading)) * 100
                FuelConsumption.objects.create(
                    vehicle=refuel.vehicle,
                    gaz_station=form.instance.gaz_station,
                    Controlor_id=self.request.user,
                    driver=refuel.vehicle.driver,
                    consumption=consumption,
                    is_active=True,
                )
            elif refuel.vehicle == form.instance.vehicle and form.instance.snitch:
                consumption = (refuel.fuel_quantity / (form.instance.snitch - refuel.snitch)) * 100
                FuelConsumption.objects.create(
                    vehicle=refuel.vehicle,
                    gaz_station=form.instance.gaz_station,
                    Controlor_id=self.request.user,
                    driver=refuel.vehicle.driver,
                    consumption=consumption,
                    is_active=True,
                )
        return super().form_valid(form)

    def get_queryset(self, *args, **kwargs):
        return Refuel.objects.select_related("vehicle__gaz_station__Controlor_id").filter(
            vehicle__gaz_station__Controlor_id=self.request.user
        )

and this work very well. I don't know if it's respecting the best practices in programming or not, I'm open to any advise.

How to get the difference between consecutive rows in MySQL?

For MySQL 8 then use Lag window function.

SELECT 
    test.id, 
    test.date, 
    test.number, 
    test.qty, 
    IFNULL(test.number - LAG(test.number) OVER w, 0) AS diff,
    ROUND(IFNULL(test.number - LAG(test.number) OVER w, 0)/ test.qty, 2) AS 'Avg'
FROM purchases test
WINDOW w AS (ORDER BY test.`date` ASC);

For MySQL 5.7 or lesser version

We can use the MySQL variable to do this job. Consider your table name is test.

SELECT 
    test.id, 
    test.date, 
    test.number, 
    test.qty, 
    @diff:= IF(@prev_number = 0, 0, test.number - @prev_number) AS diff,
    ROUND(@diff / qty, 2) 'avg',
    @prev_number:= test.number as dummy
FROM 
    test, 
    (SELECT @prev_number:= 0 AS num) AS b
ORDER BY test.`date` ASC;

-------------------------------------------------------------------------------
Output:

| id    | date          | number| qty   | diff  | avg   | dummy | 
-----------------------------------------------------------------
| 114   | 2018-10-07    | 200   | 5     | 0     | 0.00  | 200   |   
| 120   | 2018-12-01    | 300   | 10    | 100   | 10.00 | 300   |   
| 123   | 2019-02-03    | 700   | 12    | 400   | 33.33 | 700   |  
| 1126  | 2019-03-07    | 1000  | 15    | 300   | 20.00 | 1000  |

Explaination:

(SELECT @prev_number:= 0 AS num) AS b
we initialized variable @prev_number to zero in FROM clause and joined with each row of the test table.
@diff:= IF(@prev_number = 0, 0, test.number - @prev_number) AS diff First we are generating difference and then created another variable diff to reuse it for average calculation. Also we included one condition to make the diff for first row as zero.
@prev_number:= test.number as dummy we are setting current number to this variable, so it can be used by next row.

Note: We have to use this variable first, in both difference as well as average and then set to the new value, so next row can access value from the previous row.

You can skip/modify order by clause as per your requirements.

Calculate difference between consecutive rows within a same column

You're only printing one value because you only have one print statement (not counting the "we are here" ones), it's not in a loop body, and it's printing a scalar value. You'd have to change at least one of those things, probably the second to do what you want, to get it to print multiple values.

Postgresql - Difference in value between two successive rows grouped by category

You can approach this using row_number():

select category,
       sum(case when seqnum = 1 then value else - value end) as diff
from (select t.*,
             row_number() over (partition by category order by timestamp) as seqnum
      from t
     ) t
where seqnum in (1, 2)
group by category
having count(*) > 1;

SQL: Difference between consecutive rows

You can use lag() to get the date of the previous order by the same customer:

select o.*,
    datediff(
        order_date,
        lag(order_date) over(partition by member_id order by order_date, order_id)
    ) days_diff
from orders o

When there are two rows for the same date, the smallest order_id is considered first. Also note that I fixed your datediff() syntax: in Hive, the function just takes two dates, and no unit.

I just don't get the logic you want to compute num_orders.

Calculate difference between multiple rows by a group in R

You can use match to get the corresponding sbd value at wk 1 and 2.

library(dplyr)

df %>%
  group_by(code, tmp) %>%
  summarise(diff = sbd[match(1, wek)] - sbd[match(2, wek)])

#  code  tmp    diff
#  <chr> <chr> <dbl>
#1 abc01 T1    -0.67
#2 abc01 T2     0.34

If you want to add a new column in the dataframe keeping the rows same, use mutate instead of summarise.

data

It is easier to help if you provide data in a reproducible format

df <- structure(list(code = c("abc01", "abc01", "abc01", "abc01", "abc01", 
"abc01", "abc01", "abc01", "abc01", "abc01", "abc01", "abc01", 
"abc01", "abc01", "abc01", "abc01", "abc01", "abc01"), tmp = c("T1", 
"T1", "T1", "T1", "T1", "T1", "T1", "T1", "T1", "T2", "T2", "T2", 
"T2", "T2", "T2", "T2", "T2", "T2"), wek = c(1L, 1L, 2L, 2L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 1L, 1L, 1L, 1L, 1L), sbd = c(7.83, 
7.83, 8.5, 8.5, 7.83, 7.83, 7.83, 7.83, 7.83, 7.56, 7.56, 7.22, 
7.22, 7.56, 7.56, 7.56, 7.56, 7.56)), 
class = "data.frame", row.names = c(NA, -18L))

How to Calculate Difference of Values in a Successive Rows