如何在SQL中计算Status从IN转OUT时的日期运行累计天数？

先来看你的数据集示例（整理成表格更清晰）：

你的需求是：每次客户状态从IN切换到OUT时，计算该段IN状态持续的总天数，并且只在对应周期的最后一条OUT记录上显示这个累计值。下面我给你一套通用的SQL解决方案，同时解释每个步骤的作用。

解决方案思路 #

核心思路是先把每个IN到后续OUT的记录划分为独立的周期组，然后针对每个组计算IN起始日期到最后一条OUT日期的天数差，最后只在该组的最后一条OUT记录上输出结果。

完整SQL代码 #

假设你的表名为customer_status，字段分别是cust_number（客户编号）、status（状态）、record_date（记录日期，避免用Date作为字段名，因为它是SQL关键字）。以下代码适配大多数主流数据库（仅日期计算函数可能需根据数据库调整）：

WITH grouped_data AS (
    SELECT 
        cust_number,
        status,
        record_date,
        -- 为每个IN-OUT周期生成唯一组ID：每遇到一个IN，组ID加1
        SUM(CASE WHEN status = 'IN' THEN 1 ELSE 0 END) OVER (
            PARTITION BY cust_number 
            ORDER BY record_date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cycle_group,
        -- 标记每个组内的行顺序（倒序），组内最后一条记录的row_in_group为1
        ROW_NUMBER() OVER (
            PARTITION BY cust_number, 
                         SUM(CASE WHEN status = 'IN' THEN 1 ELSE 0 END) OVER (
                             PARTITION BY cust_number 
                             ORDER BY record_date 
                             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                         ) 
            ORDER BY record_date DESC
        ) AS row_in_group
    FROM customer_status
),
cycle_start_dates AS (
    SELECT 
        cycle_group,
        cust_number,
        -- 提取每个周期的IN起始日期
        MIN(CASE WHEN status = 'IN' THEN record_date END) AS in_start_date
    FROM grouped_data
    GROUP BY cycle_group, cust_number
)
SELECT 
    gd.cust_number,
    gd.status,
    gd.record_date,
    -- 只在组内最后一条OUT记录显示累计天数
    CASE 
        WHEN gd.status = 'OUT' AND gd.row_in_group = 1 
        THEN 
            -- 不同数据库的日期差函数略有不同，以下是常见数据库的写法：
            -- SQL Server: DATEDIFF(day, csd.in_start_date, gd.record_date)
            -- PostgreSQL: (gd.record_date - csd.in_start_date)::integer
            -- MySQL: DATEDIFF(gd.record_date, csd.in_start_date)
            (gd.record_date - csd.in_start_date)::integer  -- 这里以PostgreSQL为例
        ELSE NULL
    END AS "Running Total of Days"
FROM grouped_data gd
JOIN cycle_start_dates csd 
    ON gd.cust_number = csd.cust_number 
    AND gd.cycle_group = csd.cycle_group
ORDER BY gd.cust_number, gd.record_date;

代码解释 #

1. grouped_data CTE：

   • 用SUM(CASE...)窗口函数给每个客户的记录按日期分组：每遇到一条IN记录，组ID就加1，这样后续的所有OUT记录都会归到同一个组里，直到下一条IN出现。
   • 用ROW_NUMBER()窗口函数给每个组内的记录倒序编号，这样组里的最后一条（最晚的）记录编号为1，方便我们定位需要显示累计天数的行。

2. cycle_start_dates CTE：

   • 针对每个周期组，提取该组的IN起始日期（也就是组内最早的IN记录日期）。

3. 最终查询：

   • 关联两个CTE，通过CASE语句判断：只有当当前记录是OUT且是组内最后一条记录时，才计算从IN起始日期到当前日期的天数差，否则显示NULL。

适配不同数据库的注意点 #

• 日期差函数：不同数据库计算日期差的函数不一样，我在代码注释里标注了SQL Server、PostgreSQL、MySQL的写法，你可以根据自己使用的数据库替换。
• 日期格式：如果你的record_date是字符串类型，需要先转换成日期类型（比如用CAST(record_date AS DATE)），否则无法计算日期差。

这样执行后，就能得到和你示例完全一致的结果啦！

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