Блог

На основе текущей схемы меня попросили найти

— люди, которые не были проверены и подверглись воздействию какой-либо одной инфекции — Не перечисляйте никого дважды и не перечисляйте известных больных людей — Подвержены = в одном и том же месте и перекрываются во времени (для простоты не требуется время перекрытия)

Из приведенного ниже запроса я нахожу свой ответ, за исключением того, что я не могу удалить людей, которые являются «положительными», потому что вторая часть моего запроса, т. Е. промежуток времени, зависит от первой части, т. Е. Времени, когда положительные люди отправились в одни и те же места.

 select * from (
select DISTINCT person.PersonID, Register.LocID, Register.Checkin, Register.CheckOut
from person 
join Register on Person.PersonID = Register.PersonID 
join testing on person.PersonID  = testing.PersonID
where testing.Results is 'Positive' ) a 
join (
SELECT DISTINCT Person.PersonID, Register.LocID , Register.Checkin, Register.CheckOut
from person join Register on Person.PersonID = Register.PersonID 
where person.PersonID  
not in (SELECT DISTINCT testing.PersonID from testing)) b on a.LocID = b.LocID 
and b.checkin >= a.CheckIn and b.CheckIn <= a.CheckOut

  

Итак, мой вопрос в том, какая модификация требуется этому запросу, чтобы показать результаты результатов только второй части?

Я считаю, что первая часть должна быть

 select * from (
select DISTINCT person.PersonID, Register.LocID, Register.Checkin, Register.CheckOut
from person 
join Register on Person.PersonID = Register.PersonID 
join testing on person.PersonID  = testing.PersonID
where testing.Results is 'Positive' ) a 
  

И вторая часть, которая должна быть

 join (
SELECT DISTINCT Person.PersonID, Register.LocID , Register.Checkin, Register.CheckOut
from person join Register on Person.PersonID = Register.PersonID 
where person.PersonID  
not in (SELECT DISTINCT testing.PersonID from testing)) b on a.LocID = b.LocID 
and b.checkin >= a.CheckIn and b.CheckIn <= a.CheckOut
  

Для удобства чтения вы можете создавать CTE такие запросы:

 with
  -- returns all the untested persons
  untested as (select p.* from person p left join testing t on t.personid = p.personid where t.testingid is null), 
  --  returns all the infected persons
  infected as (select * from testing where results = 'Positive'),
  -- returns all the locids that infected persons visited and the start and dates of these visits
  loc_positive as (
    select r.locid, i.timestamp startdate, r.checkout enddate 
    from register r inner join infected i 
    on i.personid = r.personid and i.timestamp between r.checkin and r.checkout
  )
-- returns the distinct untested persons that visited the same locids with persons tested positive at the same time after they were tested 
select distinct u.*
from untested u 
inner join register r on r.personid = u.personid
inner join loc_positive lp on lp.locid = r.locid 
where lp.startdate <= r.checkout and lp.enddate >= r.checkin
  

Это сложный запрос. Поскольку вам не нужны дубликаты, я собираюсь предложить exists просто использовать внешний запрос persons .

Идея собрать людей в одном и том же месте в одно и то же время заключается в самосоединении register с использованием совпадений местоположения и времени. Я думаю, что это самая сложная часть запроса. Остальное проверяет, является ли пользователь положительным или нет:

 select p.*
from person p 
where not exists (select 1
                  from testing t
                  where t.personid = p.personId and t.results = 'positive'
                 ) and
      exists (select 1
              from register r1 join
                   register r2
                   on r1.locid = r2.locid and
                      r1.checkin < r2.checkout and
                      r2.checkout > r1.checkin join
                   testing t2
                   on r2.personid = t2.personid and
                      t2.results = 'positive' and
                      t2.timestamp < r2.checkout
              where r1.personid = p.personid
             );
  

Выбор времени немного сложен, но я думаю, что выбор времени имеет смысл. Кому-то нужно проверить положительный результат, прежде чем они окажутся в одном месте. Конечно, вы можете удалить t2.timestamp < r2.checkout , если нет ограничений по времени.

Решение этого ответа состояло в том, чтобы добавить distinct и имя столбца к звезде в первой строке.

 select DISTINCT unt.PersonID from (
select  person.PersonID, Register.LocID, Register.Checkin, Register.CheckOut
from person join Register on Person.PersonID = Register.PersonID join testing on person.PersonID  = testing.PersonID
where testing.Results is 'Positive' ) pos
join (
SELECT Person.PersonID, Register.LocID , Register.Checkin, Register.CheckOut
from person join Register on Person.PersonID = Register.PersonID where person.PersonID  
not in (SELECT  testing.PersonID from testing)) unt on pos.LocID = unt.LocID 
and unt.checkin >= pos.CheckIn and unt.CheckIn <= pos.CheckOut;