含删除属性的历史记录表累计计数计算技术问询

嘿，这个需求我之前帮不少开发者处理过——核心就是要跟踪每个用户标签状态的时间变化区间，然后精准统计每个月里实际拥有目标标签的用户数对吧？我给你一步步拆解，用SQL来实现：

第一步：先明确表结构假设 #

首先我假设你的历史表叫user_tag_history，字段大概是这样（如果你的表结构不同，调整字段名就行）：

-- 示例表结构
CREATE TABLE user_tag_history (
    id INT, -- 人员记录ID
    tag VARCHAR(50), -- 标签名称
    action VARCHAR(10), -- 操作类型：'add'（添加）或'remove'（删除）
    action_date DATE -- 操作发生的日期
);

第二步：核心思路——跟踪状态的时间区间 #

我们需要先为每个用户的"established"标签生成状态变化的时间线，明确每个状态（拥有/未拥有）持续的时间段，再针对每个月份统计处于"拥有"状态的用户数。

完整SQL实现（以PostgreSQL为例） #

WITH user_tag_changes AS (
    -- 1. 处理所有标签操作，计算每次操作后的状态
    SELECT
        id,
        tag,
        action,
        action_date,
        -- 根据当前操作和之前的状态，计算当前实际状态：1=拥有，0=未拥有
        CASE
            WHEN action = 'add' THEN 1
            WHEN action = 'remove' THEN 0
            -- 继承上一次的状态（如果有）
            ELSE COALESCE(LAG(current_status) OVER (PARTITION BY id, tag ORDER BY action_date), 0)
        END AS current_status
    FROM user_tag_history
    WHERE tag = 'established' -- 只过滤目标标签

    UNION ALL

    -- 2. 添加用户的初始状态：默认未拥有标签（状态0），时间设为最早操作的前一天
    SELECT
        DISTINCT id,
        'established' AS tag,
        'initial' AS action,
        (SELECT MIN(action_date) FROM user_tag_history) - INTERVAL '1 day' AS action_date,
        0 AS current_status
    FROM user_tag_history
),
user_tag_status_periods AS (
    -- 3. 为每个状态记录下一次状态变化的时间，得到状态持续的区间
    SELECT
        id,
        current_status,
        action_date AS status_start_date,
        -- 获取下一次操作的时间，没有则设为当前日期（表示状态持续到现在）
        COALESCE(LEAD(action_date) OVER (PARTITION BY id ORDER BY action_date), CURRENT_DATE) AS status_end_date
    FROM user_tag_changes
),
month_range AS (
    -- 4. 生成需要统计的所有月份（从最早操作月到当前月）
    SELECT generate_series(
        (SELECT DATE_TRUNC('month', MIN(action_date)) FROM user_tag_history),
        CURRENT_DATE,
        '1 month'
    ) AS month_start
)
-- 5. 统计每个月拥有"established"标签的用户数
SELECT
    DATE_TRUNC('month', month_start) AS month,
    COUNT(DISTINCT id) AS established_user_count
FROM month_range
LEFT JOIN user_tag_status_periods
    ON user_tag_status_periods.current_status = 1 -- 只统计拥有标签的状态
    -- 状态区间与当前月份有重叠
    AND user_tag_status_periods.status_start_date <= (month_start + INTERVAL '1 month' - INTERVAL '1 day')
    AND user_tag_status_periods.status_end_date > month_start
GROUP BY month
ORDER BY month;

关键逻辑解释 #

1. user_tag_changes：处理所有操作，包括用户的初始状态，确保每个用户的标签状态从0开始，每次操作后更新为1或0。
2. user_tag_status_periods：用LEAD函数获取下一次状态变化的时间，这样我们就知道每个状态（拥有/未拥有）从哪天开始，到哪天结束。
3. month_range：生成所有需要统计的月份，避免遗漏任何时间段。
4. 最后关联月份和状态区间，统计每个月内处于"拥有"状态的用户数——只要状态区间和该月份有重叠，就说明这个用户在该月内至少有一段时间拥有标签（如果需要精确到月末的状态，只需要调整关联条件为status_end_date > month_end即可）。

适配其他数据库的小提示 #

如果你用的是MySQL，不能直接用generate_series生成月份，你可以用递归CTE来生成月份序列；DATE_TRUNC可以换成DATE_FORMAT来处理月份格式化，核心逻辑是一样的。

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