如何根据一张表中两列的不同状态减去聚合计数？

Question

使用 MariaDB，我试图获取每月创建的项目总数减去当月删除的项目。如果没有删除任何项目，则总数应仅为该月创建的项目数。如果删除的项目多于创建的项目，则总数应为负数。

该表有一个永远不为空的 created_at 列和一个在“删除”项目后设置的 deleted_at 列。

为了说明这一点，（简化的）架构如下所示: 表项：

+----------------------------------------------------------------------------+
|   idItem  | created_at                     | deleted_at                    |
+----------------------------------------------------------------------------+
|  1        |  2020-03-20T04:28:41.000+00:00 | 2021-07-27T02:36:05.000+00:00 |
|  2        |  2020-03-20T04:28:41.000+00:00 | 2021-07-27T02:36:05.000+00:00 |
|  3        |  2021-03-02T21:39:10.000+00:00 |  ∅                            |
|  4        |  2021-03-05T21:13:13.000+00:00 |  ∅                            |
|  5        |  2021-06-08T13:49:11.000+00:00 | 2021-07-27T02:36:05.000+00:00 |
|  6        |  2021-07-13T02:36:05.000+00:00 |  ∅
|  7        |  2021-09-17T21:12:13.000+00:00 |  ∅                            |
+----------------------------------------------------------------------------+

我需要的信息是尚未删除的每月总计，如下所示：

+-----------------------------------+
|   total_existing  | during_month  |     
+-----------------------------------+
|    2              | 2020-03       | -- two were added
+-----------------------------------+
|    4              | 2021-03       | -- another two were created
+-----------------------------------+
|    5              | 2021-06       | -- another was added
+-----------------------------------+
|    3              | 2021-07       | -- three deleted, one added
+-----------------------------------+
|    4              | 2021-09       | -- one added
+-----------------------------------+

最终，我需要显示每个月的总计。 我已经尝试过这个，但这是不对的。

SELECT
  count(created.idItem) AS monthly_created_count,
  count(deleted.idItem) AS monthly_deleted_count,
  count(created.idItem) - count(deleted.idItem) as total,
  DATE_FORMAT(created.created_at, '%Y-%m') as created_month ,
  DATE_FORMAT(deleted.deleted_at, '%Y-%m') as deleted_month 
FROM 
    Item created 
  LEFT JOIN 
    Item deleted 
  ON 
  DATE_FORMAT(deleted.deleted_at, '%Y-%m') = DATE_FORMAT(created.created_at, '%Y-%m')
GROUP BY DATE_FORMAT(created.created_at, '%Y-%m'), DATE_FORMAT(deleted.deleted_at, '%Y-%m')

我一直认为我已经很接近了，但是当我们查看设置了deleted_at 日期的行时，很明显我偏离了目标。

Answer 1

如果您要查找创建/删除的累积行数，一种方法是按月/年分别计算创建和删除的记录数。。然后使用 UNION ALL 将计数连接在一起并计算总和：

SELECT t.YearMonth
      , SUM(t.TotalCreated) - SUM(t.TotalDeleted) AS TotalExisting
FROM  (
         SELECT DATE_FORMAT(created_at, '%Y-%m') AS YearMonth
                , COUNT(*) AS TotalCreated
                , 0 AS TotalDeleted
         FROM   Item
         GROUP BY DATE_FORMAT(created_at, '%Y-%m')
         
         UNION ALL

         SELECT DATE_FORMAT(deleted_at, '%Y-%m') AS YearMonth
                , 0 AS TotalCreated 
                , COUNT(*) AS TotalDeleted
         FROM   Item
         WHERE  deleted_at IS NOT NULL
         GROUP BY DATE_FORMAT(deleted_at, '%Y-%m')
) t         
GROUP BY t.YearMonth
ORDER BY t.YearMonth

结果：

 YearMonth | TotalExisting
 :-------- | ------------:
 2020-03   |             2
 2021-03   |             2
 2021-06   |             1
 2021-07   |            -2
 2021-09   |             1

然后将这些语句包装在 CTE 中并使用 窗口函数 计算滚动总计：

另请参阅 db<>fiddle

WITH cte AS (        
    SELECT t.YearMonth
           , SUM(t.TotalCreated) - SUM(t.TotalDeleted) AS TotalExisting
    FROM  (
             SELECT DATE_FORMAT(created_at, '%Y-%m') AS YearMonth
                   , COUNT(*) AS TotalCreated
                   , 0 AS TotalDeleted
             FROM   Item
             GROUP BY DATE_FORMAT(created_at, '%Y-%m')
             UNION ALL
             SELECT DATE_FORMAT(deleted_at, '%Y-%m') AS YearMonth
                    , 0 AS TotalCreated 
                    , COUNT(*) AS TotalDeleted
             FROM   Item
             WHERE  deleted_at IS NOT NULL
             GROUP BY DATE_FORMAT(deleted_at, '%Y-%m')
     ) t         
     GROUP BY t.YearMonth
     ORDER BY t.YearMonth
)
SELECT YearMonth, SUM(TotalExisting) OVER (ORDER BY YearMonth) AS TotalExisting
FROM   cte;

最终结果：

YearMonth | TotalExisting
:-------- | ------------:
2020-03   |             2
2021-03   |             4
2021-06   |             5
2021-07   |             3
2021-09   |             4