我正在查找发生在另一个日期/时间值24小时内的第一个日期/时间值。
遇到表:
ENC_ID ADMSN_TIME DISCH_TIME
16755387 03/19/13 11:10:00 03/22/13 12:55:00
PROBLEM_LIST表:
ENC_ID REVIEWED_TIME USER_ID
16755387 03/19/13 13:02:00 FOOBAR
16755387 03/19/13 13:03:00 FOOBAR
16755387 03/20/13 07:52:00 FOOBAR
16755387 03/22/13 08:35:00 FOOBAR
16755387 03/22/13 08:35:00 FOOBAR
16755387 03/22/13 08:35:00 FOOBAR
期望结果集:
ENC_ID ADMSN_TIME ADMSN_REVIEW_TIME ADMSN_REVIEWER DISCH_REVIEW_TIME DISCH_REVIEWER
16755387 03/19/13 11:10:00 03/19/13 13:02:00 FOOBAR 03/22/13 08:35:00 FOOBAR
规则:
- ABS(REVIEWED_TIME - ADMSN_TIME)<=1
- ABS(REVIEWED_TIME - DISCH_TIME)<=1
- 可能没有与入院或出院或两者相对应的审核时间
我的第一次尝试:
SELECT E.ENC_ID,
E.ADMSN_TIME,
R0.REVIEWED_TIME ADMSN_REVIEWED_TIME,
R0.USER_ID ADMSN_REVIEWER,
E.DISCH_TIME,
R1.REVIEWED_TIME DISCH_REVIEWED_TIME,
R1.USER_ID DISCH_REVIEWER
FROM ENCOUNTER E
INNER JOIN PROBLEM_LIST R0 ON HE.ENC_ID=R0.ENC_ID
and r0.REVIEWED_TIME = (
SELECT MIN(REVIEWED_TIME)
FROM PROBLEM_LIST
WHERE ENC_ID=E.ENC_ID
AND ABS(REVIEWED_TIME - ADMSN_TIME)<=1
)
INNER JOIN PROBLEM_LIST R1 ON E.ENC_ID=R1.ENC_ID
and r1.REVIEWED_TIME = (
SELECT MIN(REVIEWED_TIME)
FROM PROBLEM_LIST
WHERE ENC_ID=E.ENC_ID
AND ABS(REVIEWED_TIME - DISCH_TIME)<=1
)
问题:
- 不允许遗漏审查(规则#3);使用'OUTER JOIN'会产生语法错误
- 不能消除重复的PROBLEM_LIST记录
有办法让这个工作吗?
** edit **
SELECT DISTINCT ENC_ID,
E.ADMSN_TIME,
FIRST_VALUE(R0.REVIEWED_TIME) OVER (PARTITION BY R0.ENC_ID ORDER BY R0.REVIEWED_TIME) ADMSN_REVIEWED_TIME,
FIRST_VALUE(R0.USER_ID) OVER (PARTITION BY R0.ENC_ID ORDER BY R0.REVIEWED_TIME) ADMSN_REVIEWED_BY,
E.DISCH_TIME,
FIRST_VALUE(R1.REVIEWED_TIME) OVER (PARTITION BY R1.ENC_ID ORDER BY R1.REVIEWED_TIME) DISCH_REVIEWED_TIME
,FIRST_VALUE(R1.USER_ID) OVER (PARTITION BY R1.ENC_ID ORDER BY R1.REVIEWED_TIME) DISCH_REVIEWED_BY
FROM ENCOUNTER E
LEFT JOIN PROBLEM_LIST R0 ON E.ENC_ID=R0.ENC_ID
AND ABS(r0.REVIEWED_TIME - ADMSN_TIME)<=1
LEFT OUTER JOIN PROBLEM_LIST R1 ON E.ENC_ID=R1.ENC_ID
AND ABS(R1.REVIEWED_TIME - DISCH_TIME)<=1
这种方法可以正常工作，但是DISTINCT子句似乎不是最优的。是否有更有效的查询?
我提出了下面的，它的几个测试工作。使用LEAD分析功能和UNION。
SELECT enc_id,
admsn_time,
CASE
WHEN TO_CHAR (admsn_time, 'DDMMYYYY') =
TO_CHAR (admsn_review_time, 'DDMMYYYY')
THEN
admsn_review_time
ELSE
NULL
END
AS admsn_review_time,
CASE
WHEN TO_CHAR (admsn_time, 'DDMMYYYY') =
TO_CHAR (admsn_review_time, 'DDMMYYYY')
THEN
admsn_user
ELSE
NULL
END
AS admsn_review_user,
disch_time,
CASE
WHEN TO_CHAR (disch_time, 'DDMMYYYY') =
TO_CHAR (disch_review_time, 'DDMMYYYY')
THEN
disch_review_time
ELSE
NULL
END
AS disch_review_time,
CASE
WHEN TO_CHAR (disch_time, 'DDMMYYYY') =
TO_CHAR (disch_review_time, 'DDMMYYYY')
THEN
disch_user
ELSE
NULL
END
AS disch_review_user
FROM (SELECT enc_id,
admsn_time,
LEAD (admsn_time, 1)
OVER (PARTITION BY enc_id ORDER BY admsn_time)
admsn_review_time,
LEAD (user_id, 1)
OVER (PARTITION BY enc_id ORDER BY admsn_time)
admsn_user,
disch_time,
LEAD (disch_time, 1)
OVER (PARTITION BY enc_id ORDER BY disch_time)
disch_review_time,
LEAD (user_id, 1)
OVER (PARTITION BY enc_id ORDER BY disch_time)
disch_user,
ch
FROM (SELECT e.enc_id,
admsn_time,
disch_time,
'E' AS ch,
NULL AS user_id
FROM encounter e
UNION
SELECT enc_id,
reviewed_time,
reviewed_time,
'P',
user_id
FROM problem_list pl))
WHERE ch = 'E';
不确定这是否比第二个解决方案更有效，但它避免了distinct和两个连接到problem_list。仍然感觉过于复杂-在unpivot之后做pivot似乎是一个hack -但这部分是为了我自己的娱乐:
select enc_id, admsn_time, admsn_review_time, admsn_reviewer,
disch_time, disch_review_time, disch_reviewer
from (
select enc_id, admsn_time, disch_time, user_id, reviewed_time, review_type
from (
select *
from (
select e.enc_id, e.admsn_time, e.disch_time,
pl.user_id, pl.reviewed_time,
row_number() over (partition by e.enc_id
order by abs(pl.reviewed_time - e.admsn_time))
as admsn_reviewed_rn,
row_number() over (partition by e.enc_id
order by abs(pl.reviewed_time - e.disch_time))
as disch_reviewed_rn
from encounter e
left join problem_list pl on pl.enc_id = e.enc_id
)
unpivot (rn for review_type in (admsn_reviewed_rn as 'ADMSN',
disch_reviewed_rn as 'DISCH'))
)
where rn = 1
and (abs(reviewed_time - admsn_time) <= 1
or abs(reviewed_time - disch_time) <= 1)
)
pivot (min(reviewed_time) as review_time, min(user_id) as reviewer
for review_type in ('ADMSN' as admsn, 'DISCH' as disch))
/
:
ENC_ID ADMSN_TIME ADMSN_REVIEW_TIME ADMSN_ DISCH_TIME DISCH_REVIEW_TIME DISCH_
---------- ------------------- ------------------- ------ ------------------- ------------------- ------
16755387 03/19/0013 11:10:00 03/19/0013 13:02:00 FOOBAR 03/22/0013 12:55:00 03/22/0013 08:35:00 FOOBAR
你的基本问题是如何找到第一次之后的时间T受约束。从这里开始，
SELECT E.ENC_ID,
E.ADMSN_TIME,
min(REVIEWED_TIME) as ADMSN_REVIEWED_TIME,
FROM ENCOUNTER as E
JOIN PROBLEM_LIST as P
ON E.ENC_ID = P.ENC_ID
AND ADMSN_TIME < REVIEWED_TIME
AND REVIEWED_TIME <= ADMSN_TIME + 1
GROUP BY E.ENC_ID, E.ADMSN_TIME
把它和你需要的任何东西连接起来。