Sql-Server

根據布爾值查詢單日多個開始時間和結束時間

  • June 26, 2019

我想根據布爾值在一天中找到多個開始時間和結束時間，其中數據被劃分為一天的每半小時間隔。所以每天分為 24hrs/2 = 48。有一個時間段。

樣本數據 樣本數據續

現在我想要這樣的輸出：-

輸出

假設使用以下輸入：

在此處輸入圖像描述

預期結果為：

在此處輸入圖像描述

並且，我的腳本如下：

DROP TABLE IF EXISTS Timeline
GO
CREATE TABLE Timeline (agent_key int, agent_date date, agent_date_30_min datetime, hoops bit)
GO
INSERT INTO dbo.Timeline ( agent_key, agent_date, agent_date_30_min, hoops )
VALUES 
( 5, '2019-06-25', '2019-06-25 09:00:00', 0 ),
( 5, '2019-06-25', '2019-06-25 09:30:00', 1 ),
( 5, '2019-06-25', '2019-06-25 10:00:00', 1 ),
( 5, '2019-06-25', '2019-06-25 10:30:00', 1 ),
( 5, '2019-06-25', '2019-06-25 11:00:00', 1 ),
( 5, '2019-06-25', '2019-06-25 11:30:00', 0 ),
( 5, '2019-06-25', '2019-06-25 12:00:00', 1 ),
( 5, '2019-06-25', '2019-06-25 12:30:00', 1 ),
( 5, '2019-06-25', '2019-06-25 13:00:00', 1 ),
( 5, '2019-06-25', '2019-06-25 13:30:00', 1 ),
( 5, '2019-06-25', '2019-06-25 14:00:00', 1 ),
( 5, '2019-06-25', '2019-06-25 14:30:00', 0 ),
( 5, '2019-06-25', '2019-06-25 15:00:00', 1 ),
( 5, '2019-06-26', '2019-06-26 09:00:00', 1 ),
( 5, '2019-06-26', '2019-06-26 09:30:00', 1 ),
( 5, '2019-06-26', '2019-06-26 10:00:00', 1 ),
( 5, '2019-06-26', '2019-06-26 10:30:00', 1 ),
( 5, '2019-06-26', '2019-06-26 11:00:00', 1 ),
( 5, '2019-06-26', '2019-06-26 11:30:00', 0 ),
( 5, '2019-06-26', '2019-06-26 12:00:00', 1 ),
( 6, '2019-06-25', '2019-06-25 13:30:00', 1 ),
( 6, '2019-06-25', '2019-06-25 14:00:00', 1 ),
( 6, '2019-06-25', '2019-06-25 14:30:00', 0 ),
( 6, '2019-06-25', '2019-06-25 15:00:00', 1 ),
( 6, '2019-06-26', '2019-06-26 09:00:00', 1 ),
( 6, '2019-06-26', '2019-06-26 09:30:00', 1 ),
( 6, '2019-06-26', '2019-06-26 10:00:00', 1 ),
( 6, '2019-06-26', '2019-06-26 10:30:00', 1 ),
( 6, '2019-06-26', '2019-06-26 11:00:00', 1 ),
( 6, '2019-06-26', '2019-06-26 11:30:00', 0 ),
( 6, '2019-06-26', '2019-06-26 12:00:00', 1 )
GO

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

DROP TABLE IF EXISTS #temp
DROP TABLE IF EXISTS #temp2

SELECT      t1.*,
           Coalesce(t2.hoops,t1.hoops) AS next_hoops,
           CASE 
               WHEN CONVERT(int,t1.hoops) - CONVERT(int,Coalesce(t2.hoops,t1.hoops)) > 0 THEN 'Start'
               WHEN CONVERT(int,t1.hoops) - CONVERT(int,Coalesce(t2.hoops,t1.hoops)) < 0 THEN 'End' 
               ELSE 'No changes'
           END AS [status],
           ROW_NUMBER() OVER (PARTITION BY t1.agent_key, t1.agent_date ORDER BY t1.agent_date_30_min) AS [idx_keydate]
INTO        #temp
FROM        TimeLine t1
LEFT JOIN   TimeLine t2 
   ON      t1.agent_key = t2.agent_key
   AND     t1.agent_date_30_min = DATEADD(MINUTE,30,t2.agent_date_30_min)

