Как преобразовать простой логический оператор в SQL?

Для меня простым (хотя и уродливым) решением является использование предложений EXISTS и NOT EXISTS:

SELECT *
FROM PERSON_T INNER JOIN DISEASE_T
     USING (PERSON_ID)
WHERE DISEASE_ID = 52
  AND EXISTS (SELECT 1 FROM DRUG_T
              WHERE DRUG_T.PERSON_ID = PERSON_T.PERSON_ID
                AND DRUG_ID = 474
                AND [time condition])
  AND NOT EXISTS (SELECT 1 FROM DRUG_T
              WHERE DRUG_T.PERSON_ID = PERSON_T.PERSON_ID
                AND DRUG_ID = 234
                AND [time condition])

...и так далее. В этом примере мы спрашиваем людей, которые принимали наркотик 474, а не 234. Очевидно, вы можете сгруппировать предложения с И и ИЛИ в соответствии с тем, что вам нужно.

В сторону: мне трудно читать все заглавные буквы. Я обычно использую прописные буквы для ключевых слов SQL и строчные буквы для имен таблиц и столбцов.

Я понятия не имею, как это будет работать с большими таблицами (я полагаю, что это будет довольно паршиво, поскольку сравнение дат, как правило, довольно дорого), но вот метод, который должен работать. Он относительно многословен, но его очень легко модифицировать для различных логических случаев.

Пример 1:

SELECT dis.*
FROM disease_t dis
LEFT JOIN drug d1 ON d1.person_id = dis.person_id AND d1.drug_id = 234
LEFT JOIN drug d2 ON d2.person_id = dis.person_id AND d2.drug_id = 474
LEFT JOIN drug d3 ON d3.person_id = dis.person_id AND d3.drug_id = 26
LEFT JOIN drug d4 ON d4.person_id = dis.person_id AND d4.drug_id = 395
LEFT JOIN drug d5 ON d5.person_id = dis.person_id AND d5.drug_id = 791
LEFT JOIN drug d6 ON d6.person_id = dis.person_id AND d6.drug_id = 371
WHERE dis.disease_id = 52
AND (((d1.person_id IS NULL OR dis.startdate < d1.startdate) AND
      (d2.person_id IS NOT NULL AND d2.startdate < dis.startdate) AND
      (d3.person_id IS NULL OR dis.startdate < d3.startdate)) 
     OR
     ((d4.person_id IS NULL OR dis.startdate < d4.startdate) AND
      (d5.person_id IS NULL OR dis.startdate < d5.startdate) AND
      (d6.person_id IS NOT NULL AND d6.startdate < dis.startdate)))

Пример 2:

SELECT dis.*
FROM disease_t dis
LEFT JOIN drug d1 ON d1.person_id = dis.person_id AND d1.drug_id = 234
LEFT JOIN drug d2 ON d2.person_id = dis.person_id AND d2.drug_id = 474
LEFT JOIN drug d3 ON d3.person_id = dis.person_id AND d3.drug_id = 26
LEFT JOIN drug d4 ON d4.person_id = dis.person_id AND d4.drug_id = 395
LEFT JOIN drug d5 ON d5.person_id = dis.person_id AND d5.drug_id = 791
LEFT JOIN drug d6 ON d6.person_id = dis.person_id AND d6.drug_id = 371
WHERE dis.disease_id = 52
AND (((d1.person_id IS NOT NULL AND d1.startdate < dis.startdate) AND
      (d2.person_id IS NOT NULL AND d2.startdate < dis.startdate) AND
      (d3.person_id IS NULL OR dis.startdate < d3.startdate)) 
     or
     ((d4.person_id IS NULL OR dis.startdate < d4.startdate) AND
      (d5.person_id IS NULL OR dis.startdate < d5.startdate) AND
      (d6.person_id IS NOT NULL AND d6.startdate < dis.startdate)))

Вот запрос, который обрабатывает ( (Drug 234 = true AND Drug 474 = true AND Drug 26 = false) OR (Drug 395 = false AND Drug 791 = false AND Drug 371 = true) ), как вы написали.

