请求生成按年月统计物品状态的SQL查询语句

当然可以实现这个需求！我会用基础SQL语法一步步给你拆解，帮你理解每个部分的逻辑~

首先，先明确我们要计算的三个指标的定义（结合你的表结构）：

• opening_count：当月第一天时处于活跃状态的物品数——也就是物品在当月第一天前已添加，且没有被移除（或移除日期在当月第一天之后），同时状态为活跃。
• closing_count：当月最后一天时处于活跃状态的物品数——逻辑和期初类似，只是日期换成当月最后一天。
• inactived_items：当月内变为非活跃的物品数——也就是物品的移除日期落在当月范围内，或者状态在当月被改为非活跃（通常移除日期和状态是同步的，这里优先用移除日期判断更准确）。

核心实现步骤 #

我们需要先生成所有需要统计的年月范围（从最早的物品添加日期到当前月份），再对每个年月计算这三个指标。下面以支持CTE（通用表表达式）的数据库（比如MySQL 8+、PostgreSQL、SQL Server）为例：

-- 第一步：生成所有需要统计的年月及对应的月初、月末日期
WITH date_range AS (
    SELECT 
        DATE_FORMAT(MIN(item_added_date), '%Y-%m') AS year_month,
        DATE_FORMAT(MIN(item_added_date), '%Y-%m-01') AS month_start, -- 当月第一天
        LAST_DAY(MIN(item_added_date)) AS month_end -- 当月最后一天
    FROM your_table -- 替换成你的实际表名
    UNION ALL
    SELECT 
        DATE_FORMAT(DATE_ADD(month_start, INTERVAL 1 MONTH), '%Y-%m'),
        DATE_ADD(month_start, INTERVAL 1 MONTH),
        LAST_DAY(DATE_ADD(month_start, INTERVAL 1 MONTH))
    FROM date_range
    WHERE DATE_ADD(month_start, INTERVAL 1 MONTH) <= CURDATE() -- 生成到当前月份为止
)
-- 第二步：基于生成的年月统计三个指标
SELECT
    dr.year_month,
    -- 计算期初活跃数
    COUNT(DISTINCT CASE 
        WHEN t.item_added_date <= dr.month_start 
             AND (t.item_removed_date IS NULL OR t.item_removed_date > dr.month_start)
             AND t.item_status = '活跃' -- 加上状态判断更严谨
        THEN t.ID 
        END) AS opening_count,
    -- 计算期末活跃数
    COUNT(DISTINCT CASE 
        WHEN t.item_added_date <= dr.month_end 
             AND (t.item_removed_date IS NULL OR t.item_removed_date > dr.month_end)
             AND t.item_status = '活跃'
        THEN t.ID 
        END) AS closing_count,
    -- 计算当月非活跃物品数
    COUNT(DISTINCT CASE 
        WHEN t.item_removed_date BETWEEN dr.month_start AND dr.month_end
             AND t.item_status = '非活跃'
        THEN t.ID 
        END) AS inactived_items
FROM date_range dr
LEFT JOIN your_table t ON 
    (t.item_added_date <= dr.month_end) -- 物品在当月结束前已添加
    AND (t.item_removed_date IS NULL OR t.item_removed_date >= dr.month_start) -- 物品在当月期间存在过
GROUP BY dr.year_month, dr.month_start, dr.month_end
ORDER BY dr.year_month;

关键逻辑解释 #

1. date_range CTE：通过递归的方式生成所有需要统计的年月，确保不会漏掉任何有物品数据的月份，哪怕某个月没有新增或移除物品，也会显示该月份（数值为0）。
2. LEFT JOIN：关联物品表和年月范围表，保证每个年月都能出现在结果中。
3. 条件计数（COUNT(DISTINCT CASE...)）：只统计符合对应条件的物品ID，用DISTINCT避免同一个物品被重复计算（比如跨多个月的活跃物品）。

适配不同数据库的小提示 #

• 如果你的数据库不支持CTE（比如MySQL 5.x），可以用临时表或者数字表来生成年月范围。比如先创建一个包含0到100的数字表，然后基于这个表生成年月：

  SELECT 
      DATE_FORMAT(DATE_ADD((SELECT MIN(item_added_date) FROM your_table), INTERVAL n MONTH), '%Y-%m') AS year_month,
      DATE_FORMAT(DATE_ADD((SELECT MIN(item_added_date) FROM your_table), INTERVAL n MONTH), '%Y-%m-01') AS month_start,
      LAST_DAY(DATE_ADD((SELECT MIN(item_added_date) FROM your_table), INTERVAL n MONTH)) AS month_end
  FROM numbers n
  WHERE DATE_ADD((SELECT MIN(item_added_date) FROM your_table), INTERVAL n MONTH) <= CURDATE()
  

• 如果item_removed_date为空就代表物品一直活跃，那可以去掉状态判断的部分，只用日期来判断，逻辑会更简洁。

测试建议 #

你可以先拿几个有代表性的月份，手动统计出预期结果，再和SQL查询的结果对比，确保逻辑符合你的需求~

内容的提问来源于stack exchange，提问作者Sumeet Pujari