查詢以查找指定日期之間的活動天數（狀態 = ON 的天數）

我有一張桌子，如下所示：

create table z_test_duration
( Days     date,
 Status   char(8)
);

樣本數據如下：

想要的結果是這樣的

到目前為止，我的解決方案是：

select min(days) on_date, 
      off_day off_date, 
      off_day - min(days) cnt
      
 from (select t1.off_day, 
              t1.prev_offday, 
              t2.days            
         from (                
               select t.days off_day,
                       nvl(lag(t.days, 1) over(order by t.days),convert(datetime, '1/1/2022') - 100) prev_offday
                 from z_test_duration t
                where t.status = 'off'
                                
               ) t1
        inner join z_test_duration t2
           on t2.days > t1.prev_offday
          and t2.days < t1.off_day)
group by off_day;

我在想是否有更好的方法來解決這個問題，如果你能分享你解決這個問題的方法，我將不勝感激。

提前致謝。

這是一個“孤島”問題。

一種流行且有效的解決方案是按所需順序對行進行編號。當序列中有間隙時，排序列和行號之間的差異也會跳躍。

讓我們一步一步來看看。一、編號：

SELECT 
   Z.*, 
   Seq = Z.[Days], -- ordering column
   rn = ROW_NUMBER() OVER (ORDER BY Z.[Days]) -- numbering
FROM dbo.z_test_duration AS Z
WHERE Z.[Status] = 'on';

請注意，Seq值以相同的速度增加，rn直到出現間隙。rn通過從Seq值中減去，我們可以更清楚地看到這一點。

這裡唯一稍微複雜的Seq是 a date，因此我們需要在減去之前將其轉換為數字。我在DATEDIFF這裡使用了這個函式，但是任何將日期轉換為數字的一致方法都可以。

SELECT 
   Z.*, 
   Seq = Z.[Days],
   diff = 
       DATEDIFF(DAY, '2022-01-01', Z.[Days]) - 
           ROW_NUMBER() OVER (
               ORDER BY Z.[Days]) 
FROM dbo.z_test_duration AS Z
WHERE Z.[Status] = 'on';

diff對於組中的每個連續元素，這些值都是相同的。

現在我們知道如何分組了，最終查詢直接如下：

SELECT
   ON_DATE = MIN(G.Seq), 
   OFF_DATE = DATEADD(DAY, 1, MAX(G.Seq)),
   COUNT_OF_ACTIVE_DAYS = 1 + DATEDIFF(DAY, MIN(G.Seq), MAX(G.Seq))
FROM 
(
   SELECT 
       Z.*, 
       Seq = Z.[Days],
       grp = 
           DATEDIFF(DAY, '2022-01-01', Z.[Days]) - 
               ROW_NUMBER() OVER (
                   ORDER BY Z.[Days]) 
   FROM dbo.z_test_duration AS Z
   WHERE Z.[Status] = 'on'
) AS G
GROUP BY G.grp;

db<>fiddle線上展示

引用自：https://dba.stackexchange.com/questions/313769