/*
-- AS DEFINED BY JOINS
-- All "person_id"'s match
-- Drug 1 is not Drug 2
-- Drug 1 is not Drug 3
-- Drug 2 is not Drug 3
-- All Drugs are optional as far as the SELECT statement is concerned (left join)
   -- Drug IDs will be defined in the WHERE clause
-- All Diseases for "person_id"

-- AS DEFINED IN WHERE STATEMENT
-- Disease IS 52
-- AND ONE OF THE FOLLOWING:
--   1) Disease started AFTER Drug 1
--      Disease started AFTER Drug 2
--      Drug 1 IS 234
--      Drug 2 IS 474
--      Drug 3 IS NOT 26 (AND NOT 234 or 474, as defined in JOINs)
--   2) Disease started AFTER Drug 3
--      Drug 1 IS NOT 395
--      Drug 2 IS NOT 791
--      Drug 3 IS 371
*/

SELECT p.person_id, p.gender FROM person_t as p
LEFT JOIN drug_t    AS dr1 ON (p.person_id = dr1.person_id)
LEFT JOIN drug_t    AS dr2 ON (p.person_id = dr2.person_id AND dr1.drug_id != dr2.drug_id)
LEFT JOIN drug_t    AS dr3 ON (p.person_id = dr3.person_id AND dr1.drug_id != dr3.drug_id AND dr2.drug_id != dr3.drug_id)
JOIN      disease_t AS ds  ON (p.person_id = ds.person_id)
WHERE ds.disease_id = 52
AND (   (    (dr1.drug_start_date < ds.disease_start_date AND dr2.drug_start_date < ds.disease_start_date)
        AND (dr1.drug_id = 234 AND dr2.drug_id = 474 AND dr3.drug_id != 26)
        )
    OR
        (    (dr3.drug_start_date < ds.disease_start_date)
        AND (dr1.drug_id != 395 AND dr2.drug_id != 791 AND dr3.drug_id = 371)
        )
    )

Прошу прощения за любые ошибки, но я думаю, что что-то вроде этого будет работать (в T-SQL):

SELECT col1, col2, col3...
FROM PERSON_T AS P, DRUG_T AS DR, DISEASE_T AS DI
WHERE disease_id = 52
AND P.person_id = DI.person_id
AND P.person_id = DR.person_id
AND drug_id NOT IN(234, 26)
AND drug_id = 474
AND disease_start_date < drug_start_date
UNION
SELECT col1, col2, col3...
FROM PERSON_T AS P, DRUG_T AS DR, DISEASE_T AS DI
WHERE disease_id = 52
AND P.person_id = DI.person_id
AND P.person_id = DR.person_id
AND drug_id NOT IN(791, 395)
AND drug_id = 371
AND disease_start_date < drug_start_date

Теперь это не нужно делать с помощью UNION, но для удобства чтения я подумал, что это было проще всего, учитывая ваши условия. Возможно, это приведет вас в правильном направлении.

SELECT per.person_id, per.name, per.gender
FROM person_t per
INNER JOIN disease_t dis
USING (person_id)
INNER JOIN drug_t drug
USING (person_id)
WHERE dis.disease_id = 52 AND drug.drug_start_date < dis.disease_start_date AND ((drug.drug_id IN (234, 474) AND drug.drug_id NOT IN (26)) OR (drug.drug_id IN (371) AND drug.drug_id NOT IN (395, 791)));

Это сделает то, о чем вы просите. Заявления IN в конце говорят сами за себя.

У меня нет под рукой тестовых данных, чтобы попробовать это, но я думаю, вы могли бы сделать что-то вроде:

SELECT *
FROM DISEASE_T D
INNER JOIN DRUG_T DR ON D.PERSON_ID = DR.PERSON_ID AND D.DRUG_ID=52
INNER JOIN PERSON_T P ON P.PERSON_ID = D.PERSON_ID
GROUP BY PERSON_ID
HAVING SUM(
    CASE WHEN DRUG_ID=234 AND DRUG_START_DATE<DISEASE_START_DATE THEN -1 
    WHEN DRUG_ID=474 AND DRUG_START_DATE<DISEASE_START_DATE THEN 1 
    WHEN DRUG_ID=26 AND DRUG_START_DATE<DISEASE_START_DATE THEN -1 
    ELSE 0 END) = 1
    OR
    SUM(
    CASE WHEN DRUG_ID=395 AND DRUG_START_DATE<DISEASE_START_DATE THEN -1 
    WHEN DRUG_ID=791 AND DRUG_START_DATE<DISEASE_START_DATE THEN -1 
    WHEN DRUG_ID=371 AND DRUG_START_DATE<DISEASE_START_DATE THEN 1 
    ELSE 0 END) = 1

Случай, который, я знаю, потерпит неудачу, если у вас есть несколько записей для одного и того же человека и одного и того же лекарства/заболевания в таблицах лекарств/заболеваний. Если это так, вы также можете изменить предложение HAVING, чтобы оно выглядело примерно так:

(SUM(CASE WHEN DRUG_ID=234 AND DRUG_START_DATE<DISEASE_START_DATE THEN 1 ELSE 0 END) = 0
AND SUM(CASE WHEN DRUG_ID=474 AND DRUG_START_DATE<DISEASE_START_DATE THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN DRUG_ID=26 AND DRUG_START_DATE<DISEASE_START_DATE THEN 1 ELSE 0 END) = 0)
OR
(SUM(CASE WHEN DRUG_ID=395 AND DRUG_START_DATE<DISEASE_START_DATE THEN 1 ELSE 0 END) = 0
AND SUM(CASE WHEN DRUG_ID=791 AND DRUG_START_DATE<DISEASE_START_DATE THEN 1 ELSE 0 END) = 0
AND SUM(CASE WHEN DRUG_ID=371 AND DRUG_START_DATE<DISEASE_START_DATE THEN 1 ELSE 0 END) > 0)

Я бы, вероятно, подошел к этой проблеме с какого-то направления, похожего на это. Это довольно гибко.

DRUG_DISEASE_CORRELATION_QUERY
===============================
DRUG_DISEASE_CORRELATION_QUERY_ID
DISEASE_ID
DESCRIPTION

(1, 52, 'What this query does.')
(2, 52, 'Add some more results.')

DRUG_DISEASE_CORRELATION_QUERY_INCLUDE_DRUG
===========================================
DRUG_DISEASE_CORRELATION_QUERY_ID
DRUG_ID

(1, 234)
(1, 474)
(2, 371)

DRUG_DISEASE_CORRELATION_QUERY_EXCLUDE_DRUG
===========================================
DRUG_DISEASE_CORRELATION_QUERY_ID
DRUG_ID

(1, 26)
(2, 395)
(2, 791)



CREATE VIEW DRUG_DISEASE_CORRELATION
AS
SELECT 
    p.*,
    q.DRUG_DISEASE_CORRELATION_QUERY_ID
FROM 
    DRUG_DISEASE_CORRELATION_QUERY q
    INNER JOIN DISEASE_T ds on ds.DISEASE_ID = q.DISEASE_ID
    INNER JOIN PERSON_T p ON p.PERSON_ID = ds.PERSON_ID
  WHERE 
    AND EXISTS (SELECT * FROM DRUG_T dr WHERE dr.PERSON_ID = p.PERSON_ID AND dr.DRUG_ID IN
        (SELECT qid.DRUG_ID FROM DRUG_DISEASE_CORRELATION_QUERY_INCLUDE_DRUG qid WHERE 
        qid.DRUG_DISEASE_CORRELATION_QUERY_ID = q.DRUG_DISEASE_CORRELATION_QUERY_ID)
        AND DRUG_START_DATE < ds.DISEASE_START_DATE)
   AND NOT EXISTS (SELECT * FROM DRUG_T dr WHERE dr.PERSON_ID = p.PERSON_ID AND dr.DRUG_ID IN
        (SELECT qed.DRUG_ID FROM DRUG_DISEASE_CORRELATION_QUERY_EXCLUDE_DRUG qed WHERE 
        qed.DRUG_DISEASE_CORRELATION_QUERY_ID = q.DRUG_DISEASE_CORRELATION_QUERY_ID)
        AND DRUG_START_DATE < ds.DISEASE_START_DATE)
