mysql - 如何在 mySQL 中排除行对的聚合？

Question

我们对操作进行了审计跟踪，并要求我报告这些操作所花费的平均时间。

不幸的是，审核跟踪包含“取消”条目，本质上排除了之前的操作。

所以，一些数据。

AuditTrail
ID OrderID ActionQty ActionDate
1  1        1        2002-02-02
2  2        1        2002-02-02
3  1       -1        2003-03-03
4  1        1        2004-04-04

和

Orders
OrderID OrderDate
1       2001-01-01
2       2002-02-02

比较的基准日期是差值 ActionDate。

所需的是这些的平均值。

在上面的示例中，需要排除 AuditTrail 条目 1 和 3，因为条目 3 是“取消”，因此需要排除之前的非取消条目(条目 1)。ID 是连续的，但对于订单来说不一定是连续的，因为有很多订单和很多审计跟踪条目。

更复杂的是，我们可以看到“取消”的运行，这需要在链中进一步回滚。

例如

AuditTrail
ID   OrderID ActionQty ActionDate
1030 99       1        2002-02-02
1031 99       1        2002-02-02
1032 99      -1        2003-03-03
1033 99      -1        2004-04-04

在此示例中，2 个输入和 2 个输出。

所以。

平均值是总数除以计数。我们可以轻松地使用 SUM(ActionQty)(GROUP'd BY AuditTrail.OrderID)来获取订单的正确计数。

获取 AuditEntry 的天数也很容易 (TIMESTAMPDIFF(DAY, Orders.OrderDate, AuditTrail.ActionDate))。

但是排除正确的......我无法解决。

有什么线索吗？

Answer 1

您可以使用行号方法进行匹配。给定

MariaDB [sandbox]> select * from t;
+------+---------+-----------+------------+
| ID   | OrderID | ActionQty | ActionDate |
+------+---------+-----------+------------+
|    1 |       1 |         1 | 2002-02-02 |
|    2 |       2 |         1 | 2002-02-02 |
|    3 |       1 |        -1 | 2003-03-03 |
|    4 |       1 |         1 | 2004-04-04 |
| 1030 |      99 |         1 | 2002-02-02 |
| 1031 |      99 |         1 | 2002-02-02 |
| 1032 |      99 |        -1 | 2003-03-03 |
| 1033 |      99 |        -1 | 2004-04-04 |
+------+---------+-----------+------------+
8 rows in set (0.00 sec)

此查询

Select s.id,s.orderid,s.actionqty,s.actiondate
from
(
select t.*, 
        if(t.orderid<>@p ,@rn:=1,@rn:=@rn+1) rn,
        @p:=t.orderid p
from t,(select @block:=0,@rn:=0,@p:=0) rn
where actionqty > 0
order by orderid,id
)s
left join
(
select t.*, 
        if(t.orderid<>@p ,@rn:=1,@rn:=@rn+1) rn,
        @p:=t.orderid p
from t,(select @block:=0,@rn:=0,@p:=0) rn
where actionqty < 0
order by orderid,id
) cans on s.orderid = cans.orderid and s.rn = cans.rn
where cans.id is null

结果

+------+---------+-----------+------------+
| id   | orderid | actionqty | actiondate |
+------+---------+-----------+------------+
|    4 |       1 |         1 | 2004-04-04 |
|    2 |       2 |         1 | 2002-02-02 |
+------+---------+-----------+------------+
2 rows in set (0.00 sec)