SELECT      t1.agent_key, 
           t1.agent_date,
           t1.agent_date_30_min,
           t1.hoops,
           (CASE WHEN t1.[idx_keydate] = 1 THEN 'Start' ELSE t1.[status] END) AS [status],
           (CASE WHEN t1.[status] = 'Start' OR t1.[idx_keydate] = 1 THEN t1.agent_date_30_min END) AS [start],
           (CASE WHEN t1.[status] = 'End' THEN DATEADD(MINUTE,-30,t1.agent_date_30_min) END) AS [end],
           ROW_NUMBER() OVER (PARTITION BY t1.agent_key, t1.agent_date ORDER BY t1.agent_date_30_min) AS [idx_keydate],
           ROW_NUMBER() OVER (PARTITION BY t1.agent_key ORDER BY t1.agent_date, t1.agent_date_30_min) AS [idx_key]
INTO        #temp2
FROM        #temp t1
WHERE       t1.[idx_keydate] = 1
   OR      t1.[status] <> 'No changes'

--INPUT
SELECT      * 
FROM        Timeline
--RESULT
SELECT      t1.agent_key,
           t1.agent_date,
           t1.[start],
           Coalesce(t2.[end],t1.[start]) AS [end]
FROM        #temp2 t1
LEFT JOIN   #temp2 t2
   ON      t2.agent_key = t1.agent_key
   AND     t2.[idx_key] = t1.[idx_key]+1
   AND     t2.[idx_keydate] = t1.[idx_keydate]+1
WHERE       t1.[start] IS NOT NULL

使用新請求更新：1 系列之間的單個零將被忽略。

DROP TABLE IF EXISTS Timeline
GO
CREATE TABLE Timeline (agent_key int, agent_date date, agent_date_30_min datetime, hoops bit)
GO
INSERT INTO dbo.Timeline ( agent_key, agent_date, agent_date_30_min, hoops )
VALUES 
( 5, '2019-06-25', '2019-06-25 09:00:00', 0 ),
( 5, '2019-06-25', '2019-06-25 09:30:00', 1 ),
( 5, '2019-06-25', '2019-06-25 10:00:00', 1 ),
( 5, '2019-06-25', '2019-06-25 10:30:00', 1 ),
( 5, '2019-06-25', '2019-06-25 11:00:00', 0 ),
( 5, '2019-06-25', '2019-06-25 11:30:00', 0 ),
( 5, '2019-06-25', '2019-06-25 12:00:00', 1 ),
( 5, '2019-06-25', '2019-06-25 12:30:00', 1 ),
( 5, '2019-06-25', '2019-06-25 13:00:00', 1 ),
( 5, '2019-06-25', '2019-06-25 13:30:00', 1 ),
( 5, '2019-06-25', '2019-06-25 14:00:00', 1 ),
( 5, '2019-06-25', '2019-06-25 14:30:00', 0 ),
( 5, '2019-06-25', '2019-06-25 15:00:00', 1 ),

( 5, '2019-06-26', '2019-06-26 09:00:00', 1 ),
( 5, '2019-06-26', '2019-06-26 09:30:00', 1 ),
( 5, '2019-06-26', '2019-06-26 10:00:00', 1 ),
( 5, '2019-06-26', '2019-06-26 10:30:00', 1 ),
( 5, '2019-06-26', '2019-06-26 11:00:00', 1 ),
( 5, '2019-06-26', '2019-06-26 11:30:00', 0 ),
( 5, '2019-06-26', '2019-06-26 12:00:00', 1 ),

( 6, '2019-06-25', '2019-06-25 13:30:00', 1 ),
( 6, '2019-06-25', '2019-06-25 14:00:00', 1 ),
( 6, '2019-06-25', '2019-06-25 14:30:00', 0 ),
( 6, '2019-06-25', '2019-06-25 15:00:00', 1 ),

( 6, '2019-06-26', '2019-06-26 09:00:00', 1 ),
( 6, '2019-06-26', '2019-06-26 09:30:00', 1 ),
( 6, '2019-06-26', '2019-06-26 10:00:00', 1 ),
( 6, '2019-06-26', '2019-06-26 10:30:00', 1 ),
( 6, '2019-06-26', '2019-06-26 11:00:00', 1 ),
( 6, '2019-06-26', '2019-06-26 11:30:00', 0 ),
( 6, '2019-06-26', '2019-06-26 12:00:00', 1 ),
( 6, '2019-06-26', '2019-06-26 12:30:00', 1 )
GO

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