GO


SELECT * FROM DRUG_DISEASE_CORRELATION WHERE DRUG_DISEASE_CORRELATION_QUERY_ID = 1
UNION
SELECT * FROM DRUG_DISEASE_CORRELATION WHERE DRUG_DISEASE_CORRELATION_QUERY_ID = 2

Если я правильно понял, вы хотите:

• Выберите из этих людей
• Кто был инфицирован одним (1) конкретным заболеванием
• Кто лечился одним или несколькими указанными препаратами
• И которые НЕ получали лечение одним или несколькими указанными другими препаратами

Это можно упростить, преобразовав ваши «требования к лекарствам» во временную таблицу той или иной формы. Это позволило бы использовать любое количество «хороших» и «плохих» наркотиков для проверки. То, что у меня есть ниже, может быть реализовано как хранимая процедура, но если это не вариант, доступен ряд запутанных вариантов.

Разбивка шагов:

Во-первых, вот как выбираются нужные пациенты. Мы будем использовать это как подзапрос позже:

SELECT [PersonData]
 from DISEASE_T di
  inner join PERSON_T pe
   on pe.Person_Id = di.Person_Id
 where di.Disease_Id = [TargetDisease]
  and [TimeConstraints]

Во-вторых, для каждого набора "целевых" лекарств, которые вы объединили вместе, настройте временную таблицу следующим образом (это синтаксис SQL Server, Postgres должен иметь что-то подобное):

CREATE TABLE #DrugSet
 (
   Drug_Id  [KeyDataType]
  ,Include  int   not null
 )

заполните его одной строкой для каждого препарата, который вы рассматриваете:

• Drug_Id = препарат, который вы проверяете
• Включить = 1, если человек должен принимать наркотик, и 0, если он не должен его принимать.

и вычислить два значения:

@GoodDrugs, количество лекарств, которое вы хотите, чтобы пациент принимал
@BadDrugs, количество лекарств, которое вы хотите, чтобы пациент не принимал

Теперь соедините все вышеперечисленное вместе в следующем запросе:

SELECT pe.[PersonData]  --  All the desired columns from PERSON_T and elsewhere
 from DRUG_T dr
  --  Filter to only include "persons of interest"
  inner join (select [PersonData]
               from DISEASE_T di
                inner join PERSON_T pe
                 on pe.Person_Id = di.Person_Id
               where di.Disease_Id = [TargetDisease]
                and [TimeConstraints]) pe
   on pe.Person_Id = dr.Person_ID
 --  Join with any of the drugs we are intersted in
 left outer join #DrugSet ta  
  on ta.Drug_Id = dr.Drug_Id
 group by pe.[PersonData]  --  Same as in the SELECT clause
 having sum(case ta.Include
              when 1 then 1  --  This patient has been given a drug that we're looking to match
              else 0         --  This patient has not been given this drug (catches NULLs, too)
            end) = @GoodDrugs
  and  sum(case ta.Include
              when 0 then 1  --  This patient has been given this drug that we're NOT looking to match
              else 0         --  This patient has not been given this drug (catches NULLs, too)
            end) = @BadDrugs

Я намеренно проигнорировал критерии времени, так как вы не вдавались в подробности о них, но их должно быть довольно просто добавить (хотя я надеюсь, что это не знаменитые последние слова). Возможна дальнейшая оптимизация, но многое зависит от данных и других возможных критериев.

Вам нужно будет запустить это один раз для каждого «набора лекарств» (то есть наборов ИСТИННЫХ или ЛОЖНЫХ лекарств, объединенных вместе), объединяя список при каждом проходе. Вероятно, вы могли бы расширить #DrugSet, чтобы учесть каждый набор лекарств, который вы проверяете, но я не хочу пытаться кодировать это без каких-либо серьезных данных для проверки.

*/

