mysql - 从版本控制的审核日志中查询每天的状态更新？

Question

我目前正在编写一个审核日志，用于跟踪各个项目的版本历史记录，即跟踪实际更改以及说明更改类型(创建、更新或删除)的标记。

现在，每个项目还有一个“状态”列，显示该项目的状态(打开、同意、也许)。

必填查询:获取到目前为止每天的商品状态计数。所以输出应该是这样的:

day | status | count
---------------------
 1  |  open  |  3
 2  |  open  |  4
 2  |  maybe |  1
 2  |  agree |  2
 3  |  open  |  2
 3  |  agree |  2

等等。我一直在努力从审核日志表 (wc_audit_log) 中构建此查询，如下图所示。还有其他列，但大部分是文本，与此查询无关(恕我直言:)

我尝试过使用 group by 和 order by 以及 year、dayofmonth、month 函数的各种组合，但似乎无法理解如何构建这个查询。最棘手的部分是版本控制方面的“日”边界和重复。也就是说，完全有可能在同一天内多次更新某个项目而无需任何状态更新或在同一天内转换多个状态。

因此，如果出现基于状态的重复项，则会选择带最新时间戳的项目。 IE。如果某个项目更新了两次并且状态两次都是“打开”，则只需选择最后一个。重复计算是可以的，即如果该项目在同一天打开并达成一致，则可以在两个地方进行计算。

但是，我仍然无法弄清楚如何构建这样的查询。上图应该仅显示那些相关列的表的一部分，但也应该给出重复项等的想法。在我看来，这使得这是一个不平凡的查询。

PS:标记为已删除的项目不会被考虑，因此不属于上表。但是，即使该项目已被删除但“过去”存在，上述情况也成立

Answer 1

我认为这符合你的要求。它计算每天具有任何给定状态的 wc_ids 的数量。它不计算一天内的重复项。

select extract(year from timestamp), extract(month from timestamp),
       extract(day from timestamp),
       status, count(distinct wc_id)
from a
group by extract(year from timestamp), extract(month from timestamp),
         extract(day from timestamp), status
order by 1, 2, 3, 4

但是，如果几天内存在重复项，则该 ID 会在这两天内以相同的状态被计数两次。