DROP TABLE IF EXISTS #temp
DROP TABLE IF EXISTS #temp2

SELECT      t1.*,
           Coalesce(t2.hoops,t1.hoops) AS prev_hoops,
           Coalesce(t3.hoops,t2.hoops,t1.hoops) AS prev_prev_hoops,
           CASE 
               WHEN t1.hoops = 1 AND Coalesce(t2.hoops,t1.hoops) = 0 AND Coalesce(t3.hoops,t2.hoops,t1.hoops) = 0
                   THEN 'Start'
               WHEN t1.hoops = 0 AND Coalesce(t2.hoops,t1.hoops) = 0 AND Coalesce(t3.hoops,t2.hoops,t1.hoops) = 1
                   THEN 'End' 
               ELSE 'No changes'
           END AS [status],
           ROW_NUMBER() OVER (PARTITION BY t1.agent_key, t1.agent_date ORDER BY t1.agent_date_30_min) AS [idx_keydate],
           ROW_NUMBER() OVER (PARTITION BY t1.agent_key, t1.agent_date ORDER BY t1.agent_date_30_min DESC) AS [idx_keydate_desc]
INTO        #temp
FROM        TimeLine t1
LEFT JOIN   TimeLine t2 
   ON      t1.agent_key = t2.agent_key
   AND     t1.agent_date_30_min = DATEADD(MINUTE,30,t2.agent_date_30_min)
LEFT JOIN   TimeLine t3
   ON      t2.agent_key = t3.agent_key
   AND     t2.agent_date_30_min = DATEADD(MINUTE,30,t3.agent_date_30_min)

SELECT      t1.agent_key, 
           t1.agent_date,
           t1.agent_date_30_min,
           t1.hoops,
           CASE WHEN (t1.[idx_keydate] = 1 AND t1.hoops = 1) THEN 'Start'
               WHEN ([idx_keydate_desc] = 1 AND t1.hoops = 1) THEN 'End'
               ELSE t1.[status]
           END AS [status],
           (CASE WHEN t1.[status] = 'Start' OR (t1.[idx_keydate] = 1 AND t1.hoops = 1) THEN t1.agent_date_30_min END) AS [start],
           CASE WHEN t1.[status] = 'End' THEN DATEADD(MINUTE,-60,t1.agent_date_30_min) 
               WHEN ([idx_keydate_desc] = 1 AND t1.hoops = 1)  THEN t1.agent_date_30_min
           END AS [end],
           ROW_NUMBER() OVER (PARTITION BY t1.agent_key, t1.agent_date ORDER BY t1.agent_date_30_min) AS [idx_keydate],
           ROW_NUMBER() OVER (PARTITION BY t1.agent_key ORDER BY t1.agent_date, t1.agent_date_30_min) AS [idx_key]
INTO        #temp2
FROM        #temp t1
WHERE       CASE WHEN (t1.[idx_keydate] = 1 AND t1.hoops = 1) THEN 'Start'
               WHEN ([idx_keydate_desc] = 1 AND t1.hoops = 1) THEN 'End'
               ELSE t1.[status]
           END <> 'No changes'

--INPUT
SELECT      * 
FROM        Timeline
--RESULT
SELECT      t1.agent_key,
           t1.agent_date,
           t1.[start],
           Coalesce(t2.[end],t1.[start]) AS [end]
FROM        #temp2 t1
LEFT JOIN   #temp2 t2
   ON      t2.agent_key = t1.agent_key
   AND     t2.[idx_key] = t1.[idx_key]+1
   AND     t2.[idx_keydate] = t1.[idx_keydate]+1
WHERE       t1.[start] IS NOT NULL

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

相關問答

Sql-Server

SQL Server 使用的總記憶體（64 位）

  • March 14, 2022
檢視問答
Sql-Server

優先選擇 SQL Server 2012 而非 2008 R2 的客觀商業原因是什麼？

  • May 8, 2020
檢視問答
Sql-Server

鎖使用的記憶體

  • May 7, 2020
檢視問答
Sql-Server

如何在 Sql Server 中以待機/只讀模式將使用者添加到數據庫

  • February 15, 2020
檢視問答
Sql-Server

如何將 CSV 數據的第一行設置為 SQL Server 中的列名？

  • December 12, 2019
檢視問答
Sql-Server

如何查找所有 Sql Server 使用者權限權限

  • December 4, 2019
檢視問答