Ни один из приведенных ответов не работает. Опять же, вот шаблон, который я хочу реализовать: ((Лекарство 234 = истина И Лекарство 474 = правда И Лекарство 26 = ложь) ИЛИ (Лекарство 395 = ложь И Лекарство 791 = ложь И Лекарство 371 = правда))

Я считаю, что следующий запрос будет работать для (лекарство 234 = правда И лекарство 474 = правда И лекарство 26 = ложь). Учитывая это, довольно легко добавить вторую половину запроса.

SELECT  p.person_id, p.gender FROM person_t as p 
    join drug_t as dr on dr.person_id = p.person_id 
    join disease_t as ds on ds.person_id=p.person_id 
    WHERE dr.drug_start_date < ds.disease_start_date AND disease_id = 52 AND dr.drug_id=234
INTERSECT
SELECT  p.person_id, p.gender FROM person_t as p 
    join drug_t as dr on dr.person_id = p.person_id 
    join disease_t as ds on ds.person_id=p.person_id 
    WHERE dr.drug_start_date < ds.disease_start_date AND disease_id = 52 AND dr.drug_id=474
INTERSECT (
SELECT p.person_id, p.gender
    FROM person_t as p 
    JOIN disease_t as ds on ds.person_id = p.person_id 
    LEFT JOIN drug_t as dr ON dr.person_id = p.person_id  AND dr.drug_id = 26
    WHERE disease_id = 52 AND dr.person_id is null 
UNION 
SELECT p.person_id, p.gender
    FROM person_t as p 
    JOIN disease_t as ds on ds.person_id = p.person_id 
    JOIN drug_t as dr ON dr.person_id = p.person_id  AND dr.drug_id = 26
    WHERE disease_id = 52 AND dr.drug_start_date > ds.disease_start_date)

Этот запрос работает, но довольно уродлив. Я также подозреваю, что это будет очень медленно, когда у меня будет производственная база данных со 100 миллионами человек. Могу ли я что-нибудь сделать, чтобы упростить/оптимизировать этот запрос?

Я попытался сломать проблему и следовать настолько логично, насколько мог.

Во-первых, три таблицы (Person_T, Drugs_T, Disease_T) можно представить так, как показано на рис. 1.0:

Человек может иметь несколько наркотиков и несколько заболеваний. У каждого лекарства и болезни есть дата начала и дата окончания.

Поэтому я бы сначала денормализовал три таблицы в одну таблицу (Table_dn) таким образом:

dnId | PersonId | DrugId | DiseaseId | DgSt | DgEn | DiSt | DiEn
----   --------   ------   ---------   ----   ----   ----   ----

Эта денормализованная таблица может быть временной, если это необходимо, несмотря на то, что Table_dn теперь содержит весь глобальный набор данных, как показано на рис. 2.0 (обозначено как G).

Насколько я понимаю ваше описание, я вижу по существу двухслойный фильтр.

Фильтр 1

Этот фильтр представляет собой просто логический набор лекарств Комбинации, как вы уже указали в описании своего вопроса. например:

(drug a = 1 & drug b = 0 & etc) OR (.....

Фильтр 2

Этот фильтр немного сложнее первого, это критерии диапазона дат. На рис. 3.0 этот диапазон дат показан КРАСНЫМ цветом. Желтый цвет представляет даты записи, которые охватывают несколько способов:

• до КРАСНОГО периода
• После КРАСНОГО периода
• Между КРАСНЫМ периодом
• Окончание до окончания КРАСНОГО периода
• Начало после начала КРАСНОГО периода

Теперь ЖЕЛТЫЕ периоды дат могут быть периодом употребления наркотиков или периодом болезни ИЛИ их комбинацией.

Этот фильтр следует применять к набору результатов, полученных из первых результатов.

Конечно, в зависимости от вашего точного вопроса эти два фильтра могут потребоваться наоборот (например, сначала f2, затем f1).

Псевдокод SQL:

Select sub.*
From    
      (select    * 
       from      Table_dn 
       where     [Filter 1]
      ) as sub

where [Filter 2]