如何通过T-SQL查询机构处于监控名单的日期区间？

我来帮你解决这个问题——你需要提取机构每次进入监控名单的连续周期，这是典型的「连续相同状态分组」场景，之前用MIN()/MAX()分组的方法失效，就是因为它没法区分中间跳出名单的情况。用窗口函数的组合完全可以搞定，下面是具体的思路和代码：

需求回顾 #

• 现有数据字段：OrgCode、OrgName、ReviewDate、MonitorList（1=在监控名单，0=不在）
• 目标输出：每个机构每次进入监控名单的起始日期（首次出现1的日期）和结束日期（状态切换为0的日期）

示例输入数据 #

OrgCode OrgName         ReviewDate MonitorList
8000    Organization A  3/6/2014   1
8000    Organization A  6/4/2014   1
8000    Organization A  9/4/2014   1
8000    Organization A  12/4/2014  0
8000    Organization A  3/5/2015   1
8000    Organization A  6/4/2015   1
8000    Organization A  9/16/2015  1
8000    Organization A  12/16/2015 1
8000    Organization A  3/9/2016   1
8000    Organization A  6/2/2016   1
8000    Organization A  9/8/2016   1
8000    Organization A  12/8/2016  1
8000    Organization A  3/9/2017   0
8000    Organization A  6/14/2018  0

期望输出 #

OrgCode OrgName         MonitorStartDate MonitorEndDate
8000    Organization A  3/6/2014         12/4/2014
8000    Organization A  3/5/2015         3/9/2017

解决方案：窗口函数分组+起止日期提取 #

核心思路是先给连续的相同监控状态打上组ID，再针对每个监控组提取起止日期：

WITH ranked_data AS (
    SELECT
        OrgCode,
        OrgName,
        ReviewDate,
        MonitorList,
        -- 生成组ID：当当前状态和上一条不同时，组ID+1，实现连续相同状态归为一组
        SUM(CASE 
            WHEN LAG(MonitorList) OVER (PARTITION BY OrgCode, OrgName ORDER BY ReviewDate) != MonitorList 
            THEN 1 
            ELSE 0 
        END) OVER (PARTITION BY OrgCode, OrgName ORDER BY ReviewDate) AS group_id
    FROM your_table_name -- 替换成你的表名
),
monitor_periods AS (
    SELECT
        OrgCode,
        OrgName,
        MIN(ReviewDate) AS MonitorStartDate,
        -- 取下一个组的第一条记录日期作为当前监控周期的结束日期
        LEAD(MIN(ReviewDate)) OVER (PARTITION BY OrgCode, OrgName ORDER BY group_id) AS MonitorEndDate
    FROM ranked_data
    GROUP BY OrgCode, OrgName, group_id, MonitorList
    HAVING MonitorList = 1 -- 只保留处于监控名单的组
)
SELECT
    OrgCode,
    OrgName,
    MonitorStartDate,
    MonitorEndDate
FROM monitor_periods
ORDER BY OrgCode, OrgName, MonitorStartDate;

代码解释 #

1. ranked_data CTE：

   • 用LAG()函数获取当前记录的上一条MonitorList值，对比当前值，状态变化时生成一个新的组ID。
   • 这样连续的MonitorList=1或0会被分到同一个组里，完美区分多次进出名单的情况。

2. monitor_periods CTE：

   • 按组分组，取每个监控组的最小日期作为周期起始日期。
   • 用LEAD()函数获取下一个组的最小日期，作为当前监控周期的结束日期（因为下一个组的第一条记录就是状态切换的节点）。
   • 通过HAVING MonitorList=1过滤掉非监控状态的组。

3. 最终查询：输出整理后的监控周期，按机构和起始日期排序。

特殊情况处理 #

如果某机构最后一条记录是MonitorList=1（没有后续的0记录），LEAD()会返回NULL。你可以根据需求修改MonitorEndDate的逻辑，比如用该组的最大日期作为结束日期：

COALESCE(LEAD(MIN(ReviewDate)) OVER (...), MAX(ReviewDate)) AS MonitorEndDate

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