아래와 같이 약속 표가 있습니다. 각 약속은 “신규”또는 “추종”으로 분류해야합니다. (환자에 대한) 첫 예약 후 30 일 이내에 (환자에 대한) 약속은 후속 조치입니다. 30 일 후에 약속은 다시 “신규”입니다. 30 일 이내의 약속은 “사후 관리”가됩니다.
현재 while 루프를 입력 하여이 작업을 수행하고 있습니다.
WHILE 루프없이 이것을 달성하는 방법?
표
CREATE TABLE #Appt1 (ApptID INT, PatientID INT, ApptDate DATE)
INSERT INTO #Appt1
SELECT 1,101,'2020-01-05' UNION
SELECT 2,505,'2020-01-06' UNION
SELECT 3,505,'2020-01-10' UNION
SELECT 4,505,'2020-01-20' UNION
SELECT 5,101,'2020-01-25' UNION
SELECT 6,101,'2020-02-12' UNION
SELECT 7,101,'2020-02-20' UNION
SELECT 8,101,'2020-03-30' UNION
SELECT 9,303,'2020-01-28' UNION
SELECT 10,303,'2020-02-02' 답변
재귀 쿼리를 사용해야합니다.
30 일 기간은 prev부터 계산됩니다 (재귀 / 기발한 업데이트 / 루프 없이는 수행 할 수 없음). 그렇기 때문에 모든 기존 답변 만 ROW_NUMBER실패했습니다.
WITH f AS (
SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY PatientId ORDER BY ApptDate)
FROM Appt1
), rec AS (
SELECT Category = CAST('New' AS NVARCHAR(20)), ApptId, PatientId, ApptDate, rn, startDate = ApptDate
FROM f
WHERE rn = 1
UNION ALL
SELECT CAST(CASE WHEN DATEDIFF(DAY, rec.startDate,f.ApptDate) <= 30 THEN N'FollowUp' ELSE N'New' END AS NVARCHAR(20)),
f.ApptId,f.PatientId,f.ApptDate, f.rn,
CASE WHEN DATEDIFF(DAY, rec.startDate, f.ApptDate) <= 30 THEN rec.startDate ELSE f.ApptDate END
FROM rec
JOIN f
ON rec.rn = f.rn - 1
AND rec.PatientId = f.PatientId
)
SELECT ApptId, PatientId, ApptDate, Category
FROM rec
ORDER BY PatientId, ApptDate; 산출:
+---------+------------+-------------+----------+
| ApptId | PatientId | ApptDate | Category |
+---------+------------+-------------+----------+
| 1 | 101 | 2020-01-05 | New |
| 5 | 101 | 2020-01-25 | FollowUp |
| 6 | 101 | 2020-02-12 | New |
| 7 | 101 | 2020-02-20 | FollowUp |
| 8 | 101 | 2020-03-30 | New |
| 9 | 303 | 2020-01-28 | New |
| 10 | 303 | 2020-02-02 | FollowUp |
| 2 | 505 | 2020-01-06 | New |
| 3 | 505 | 2020-01-10 | FollowUp |
| 4 | 505 | 2020-01-20 | FollowUp |
+---------+------------+-------------+----------+작동 방식 :
- f-시작점 확보 (앵커-모든 환자 ID 당)
- rec-recursibe 부분, 현재 값과 이전의 차이가> 30 인 경우 PatientId와 관련하여 범주와 시작점을 변경합니다.
- 메인-디스플레이 결과 정렬
비슷한 수업 :
Oracle의 조건부 SUM- 윈도우 함수 캡핑
특정 조건이 충족 될 때까지 총계 실행 -기발한 업데이트
추가
프로덕션에서이 코드를 사용하지 마십시오!
그러나 cte를 사용하는 것 외에 언급 할 가치가있는 또 다른 옵션은 임시 테이블을 사용하고 “라운드”로 업데이트하는 것입니다
“단일”라운드에서 수행 할 수 있습니다 (기발한 업데이트).
CREATE TABLE Appt_temp (ApptID INT , PatientID INT, ApptDate DATE, Category NVARCHAR(10))
INSERT INTO Appt_temp(ApptId, PatientId, ApptDate)
SELECT ApptId, PatientId, ApptDate
FROM Appt1;
CREATE CLUSTERED INDEX Idx_appt ON Appt_temp(PatientID, ApptDate);질문:
DECLARE @PatientId INT = 0,
@PrevPatientId INT,
@FirstApptDate DATE = NULL;
UPDATE Appt_temp
SET @PrevPatientId = @PatientId
,@PatientId = PatientID
,@FirstApptDate = CASE WHEN @PrevPatientId <> @PatientId THEN ApptDate
WHEN DATEDIFF(DAY, @FirstApptDate, ApptDate)>30 THEN ApptDate
ELSE @FirstApptDate
END
,Category = CASE WHEN @PrevPatientId <> @PatientId THEN 'New'
WHEN @FirstApptDate = ApptDate THEN 'New'
ELSE 'FollowUp'
END
FROM Appt_temp WITH(INDEX(Idx_appt))
OPTION (MAXDOP 1);
SELECT * FROM Appt_temp ORDER BY PatientId, ApptDate;답변
재귀 cte 로이 작업을 수행 할 수 있습니다. 먼저 각 환자 내에서 apptDate로 주문해야합니다. 그것은 방아쇠 cte에 의해 달성 될 수 있습니다.
그런 다음 재귀 cte의 앵커 부분에서 각 환자의 첫 번째 주문을 선택하고 상태를 ‘신규’로 표시하고 apptDate를 가장 최근의 ‘신규’레코드의 날짜로 표시하십시오.
재귀 cte의 재귀 부분에서 다음 약속까지 증가하고 현재 약속과 가장 최근의 ‘새’약속 날짜 사이의 일 차이를 계산하십시오. 30 일을 초과하면 ‘새로 표시’하고 가장 최근의 새로운 약속 날짜를 재설정하십시오. 그렇지 않으면 ‘추적’으로 표시하고 새 약속 날짜 이후의 기존 날짜를 따르십시오.
마지막으로 기본 쿼리에서 원하는 열을 선택하십시오.
with orderings as (
select *,
rn = row_number() over(
partition by patientId
order by apptDate
)
from #appt1 a
),
markings as (
select apptId,
patientId,
apptDate,
rn,
type = convert(varchar(10),'new'),
dateOfNew = apptDate
from orderings
where rn = 1
union all
select o.apptId, o.patientId, o.apptDate, o.rn,
type = convert(varchar(10),iif(ap.daysSinceNew > 30, 'new', 'follow up')),
dateOfNew = iif(ap.daysSinceNew > 30, o.apptDate, m.dateOfNew)
from markings m
join orderings o
on m.patientId = o.patientId
and m.rn + 1 = o.rn
cross apply (select daysSinceNew = datediff(day, m.dateOfNew, o.apptDate)) ap
)
select apptId, patientId, apptDate, type
from markings
order by patientId, rn;Abhijeet Khandagale의 답변이 간단한 쿼리로 요구 사항을 충족시키는 것처럼 보였기 때문에이 답변을 처음에 삭제했다고 언급해야합니다. 그러나 귀하의 비즈니스 요구 사항 및 추가 된 샘플 데이터에 대한 귀하의 의견과 함께, 나는 이것이 귀하의 요구를 충족한다고 믿기 때문에 삭제하지 않았습니다.
답변
그것이 정확히 구현했는지 확실하지 않습니다. 그러나 cte를 사용하는 것 외에 언급 할 가치가있는 또 다른 옵션은 임시 테이블을 사용하고 “라운드”로 업데이트하는 것입니다. 따라서 모든 상태가 올바르게 설정되지 않고 임시 테이블을 업데이트하고 반복적 인 방식으로 결과를 작성합니다. 단순히 로컬 변수를 사용하여 반복 횟수를 제어 할 수 있습니다.
따라서 각 반복을 두 단계로 나눕니다.
- 새 레코드에 가까운 모든 후속 조치 값을 설정하십시오. 올바른 필터를 사용하는 것은 매우 쉽습니다.
- 상태가 설정되지 않은 나머지 레코드의 경우 동일한 PatientID를 가진 그룹에서 먼저 선택할 수 있습니다. 그리고 그들은 첫 단계에서 처리되지 않았기 때문에 새로운 것이라고 말합니다.
그래서
CREATE TABLE #Appt2 (ApptID INT, PatientID INT, ApptDate DATE, AppStatus nvarchar(100))
select * from #Appt1
insert into #Appt2 (ApptID, PatientID, ApptDate, AppStatus)
select a1.ApptID, a1.PatientID, a1.ApptDate, null from #Appt1 a1
declare @limit int = 0;
while (exists(select * from #Appt2 where AppStatus IS NULL) and @limit < 1000)
begin
set @limit = @limit+1;
update a2
set
a2.AppStatus = IIF(exists(
select *
from #Appt2 a
where
0 > DATEDIFF(day, a2.ApptDate, a.ApptDate)
and DATEDIFF(day, a2.ApptDate, a.ApptDate) > -30
and a.ApptID != a2.ApptID
and a.PatientID = a2.PatientID
and a.AppStatus = 'New'
), 'Followup', a2.AppStatus)
from #Appt2 a2
--select * from #Appt2
update a2
set a2.AppStatus = 'New'
from #Appt2 a2 join (select a.*, ROW_NUMBER() over (Partition By PatientId order by ApptId) rn from (select * from #Appt2 where AppStatus IS NULL) a) ar
on a2.ApptID = ar.ApptID
and ar.rn = 1
--select * from #Appt2
end
select * from #Appt2 order by PatientID, ApptDate
drop table #Appt1
drop table #Appt2최신 정보. Lukasz가 제공 한 의견을 읽으십시오. 훨씬 더 똑똑합니다. 나는 대답을 아이디어로 남겨 둡니다.
답변
재귀 공통 표현은 루프를 피하는 쿼리를 최적화하는 좋은 방법이라고 생각하지만 경우에 따라 성능이 저하 될 수 있으므로 가능하면 피해야합니다.
아래 코드를 사용하여 문제를 해결하고 더 많은 가치가 있는지 테스트하지만 실제 데이터로 테스트하는 것이 좋습니다.
WITH DataSource AS
(
SELECT *
,CEILING(DATEDIFF(DAY, MIN([ApptDate]) OVER (PARTITION BY [PatientID]), [ApptDate]) * 1.0 / 30 + 0.000001) AS [GroupID]
FROM #Appt1
)
SELECT *
,IIF(ROW_NUMBER() OVER (PARTITION BY [PatientID], [GroupID] ORDER BY [ApptDate]) = 1, 'New', 'Followup')
FROM DataSource
ORDER BY [PatientID]
,[ApptDate];
아이디어는 매우 간단합니다. 그룹별로 레코드를 분리하고 싶습니다 (30 일). 가장 작은 레코드 그룹 new은 다른 그룹 follow ups입니다. 명세서가 어떻게 작성되는지 확인하십시오.
SELECT *
,DATEDIFF(DAY, MIN([ApptDate]) OVER (PARTITION BY [PatientID]), [ApptDate])
,DATEDIFF(DAY, MIN([ApptDate]) OVER (PARTITION BY [PatientID]), [ApptDate]) * 1.0 / 30
,CEILING(DATEDIFF(DAY, MIN([ApptDate]) OVER (PARTITION BY [PatientID]), [ApptDate]) * 1.0 / 30 + 0.000001)
FROM #Appt1
ORDER BY [PatientID]
,[ApptDate];
그래서:
- 먼저, 우리는 각 그룹에 대해 첫 번째 날짜를 얻고 현재 날짜와의 일 차이를 계산합니다.
- 그런 다음 그룹을 만들고 싶습니다-
* 1.0 / 30추가 - 30 일, 60 일, 90 일 등 우리는 정수를 받고 새로운 기간을 시작하고 싶었습니다
+ 0.000001. 또한 천장 기능을 사용하여smallest integer greater than, or equal to, the specified numeric expression
그게 다야. 이러한 그룹이 있으면 우리는 단순히 ROW_NUMBER시작 날짜를 찾고 new나머지를로 남겨두고 사용합니다 follow ups.
답변
모든 사람과 IMHO와 관련하여
There is not much difference between While LOOP and Recursive CTE in terms of RBAR사용할 때 많은 성능 향상이 없습니다 Recursive CTE및 Window Partition function모든 일인치
Appid해야 할 int identity(1,1), 또는 계속 증가해야한다 clustered index.
다른 이점과는 별도로 APPDate해당 환자의 모든 연속 행 이 더 커야합니다.
이렇게하면 APPDate에>, <와 같은 연산자를 APPID배치하는 것보다 효율적인 쿼리에서 쉽게 재생할 수 있습니다 inequality. inequalityAPPID에>, <와 같은 연산자를 넣으면 Sql Optimizer에 도움이됩니다.
또한 테이블에 두 개의 날짜 열이 있어야합니다.
APPDateTime datetime2(0) not null,
Appdate date not null이것들은 가장 중요한 테이블에서 가장 중요한 열이므로 변환하지 마십시오.
그래서 Non clustered indexAppdate 만들 수 있습니다
Create NonClustered index ix_PID_AppDate_App on APP (patientid,APPDate) include(other column which is not i predicate except APPID)다른 샘플 데이터로 스크립트를 테스트하고 lemme은 작동하지 않는 샘플 데이터를 알고 있습니다. 작동하지 않더라도 스크립트 논리 자체에서 수정할 수 있다고 확신합니다.
CREATE TABLE #Appt1 (ApptID INT, PatientID INT, ApptDate DATE)
INSERT INTO #Appt1
SELECT 1,101,'2020-01-05' UNION ALL
SELECT 2,505,'2020-01-06' UNION ALL
SELECT 3,505,'2020-01-10' UNION ALL
SELECT 4,505,'2020-01-20' UNION ALL
SELECT 5,101,'2020-01-25' UNION ALL
SELECT 6,101,'2020-02-12' UNION ALL
SELECT 7,101,'2020-02-20' UNION ALL
SELECT 8,101,'2020-03-30' UNION ALL
SELECT 9,303,'2020-01-28' UNION ALL
SELECT 10,303,'2020-02-02'
;With CTE as
(
select a1.* ,a2.ApptDate as NewApptDate
from #Appt1 a1
outer apply(select top 1 a2.ApptID ,a2.ApptDate
from #Appt1 A2
where a1.PatientID=a2.PatientID and a1.ApptID>a2.ApptID
and DATEDIFF(day,a2.ApptDate, a1.ApptDate)>30
order by a2.ApptID desc )A2
)
,CTE1 as
(
select a1.*, a2.ApptDate as FollowApptDate
from CTE A1
outer apply(select top 1 a2.ApptID ,a2.ApptDate
from #Appt1 A2
where a1.PatientID=a2.PatientID and a1.ApptID>a2.ApptID
and DATEDIFF(day,a2.ApptDate, a1.ApptDate)<=30
order by a2.ApptID desc )A2
)
select *
,case when FollowApptDate is null then 'New'
when NewApptDate is not null and FollowApptDate is not null
and DATEDIFF(day,NewApptDate, FollowApptDate)<=30 then 'New'
else 'Followup' end
as Category
from cte1 a1
order by a1.PatientID
drop table #Appt1답변
질문에 명확하게 설명되어 있지는 않지만 약속 날짜를 단순히 30 일 그룹으로 분류 할 수는 없다는 것을 쉽게 알 수 있습니다. 사업 적으로 이해가되지 않습니다. 그리고 appt id도 사용할 수 없습니다. 오늘 새로운 약속을 잡을 수 있습니다2020-09-06. 이 문제를 해결하는 방법은 다음과 같습니다. 먼저 첫 번째 약속을 얻은 다음 각 약속과 첫 번째 appt 간의 날짜 차이를 계산하십시오. 0이면 ‘New’로 설정하십시오. <= 30 ‘사후 관리’인 경우. > 30 인 경우 ‘미정’으로 설정하고 ‘미정’이 더 이상 없을 때까지 다음 라운드 점검을 수행하십시오. 이를 위해서는 실제로 while 루프가 필요하지만 각 약속 날짜를 반복하지는 않지만 몇 가지 데이터 세트 만 반복합니다. 실행 계획을 확인했습니다. 행이 10 개 뿐이지 만 쿼리 비용은 재귀 CTE를 사용하는 것보다 훨씬 저렴하지만 Lukasz Szozda의 부록 방법만큼 저렴하지는 않습니다.
IF OBJECT_ID('tempdb..#TEMPTABLE') IS NOT NULL DROP TABLE #TEMPTABLE
SELECT ApptID, PatientID, ApptDate
,CASE WHEN (DATEDIFF(DAY, MIN(ApptDate) OVER (PARTITION BY PatientID), ApptDate) = 0) THEN 'New'
WHEN (DATEDIFF(DAY, MIN(ApptDate) OVER (PARTITION BY PatientID), ApptDate) <= 30) THEN 'Followup'
ELSE 'Undecided' END AS Category
INTO #TEMPTABLE
FROM #Appt1
WHILE EXISTS(SELECT TOP 1 * FROM #TEMPTABLE WHERE Category = 'Undecided') BEGIN
;WITH CTE AS (
SELECT ApptID, PatientID, ApptDate
,CASE WHEN (DATEDIFF(DAY, MIN(ApptDate) OVER (PARTITION BY PatientID), ApptDate) = 0) THEN 'New'
WHEN (DATEDIFF(DAY, MIN(ApptDate) OVER (PARTITION BY PatientID), ApptDate) <= 30) THEN 'Followup'
ELSE 'Undecided' END AS Category
FROM #TEMPTABLE
WHERE Category = 'Undecided'
)
UPDATE #TEMPTABLE
SET Category = CTE.Category
FROM #TEMPTABLE t
LEFT JOIN CTE ON CTE.ApptID = t.ApptID
WHERE t.Category = 'Undecided'
END
SELECT ApptID, PatientID, ApptDate, Category
FROM #TEMPTABLE답변
이것이 도움이되기를 바랍니다.
WITH CTE AS
(
SELECT #Appt1.*, RowNum = ROW_NUMBER() OVER (PARTITION BY PatientID ORDER BY ApptDate, ApptID) FROM #Appt1
)
SELECT A.ApptID , A.PatientID , A.ApptDate ,
Expected_Category = CASE WHEN (DATEDIFF(MONTH, B.ApptDate, A.ApptDate) > 0) THEN 'New'
WHEN (DATEDIFF(DAY, B.ApptDate, A.ApptDate) <= 30) then 'Followup'
ELSE 'New' END
FROM CTE A
LEFT OUTER JOIN CTE B on A.PatientID = B.PatientID
AND A.rownum = B.rownum + 1
ORDER BY A.PatientID, A.ApptDate