커서를 사용하는 SQL 쿼리를 작성했는데 서버에서 너무 많은 리소스를 차지하고 느리다는 것을 깨달았습니다.
그래서 이것은 표입니다.
Assigned_group이 또는 prev_assigned_group이 "% Hotline %"과 같은 상태 인 Status = Assigned와 Status = In Progress 사이의 시간 차이를 계산해야합니다.
또한 시퀀스가 있으므로 가장 작은 시퀀스 번호로 시작해야하므로 Incident ID를 구별하고 행을 Incident_Sequence로 오름차순으로 정렬합니다.
여태까지는 그런대로 잘됐다.
첫 번째 행부터 시작하여 상태가 "진행 중"임을 알 때까지 다음 행으로 이동합니다. 상태가 발견되면 첫 번째 할당 된 행과 진행 중 상태가 발견 된 행의 차이를 계산합니다.
그래서 큰 질문은 : 커서없이 이것을 할 수 있습니까? 그렇다면 어떻게?
SET NOCOUNT ON
DECLARE @day DATE
SET @day = '20160606'
CREATE TABLE #result(
[Assigned_Group] NVARCHAR(100),
[ProgressTime] INTEGER,
[Tickets] INTEGER,
[Avarage] FLOAT
)
INSERT INTO #result(Assigned_Group,ProgressTime,Tickets,Avarage)
SELECT DISTINCT Assigned_Group,0,0,0.0
FROM [grs_dwh].[smt].[Aht]
WHERE (Assigned_Group like 'CI-Hotline%' OR Prev_assigned_Group like 'CI-Hotline%')
and CONVERT(DATE,Last_Modified_Date, 104) = @day
-- raw
SELECT [Incident_Sequence]
,[Incident_Id]
,[Assigned_Group]
,[Prev_assigned_Group]
,[Status]
,[Last_Modified_Date]
,[Service]
INTO #rawData
FROM [grs_dwh].[smt].[Aht]
WHERE (Assigned_Group like 'CI-Hotline%' OR Prev_assigned_Group like 'CI-Hotline%')
and CONVERT(DATE,Last_Modified_Date, 104) = @day
ORDER BY Incident_Sequence asc
--CREATE TABLE #orderList(
--)
SELECT DISTINCT[Incident_id] INTO #incidentList FROM #rawData
DECLARE cur0 CURSOR FOR SELECT incident_Id FROM #incidentList
DECLARE @currentIncident NVARCHAR(15)
OPEN cur0
FETCH next from cur0 INTO @currentIncident
WHILE @@FETCH_STATUS = 0
BEGIN
-- PRINT @currentIncident
SELECT * INTO #tmpTable FROM #rawData WHERE Incident_Id = @currentIncident ORDER BY Incident_Sequence
DECLARE cur1 CURSOR FOR SELECT * FROM #tmpTable ORDER BY Incident_Sequence ASC
DECLARE @incident_Sequence INTEGER
DECLARE @incident_Id NVARCHAR(50)
DECLARE @assigned_Group NVARCHAR(100)
DECLARE @previous_Assiggned NVARCHAR(100)
DECLARE @status NVARCHAR(50)
DECLARE @last_Modified_Date DATETIME
DECLARE @service NVARCHAR(50)
DECLARE @progressFound BIT
DECLARE @startProgressDate DATETIME
DECLARE @ticketProgressTime INTEGER
DECLARE @resultGroup NVARCHAR(100)
SET @progressFound = 0
OPEN cur1
FETCH next from cur1
INTO @incident_Sequence, @incident_Id, @assigned_Group, @previous_Assiggned, @status, @last_Modified_Date, @service
WHILE @@FETCH_STATUS = 0
BEGIN
IF @progressFound = 0 AND @status <> 'In Progress'
BEGIN
FETCH next from cur1 INTO @incident_Sequence, @incident_Id, @assigned_Group, @previous_Assiggned, @status, @last_Modified_Date, @service
CONTINUE
END
IF @progressFound = 0
BEGIN
SET @startProgressDate = @last_Modified_Date
SET @resultGroup = @assigned_Group
SET @progressFound = 1
FETCH next from cur1 INTO @incident_Sequence, @incident_Id, @assigned_Group, @previous_Assiggned, @status, @last_Modified_Date, @service
CONTINUE
END
ELSE
BEGIN
SET @ticketProgressTime = DATEDIFF(SECOND, @startProgressDate, @last_Modified_Date)
UPDATE #result SET ProgressTime = ProgressTime + @ticketProgressTime, Tickets = Tickets+1 WHERE Assigned_Group = @resultGroup
SET @ticketProgressTime = 0
SET @progressFound = 0
END
FETCH next from cur1
INTO @incident_Sequence, @incident_Id, @assigned_Group, @previous_Assiggned, @status, @last_Modified_Date, @service
END
CLOSE cur1
DEALLOCATE cur1
--IF @incident_Id = 'INC000010047798'
--SELECT * FROM #tmpTable ORDER BY Incident_Sequence ASC
DROP TABLE #tmpTable
FETCH next from cur0 INTO @currentIncident
END
CLOSE cur0
DEALLOCATE cur0
SET NUMERIC_ROUNDABORT OFF
UPDATE #result SET Avarage = CAST(ProgressTime AS float) / CASE WHEN Tickets = 0 THEN 1 ELSE CAST(Tickets AS float) END
SELECT * FROM #result
ORDER BY 1 asc
DROP TABLE #result
DROP TABLE #rawData
DROP TABLE #incidentList
ROW_NUMBER
함수를 사용하여 시퀀스를 추적하고 자체 조인을 수행하여 해당 값을 계산할 수 있습니다.
;WITH Data AS
( -- Sample data (http://i.stack.imgur.com/TfzL7.png)
SELECT Id, Incident_Id Incident, Incident_Sequence Sequence,
Prev_Assigned_Group Previous, Assigned_Group GroupName,
Status, CAST(Last_Modified_Date AS DATETIME) Modified,
ROW_NUMBER() OVER (PARTITION BY Incident_Id ORDER BY Id) RowNumber -- Start over the count for every Incident_Id
FROM ( VALUES
(164293, 05, 'INC000010047798', 'Eastern Europe1' , 'CI-Hotline North America', 'Assigned' , '2016-06-04 12:28:46'),
(171241, 07, 'INC000010047798', 'CI-Hotline' , 'Eastern Europe1' , 'Assigned' , '2016-06-06 06:42:16'),
(171919, 09, 'INC000010047798', 'CI-Hotline' , 'Eastern Europe1' , 'In Progress', '2016-06-06 06:46:19'),
(172138, 10, 'INC000010047798', 'CI-Hotline Romania', 'CI-Hotline' , 'Assigned' , '2016-06-06 06:46:35'),
(172483, 12, 'INC000010047798', 'CI-Hotline Romania', 'CI-Hotline' , 'In Progress', '2016-06-06 07:11:53'),
(173003, 15, 'INC000010047798', 'Austria Adria3' , 'CI-Hotline Romania' , 'Assigned' , '2016-06-06 07:15:36'),
(208011, 17, 'INC000010047798', 'Austria Adria3' , 'CI-Hotline Romania' , 'Resolved' , '2016-06-10 12:14:05')
) AS X(Id, Incident_Sequence, Incident_Id, Assigned_Group, Prev_Assigned_Group, Status, Last_Modified_Date)
WHERE Assigned_Group LIKE '%HOTLINE%' OR
Prev_Assigned_Group LIKE '%HOTLINE%'
)
SELECT Assigned.Incident,
Assigned.Status + ' » ' + InProgress.Status,
DATEDIFF(second, InProgress.Modified, Assigned.Modified) / 60.0 / 60.0 Hours
--,Assigned.*, InProgress.*
FROM Data Assigned
JOIN Data InProgress
ON Assigned.Incident = InProgress.Incident AND
Assigned.RowNumber = InProgress.RowNumber + 1
다음 번에는 샘플 데이터를 텍스트 형식으로 보내 주시기 바랍니다 ;-)
편집 :'In Progress'
다음 'In Progress'
상태 (또는 사용 가능한 마지막 상태)와 다른 상태 간의 시간 차이를 계산하려면 다음 코드를 사용하십시오.
;WITH Data AS
( -- Sample data (http://i.stack.imgur.com/TfzL7.png)
SELECT Id, Incident_Id Incident, Incident_Sequence Sequence,
Prev_Assigned_Group Previous, Assigned_Group GroupName,
Status, CAST(Last_Modified_Date AS DATETIME) Modified,
ROW_NUMBER() OVER
( PARTITION BY Incident_Id
ORDER BY Incident_Sequence
) RowNumber -- Start over the count for every Incident_Id
FROM [aht_data_one_day]
WHERE -- Incident_Id IN ('INC000010164572') AND
(Assigned_Group LIKE '%HOTLINE%' OR Prev_Assigned_Group LIKE '%HOTLINE%')
)
SELECT Assigned.Id, Assigned.Incident,
CAST(Assigned.Sequence AS VARCHAR(5)) + ' » ' +
CAST(InProgress.Sequence AS VARCHAR(5)) Transition,
DATEDIFF(second, Assigned.Modified, InProgress.Modified) TotalSeconds
--, Assigned.*, InProgress.*
FROM Data Assigned
JOIN Data InProgress
ON Assigned.Status NOT IN ('In Progress') AND
InProgress.Id = ISNULL(
( -- Try to locate the next 'In Progress' status
SELECT MIN(Id)
FROM Data
WHERE Status IN ('In Progress') AND
Data.Incident = Assigned.Incident AND
Data.RowNumber > Assigned.RowNumber -- That's the trick
),
( -- If not found, get the latest status
SELECT MAX(Id)
FROM Data
WHERE Data.Incident = Assigned.Incident
))
ORDER BY Assigned.Incident, Assigned.Id
편집 : 데이터 업데이트시 이전 코드를 약간 변경하여 유지합니다. 다음은 대규모 인시던트 및 쿼리에서 반환 된 데이터에 대한 논리 유효성 검사를 따릅니다.
Id Incident Transition TotalSeconds
----------- --------------- ------------- ------------
172090 INC000010164572 10 » 13 1877
172939 INC000010164572 15 » 25 6578
173241 INC000010164572 17 » 25 4045
173597 INC000010164572 20 » 25 3616
173949 INC000010164572 23 » 25 1125
174298 INC000010164572 27 » 34 981
174468 INC000010164572 30 » 34 287
174647 INC000010164572 33 » 34 100
174773 INC000010164572 36 » 36 0
편집 : 마지막 시도
SELECT InProgress.Id, InProgress.Incident,
CAST(InProgress.Sequence AS VARCHAR(5)) + ' » ' +
CAST(NextStatus.Sequence AS VARCHAR(5)) Transition,
DATEDIFF(second, InProgress.Modified, NextStatus.Modified) TotalSeconds
-- , InProgress.*, NextStatus.*
FROM Data InProgress
JOIN Data NextStatus
ON InProgress.Status IN ('In Progress') AND
InProgress.Incident = NextStatus.Incident AND
NextStatus.Id = ISNULL(
( -- Try to locate the next status different than 'In Progress'
SELECT MIN(Id)
FROM Data
WHERE Status NOT IN ('In Progress') AND
Data.Incident = InProgress.Incident AND
Data.RowNumber > InProgress.RowNumber -- That's the trick
),
( -- If not found, get the latest status
SELECT MAX(Id)
FROM Data
WHERE Data.Incident = InProgress.Incident
))
ORDER BY InProgress.Incident, InProgress.Id
산출:
Id Incident Transition TotalSeconds
----------- --------------- ------------- ------------
172564 INC000010164572 13 » 15 236
174123 INC000010164572 25 » 27 688
174689 INC000010164572 34 » 36 77
행운을 빕니다.
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다