Calculate Delta(Difference of Current and Previous Row) in SQL

Calculate delta(difference of current and previous row) mysql group by specific column

Try this:

drop table a;
create table a( id integer not null primary key, d datetime, user_id integer );
insert into a values (1,now() + interval 0 day, 1 );
insert into a values (2,now() + interval 1 day, 1 );
insert into a values (3,now() + interval 2 day, 1 );
insert into a values (4,now() + interval 0 day, 2 );
insert into a values (5,now() + interval 1 day, 2 );
insert into a values (6,now() + interval 2 day, 2 );

select t1.user_id, t1.d, t2.d, datediff(t2.d,t1.d)
from a t1, a t2
where t1.user_id=t2.user_id
and t2.d = (select min(d) from a t3 where t1.user_id=t3.user_id and t3.d > t1.d)

Which means: join your table to itself on user_ids and adjacent datetime entries and compute the difference.

Calculate delta(difference of current and previous row) in sql

Here's your original query modified accordingly:

select
  tt1.id,
  tt1.amount,
  tt1.AccountId,
  (tt1.amount-ifnull(tt2.amount, 0)) as delta
from trans tt1
  left outer JOIN trans tt2 on tt1.accountid = tt2.accountid
    and month(tt1.date1)-month(tt2.date1)=1;

The month comparison is moved from where to on, which makes a difference for left join, and tt2.amount is replaced with ifnull(tt2.amount, 0).

---

The UPDATE version of the script:

update tt1
set delta = (tt1.amount-ifnull(tt2.amount, 0))
from trans tt1
  left outer JOIN trans tt2 on tt1.accountid = tt2.accountid
    and month(tt1.date1)-month(tt2.date1)=1;

---

The correct MySQL syntax for the above update should actually be:

update trans tt1 
             left outer JOIN trans tt2 
             on tt1.accountid = tt2.accountid 
             and month(tt1.date1)-month(tt2.date1)=1 
set tt1.delta = (tt1.amount-ifnull(tt2.amount, 0));

(Thanks @pinkb.)

SQL Find difference between previous and current row

See sqlFiddle

;WITH tblDifference AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY id) AS RowNumber, columnOfNumbers 
    FROM tableName
)

SELECT cur.columnOfNumbers, cur.columnOfNumbers - previous.columnOfNumbers
FROM tblDifference cur
LEFT OUTER JOIN tblDifference previous
ON cur.RowNumber = previous.RowNumber + 1

How to calculate time difference between current and previous row in MySQL

To get the time difference in minutes between the current and previous row, you can use timestampdiff on datenow and the previous time, which you can get via subquery:

select ticketid, datenew,
    timestampdiff(minute,datenew,(select datenew from mytable t2
        where t2.ticketid < t1.ticketid order by t2.ticketid desc limit 1)) as diff
from mytable t1

Update

Here's another way using a variable to store the previous datenew value that might be faster:

select ticketid, datenew, timestampdiff(minute,datenew,prevdatenew)
from (
    select ticketid, datenew, @prevDateNew as prevdatenew, 
      @prevDateNew := datenew
    from mytable order by ticketid
) t1

In SQL (Impala) how to calculate time delta between rows that satisfies condition and next one?

You can use lead():

select 
    user, 
    row_number() over(partition by user order by timestamp) no,
    delta
from (
    select 
        t.*,
        unixtimestamp(lead(timestamp) over(partition by user order by timestamp))
            - unixtimestamp(timestamp) delta
    from mytable t
) t
where event = 'listen_music'
order by user, no;

The subquery computes the difference between the current and the next record of the same user. The outer query filters on the target event and numbers the records using row_number().

Query data from previous row and update current row

Why not just sort by the keys and date and then use the lag function to look one row back. Maybe you already tried this, but used the lag only inside the if type is missing block. This won't help you as noted in the documentation

Storing values at the bottom of the queue and returning values from
the top of the queue occurs only when the function is executed. An
occurrence of the LAG n function that is executed conditionally stores
and return values only from the observations for which the condition
is satisfied.

Instead, compute the lag in every row, not just in those satisfying the condition.

proc sort data=have;
  by ID1 ID2 day;
run;

data want;
  set have;
  by ID1 ID2;
  lag_amount_a = lag(amount_a);
  lag_amount_b = lag(amount_b);
  lag_day = lag(day);
  lag_type = lag(type);
  if ID1 in ("a1", "a2", "a3") and missing(type) then do;
    // check if row before matches ID1, ID2 and day - 1
    if not first.ID2 and day = lag_day + 1 then do;
      amount_a = lag_amount_a;
      amount_b = lag_amount_b;
      type = lag_type;
    end;
  end;
run;

Difference in seconds between current row and previous row and store the value in separate column when order ID also matches using google bigquery

Try below

And I would recommend you to check out Window functions

SELECT
  DATE, id,
  IFNULL(TIMESTAMP_TO_SEC(TIMESTAMP(DATE)) -   
         TIMESTAMP_TO_SEC(TIMESTAMP(prev_date))
    , 0) AS Difference_In_Seconds
FROM (
  SELECT  
    DATE, id,
    LEAD(DATE) OVER(PARTITION BY id ORDER BY DATE DESC) AS prev_date
  FROM
    (SELECT STRING(DATE) AS DATE, id FROM [test:product.tab1] )
)
ORDER BY id, DATE DESC

Creating a new RANK based on delta of previous row

Basically, you want to use lag() to get the previous date and then do a cumulative sum. This looks like:

select c.*,
       sum(case when prev_end >= dateadd(day, -1, start) then 0 else 1
           end) over (partition by employee order by start) as ranking
from (select c.*,
             lag(end) over (partition by employee order by start) as prev_end
      from contracts c
     ) c;

You mention that you might want to recalculate the new start and end. You would just use the above as a subquery/CTE and aggregate on employee and ranking.

---

Related Topics