# 计算同一个表中不同行之间的差异[重复](Calculate delta's between different rows in same table [duplicate])

```
这个问题在这里已经有了答案：

如何使用dplyr [duplicate] 4个答案 找到R数据帧中两行中值之间的差异

我有一张桌子，里面有很多来自不同米的测量数据。 每个测量值都存储在一个新行中，并具有实际的仪表值。 我需要每米每次连续测量之间的差异。
简化输入：
``` [2016-11-03,MeterA,45]
[2016-11-03,MeterB,45]
[2016-11-04,MeterA,47]
[2016-11-04,MeterB,54]
```
目前我正在做几个for循环，但这需要很长时间，并且可能有一个更有效的方法。 现在的代码
```data\$diff <- 0;
for (i in 1:nrow(submeter)){
if(i > 1){
prow = submeter[i-1,]
row = submeter[i,]
data[which(data\$Address ==  address & data\$Meter == meter &    data\$UCPTlogTime == row\$UCPTlogTime),]\$diff <- row\$UCPTvalue - prow\$UCPTvalue
}
}
}
}
```
期望的输出
``` [2016-11-03,MeterA,0]
[2016-11-03,MeterB,0]
[2016-11-04,MeterA,2]
[2016-11-04,MeterB,9]
```

How to find difference between values in two rows in an R dataframe using dplyr  4 answers

I've got a table which contains a lot of measurements from different meters. Each measurement is stored in a new row and has the actual meter value. I need to have the difference between each successive measurement per meter.
Simplified imput:
``` [2016-11-03,MeterA,45]
[2016-11-03,MeterB,45]
[2016-11-04,MeterA,47]
[2016-11-04,MeterB,54]
```
Currently I am doing this with several for loops but this takes long and there probably is a more efficient way. Code currently
```data\$diff <- 0;
for (i in 1:nrow(submeter)){
if(i > 1){
prow = submeter[i-1,]
row = submeter[i,]
data[which(data\$Address ==  address & data\$Meter == meter &    data\$UCPTlogTime == row\$UCPTlogTime),]\$diff <- row\$UCPTvalue - prow\$UCPTvalue
}
}
}
}
```
Desired output
``` [2016-11-03,MeterA,0]
[2016-11-03,MeterB,0]
[2016-11-04,MeterA,2]
[2016-11-04,MeterB,9]
``````

## 最满意答案

``` dplyr使用`lag`函数很容易。 假设数据`UCPTlogTime`的列名为`UCPTlogTime` ， `Address` ， `Meter`和`UCPTvalue` ：
```library(dplyr)

data <- data %>% group_by(Address, Meter) %>%
mutate(delta = order_by(UCPTlogTime, UCPTvalue - lag(UCPTvalue))) %>%
mutate(delta = ifelse(is.na(delta), 0, delta))
```This is a breeze with dplyr using the `lag` function. Assuming the columns in your dataframe are named `UCPTlogTime`, `Address`, `Meter`, and `UCPTvalue`:
```library(dplyr)

data <- data %>% group_by(Address, Meter) %>%
mutate(delta = order_by(UCPTlogTime, UCPTvalue - lag(UCPTvalue))) %>%
mutate(delta = ifelse(is.na(delta), 0, delta))
``````

• ## 计算两个不同表中两行之间的差异(Calculate difference between two rows from two different table)[2021-10-23]

只需使用join s： select t2.start_date, t2.end_date, (t1e.value - t1s.value) as difference from table2 t2 join table1 t1s on t2.start_date = t1s.date join table1 t1e on t2.end_date = t1e.date; 如果table2某些日期可能与table1不匹配，则使用left join而不是inner jo ...
• ## 如何计算同一个表中两个不同行之间的时差？(How do you calculate a time difference between 2 different rows in the same table?)[2022-12-16]

SELECT t._cust_id, MAX(order_time) order_time, MAX(delivery_time) delivery_time, MAX(delivery_time) - MAX(order_time) as diff_time FROM yourTable t WHERE t.cust_id = 4 GROUP BY t.cust_id 要么 SELECT o.order_time, d.delivery_time, d.delivery_time - ...
• ## 查找表中的所有重复行(Finding all duplicate rows in a table)[2023-03-08]

SELECT t1.* FROM t1,
t2 WHERE t1.first=t2.first AND t1.last=t2.last AND t1.value<>t2.value ; SELECT t1.* FROM
t1,
t2 WHERE t1.first=t2.first AND t1.last=t2.last AND t1.value<>t2.value ;
• ## 在SQL SERVER中显示同一个表中的行之间的差异(Displaying the difference between rows in the same table in SQL SERVER)[2023-05-17]

也许这些加入了CTE的 ROW_NUMBER + CASE ： WITH cte AS ( SELECT empid, name, salary, rn=ROW_NUMBER()OVER(PARTITION BY empid ORDER BY createddate) FROM tblemp ) SELECT oldname=CASE WHEN c1.Name=c2.Name THEN '' ELSE C1.Name END, ...
• ## 删除与其他表匹配的重复行(delete duplicate rows matching with other table)[2022-04-08]

尝试： DELETE FROM tableB WHERE ctid IN ( SELECT BB.ctid FROM ( SELECT a, b, c, count(*) cnt FROM tablea GROUP BY a, b, c ) AA JOIN ( SELECT ctid, a, b, c, row_number() over (partition by a,b,c) cnt ...
• ## 如何复制自引用表的行(How to duplicate rows of self-referenced table)[2022-11-10]

这没关系，没有任何假设： DECLARE @baseMonthId int = 1 DECLARE @newMonthId int = 2 DECLARE @newRows TABLE(id int, orig_id int) MERGE INTO Entity USING ( SELECT Id, Name, ParentId FROM Entity WHERE MonthId = @baseMonthId ) AS cf ON 1 = 0 WHEN NOT MATCHED THEN INSE ...
• ## 计算同一个表中不同行之间的差异[重复](Calculate delta's between different rows in same table [duplicate])[2023-03-21]

dplyr使用lag函数很容易。 假设数据UCPTlogTime的列名为UCPTlogTime ， Address ， Meter和UCPTvalue ： library(dplyr) data <- data %>% group_by(Address, Meter) %>% mutate(delta = order_by(UCPTlogTime, UCPTvalue - lag(UCPTvalue))) %>% mutate(delta = ifelse(is.na(delta), 0, de ...
• ## 比较2个MySQL行并计算delta(Comparing 2 MySQL rows and calculating the delta)[2022-11-13]

你可以使用变量来获取它。 drop table if exists Statistics; create table if not exists Statistics (ID int, Datetime_Scan date, Count int); insert into Statistics values ( 2, '2017/4/6', 302), (10, '2017/4/7', 391), (18, '2017/4/8', 500), (26, '2017/ ...
• ## 删除不在链接表中的重复行(Removing duplicate rows that are not in a link table)[2022-08-04]

尝试一下： DELETE t OUTPUT deleted.* FROM ItemTable t JOIN ( SELECT DENSE_RANK() OVER (PARTITION BY ItemLabel ORDER BY lt.ItemID DESC, it.id) num , it.Id FROM ItemTable it LEFT JOIN LinkTable lt ON lt.ItemId = it.id ) t2 ON t ...
• ## 比较一个表中的重复行，并返回另一个表中的值(Comparing duplicate rows in one table, and returning values in another table)[2022-06-08]

就像是？ SELECT * FROM table2 WHERE table2.id NOT IN (SELECT DISTINCT table1.id FROM table1 WHERE table1.checkin > sysdate OR table1.checkout > sysdate) AND table2.id IN (SELECT ...

更多

更多