我一直试图从事件表中进行查询（下面我展示了一个具有预期输出的示例）。
此表有3个以上的事件类型，但我只显示相关的事件类型。相关的3种类型是：“HRa1”（代码211）、“HRa2”（代码12712）和“HRy”（代码220048）“HRa”分为“HRa1”和“HRa2”，因为它们不同于将它们插入桌子上的人，但它们不会同时发生。
我要选择以下选项：
如果这三种类型中只有一种出现：如果是“HRa1”或“HRa2”，请选择其所有“HRa1”/“HRa2”值（列值num）都在50到110之间的类型；如果是“HRy”，请选择其所有值（列值）都等于“nove”的类型。见ID=1、2、3、4和5。其他类型的事件也可以出现，这无关紧要，但只有上述3个事件中的一个出现（参见ID=3）。
如果同时出现“HRa1/“HRa2”和“HRy”，则所有“HRa1/“HRa2”条目中的“HRa1/“HRa2”（valuenum）必须介于50和110之间，“HRy”（valuenum）条目必须都等于“nove”。ID=8显示了一个不满足此条件的示例。
如果三个都没有出现，当然排除。
我怎样才能做到这一点？
drop table testevents cascade;
create table testevents(
hadm_id int not null,
subject_id int not null,
eventtype int not null,
eventlabel char(30) not null,
value char(360) not null,
valuenum int
);
insert into testevents(hadm_id, subject_id, eventtype, eventlabel, value, valuenum)
values
(1, 1, 211, 'HRa1', '59', 59),
(1, 1, 211, 'HRa1', '89', 59),
(1, 1, 211, 'HRa1', '54', 59),
(1, 1, 211, 'HRa1', '99', 59),
(1, 1, 211, 'HRa1', '69', 59),
(1, 1, 211, 'HRa1', '104', 59),
(2, 2, 211, 'HRa1', '59', 59),
(2, 2, 211, 'HRa1', '89', 89),
(2, 2, 211, 'HRa1', '54', 54),
(2, 2, 211, 'HRa1', '140', 140), -- not allowed
(2, 2, 211, 'HRa1', '69', 69),
(2, 2, 211, 'HRa1', '104', 104),
(3, 3, 12712, 'HRa2', '69', 69),
(3, 3, 12712, 'HRa2', '89', 89),
(3, 3, 12712, 'HRa2', '94', 94),
(3, 3, 12712, 'HRa2', '59', 59),
(3, 3, 12712, 'HRa2', '69', 69),
(3, 3, 12712, 'HRa2', '84', 84),
(3, 3, 353, 'RXa', 'OK', null),
(4, 4, 220048, 'HRy', 'sinus', null),
(4, 4, 220048, 'HRy', 'sinus', null),
(4, 4, 220048, 'HRy', 'sinus', null),
(4, 4, 220048, 'HRy', 'sinus', null),
(4, 4, 220048, 'HRy', 'sinus', null),
(4, 4, 220048, 'HRy', 'sinus', null),
(5, 5, 220048, 'HRy', 'sinus', null),
(5, 5, 220048, 'HRy', 'sinus', null),
(5, 5, 220048, 'HRy', 'sinus', null),
(5, 5, 220048, 'HRy', 'AF', null), -- not allowed
(5, 5, 220048, 'HRy', 'sinus', null),
(5, 5, 220048, 'HRy', 'sinus', null),
(6, 6, 220048, 'HRy', 'sinus', null),
(6, 6, 220048, 'HRy', 'sinus', null),
(6, 6, 211, 'HRa2', '94', 95),
(6, 6, 211, 'HRa2', '150', 150), -- not allowed
(6, 6, 220048, 'HRy', 'sinus', null),
(6, 6, 220048, 'HRy', 'sinus', null),
(7, 7, 220048, 'HRy', 'sinus', null),
(7, 7, 220048, 'HRy', 'sinus', null),
(7, 7, 211, 'HRa2', '94', 94),
(7, 7, 211, 'HRa2', '87', 87),
(7, 7, 220048, 'HRy', 'sinus', null),
(7, 7, 220048, 'HRy', 'sinus', null),
(7, 7, 4053, 'BP', '87', 87),
(7, 7, 503, 'RR', '72', 72),
(8, 8, 220048, 'HRy', 'sinus', null),
(8, 8, 211, 'HRa2', '94', 94),
(8, 8, 211, 'HRa2', '87', 87),
(8, 8, 220048, 'HRy', 'AF', null), -- not allowed
(8, 8, 220048, 'HRy', 'sinus', null),
(8, 8, 4053, 'BP', '87', 87),
(8, 8, 503, 'RR', '72', 72),
(9, 9, 220048, 'HRy', 'AF', null), -- not allowed
(9, 9, 211, 'HRa2', '134', 134), -- not allowed
(9, 9, 211, 'HRa2', '187', 187), -- not allowed
(9, 9, 220048, 'HRy', 'AF', null), -- not allowed
(9, 9, 220048, 'HRy', 'AF', null) -- not allowed
;
output:
hadm_id, subject_id
1 1
3 3
4 4
7 7
我尝试了以下查询，但ID=8的查询失败：
select subject_id, hadm_id
from testevents
where eventlabel in ('HRa1', 'HRa2', 'HRy')
group by subject_id, hadm_id
having ((min(valuenum) >= 50 and max(valuenum) <= 110) or
(min(value) = 'sinus' and max(value) = 'sinus'))
order by subject_id;
有人能帮我吗？
非常感谢！
最佳答案
你的情况有点复杂。我认为这符合逻辑：
select subject_id, hadm_id
from testevents
where eventlabel in ('HRa1', 'HRa2', 'HRy')
group by subject_id, hadm_id
having (min(eventlabel) = max(eventlabel) and
min(eventlabel) in ('HRa1', 'HRa2') and
min(valuenum) >= 50 and
max(valuenum) <= 110
) or
(min(eventlabel) = max(eventlabel) and
min(eventlabel) in ('HRy') and
min(value) = 'sinus' and
max(value) = 'sinus'
) or
(sum( (eventlabel in ('HRa1', 'HRa2'))::int) > 0 and
sum( (eventlabel in ('HRy'))::int) > 0 and
min(case when eventlabel in ('HRa1', 'HRa2') then valuenum end) >= 50 and
max(case when eventlabel in ('HRa1', 'HRa2') then valuenum end) <= 110 and
sum( (eventlabel in ('HRy') and value <> 'sinus')::int ) = 0
)
Here是一个SQL小提琴。