基于另一张表logtime字段增减计算历史状态计数的SQL实现问题

表结构说明 #

• listings表：存储产品唯一参考编号refno与产品当前状态，产品从Publish状态完成售出后会更新为Sold状态，每个refno仅对应一条记录。状态编码映射规则为'D'=>'Draft','N'=>'Action','Y'=>'Publish'。
  
• logs表：全量记录所有产品的状态变更历史，通过refno与listings表关联，同一refno可对应多条变更记录。每次状态变更时新增一条记录，写入当前时间为logtime，同时同步更新listings表对应产品的状态与update_date字段。示例数据如下：

现有SQL逻辑 #

获取指定时间段logs表数据的语句：

SELECT refno, logtime, status_from, status_to FROM ( 
SELECT refno, logtime, status_from, status_to, ROW_NUMBER() OVER(PARTITION BY refno ORDER BY logtime DESC)
AS RN FROM crm_logs WHERE logtime < '2021-10-12 00:00:00' ) r 
WHERE r.RN = 1 UNION SELECT refno, logtime, status_from, status_to
FROM crm_logs WHERE logtime <= '2021-10-12 00:00:00' AND logtime >= '2015-10-02 00:00:00'
ORDER BY `refno` ASC

查询当前各状态产品总数的语句：

SELECT SUM(status_to = 'D') AS draft, SUM(status_to = 'N') AS action, SUM(status_to = 'Y') AS publish FROM `crm_listings`

需求说明 #

当前仅能查询实时状态计数，需要回溯历史日期的各状态计数。例如今日Action状态计数为15，昨日为10，需要查询得到昨日的Action状态数值10。

实现方案 #

核心逻辑为：历史日期状态计数 = 当前状态计数 - 历史日期到当前时间内转入该状态的产品数量 + 历史日期到当前时间内转出该状态的产品数量。为避免同一产品多次变更导致重复计数，按refno分组去重，仅统计每个产品是否有过转入/转出操作。

单状态（Action）历史查询SQL #

WITH current_action AS (
    -- 获取当前Action状态总数
    SELECT COUNT(*) AS cnt
    FROM crm_listings
    WHERE status_to = 'N'
),
period_changes AS (
    -- 统计目标日期到当前的状态变更
    SELECT
        refno,
        MAX(CASE WHEN status_to = 'N' THEN 1 ELSE 0 END) AS into_action,
        MAX(CASE WHEN status_from = 'N' THEN 1 ELSE 0 END) AS out_action
    FROM crm_logs
    -- 替换下方时间为你要查询的历史日期零点
    WHERE logtime >= '2023-10-01 00:00:00'
      AND logtime < NOW()
    GROUP BY refno
)
SELECT
    current_action.cnt - SUM(into_action) + SUM(out_action) AS history_action_count
FROM period_changes, current_action;

全状态历史查询SQL #

WITH current_counts AS (
    -- 获取当前各状态总数
    SELECT
        SUM(status_to = 'D') AS draft_cnt,
        SUM(status_to = 'N') AS action_cnt,
        SUM(status_to = 'Y') AS publish_cnt
    FROM crm_listings
),
period_changes AS (
    -- 统计目标日期到当前的所有状态变更
    SELECT
        refno,
        MAX(CASE WHEN status_to = 'D' THEN 1 ELSE 0 END) AS into_draft,
        MAX(CASE WHEN status_from = 'D' THEN 1 ELSE 0 END) AS out_draft,
        MAX(CASE WHEN status_to = 'N' THEN 1 ELSE 0 END) AS into_action,
        MAX(CASE WHEN status_from = 'N' THEN 1 ELSE 0 END) AS out_action,
        MAX(CASE WHEN status_to = 'Y' THEN 1 ELSE 0 END) AS into_publish,
        MAX(CASE WHEN status_from = 'Y' THEN 1 ELSE 0 END) AS out_publish
    FROM crm_logs
    -- 替换下方时间为你要查询的历史日期零点
    WHERE logtime >= '2023-10-01 00:00:00'
      AND logtime < NOW()
    GROUP BY refno
)
SELECT
    draft_cnt - SUM(into_draft) + SUM(out_draft) AS history_draft,
    action_cnt - SUM(into_action) + SUM(out_action) AS history_action,
    publish_cnt - SUM(into_publish) + SUM(out_publish) AS history_publish
FROM period_changes, current_counts;

内容的提问来源于stack exchange，提问作者Jay Modi